Oracle 触发器调用存储过程|转||待研究|

最新推荐文章于 2024-10-17 18:58:27 发布

weixin_34007291

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量107

点赞数

文章标签：数据库

Oracle触发器调用存储过程资料收集整理

触发器：Trigger

存储过程：Stored Procedure

// ******************** 收集1 ********************

1.触发器写法

create or replace trigger HVM_ZTPJ_BYQ
after insert or update or delete on Xftpj_Pjjl_Byq
declare

begin

--直接写存储过程名称+;

hvm_byq_tj(1);

end HVM_ZTPJ_BYQ;

2.触发器的控制

alter table xftpj_pjjl_byq disable all triggers; --禁用触发器
alter table xftpj_pjjl_byq enable all triggers; --启用触发器

// ***************************************************

// ********************* 收集2 *********************

触发器传参数给存储过程，存储过程中有 insert tableA where id=1....
接着 update tableA where id=1.

由于insert语句不是自动提交，所以当insert语句没有 commit 的时候，update 会报“表/视图发生了变化，程序不能读它”的错误，也就是id=1这一行被insert锁住了。此时，在触发器中调用自治事务，问题得以解决。

Oracle自治事务(Autonomous Transaction)将一个主事务分割成几个子事务，在执行完子事务以后再继续主事务。这里的关键是，子事务是独立于主事务的，子事务中的Rollback 和 Commit 操作只会影响子事务中的DML操作；同样，主事务中的 Rollback 和 Commit 操作只会影响事务中的DML操作，而不会影响子事务中的操作。在子事务中已经commit的操作，不会被主事务中的rollback撤销。

制定PL/SQL程序块为自治事务可以通过在程序开头使用如下命令实现
PRAGMA AUTONOMOUS_TRANSACTION
定义自治事务必须遵循以下规则：
1.如果要被定义为自治事务的程序是匿名的，则它必须是一个最外层的程序块。
2.如果不是匿名的，则它必须是函数或者过程，或者是包含在一个中。在一个包中，只有其中的函数或过程能够定义成自治事务。整个包不能申明为自治事务。
3.一个对象的方法可以申明为自治事务
4.触发器可以申明为自治事务
5.内嵌程序块不能申明为自治事务

注意：对于一个匿名的自治事务程序块来说，只有这个块的begin和end之间的代码被看作是自治事务。

触发器代码如下：

 1 create or replace trigger trigger_main2_update
 2        before update on t_busi_main_presend2  for each row
 3          declare
 4 pragma autonomous_transaction; --声明该触发器的事务为自治事务
 5 begin 
 6           DBMS_OUTPUT.PUT_LINE(:new.SHSTATUS);
 7            if  :new.SHSTATUS='1' and :old.SHSTATUS='0'    then
 8            p_main2_mx(:new.id,:new.smscontent,:new.allcode,:new.phonetype,:new.sjtongdaoid,:new.cjr,:new.pretongdaoid,:new.clientid,:new.shr,:new.pretime,:new.cjsj,:new.shstatus,:new.kouchucnt,:new.dxlx,:new.allcount); --调用存储过程，并给存储过程传参数
 9             :new.SENDSTATUS:='1' ;
10            end if;        
11       commit;
12 end;
13 
14

运用AT(autonomous_transaction)时,有一些注意事项,简单列举如下:
1.     在匿名PL/SQL块中,只有顶级的匿名PL/SQL块可以被设为AT
2.     如果AT试图访问被MT控制的资源,可能有deadlock 发生.
3.     Package 不能被声明为AT,只有 package 所拥有的 function 和 procedure 才能声明为AT
4.     AT程序必须以commit 或 rollback 结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back