我需要编写一个 PL/SQL 过程,在这个过程中,我需要在其自己的事务范围内调用另一个过程,并提交它,无论主事务是否失败或提交。换句话说,我需要类似的东西REQUIRES NEW
交易传播。
就像是:
procedure mainProcedure(arugements) is
begin
// some statements
nestedProcedure(someArguments);
// some other statements
end;
procedure nestedProcedure(arguments) is
begin
// start a new transaction
// some statements, lock some objects!
// commit the new transaction and release locked objects
end;
我怎样才能实现这个目标?
看一下自主交易 https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/autotransaction_pragma.htm#LNPLS01302。另请参阅演示
CREATE TABLE t (
test_value VARCHAR2(25));
CREATE OR REPLACE PROCEDURE child_block IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO t
(test_value)
VALUES
('Child block insert');
COMMIT;
END child_block;
/
CREATE OR REPLACE PROCEDURE parent_block IS
BEGIN
INSERT INTO t
(test_value)
VALUES
('Parent block insert');
child_block;
ROLLBACK;
END parent_block;
/
执行:
-- empty the test table
TRUNCATE TABLE t;
-- run the parent procedure
exec parent_block;
-- check the results
SELECT * FROM t;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)