【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. 存储过程的调用
调用存储过程有两种常见方式，取决于存储过程是否有参数：
- 无参数存储过程调用：
BEGINprocedure_name;
END;
例如，假设存在一个名为 delete_old_records 的无参数存储过程，用于删除旧记录：
BEGINdelete_old_records;
END;
- 有参数存储过程调用：
BEGINprocedure_name(parameter1_value, parameter2_value);
END;
如果存储过程有 IN 参数，可以直接传递值；如果有 OUT 或 IN OUT 参数，需要先声明变量来接收返回值。例如，假设有一个存储过程 calculate_total，用于计算订单总金额并返回：
DECLAREtotal_amount NUMBER;
BEGINcalculate_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
BEGINDBMS_OUTPUT.PUT_LINE('进入存储过程');-- 其他逻辑DBMS_OUTPUT.PUT_LINE('离开存储过程');
END;
然后调用存储过程：
SET SERVEROUTPUT ON;
BEGINtest_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;