Oracle数据库之带参数的存储过程（十）

概述

在 Oracle 数据库中，创建带参数的存储过程是很常见的。以下是一个简单的示例，说明如何创建一个接受参数的存储过程，并在过程中执行一些操作。

示例：创建一个接受参数的存储过程

假设我们有一个名为 employees 的表，它有一个 salary 列，我们想要创建一个存储过程来更新某个员工的薪水。

CREATE OR REPLACE PROCEDURE update_salary(  
    p_employee_id IN employees.employee_id%TYPE,  
    p_new_salary IN employees.salary%TYPE  
) IS  
BEGIN  
    -- 更新薪水  
    UPDATE employees  
    SET salary = p_new_salary  
    WHERE employee_id = p_employee_id;  
  
    -- 提交事务（如果需要的话）  
    COMMIT;  
  
    -- 可以添加异常处理部分  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('没有找到对应的员工ID。');  
    WHEN OTHERS THEN  
        -- 打印错误信息并回滚事务  
        DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);  
        ROLLBACK;  
END update_salary;  
/

在这个例子中：

• 我们使用了 IN 关键字来指定参数是输入参数。
• 我们使用了 %TYPE 属性来确保参数的数据类型与 employees 表中的相应列的数据类型匹配。
• 在存储过程的主体中，我们执行了一个 UPDATE 语句来更新薪水。
• 我们还添加了一个简单的异常处理部分，用于处理可能出现的错误。

执行带参数的存储过程

要执行这个存储过程，你可以使用以下命令（假设你已经启用了 DBMS_OUTPUT）：

-- 在 SQL*Plus 或其他工具中，你可能需要先启用 DBMS_OUTPUT  
SET SERVEROUTPUT ON;  
  
-- 执行存储过程  
BEGIN  
    update_salary(1001, 5000); -- 假设员工ID为1001，新薪水为5000  
END;  
/

注意：在生产环境中，通常不建议在存储过程中直接提交事务，因为这可能会干扰调用存储过程的外部事务的逻辑。但是，在这个简单的示例中，为了保持简洁性，我选择了提交事务。在实际应用中，你可能需要根据具体需求来决定是否在存储过程中提交事务。

Oracle有三种参数模式：

在Oracle中，存储过程和函数都可以有参数，并且这些参数可以指定为三种模式：IN、OUT和IN OUT。以下是这三种参数模式的详细解释：

1. IN模式：

   • 这是默认的参数模式，如果不显式指定参数模式，则默认为IN模式。
   • 在IN模式下，参数的值从调用者传递给被调用的存储过程或函数。在存储过程或函数内部，这个参数是只读的，不能被修改。
   • 当存储过程或函数执行完毕后，传递给它的参数值不会发生变化。

2. OUT模式：

   • 在OUT模式下，参数用于从存储过程或函数返回值给调用者。
   • 调用存储过程或函数时，传递给OUT参数的实际值会被忽略。在存储过程或函数内部，OUT参数会被初始化为NULL（除非在过程中显式地给它赋值）。
   • OUT参数在存储过程或函数内部是可读写的，因此可以在过程中修改它的值。当存储过程或函数执行完毕后，OUT参数的值会被返回给调用者。

3. IN OUT模式：

   • IN OUT模式是IN和OUT模式的组合。
   • 调用存储过程或函数时，IN OUT参数的实际值会被传递给过程或函数。在过程或函数内部，这个参数既可以被读取也可以被修改。
   • 当存储过程或函数执行完毕后，IN OUT参数的值会被返回给调用者，并且这个值可能已经在过程或函数内部被修改过。

通过合理使用这三种参数模式，可以使Oracle的存储过程和函数更加灵活和强大。

IN参数

在Oracle中，IN参数是存储过程、函数或包中过程的最常用的参数模式。当您定义一个带有IN参数的存储过程或函数时，您实际上是在告诉Oracle这个参数是从调用者传递到存储过程或函数内部的，并且在存储过程或函数内部这个参数是只读的。

以下是一个简单的例子，展示了如何在Oracle中定义和使用一个带有IN参数的存储过程：

首先，我们创建一个简单的表：

CREATE TABLE employees (  
    emp_id NUMBER PRIMARY KEY,  
    emp_name VARCHAR2(50),  
    salary NUMBER(10,2)  
);

然后，我们创建一个存储过程，该过程接受一个IN参数（员工ID），并用于更新该员工的薪水：

CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_new_salary IN NUMBER) IS  
BEGIN  
    -- 检查员工ID是否存在  
    IF NOT EXISTS (SELECT 1 FROM employees WHERE emp_id = p_emp_id) THEN  
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found.');  
    END IF;  
  
    -- 更新员工的薪水  
    UPDATE employees SET salary = p_new_salary WHERE emp_id = p_emp_id;  
    COMMIT;  
    DBMS_OUTPUT.PUT_LINE('Salary updated successfully for employee ID: ' || p_emp_id);  
EXCEPTION  
    WHEN OTHERS THEN  
        ROLLBACK;  
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);  
END update_salary;  
/

在这个例子中，p_emp_id和p_new_salary都是IN参数。当您调用这个存储过程时，您需要为这两个参数提供值。

调用存储过程的示例：

BEGIN  
    update_salary(1001, 5000.00);  
END;  
/

请注意，因为p_emp_id和p_new_salary都是IN参数，所以在存储过程内部您不能修改它们的值（尽管您可以根据这些值来修改表中的数据）。如果您尝试在存储过程内部给p_emp_id或p_new_salary赋值，Oracle会报错，因为这些参数是只读的。

最后，要查看DBMS_OUTPUT.PUT_LINE的输出，您需要在SQL*Plus或类似的工具中启用输出（通过执行SET SERVEROUTPUT ON）。如果您使用的是其他工具或IDE，您可能需要查看该工具如何捕获和显示DBMS_OUTPUT的输出。

OUT参数

在Oracle中，OUT参数是存储过程或函数中的一种参数类型，它用于从存储过程或函数返回值给调用者。与IN参数不同，OUT参数在存储过程或函数内部被赋值，并且这个值在过程或函数执行完毕后会被返回给调用者。

OUT参数的使用场景通常包括需要返回多个值给调用者的情况。在Oracle中，虽然函数可以返回一个值，但如果你需要返回多个值，那么就需要使用OUT参数或者返回一个自定义类型（如对象类型或表类型）。

以下是使用OUT参数的简单示例：

假设我们有一个名为employees的表，我们想要编写一个存储过程来查询某个员工的姓名、月薪和职位，并将这些信息作为OUT参数返回。

首先，创建存储过程：

CREATE OR REPLACE PROCEDURE query_employee_info(  
    eno IN NUMBER,  
    pename OUT VARCHAR2,  
    psal OUT NUMBER,  
    pjob OUT VARCHAR2  
) AS  
BEGIN  
    SELECT ename, sal, job INTO pename, psal, pjob  
    FROM employees  
    WHERE emp_id = eno;  
END query_employee_info;  
/

在上面的存储过程中，我们定义了四个参数：一个IN参数eno用于接收员工ID，以及三个OUT参数pename、psal和pjob用于返回员工的姓名、月薪和职位。

接下来，我们调用这个存储过程，并声明变量来接收OUT参数的值：

DECLARE  
    v_ename VARCHAR2(50);  
    v_sal NUMBER(10,2);  
    v_job VARCHAR2(50);  
BEGIN  
    query_employee_info(1001, v_ename, v_sal, v_job); -- 假设员工ID为1001  
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_ename || ', Salary: ' || TO_CHAR(v_sal) || ', Job: ' || v_job);  
END;  
/

在上面的调用代码中，我们首先声明了三个变量v_ename、v_sal和v_job来接收OUT参数的值。然后，我们调用query_employee_info存储过程，并传入员工ID和这三个变量作为参数。存储过程执行完毕后，这三个变量的值就被设置为了查询结果中的值，然后我们使用DBMS_OUTPUT.PUT_LINE来打印这些信息。

请注意，为了查看DBMS_OUTPUT.PUT_LINE的输出，你需要在SQLPlus或其他工具中启用输出（例如，在SQLPlus中执行SET SERVEROUTPUT ON）。

IN OUT参数

在Oracle中，IN OUT参数是存储过程或函数的一种参数类型，它允许参数既作为输入传递给存储过程或函数，也可以作为输出从存储过程或函数返回给调用者。换句话说，IN OUT参数是IN和OUT参数特性的结合。

当你在存储过程或函数内部修改IN OUT参数的值时，这个修改后的值会在存储过程或函数执行完毕后返回给调用者。

以下是一个使用IN OUT参数的简单示例：

假设我们有一个存储过程，它接受一个IN OUT参数来累加数值：

CREATE OR REPLACE PROCEDURE add_to_value(  
    p_value IN OUT NUMBER  
) AS  
BEGIN  
    -- 将传入的值增加10  
    p_value := p_value + 10;  
END add_to_value;  
/

现在，我们可以调用这个存储过程，并使用一个变量来传递和接收值：

DECLARE  
    v_num NUMBER := 5; -- 初始值  
BEGIN  
    -- 调用存储过程，将v_num作为IN OUT参数传递  
    add_to_value(v_num);  
      
    -- 打印修改后的值  
    DBMS_OUTPUT.PUT_LINE('Value after add_to_value: ' || TO_CHAR(v_num));  
END;  
/

在上面的代码中，我们首先声明了一个变量v_num并赋值为5。然后，我们调用add_to_value存储过程，并将v_num作为IN OUT参数传递。在存储过程内部，p_value（即v_num的引用）被增加了10。当存储过程执行完毕后，v_num的值已经被修改为15，并且这个值会被打印出来。

注意，要查看DBMS_OUTPUT.PUT_LINE的输出，你需要在SQLPlus或其他工具中启用输出（例如，在SQLPlus中执行SET SERVEROUTPUT ON）。