更新时间:2024-11-01 GMT+08:00
Package支持自治事务
自治事务可以在package中的存储过程或者函数中定义,标识符为PRAGMA AUTONOMOUS_TRANSACTION,其余语法与创建package中存储过程或函数语法相同,示例如下。
--建表 drop table t2; create table t2(a int, b int); insert into t2 values(1,2); select * from t2; --创建包含自治事务的package中的存储过程和函数 CREATE OR REPLACE PACKAGE autonomous_pkg AS PROCEDURE autonomous_4(a int, b int); FUNCTION autonomous_32(a int ,b int) RETURN int; END autonomous_pkg; / CREATE OR REPLACE PACKAGE body autonomous_pkg AS PROCEDURE autonomous_4(a int, b int) AS DECLARE num3 int := a; num4 int := b; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into t2 values(num3, num4); END; FUNCTION autonomous_32(a int ,b int) RETURN int AS DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into t2 values(a, b); return 1; END; END autonomous_pkg; / --创建调用package自治事务存储过程和函数的普通存储过程 CREATE OR REPLACE PROCEDURE autonomous_5(a int, b int) AS DECLARE va int; BEGIN insert into t2 values(666, 666); autonomous_pkg.autonomous_4(a,b); va := autonomous_pkg.autonomous_32(a + 1, b + 1); rollback; END; / --调用普通存储过程 select autonomous_5(11,22); --查看表结果 select * from t2 order by a;
上述例子,最后在回滚的事务块中执行包含package自治事务的存储过程和函数,直接说明了自治事务的特性,即主事务的回滚,不会影响自治事务已经提交的内容。
父主题: 自治事务