可以这样回退部分事务来实现:这是通过一种称为“检查点”的机制实现的,如SQL>UPDATE TABLE_NAME SET AA=1000 WHERE COL1='AA'; SAVEPOINT A;UPDATE TABLE_NAME SET AA=1500 WHERE COL1='BB'; SAVEPOINT B;ROLLBACK TO SAVEPOINT A;
测试环境2K+ORACLE9ISQL> CREATE TABLE A ( A VARCHAR2Table created.SQL> INSERT INTO A VALUES('33');1 row created.SQL> C/33/44 1* INSERT INTO A VALUES('44') SQL> /1 row created.SQL> SELECT * FROM A;A ---- 33 44SQL> SAVEPOINT AAA;Savepoint created.SQL> INSERT INTO A VALUES('55');1 row created.SQL> ROLLBACK TO SAVEPOINT AAA;Rollback complete.SQL> SELECT * FROM A;A ---- 33 44SQL> ROLLBACK;Rollback complete.SQL> SELECT * FROM A;no rows selected
你想只提交那个函数本身的操作是不可能的 解决办法如楼上,在第一个函数前设置检查点,然后再你想做全部回滚得地方ROLLBACK TO SAVEPOINT...
最后贴一个使用自治事务的例子,结贴: declare procedure local is pragma AUTONOMOUS_TRANSACTION; begin insert into emp values ('New Record','1'); commit; end; begin delete from emp; local; rollback; end;
1* INSERT INTO A VALUES('44')
SQL> /1 row created.SQL> SELECT * FROM A;A
----
33
44SQL> SAVEPOINT AAA;Savepoint created.SQL> INSERT INTO A VALUES('55');1 row created.SQL> ROLLBACK TO SAVEPOINT AAA;Rollback complete.SQL> SELECT * FROM A;A
----
33
44SQL> ROLLBACK;Rollback complete.SQL> SELECT * FROM A;no rows selected
解决办法如楼上,在第一个函数前设置检查点,然后再你想做全部回滚得地方ROLLBACK TO SAVEPOINT...
declare
procedure local is pragma AUTONOMOUS_TRANSACTION;
begin
insert into emp values ('New Record','1');
commit;
end; begin
delete from emp;
local;
rollback;
end;