一文详解Oracle存储过程

什么是存储过程？

存储过程是一种在Oracle数据库中以过程方式封装一组SQL操作集合的技术，可以在客户端不需要编写SQL，直接调用存储过程获得数据结果。

存储过程的优点

• 安全性：存储过程可以设定访问权限，只允许特定的用户访问和执行。
• 高效性：存储过程可以预编译，提高了数据库的性能和执行速度。
• 可维护性：存储过程可以修改、删除、添加，比起一条SQL语句更容易维护、管理。

如何创建存储过程？

创建存储过程的语法如下：

CREATE OR REPLACE PROCEDURE procedure_name
(parameter_name [IN|OUT] parameter_type [(parameter_size)])
IS | AS
BEGIN
-- statements to be executed
END;

• CREATE OR REPLACE: 创建或替换存储过程。
• PROCEDURE procedure_name: 存储过程的名称。
• parameter_name [IN|OUT] parameter_type [(parameter_size)]: 存储过程的参数。
• parameter_name: 参数名称。
• [IN|OUT]: 参数类型，IN表示输入参数，OUT表示输出参数。
• parameter_type: 参数的数据类型。
• (parameter_size): 参数的长度。
• IS|AS: 存储过程开始的标志。
• BEGIN...END: 存储过程内部执行的代码块。

下面是一个简单的例子：

创建一个名为get_employee的存储过程，查询员工表employee中的员工号、姓名、部门和职位。

CREATE OR REPLACE PROCEDURE get_employee
(
    p_empno IN employee.empno%type,
    p_ename OUT employee.ename%type,
    p_deptno OUT employee.deptno%type,
    p_job OUT employee.job%type
)
IS
BEGIN
    SELECT ename, deptno, job
    INTO p_ename, p_deptno, p_job
    FROM employee
    WHERE empno = p_empno;
END;

该存储过程包含4个参数：p_empno输入参数，p_ename、p_deptno和p_job输出参数。存储过程从employee表中查询与输入参数p_empno匹配的信息，并将结果赋值给输出参数。

如何调用存储过程？

调用存储过程的语法如下：

EXECUTE procedure_name(parameter_name);

• EXECUTE: 执行存储过程。
• procedure_name: 存储过程的名称。
• parameter_name: 存储过程的参数。

继续使用前面的例子，展示如何调用get_employee存储过程。

DECLARE
    v_ename employee.ename%type;
    v_deptno employee.deptno%type;
    v_job employee.job%type;
BEGIN
    get_employee(7369, v_ename, v_deptno, v_job);
    dbms_output.put_line('Employee name is ' || v_ename);
    dbms_output.put_line('Employee department number is ' || v_deptno);
    dbms_output.put_line('Employee job is ' || v_job);
END;

调用存储过程get_employee，同时在DECLARE语句中声明变量v_ename、v_deptno和v_job作为输出参数，将查询结果赋值给这三个变量，并使用dbms_output.put_line()函数显示结果。

存储过程的应用场景

• 在复杂的业务场景中，存储过程可以简化SQL语句的复杂度，提高开发效率。
• 存储过程可以实现重复的SQL逻辑，减少代码重复率。
• 存储过程可以封装常见的业务流程，提高程序的可读性、可维护性、可扩展性。
• 存储过程可以作为后台计算任务运行，处理海量数据，并将计算结果存储到其他表格或改变程序的状态。

示例一

使用存储过程自动创建数据库的备份和日志文件。

CREATE OR REPLACE PROCEDURE backup_database
AS
BEGIN
    INSERT INTO backup_log (backup_time, backup_type, backup_result)
    VALUES (SYSDATE, 'DATABASE', 'STARTED');
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY backup_dir AS ''/var/backup''';
    EXECUTE IMMEDIATE 'BACKUP DATABASE TO DIRECTORY backup_dir';
    INSERT INTO backup_log (backup_time, backup_type, backup_result)
    VALUES (SYSDATE, 'DATABASE', 'SUCCESS');
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO backup_log (backup_time, backup_type, backup_result)
        VALUES (SYSDATE, 'DATABASE', 'FAILED');
        RAISE;
END;

该存储过程首先在备份日志表格backup_log中记录当前备份的时间、备份类型和结果，随后创建备份目录并对数据库做备份，最后更新备份日志表格中的结果。如果备份过程中遇到错误，存储过程会回滚并抛出异常，同时在备份日志表格中记录备份失败的结果。

示例二

使用存储过程将所有员工的薪水增加10%。

CREATE OR REPLACE PROCEDURE increase_salary
IS
BEGIN
    UPDATE employee
    SET salary = salary * 1.1;
    COMMIT;
END;

该存储过程直接对员工表格employee做更新操作，并增加所有员工的薪水10%。在结束前，使用COMMIT命令将结果保存，使修改生效。