【Oracle学习笔记】7.存储过程（Stored Procedure）

Oracle中的存储过程是一组为了完成特定功能而预先编译并存储在数据库中的SQL语句和PL/SQL代码块。它可以接受参数、执行操作（如查询、插入、更新、删除数据等），并返回结果。以下从多个方面详细讲解：

1. 存储过程的创建

创建存储过程使用 CREATE OR REPLACE PROCEDURE 语句，基本语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [IN | OUT | IN OUT] data_type [:= default_value],
     parameter2 [IN | OUT | IN OUT] data_type [:= default_value],
    ...)]
IS
    -- 声明部分，用于声明变量、游标等
    variable1 data_type;
    variable2 data_type := initial_value;
BEGIN
    -- 执行部分，包含SQL语句和PL/SQL逻辑
    -- 例如，插入数据
    INSERT INTO your_table (column1, column2) VALUES (parameter1, variable1);
    -- 更新数据
    UPDATE your_table SET column2 = parameter2 WHERE column1 = variable2;
    -- 可以进行条件判断
    IF variable1 > 10 THEN
        -- 执行某些操作
        DELETE FROM your_table WHERE column1 = variable2;
    END IF;
    -- 循环操作
    FOR i IN 1..10 LOOP
        -- 执行循环内的操作
        INSERT INTO another_table (column3) VALUES (i);
    END LOOP;
EXCEPTION
    -- 异常处理部分，捕获并处理执行过程中的异常
    WHEN NO_DATA_FOUND THEN
        -- 处理没有找到数据的异常
        DBMS_OUTPUT.PUT_LINE('没有找到数据');
    WHEN OTHERS THEN
        -- 处理其他异常
        DBMS_OUTPUT.PUT_LINE('发生其他错误:'|| SQLERRM);
END;

• CREATE [OR REPLACE]：CREATE 用于创建新的存储过程，OR REPLACE 表示如果存储过程已存在，则替换它。这样可以在不删除存储过程的情况下修改其定义。
• procedure_name：存储过程的名称，遵循数据库对象命名规则。
• parameter：存储过程可以有零个或多个参数。参数类型分为 IN（输入参数，默认类型，用于向存储过程传递值）、OUT（输出参数，用于从存储过程返回值）和 IN OUT（既可以输入值，也可以返回值）。参数可以有默认值。
• IS：开始声明部分，用于声明存储过程内部使用的变量、游标等。
• BEGIN：开始执行部分，包含实际要执行的SQL语句和PL/SQL逻辑。
• EXCEPTION：异常处理部分，用于捕获并处理执行过程中可能出现的异常。

2. 存储过程的调用

调用存储过程有两种常见方式，取决于存储过程是否有参数：

• 无参数存储过程调用：

BEGIN
    procedure_name;
END;

例如，假设存在一个名为 delete_old_records 的无参数存储过程，用于删除旧记录：

BEGIN
    delete_old_records;
END;

• 有参数存储过程调用：

BEGIN
    procedure_name(parameter1_value, parameter2_value);
END;

如果存储过程有 IN 参数，可以直接传递值；如果有 OUT 或 IN OUT 参数，需要先声明变量来接收返回值。例如，假设有一个存储过程 calculate_total，用于计算订单总金额并返回：

DECLARE
    total_amount NUMBER;
BEGIN
    calculate_total('2023 - 10 - 01', '2023 - 10 - 31', total_amount);
    DBMS_OUTPUT.PUT_LINE('订单总金额为:'|| total_amount);
END;

这里 calculate_total 存储过程接受两个 IN 参数（日期范围）和一个 OUT 参数（用于返回总金额）。

3. 存储过程的优势

• 提高代码复用性：将常用的业务逻辑封装在存储过程中，不同的应用程序或SQL脚本可以多次调用，避免重复编写相同的代码。
• 增强安全性：通过对存储过程授权，而不是直接对底层表授权，可以限制用户对数据的访问方式和范围。用户只能通过执行存储过程来操作数据，而不能直接访问表，从而保护数据的完整性和安全性。
• 提升性能：存储过程在数据库服务器端编译并存储，执行时直接从服务器端调用，减少了网络传输开销。而且，数据库可以对存储过程进行优化，缓存执行计划，提高执行效率。
• 简化应用程序开发：应用程序只需调用存储过程，而无需关心复杂的SQL逻辑和数据处理细节，降低了开发难度，提高了开发效率。

4. 存储过程的调试

• 使用 DBMS_OUTPUT 包：在存储过程中使用 DBMS_OUTPUT.PUT_LINE 语句输出调试信息。在调用存储过程之前，需要先设置 SET SERVEROUTPUT ON 开启输出功能。例如：

CREATE OR REPLACE PROCEDURE test_proc
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('进入存储过程');
    -- 其他逻辑
    DBMS_OUTPUT.PUT_LINE('离开存储过程');
END;

然后调用存储过程：

SET SERVEROUTPUT ON;
BEGIN
    test_proc;
END;

• 使用Oracle SQL Developer等工具：这些工具提供了可视化的调试界面，可以设置断点、查看变量值、单步执行存储过程等，方便定位和解决问题。

5. 存储过程的管理

• 查看存储过程定义：可以使用 DESC 命令查看存储过程的参数列表，使用 USER_SOURCE 视图查看存储过程的源代码。例如，查看 test_proc 的参数：

DESC test_proc;

查看 test_proc 的源代码：

SELECT text
FROM USER_SOURCE
WHERE name = 'TEST_PROC'
ORDER BY line;

• 修改存储过程：使用 CREATE OR REPLACE PROCEDURE 语句重新创建存储过程，即可修改其定义。
• 删除存储过程：使用 DROP PROCEDURE 语句删除存储过程。例如：

DROP PROCEDURE test_proc;