在 Oracle 数据库开发与运维中，存储过程是提升效率、简化复杂业务逻辑的核心工具。无论是 “创建存储过程”“调用执行”，还是 “循环与游标使用”“定时任务配置”，都是开发者高频需求。本文从基础概念到实战案例，系统拆解 Oracle 存储过程的关键操作，帮你快速上手并解决实际问题。
一、基础认知：什么是 Oracle 存储过程？
Oracle 存储过程是预编译后存储在数据库中的 PL/SQL 代码块，可封装复杂业务逻辑（如数据查询、批量更新、事务处理等），通过 “调用” 直接执行，无需重复编写 SQL。其核心优势包括：
1.提升效率：预编译一次，多次调用无需重新解析，减少数据库交互开销；
2.简化维护：业务逻辑集中在存储过程中，修改时只需更新存储过程，无需改动应用代码；
3.保障安全：可通过权限控制，让用户调用存储过程但不直接操作底层表，避免数据泄露；
4.支持事务：能统一管理事务（commit/rollback），确保业务操作的原子性。
二、核心操作：Oracle 存储过程的创建、调用与执行
这是开发者最常用的三大操作，以下结合语法规则和实例详细说明，确保零基础也能看懂。
1. 创建存储过程：语法与实例（解决 “oracle 创建存储过程”“oracle 存储过程写法” 需求）
（1）基本语法
创建存储过程的通用语法结构如下，需包含 “存储过程名、参数（可选）、PL/SQL 代码块、异常处理（可选）”：
CREATE OR REPLACE PROCEDURE 存储过程名(
-- 参数列表：IN（输入参数，默认）、OUT（输出参数）、IN OUT（既输入又输出）
参数1 数据类型 [IN/OUT/IN OUT] := 默认值,
参数2 数据类型 [IN/OUT/IN OUT] := 默认值
) AS
-- 声明部分：定义变量、常量、游标等（可选）
变量名 数据类型;
BEGIN
-- 执行部分：核心业务逻辑（必须）
业务SQL/PL/SQL代码;
EXCEPTION
-- 异常处理部分：捕获并处理执行中的错误（可选）
WHEN 异常类型 THEN
错误处理逻辑;
END 存储过程名;
/ -- 结束符，执行存储过程创建
注意：CREATE OR REPLACE表示 “若存储过程已存在，则覆盖更新”，避免重复删除再创建的麻烦；参数的数据类型需符合 Oracle 规范（如VARCHAR2(50)、NUMBER(10)，无需指定长度的如DATE）。
（2）实战实例：创建一个简单的存储过程
需求：创建存储过程P_GET_EMP_INFO，根据员工 ID（输入参数）查询员工姓名和薪资，并通过输出参数返回结果。
CREATE OR REPLACE PROCEDURE P_GET_EMP_INFO(
P_EMP_ID IN NUMBER, -- 输入参数：员工ID
P_EMP_NAME OUT VARCHAR2, -- 输出参数：员工姓名
P_EMP_SAL OUT NUMBER -- 输出参数：员工薪资
) AS
BEGIN
-- 从EMP表查询数据，赋值给输出参数
SELECT ENAME, SAL
INTO P_EMP_NAME, P_EMP_SAL
FROM EMP
WHERE EMPNO = P_EMP_ID;
EXCEPTION
-- 捕获“无数据找到”异常
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('错误：未找到ID为' || P_EMP_ID || '的员工');
P_EMP_NAME := NULL;
P_EMP_SAL := NULL;
-- 捕获其他所有异常
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('执行错误：' || SQLERRM); -- 输出错误信息
END P_GET_EMP_INFO;
/
执行上述代码后，存储过程会保存到数据库中，可通过后续 “查询存储过程” 操作验证是否创建成功。
2. 调用与执行存储过程：3 种常用方式（解决 “oracle 执行存储过程”“oracle 调用存储过程” 需求）
存储过程创建后，需通过 “调用” 触发执行，不同场景对应不同调用方式，以下是最常用的 3 种：
（1）通过EXECUTE（或EXEC）调用（适合简单无输出参数或输出参数可忽略的场景）
若存储过程无输出参数（如仅执行数据插入 / 更新），可直接用EXEC调用：
-- 实例：调用存储过程P_INSERT_EMP（假设该存储过程无输出参数，仅插入员工数据）
EXEC P_INSERT_EMP(1001, '张三', '开发工程师', 8000);
/
（2）通过PL/SQL块调用（适合有输出参数，需接收结果的场景）
以之前创建的P_GET_EMP_INFO为例，需定义变量接收输出参数的结果：
DECLARE
V_NAME VARCHAR2(50); -- 接收员工姓名的变量
V_SAL NUMBER; -- 接收员工薪资的变量
BEGIN
-- 调用存储过程，传入员工ID=7369，输出结果赋值给V_NAME和V_SAL
P_GET_EMP_INFO(7369, V_NAME, V_SAL);
-- 打印结果（需先开启DBMS_OUTPUT显示，执行SET SERVEROUTPUT ON）
DBMS_OUTPUT.PUT_LINE('员工姓名：' || V_NAME);
DBMS_OUTPUT.PUT_LINE('员工薪资：' || V_SAL);
END;
/
执行前需开启输出：在 PL/SQL Developer 或 SQL*Plus 中，先执行SET SERVEROUTPUT ON，否则无法看到DBMS_OUTPUT的打印结果。
（3）通过Java/Python等应用程序调用（适合项目开发中集成存储过程的场景）
以 Java 为例，通过 JDBC 调用 Oracle 存储过程，核心代码如下：
// 1. 加载驱动并建立数据库连接（省略连接代码）
Connection conn = ...;
// 2. 定义调用存储过程的SQL（?表示占位符，对应输入/输出参数）
String sql = "{call P_GET_EMP_INFO(?, ?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
// 3. 设置输入参数（员工ID=7369）
cstmt.setInt(1, 7369);
// 4. 注册输出参数（指定参数类型）
cstmt.registerOutParameter(2, OracleTypes.VARCHAR); // 员工姓名（字符串）
cstmt.registerOutParameter(3, OracleTypes.NUMBER); // 员工薪资（数字）
// 5. 执行存储过程
cstmt.execute();
// 6. 获取输出结果
String empName = cstmt.getString(2);
double empSal = cstmt.getDouble(3);
System.out.println("员工姓名：" + empName);
System.out.println("员工薪资：" + empSal);
// 7. 关闭资源（省略）
3. 查询与查看存储过程：找到已创建的存储过程（解决 “oracle 查询存储过程”“oracle 查看存储过程” 需求）
创建存储后，若需查看其代码、状态或所属用户，可通过 Oracle 系统视图查询，常用方式如下：
（1）查看存储过程的基本信息（名称、所属用户、状态）
通过ALL_PROCEDURES（所有用户可访问的存储过程）或USER_PROCEDURES（当前用户的存储过程）视图查询：
-- 查看当前用户下所有存储过程的名称和状态
SELECT OBJECT_NAME AS 存储过程名, STATUS AS 状态
FROM USER_PROCEDURES
WHERE OBJECT_TYPE = 'PROCEDURE';
-- 查看指定存储过程（如P_GET_EMP_INFO）的所属用户和创建时间
SELECT OWNER AS 所属用户, OBJECT_NAME AS 存储过程名, CREATED AS 创建时间
FROM ALL_PROCEDURES
WHERE OBJECT_NAME = 'P_GET_EMP_INFO'
AND OBJECT_TYPE = 'PROCEDURE';
状态说明：VALID表示存储过程编译通过，可正常调用；INVALID表示编译失败（如依赖表被删除），需重新编译（执行ALTER PROCEDURE 存储过程名 COMPILE;）。
（2）查看存储过程的完整代码
通过USER_SOURCE（当前用户的存储过程源代码）或ALL_SOURCE视图查询，需按LINE排序（代码按行存储）：
-- 查看当前用户下P_GET_EMP_INFO的完整代码
SELECT TEXT AS 存储过程代码
FROM USER_SOURCE
WHERE NAME = 'P_GET_EMP_INFO'
AND TYPE = 'PROCEDURE'
ORDER BY LINE;
执行后，TEXT列会按行显示存储过程的所有代码，拼接后即可得到完整内容。
三、进阶实战：存储过程的循环、游标与返回值处理
在复杂业务场景中，“循环遍历数据”“游标处理结果集”“返回多组数据” 是高频需求，以下结合实例拆解关键操作。
1. 存储过程中的循环：3 种常用循环方式（解决 “oracle 存储过程循环” 需求）
Oracle 存储过程支持FOR循环、WHILE循环、LOOP循环，分别适用于不同场景：
（1）FOR 循环（适合已知循环次数的场景）
需求：循环插入 5 条测试数据到TEST_TABLE表（表结构：ID NUMBER, NAME VARCHAR2 (50)）：
CREATE OR REPLACE PROCEDURE P_LOOP_INSERT
AS
BEGIN
-- 循环5次（从1到5）
FOR I IN 1..5 LOOP
INSERT INTO TEST_TABLE (ID, NAME)
VALUES (I, '测试数据_' || I);
END LOOP;
COMMIT; -- 提交事务
DBMS_OUTPUT.PUT_LINE('循环插入完成，共插入5条数据');
END P_LOOP_INSERT;
/
（2）WHILE 循环（适合未知循环次数，需判断条件的场景）
需求：查询EMP表中薪资小于 3000 的员工，循环更新其薪资（增加 10%），直到无符合条件的员工：
CREATE OR REPLACE PROCEDURE P_WHILE_UPDATE
AS
V_COUNT NUMBER := 1; -- 计数器，初始为1（确保进入循环）
BEGIN
-- 当存在薪资<3000的员工时，继续循环
WHILE V_COUNT > 0 LOOP
-- 更新薪资（增加10%）
UPDATE EMP
SET SAL = SAL * 1.1
WHERE SAL < 3000
AND ROWNUM = 1; -- 每次更新1条，避免批量锁表
-- 检查是否还有符合条件的员工
SELECT COUNT(*) INTO V_COUNT
FROM EMP
WHERE SAL < 3000;
DBMS_OUTPUT.PUT_LINE('当前剩余需更新员工数：' || V_COUNT);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('薪资更新完成');
END P_WHILE_UPDATE;
/
2. 存储过程中的游标：处理结果集（解决 “oracle 存储过程游标” 需求）
游标是 “指向结果集的指针”，用于在存储过程中遍历查询结果集（如批量处理多条数据），分为显式游标和隐式游标，显式游标需手动定义，适用于复杂结果集处理：
实例：用显式游标批量处理员工数据
需求：创建存储过程P_CURSOR_PROC，查询EMP表中部门编号为 30 的员工，打印每个员工的姓名和薪资，并统计该部门总人数：
CREATE OR REPLACE PROCEDURE P_CURSOR_PROC
AS
-- 1. 定义游标：指定查询结果集
CURSOR C_EMP IS
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = 30;
-- 2. 定义变量，用于接收游标中的每行数据
V_ENAME VARCHAR2(50);
V_SAL NUMBER;
V_TOTAL NUMBER := 0; -- 统计总人数
BEGIN
-- 3. 打开游标
OPEN C_EMP;
-- 4. 循环提取游标数据（FETCH ... INTO 接收变量）
LOOP
FETCH C_EMP INTO V_ENAME, V_SAL;
EXIT WHEN C_EMP%NOTFOUND; -- 当游标无数据时，退出循环
-- 打印当前员工信息
DBMS_OUTPUT.PUT_LINE('员工姓名：' || V_ENAME || '，薪资：' || V_SAL);
V_TOTAL := V_TOTAL + 1; -- 计数+1
END LOOP;
-- 5. 关闭游标
CLOSE C_EMP;
-- 打印统计结果
DBMS_OUTPUT.PUT_LINE('部门30总员工数：' || V_TOTAL);
END P_CURSOR_PROC;
/
执行结果：调用该存储过程后，会依次打印部门 30 的所有员工信息，最后输出总人数，适合批量处理有明确条件的结果集。
3. 存储过程的返回值：3 种实现方式（解决 “oracle 存储过程返回值” 需求）
Oracle 存储过程本身不直接支持 “return 返回值”，需通过输出参数、游标、自定义函数（FUNCTION） 实现返回数据的需求，以下是常用方式：
（1）通过 OUT 参数返回单个值（适合返回单个结果，如统计数、状态码）
实例：创建存储过程P_GET_DEPT_COUNT，根据部门编号返回该部门的员工总数：
CREATE OR REPLACE PROCEDURE P_GET_DEPT_COUNT(
P_DEPTNO IN NUMBER, -- 输入参数：部门编号
P_COUNT OUT NUMBER -- 输出参数：返回员工总数
) AS
BEGIN
SELECT COUNT(*) INTO P_COUNT
FROM EMP
WHERE DEPTNO = P_DEPTNO;
EXCEPTION
WHEN OTHERS THEN
P_COUNT := 0; -- 异常时返回0
DBMS_OUTPUT.PUT_LINE('查询错误：' || SQLERRM);
END P_GET_DEPT_COUNT;
/
调用时通过变量接收P_COUNT，即可获取返回的员工总数。
（2）通过游标返回多组数据（适合返回多条结果，如查询列表）
实例：创建存储过程P_GET_EMP_LIST，根据薪资范围返回符合条件的员工列表（用游标输出）：
CREATE OR REPLACE PROCEDURE P_GET_EMP_LIST(
P_MIN_SAL IN NUMBER, -- 输入参数：最低薪资
P_MAX_SAL IN NUMBER, -- 输入参数：最高薪资
P_EMP_CURSOR OUT SYS_REFCURSOR -- 输出参数：游标（返回员工列表）
) AS
BEGIN
-- 打开游标，将查询结果集赋值给游标
OPEN P_EMP_CURSOR FOR
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL BETWEEN P_MIN_SAL AND P_MAX_SAL;
END P_GET_EMP_LIST;
/
调用时，通过SYS_REFCURSOR类型的变量接收游标，再遍历游标获取多条员工数据。
（3）通过 FUNCTION 替代（适合需直接返回单个值，类似 “函数调用” 的场景）
若需像 “函数” 一样直接返回值（如SELECT 函数名(参数) FROM DUAL），可创建FUNCTION（本质是特殊的存储过程，支持 return）：
-- 创建函数F_GET_EMP_SAL，根据员工ID返回薪资
CREATE OR REPLACE FUNCTION F_GET_EMP_SAL(
P_EMP_ID IN NUMBER
) RETURN NUMBER -- 指定返回值类型
AS
V_SAL NUMBER;
BEGIN
SELECT SAL INTO V_SAL
FROM EMP
WHERE EMPNO = P_EMP_ID;
RETURN V_SAL; -- 直接返回薪资
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1; -- 异常时返回-1
END F_GET_EMP_SAL;
/
-- 调用函数：直接查询返回值
SELECT F_GET_EMP_SAL(7369) AS 员工薪资 FROM DUAL;
四、高频需求：删除存储过程与定时执行
除了基础操作，“删除无用存储过程” 和 “定时执行存储过程” 也是常见需求，以下是具体实现：
1. 删除存储过程（解决 “oracle 删除存储过程” 需求）
若存储过程不再使用，可通过DROP PROCEDURE删除，语法简单：
-- 删除存储过程P_OLD_PROC（若存在则删除）
DROP PROCEDURE IF EXISTS P_OLD_PROC</doubaocanvas>