Oracle数据库存储过程是一组为了完成特定功能的PL_SQL语句集合，经编译后存储在数据库中，用户可以通过指定存储过程的名字并给出参数来执行它。存储过程可以接收输入参数、返回输出参数，也能包含复杂的业务逻辑判断、循环操作等，适合处理需要多次执行的批量数据操作、复杂业务规则校验等场景。
Oracle存储过程的核心优势
使用存储过程相比直接执行零散SQL语句有不少优势：
- 提升执行效率：存储过程在首次执行时会被编译并缓存执行计划，后续调用无需重复编译，减少解析开销。
- 减少网络传输：只需要传递存储过程名称和参数，不需要传输大量SQL语句，降低网络压力。
- 代码复用性高：封装好的逻辑可以被多个应用、多个业务模块重复调用，避免重复编写相同代码。
- 安全性更好：可以给用户授予存储过程的执行权限，而不用直接开放底层表的操作权限，降低数据误操作风险。
创建存储过程的基本语法
创建存储过程使用CREATE OR REPLACE PROCEDURE语句，基本结构如下：
-- 创建存储过程基本语法
CREATE OR REPLACE PROCEDURE 存储过程名称(
-- 输入参数，IN可以省略，默认是输入参数
参数名1 IN 参数类型,
参数名2 IN 参数类型,
-- 输出参数，需要显式声明OUT
参数名3 OUT 参数类型
) AS
-- 声明局部变量
局部变量名 变量类型;
BEGIN
-- 业务逻辑代码
-- 可以给输出参数赋值
参数名3 := 计算结果;
-- 可以执行DML操作
INSERT INTO 表名(列1,列2) VALUES(参数名1,参数名2);
COMMIT;
EXCEPTION
-- 异常处理逻辑
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END 存储过程名称;
存储过程使用示例
无参存储过程示例
下面创建一个简单的无参存储过程，实现向测试表插入一条固定数据：
-- 先创建测试表
CREATE TABLE test_proc_table(
id NUMBER PRIMARY KEY,
content VARCHAR2(50),
create_time DATE
);
-- 创建无参存储过程
CREATE OR REPLACE PROCEDURE proc_insert_test_data AS
v_max_id NUMBER;
BEGIN
-- 查询当前最大id
SELECT NVL(MAX(id),0) INTO v_max_id FROM test_proc_table;
-- 插入新数据
INSERT INTO test_proc_table(id,content,create_time)
VALUES(v_max_id + 1, '存储过程插入的数据', SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('数据插入成功，新增id为：' || (v_max_id + 1));
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('插入失败，错误原因：' || SQLERRM);
RAISE;
END proc_insert_test_data;
带输入输出参数的存储过程示例
创建一个根据部门编号查询部门员工总数的存储过程，输入部门编号，输出员工总数：
-- 假设存在员工表emp，包含deptno（部门编号）列
CREATE OR REPLACE PROCEDURE proc_get_emp_count(
p_deptno IN NUMBER, -- 输入参数：部门编号
p_emp_count OUT NUMBER -- 输出参数：员工总数
) AS
BEGIN
-- 查询指定部门的员工数量
SELECT COUNT(*) INTO p_emp_count FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('部门' || p_deptno || '的员工总数为：' || p_emp_count);
EXCEPTION
WHEN OTHERS THEN
p_emp_count := -1; -- 异常时返回-1表示查询失败
DBMS_OUTPUT.PUT_LINE('查询失败，错误原因：' || SQLERRM);
RAISE;
END proc_get_emp_count;
存储过程的调用方式
在PL_SQL块中调用
在PL_SQL开发工具中，可以直接使用匿名块调用存储过程：
-- 调用无参存储过程
BEGIN
proc_insert_test_data;
END;
/
-- 调用带参数的存储过程
DECLARE
v_count NUMBER;
BEGIN
proc_get_emp_count(10, v_count); -- 查询10号部门的员工数
IF v_count != -1 THEN
DBMS_OUTPUT.PUT_LINE('调用结果：员工数为' || v_count);
END IF;
END;
/
在JDBC中调用
Java程序通过JDBC调用存储过程的示例如下：
import java.sql.*;
public class CallOracleProc {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
String user = "test";
String password = "test123";
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
// 调用带输入输出参数的存储过程，?是占位符
cs = conn.prepareCall("{call proc_get_emp_count(?, ?)}");
// 设置输入参数
cs.setInt(1, 20);
// 注册输出参数类型
cs.registerOutParameter(2, Types.NUMERIC);
// 执行存储过程
cs.execute();
// 获取输出参数结果
int empCount = cs.getInt(2);
System.out.println("20号部门员工总数：" + empCount);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (cs != null) cs.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
存储过程的修改与删除
修改存储过程
如果需要修改存储过程的逻辑，不需要先删除再创建，直接使用CREATE OR REPLACE PROCEDURE语句重新编译即可，Oracle会自动替换原有存储过程：
-- 修改之前的proc_insert_test_data存储过程，增加插入内容的前缀
CREATE OR REPLACE PROCEDURE proc_insert_test_data AS
v_max_id NUMBER;
BEGIN
SELECT NVL(MAX(id),0) INTO v_max_id FROM test_proc_table;
INSERT INTO test_proc_table(id,content,create_time)
VALUES(v_max_id + 1, '修改后：存储过程插入的数据', SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('数据插入成功，新增id为：' || (v_max_id + 1));
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('插入失败，错误原因：' || SQLERRM);
RAISE;
END proc_insert_test_data;
删除存储过程
删除存储过程使用DROP PROCEDURE语句：
-- 删除存储过程 DROP PROCEDURE proc_insert_test_data;
存储过程常见问题与注意事项
- 存储过程中的DML操作需要注意事务控制，要么显式提交要么回滚，避免出现事务悬挂问题。
- 参数类型不要指定长度，比如声明参数时写
VARCHAR2即可，不需要写VARCHAR2(50)，长度由调用时传入的值决定。 - 调试存储过程可以使用DBMS_OUTPUT输出日志，或者使用Oracle自带的企业管理器、PL_SQL Developer等工具的调试功能。
- 存储过程名不要和数据库中的表名、视图名重名，避免调用时出现歧义。
- 如果存储过程需要返回多个结果集，可以结合游标类型参数实现，将游标作为输出参数传递给调用方。