创建具有自动提交功能的oracle表 [英] Create oracle table with auto commit on
问题描述
这是场景, 我正在编写一个在多个表上执行DML(insert,delete)操作的过程.如果出现异常,我需要回滚除一个表以外的大多数表中的数据.
Here is the scenario, I am writing a procedure which performs DML(insert,delete) operations on multiple tables. In case of exception I need to rollback data from most of the tables except one.
示例:
程序{
1 Insert into table1
2 Delete from table2
3 Insert into Table3
4 Insert into table4
Commit;
例外
Rollback;
}
如果第4个插入中有任何异常,我需要回滚除第3个以外的所有先前的事务. 我知道,提交/回滚不能那样工作.但是也许在创建table3时有办法,我可以设置一些参数,以便自动提交table3上的每个INSERT语句.
If there is any exception in 4th Insert, I need to rollback all previous transaction except 3rd one. I know, commit/rollback doesn't work like that. But maybe is there way while creating table3, I can set some parameter so that every INSERT statement on table3 will be auto commited.
推荐答案
可以使用自主交易.
CREATE TABLE t1 (x INTEGER PRIMARY KEY);
CREATE TABLE t2 (x INTEGER);
CREATE TABLE t3 (x INTEGER);
CREATE TABLE t4 (x INTEGER REFERENCES t1(x));
然后运行此匿名PL/SQL.仔细查看,本地过程upd_table_3
在声明时未运行.在主BEGIN
块中调用时运行:
Then run this anononymous PL/SQL. Look carefully, the local procedure upd_table_3
isn't run when it's declared. It's run when called within the main BEGIN
block:
DECLARE
PROCEDURE upd_table_3 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO t3 VALUES (3);
COMMIT;
END;
BEGIN
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
upd_table_3;
INSERT INTO t4 VALUES (4);
COMMIT;
END;
/
在第四个插入ORA-02291
的过程中,该过程应故意失败.我是这样做的.然后
The procedure should fail intentionally on the 4th insert with ORA-02291
. I made it this way. Then
ROLLBACK;
除t3
以外,所有表都应回滚,我们在自动事务中进行了插入.
All the tables should be rolled back except t3
, which we did the insert in an autonomous transaction.
SELECT * FROM t1;
no rows selected
SELECT * FROM t2;
no rows selected
SELECT * FROM t3;
X
----------
3
SELECT * FROM t4;
no rows selected
这篇关于创建具有自动提交功能的oracle表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!