Oracle存储过程参数设置方法详解

一、存储过程参数类型概述

Oracle存储过程支持三种参数类型：

1. IN参数：输入参数（默认类型），只能被过程读取
2. OUT参数：输出参数，过程可写入，调用者可读取
3. IN OUT参数：输入输出参数，过程可读写，调用者也可读写

二、参数定义语法

CREATE OR REPLACE PROCEDURE procedure_name (
    param1 [IN | OUT | IN OUT] datatype [:= default_value],
    param2 [IN | OUT | IN OUT] datatype [:= default_value],
    ...
)
IS
    -- 声明部分
BEGIN
    -- 执行部分
EXCEPTION
    -- 异常处理部分
END procedure_name;

三、参数传递方法

1. 位置表示法

按照参数定义的顺序传递值：

BEGIN
    procedure_name(value1, value2, ...);
END;

2. 名称表示法

使用参数名指定值，顺序不重要：

BEGIN
    procedure_name(param1 => value1, param2 => value2);
END;

3. 混合表示法

结合位置和名称表示法，位置参数必须在名称参数之前：

BEGIN
    procedure_name(value1, param2 => value2);
END;

四、参数默认值设置

可以为IN参数设置默认值：

CREATE OR REPLACE PROCEDURE add_employee (
    p_name IN VARCHAR2,
    p_salary IN NUMBER := 5000,
    p_dept IN NUMBER DEFAULT 10
)
IS
BEGIN
    -- 过程体
END;

调用时可省略有默认值的参数：

BEGIN
    add_employee('张三'); -- 使用默认salary和dept
    add_employee('李四', 6000); -- 使用默认dept
END;

五、OUT参数使用示例

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2,
    p_salary OUT NUMBER
)
IS
BEGIN
    SELECT employee_name, salary
    INTO p_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
END;

调用OUT参数的过程：

DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    get_employee_info(100, v_name, v_salary);
    DBMS_OUTPUT.PUT_LINE('姓名: ' || v_name || ', 薪资: ' || v_salary);
END;

六、IN OUT参数使用示例

CREATE OR REPLACE PROCEDURE increment_counter (
    p_counter IN OUT NUMBER,
    p_increment IN NUMBER DEFAULT 1
)
IS
BEGIN
    p_counter := p_counter + p_increment;
END;

调用IN OUT参数的过程：

DECLARE
    v_count NUMBER := 0;
BEGIN
    increment_counter(v_count); -- 增加1
    DBMS_OUTPUT.PUT_LINE('计数: ' || v_count);

    increment_counter(v_count, 5); -- 增加5
    DBMS_OUTPUT.PUT_LINE('计数: ' || v_count);
END;

七、参数传递注意事项

1. 参数类型匹配：传递的值必须与参数声明类型兼容
2. OUT参数变量：调用时OUT参数必须使用变量，不能是常量或表达式
3. 参数大小限制：PL/SQL对参数数量有限制（通常1000个左右）
4. 参数传递方式：大型参数考虑使用NOCOPY提示减少复制开销

八、NOCOPY提示使用

对于大型OUT或IN OUT参数，可以使用NOCOPY提示提高性能：

CREATE OR REPLACE PROCEDURE process_large_data (
    p_data IN OUT NOCOPY CLOB
)
IS
BEGIN
    -- 处理大型CLOB数据
END;

九、参数验证最佳实践

1. 对输入参数进行有效性检查
2. 为关键参数添加注释说明
3. 考虑使用断言验证关键假设
4. 提供清晰的错误消息

CREATE OR REPLACE PROCEDURE update_salary (
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
)
IS
BEGIN
    -- 参数验证
    IF p_new_salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '薪资不能为负数');
    END IF;

    -- 业务逻辑
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_emp_id;

    -- 检查是否更新成功
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, '员工ID不存在: ' || p_emp_id);
    END IF;
END;

通过以上方法，您可以更有效地设计和使用Oracle存储过程的参数，提高代码的可读性、可维护性和性能。