Oracle存储过程怎么写和用，带点实际操作的例子讲解

很多人觉得Oracle存储过程听起来很高级，有点难，其实我们可以把它想象成一个“预制的工具”或者“一套提前写好的指令”，你把它放在数据库里，什么时候要用，直接叫它的名字，它就开始干活了，这样做最大的好处就是高效和复用,你不用每次都重新写一大堆复杂的SQL语句。

第一部分：存储过程到底是个啥？

想象一下，你开了一家小店，每天关门后都要做三件事：第一，把当天的营业额加到总账上；第二，清点一下库存，卖掉的要减掉；第三，如果某个商品卖得特别好导致库存低于警戒线了，就自动记下来明天要进货，你每天都要重复这三步,很麻烦。

这时候，你可以写一张“每日结单流程”清单，贴在墙上，以后每天下班，你只需要对伙计喊一声：“嘿，执行每日结单！”伙计就会按照清单上的步骤，一口气把三件事全做完，这个“每日结单流程”清单，就是一个存储过程，它把多个操作步骤打包在一起,通过一个简单的指令来触发。

在Oracle数据库里，存储过程就是这个“清单”，它是一组为了完成特定功能的SQL语句集合，经过编译后存储在数据库中，你只需要调用存储过程的名字,数据库就会按顺序执行里面的所有语句。

第二部分：动手写第一个简单的存储过程

我们来创建一个最简单的存储过程，感受一下，假设我们有一个员工表（employees），我们想写一个存储过程,用来给某个员工涨工资。

根据Oracle官方文档中关于CREATE PROCEDURE的说明（来源：Oracle Database PL/SQL Language Reference）,一个存储过程的基本结构是这样的：

CREATE OR REPLACE PROCEDURE 过程名 (参数1 模式 数据类型, 参数2 模式 数据类型, ...)
IS
-- 这里可以声明一些变量，就像提前准备一些工具
BEGIN
    -- 这里写主要的执行逻辑，比如各种SQL语句
    NULL; -- NULL表示什么都不做，只是个占位符
EXCEPTION
    -- 这里写发生错误时的处理办法（可选）
END;

我们来实现“给员工涨工资”的过程：

CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id IN employees.employee_id%TYPE,  -- IN 模式的参数，表示传入的员工ID
    p_amount IN NUMBER                       -- IN 模式的参数，表示要涨薪的金额
)
IS
    -- 这里我们暂时不需要声明变量，所以是空的
BEGIN
    -- 主要的逻辑：更新员工表，将指定ID员工的薪水增加指定的金额
    UPDATE employees
    SET salary = salary + p_amount
    WHERE employee_id = p_emp_id;
    -- 提交事务，让更新生效（在实际中，有时会在调用处提交，这里仅为示例）
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('员工 ' || p_emp_id || ' 的薪水已成功增加 ' || p_amount);
EXCEPTION
    -- 异常处理：如果找不到这个员工ID，就提示一下
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('错误：未找到员工ID为 ' || p_emp_id || ' 的记录。');
END raise_salary;
/

我们来解释一下关键点：

• CREATE OR REPLACE：如果这个名字的过程不存在就创建，存在就替换,非常方便修改。
• p_emp_id IN ...：这是参数。IN 表示这个参数是传入给存储过程的。employees.employee_id%TYPE 是一种聪明的写法，意思是参数的数据类型和 employees 表的 employee_id 字段的类型保持一致,这样就不容易出错。
• BEGIN ... END：中间就是核心逻辑,这里是一条UPDATE语句。
• DBMS_OUTPUT.PUT_LINE：这是Oracle内置的用于在命令行输出信息的过程，就像程序里的print语句,方便我们看结果。
• EXCEPTION：用来捕获和处理可能出现的错误。

第三部分：如何使用我们创建的存储过程

存储过程创建好了，怎么用呢？有几种常见方法：

方法1：在SQLPLUS或者SQL Developer中直接调用 在工具里，先确保打开了输出显示（在SQL Developer中需要点击“启用DBMS输出”的按钮）,然后执行：

BEGIN
   raise_salary(100, 500); -- 给ID为100的员工涨500块钱工资
END;
/

执行后，你会在DBMS输出窗口看到提示信息：“员工 100 的薪水已成功增加 500”。

方法2：从另一个程序或存储过程中调用 存储过程可以互相调用，比如你可以写一个“年度调薪”过程，里面循环调用上面的raise_salary给一批人涨工资。

方法3：在应用程序中调用 比如在Java（使用JDBC）、Python等编程语言中，可以通过数据库连接来调用存储过程，把参数传进去，这就像是你的Java程序对着数据库喊了一声：“喂，执行raise_salary！”

第四部分：一个更综合的例子——带输出参数的存储过程

我们不仅想执行操作，还想从存储过程里拿回一些结果,我们想查一个员工的名字和当前工资。

根据Oracle关于过程参数的说明（来源：Oracle Database PL/SQL Language Reference），参数模式除了IN，还有OUT（输出）和IN OUT（输入输出）。

CREATE OR REPLACE PROCEDURE get_employee_info (
    p_emp_id IN employees.employee_id%TYPE,
    o_emp_name OUT employees.last_name%TYPE,  -- OUT 参数，用于输出员工姓名
    o_emp_salary OUT employees.salary%TYPE    -- OUT 参数，用于输出员工薪水
)
IS
BEGIN
    -- 将查询到的姓名和薪水赋值给两个OUT参数
    SELECT last_name, salary
    INTO o_emp_name, o_emp_salary
    FROM employees
    WHERE employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        o_emp_name := '未知';
        o_emp_salary := 0;
END get_employee_info;
/

这个存储过程有两个OUT参数，它不直接显示结果，而是把结果“塞”到这两个参数里,交给调用者处理。

调用这个带OUT参数的过程,需要提供变量来接收结果：

DECLARE
    v_name employees.last_name%TYPE;
    v_sal employees.salary%TYPE;
BEGIN
    -- 调用过程，传入ID，并用变量v_name和v_sal接收输出的值
    get_employee_info(100, v_name, v_sal);
    -- 然后我们可以打印接收到的值
    DBMS_OUTPUT.PUT_LINE('员工姓名：' || v_name || ', 工资：' || v_sal);
END;
/

总结一下

存储过程就是一个存储在数据库里的“脚本”或“工具包”，你把它定义好之后，可以反复使用，避免了代码重复，也提高了执行效率（因为数据库已经把它编译优化好了），从简单的数据增删改查，到复杂的业务逻辑封装，它都能胜任，刚开始学的时候，从最简单的带IN参数的例子入手，然后再尝试使用OUT参数来返回数据，一步步来,就会发现它其实并没有想象中那么神秘。