oracle存储过程与触发器

存储过程和触发器是 Oracle 数据库中的强大功能，可以提高数据库的自动化和灵活性。

存储过程：

存储过程是一组针对数据库操作的预定义的 SQL 语句，它可以存储在数据库中，供以后调用使用。在 Oracle 中，存储过程用 PL/SQL 语言编写，它是一种结合了 SQL 和程序设计的语言。PL/SQL 具有很强的数据操作能力和过程控制能力，可以方便地编写出高效的存储过程来。

存储过程的好处：

增加数据库的执行效率，减少网络通信的开销。因为存储过程已经被预先编译和优化，所以在执行时不需要反复进行解析和优化，可以直接调用执行。

通过参数来实现动态化的操作，不仅可以简化代码，还可以避免 SQL 注入等风险。

存储过程的创建和执行：

创建存储过程：在 Oracle 中，创建存储过程需要使用 CREATE PROCEDURE 语句。例如创建一个简单的存储过程，它接受两个参数并输出它们的和，可以使用 CREATE OR REPLACE PROCEDURE add_nums( num1 IN NUMBER, num2 IN NUMBER, sum OUT NUMBER) IS BEGIN sum := num1 + num2; END add_nums;。

执行存储过程：在 Oracle 中，执行存储过程需要使用 EXECUTE 或 EXECUTE IMMEDIATE 语句。例如，执行上述示例程序，可以使用如下的语句：DECLARE result NUMBER; BEGIN add_nums(10, 20, result); DBMS_OUTPUT.PUT_LINE('The sum is: ’ || result); END ;。

参数类型：在存储过程中，参数可以是输入参数、输出参数或双向参数。输入参数指定存储过程的输入，输出参数指定存储过程的输出，双向参数既可以进行输入，也可以进行输出。声明参数类型的方法如下：(param_name [IN | OUT | IN OUT] param_type [,…])。例如创建一个包含三个参数的存储过程 my_proc，第一个参数 num 是输入参数，第二个参数 str 是双向参数，第三个参数 cur 是输出参数，可以使用 CREATE OR REPLACE PROCEDURE my_proc ( num IN NUMBER, str IN OUT VARCHAR2, cur OUT SYS_REFCURSOR) IS BEGIN – 逻辑实现 END my_proc;。

纪录集处理：用存储过程来操作数据时常常需要返回查询结果列表。Oracle 提供了两种类型的纪录集：游标和 PL/SQL 表。

游标：游标是一种返回结果集的数据结构，它可以遍历查询结果。游标可以是显式或隐式的，显式游标需要声明一个游标变量，并在代码中打开和关闭它，隐式游标则由 Oracle 自动创建和管理。例如创建一个演示如何使用游标的存储过程，可以使用 CREATE OR REPLACE PROCEDURE get_employee( id_list IN VARCHAR2, emp_cur OUT SYS_REFCURSOR) IS BEGIN OPEN emp_cur FOR ‘SELECT * FROM employees WHERE id IN (’ || id_list || ‘)’; END get_employee;。

PL/SQL 表：PL/SQL 表是一种类似于数组的数据结构，它可以存储一组值。在 Oracle 中，可以在存储过程中声明和使用 PL/SQL 表。例如创建一个名为 my_package 的包，其中声明了一个名为 num_list 的 PL/SQL 表类型和一个使用该类型的存储过程 sum_nums，可以使用 CREATE OR REPLACE PACKAGE my_package IS TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER; PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER); END my_package; CREATE OR REPLACE PACKAGE BODY my_package IS PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER) IS total NUMBER := 0; BEGIN FOR indx IN 1…nums.COUNT LOOP total := total + nums(indx); END LOOP; sum := total; END sum_nums; END my_package;。

触发器：

触发器类似与存储过程，都是为了实现特殊功能而执行的代码块。触发器不允许用户显示传递参数，不能够返回参数值，不允许用户调用触发器。触发器只是在 Oracle 合适的时间自动调用，非常类似于面向编程中的拦截器。

触发器的类型：

按照触发事件类型、对象不同分为：语句触发器，行触发器，Instead of 触发器，系统事件触发器，用户触发器。

前三者针对一般数据表有 insert、update、delet、DML 操作，后两者为系统事件，drop、alter、DDL 操作。DML - DML (Data Manipulation Language) 数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。DDL (Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

Oracle 中的触发器共有语句触发器、行触发器、instead of 触发器、系统事件触发器和用户事件触发器五类。

语句触发器和行级触发器的区别：

语句触发器：针对一次 DML 操作，无论影响到表多少条记录，只触发一次。

行级触发器：针对 DML 操作影响到的所有记录，均会触发一次。

instead of 触发器的主要特点：

instead of 触发器，则完全代替了 DML 操作，也就是说，执行触发器动作，而本来的 DML 操作不会执行。

替代触发器只能建立在视图上不能建立在表上。用户在视图上执行的 DML 操作将被替代触发器中的操作代替。替代触发器主要解决对不可更新视图执行更新操作时带来的问题。在定义视图时，如果视图中没有选择基础表的主键咧，或者视图中的数据来自多个基础表，那么用户将无法对这样的视图直接执行插入、修改、删除操作。这种情况下，用户可以针对是视图创建一个替代触发器，将对视图的更新操作转换为对基础表的操作。

触发器应用场景：

对于视图的更新操作可以利用 instead of 触发器进行，从而自定义如何更新源数据表。

信息汇总：当明细数据更新时，可以利用触发器实现汇总数据的更新。

数据备份：当数据更新时，可以利用触发器将原数据记录到历史数据表中，从而实现数据跟踪。

用户事件触发器：

用户事件触发器创建时不再针对单个数据库对象，而是建立在用户或用户的模式之上。

触发器的创建和使用示例：

利用触发器对在 scott.emp 表上执行的 DML 操作进行安全性检查，只有 scott 用户登录数据库后才能向该表中执行 DML 操作：

在 scott 用户下创建触发器语句：create or replace trigger tri_dm1 before insert or update or delete on scott.emp begin if user <>‘SCOTT’ then raise_application_error(-20001,‘You don’‘t have access to modify this table.’); end if; end;/。

以 system 用户连接，并对 emp 表执行 DML 操作，会触发错误。以 scott 用户连接，并对 emp 表执行 DML 操作，可以正常执行。

利用触发器进行表和备份表之间的同步复制：

在 scott 用户下创建 scott.emp 表的复本 employee：conn scott/tiger; create table employee as select * from scott.emp;。

在 scott 用户下创建能实现 scott.emp 和 employee 两表之间同步复制的 DML 触发器：create or replace trigger duplicate_empafter update or insert or delete on scott.emp for each row begin if inserting then insert into employee values (:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno); elsif deleting then delete from employee where empno=:old.empno; else update employee set empno=:new.empno,ename=:new.ename,job=:new.job,mgr=:new.mgr,hiredate=:new.hiredate,sal=:new.sal,comm=:new.comm,deptno=:new.deptno where empno=:old.empno; end if; end;/。

对 scott.emp 表进行插入、删除和更新操作，查询 scott.emp 表和 employee 表中插入、删除和更新的记录，可以看到两表同步更新。

建立触发器，对 scott.emp 表进行 DML 操作时的时间、用户进行日志记录：

建立日志表 emp_log：create table emp_log(who varchar2(30), when date, oper varchar2(10));。

在 emp 表上建立语句级触发器，将对 emp 表执行的操作记录到 emp_log 表中：create or replace trigger dm1_log after insert or update or delete on scott.emp declare oper emp_log.oper%type; begin if inserting then oper:=‘insert’; elsif deleting then oper:=‘delete’; else oper:=‘update’; end if; insert into emp_log values(user,sysdate,oper); end;/。

对 scott.emp 执行 DML 操作，查看 emp_log 表中的数据，可以看到操作记录。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_56693899/article/details/143579451