Oracle 存储过程中的事务处理

Oracle存储过程中的事务处理

当在SQL*Plus中进行操作时，用户可以使用COMMIT语句将在事务中的所有操作“保存”到数据库中。如果用户需要撤销所有的操作，则可以使用ROLLBACK语句回退事务中未提交的操作，使数据库返回到事务处理开始前的状态。在PL/SQL过程中，不仅可以包括插入和更新这类的DML操作，还可以包括事务处理语句COMMIT和ROLLBACK。

Oracle支持事务的嵌套，即在事务处理中进行事务处理。在嵌套的事务处理过程中，子事务可以独立于父事务处理进行提交和回滚。对于过程而言，每个过程就相当于一个子事务，用户可以在自己事务处理的任何地方调用该过程，并且无论父事务是提交还是回滚，用户都可以确保过程中的子事务被执行。

下面通过一个示例演示过程中的事务处理。

（1）以用户SCOTT身份连接到数据库，并建立两个表TEMP和LOG_TABLE。

SQL> create table temp(n number);

表已创建。

SQL> create table log_table(

2 username varchar2(20),

3 message varchar2(4000));

表已创建。

（2）建立一个存储过程INSERT_INTO_LOG，用于向表LOG_TABLE添加记录。

SQL> create or replace procedure insert_into_log(msg_param varchar2) is

2 pragma autonomous_transaction;

3 begin

4 insert into log_table(username,message)

5 values(user,msg_param);

6 commit;

7 end insert_into_log;

8 /

过程已创建。

其中，PRAGMA AUTONOMOUS_TRANSACTION语句表示自动开始一个自治事务，实际上该语句也可以省略。

（3）在匿名程序块中调用INSERT_INTO_LOG过程向LOG_TABLE表中添加数据，并使用INSERT语句向表TEMP添加数据。

SQL> begin

2 insert_into_log('添加数据到TEMP表之前调用');

3 insert into temp

4 values(1);

5 insert_into_log('添加数据到TEMP表之后调用');

6 rollback;

7 end;

8 /

PL/SQL 过程已成功完成。

由于在INSERT_INTO_LOG过程中使用COMMIT语句提交了过程中的事务，因此当匿名程序块中的父事务回滚时，存储过程已经向LOG_TABLE表提交了添加的数据。这里需要注意，如果在中间使用INSERT语句添加数据，那么该INSERT语句是否会随子过程中事务的提交而被提交呢？下面对表中的数据进行分析：

SQL> select * from temp;

未选定行

SQL> select * from log_table;

USERNAME MESSAGE

-------------------- --------------------------------------

SCOTT 添加数据到TEMP表之前调用

SCOTT 添加数据到TEMP表之后调用

从结果中可以看出，在TEMP表中并没有记录，这说明INSERT语句被撤销了。而在表LOG_TABLE中则包含了两条记录，这说明过程中的子事务已经被提交。即过程中的子事务与调用父事务可以互相不干涉地运行。