当前位置：首页 > 问答 > 正文

Oracle存储过程那些实用代码写法和细节分享，帮你更好理解应用

芮以莲
问答
2025-12-27 01:43:35
2

基础结构：不只是BEGIN和END

很多人觉得存储过程就是BEGIN和END包起来的一段SQL,一个完整的存储过程结构能避免很多坑，最基本的模板是这样的：

CREATE OR REPLACE PROCEDURE your_procedure_name (
    p_input_param IN VARCHAR2, -- 输入参数
    p_output_param OUT NUMBER -- 输出参数
) AS
    -- 声明部分：这里定义变量、游标、异常等
    v_local_variable DATE := SYSDATE; -- 局部变量
    v_count NUMBER;
BEGIN
    -- 执行部分：主要的业务逻辑在这里
    NULL; -- 先写个NULL，避免空过程报错
EXCEPTION
    -- 异常处理部分：非常重要！
    WHEN NO_DATA_FOUND THEN
        NULL; -- 处理没有找到数据的异常
    WHEN OTHERS THEN
        NULL; -- 处理其他所有未知异常
END your_procedure_name;

细节分享（基于常见开发经验）： 养成在AS关键字后面立刻声明变量的习惯，而不是紧跟着BEGIN。EXCEPTION部分绝对不能省略，即使只是简单记录日志，也能让你的程序在出错时有迹可循，而不是默默失败。

参数传递的三种模式：IN, OUT, IN OUT

参数是存储过程与外界沟通的桥梁,有三种模式：

IN（默认）：只读模式，调用过程时，你必须为它提供一个值，过程内部不能修改它，就像你把一份文件给别人看，他不能涂改。
```
PROCEDURE show_employee (p_emp_id IN NUMBER) ...
```
OUT：只写模式，调用过程时，你传一个变量进去（通常是个未初始化的变量），过程内部会为这个变量赋值，执行完毕后，你就能拿到这个值，就像你给别人一个空盒子，他还给你时，里面可能装了东西。
```
PROCEDURE get_employee_count (p_total_count OUT NUMBER) ...
```
IN OUT：可读可写模式，调用时传一个变量进去，过程内部既能读取它的原始值，也能修改它，修改后的值会返回给调用者，就像你给别人一个装了东西的盒子，他可能会换掉里面的东西再还给你。

细节分享（基于常见开发经验）： 对于OUT和IN OUT参数，在调用存储过程时，传入的实参必须是变量，而不能是常量或表达式，直接传数字10是不行的。

动态SQL：让SQL语句“活”起来

当你的SQL语句条件或表名不确定,需要根据参数动态拼接时，就要用到动态SQL，主要使用EXECUTE IMMEDIATE。

写法示例：根据表名和条件动态查询

CREATE OR REPLACE PROCEDURE dynamic_query (
    p_table_name IN VARCHAR2,
    p_where_clause IN VARCHAR2
) AS
    v_sql VARCHAR2(2000);
    v_result NUMBER;
BEGIN
    -- 拼接SQL语句，这是一个字符串
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    END IF;
    -- 执行动态SQL，并将结果 INTO 到变量中
    EXECUTE IMMEDIATE v_sql INTO v_result;
    -- 使用结果
    DBMS_OUTPUT.PUT_LINE('总数是： ' || v_result);
END;

细节分享（基于常见开发经验）： 动态SQL虽然强大，但极易引发SQL注入安全问题，永远不要直接拼接用户输入的内容，如果必须拼接，一定要进行严格的过滤和校验，更好的方法是使用绑定变量，如下所示：

-- 使用绑定变量更安全的写法
v_sql := 'SELECT COUNT(*) FROM emp WHERE deptno = :dept_id AND salary > :min_sal';
EXECUTE IMMEDIATE v_sql INTO v_result USING p_dept_id, p_min_salary;

使用USING子句传递参数，Oracle会将其视为数据而非SQL指令的一部分，从而从根本上杜绝SQL注入。

游标的使用：处理多行数据的结果集

当你需要逐行处理一个查询返回的多条记录时,游标是必不可少的，有两种常用写法：

显式游标（推荐，控制力强）

DECLARE
    CURSOR cur_emp IS -- 声明游标
        SELECT employee_id, first_name FROM employees WHERE department_id = 10;
    v_emp_id employees.employee_id%TYPE; -- 使用%TYPE定义变量，类型与表字段一致
    v_emp_name employees.first_name%TYPE;
BEGIN
    OPEN cur_emp; -- 打开游标
    LOOP
        FETCH cur_emp INTO v_emp_id, v_emp_name; -- 提取一行数据
        EXIT WHEN cur_emp%NOTFOUND; -- 如果取不到数据就退出循环
        -- 处理每一行数据，比如打印或插入另一张表
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Name: ' || v_emp_name);
    END LOOP;
    CLOSE cur_emp; -- 关闭游标
END;

隐式游标FOR循环（代码简洁）

BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10) 
    LOOP
        -- 无需声明游标和变量，直接通过emp_rec.字段名访问
        DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.first_name);
    END LOOP;
END;

细节分享（基于常见开发经验）： 对于简单的循环遍历，隐式游标FOR循环写法更简洁，不易出错（它会自动打开、关闭游标），但如果你需要在循环内部进行更复杂的控制（比如满足某个条件时跳出），显式游标更灵活，使用%TYPE来声明变量是一个非常好的习惯，当表结构字段类型改变时，你的代码无需修改。

异常处理：让你的程序更健壮

存储过程执行中可能会出错,比如除零、找不到数据、唯一约束冲突等，良好的异常处理是专业和业余代码的区别。

CREATE OR REPLACE PROCEDURE safe_division (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER,
    p_result OUT NUMBER
) AS
BEGIN
    p_result := p_num1 / p_num2; -- 如果p_num2为0，会触发ZERO_DIVIDE异常
    DBMS_OUTPUT.PUT_LINE('计算结果是： ' || p_result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('错误：除数不能为零！');
        p_result := NULL; -- 给输出参数一个安全值
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('错误：数值转换或类型不匹配！');
    WHEN OTHERS THEN -- 捕获其他所有未预料到的异常
        DBMS_OUTPUT.PUT_LINE('发生未知错误，错误代码： ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('错误信息： ' || SQLERRM);
        -- 在实际应用中，这里通常还会将错误信息记录到日志表中
        RAISE; -- 可以选择将异常再次抛出，让调用者也知道
END;

细节分享（基于常见开发经验）： WHEN OTHERS THEN 是一个“兜底”的异常处理器，但要谨慎使用，如果捕获了OTHERS异常却不做任何处理（或只是简单记录），可能会掩盖真正的业务逻辑错误，最佳实践是：先捕获你知道的、特定的异常（如ZERO_DIVIDE, NO_DATA_FOUND, DUP_VAL_ON_INDEX等），最后再用OTHERS来处理未知异常，并且通常会在日志中记录详细信息，甚至重新抛出（RAISE）。

调试和日志：不可或缺的帮手

存储过程在服务器端运行,看不见摸不着，调试和日志非常重要。

最简单调试：使用DBMS_OUTPUT.PUT_LINE在控制台输出信息，但记得在执行前先执行SET SERVEROUTPUT ON（在SQLPlus或SQL Developer中）。
```
DBMS_OUTPUT.PUT_LINE('当前变量v_count的值是： ' || v_count);
```

实用日志表：创建一个专门的日志表，在过程的关键节点插入记录，这样即使程序执行完毕，日志也永久保存，便于排查历史问题。

-- 创建日志表
CREATE TABLE proc_log (
    log_id NUMBER PRIMARY KEY,
    proc_name VARCHAR2(100),
    log_message VARCHAR2(4000),
    log_time TIMESTAMP
);
-- 在存储过程中插入日志
INSERT INTO proc_log (log_id, proc_name, log_message, log_time)
VALUES (log_seq.NEXTVAL, 'your_procedure_name', '过程开始执行...', SYSTIMESTAMP);
COMMIT;

细节分享（基于常见开发经验）： 在生产环境中，DBMS_OUTPUT是看不到的，所以建立日志表是更可靠的方法，可以在过程的开始、结束、关键判断分支和异常处理块中都插入日志，这样一旦出问题，就能像看“黑匣子”一样还原执行路径。

写好Oracle存储过程的关键在于：清晰的结构、安全的参数和动态SQL处理、高效的游标使用、周全的异常处理，以及有效的调试日志手段，多练习这些实用的写法，能让你更好地理解和应用存储过程。

Oracle存储过程那些实用代码写法和细节分享，帮你更好理解应用