过程级原子性是什么?
以下人为改变数据库的行为:也就是让它无视错误
ORACLE会把存储过程调用处理成原子语句
测试:
SQL> create or replace procedure p
2 as
3 begin
4 insert into t values (1);
5 insert into t values (-1);
6 end;
7 /
Warning: Procedure created with compilation errors.
SQL> create or replace procedure p
2 as
3 begin
4 insert into s1 values (1);
5 insert into s1 values (-1);
6 end;
7 /
Procedure created.
SQL> select * from s;
CNT
----------
0
SQL> select * from s1;
no rows selected
SQL> begin
2 p;
3 end;
4 /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C006297) violated
ORA-06512: at "SCOTT.P", line 5
ORA-06512: at line 2
SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT
----------
0
SQL>
ORACLE内部执行以上过程的伪代码如下:
savepoint xxx
execute p;
if failed rollback to savepoint xxx
以下人为改变数据库的行为:也就是让它无视错误
测试:
SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT
----------
0
SQL> begin
2 p;
3 exception
4 when others then null;
5 end;
6 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL procedure successfully completed.
SQL> select * from s1;
X
----------
1
SQL> select * from s;
CNT
----------
1
SQL>
这次执行成功了,为什么,因为这样执行过程会自动捕获错误并且忽略错误,IF ERROR ROLLBACK TO SAVEPOINT这样的语句没有起作用,所以就运行成功了
。这样的写法是不正常的。因为它改变了数据库本来的表现。
以下将语句级原子性恢复为整个PLSQL块级原子性
SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT
----------
0
SQL> begin
2
3 savepoint sp1;
4 p;
5 exception when others then
6 rollback to sp1;
7 end;
8 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL procedure successfully completed.
SQL> select * from s1;
no rows selected
SQL> select * from s;
CNT
----------
0
SQL>
这种也是不好的写法
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731246/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-731246/