PRAGMA AUTONOMOUS_TRANSACTION

自治事务的特点:
不依赖于原有Main程序,比如Main程序中有未提交的数据,那么在自治事务中查找不到;
在自治事务中,commit或者rollback只会提交或回滚当前自治事务中的DML语句,不会影响到Main程序中的其他DML语句。

drop table t_pragma purge;
CREATE TABLE t_pragma (string VARCHAR2(25));  
      
CREATE OR REPLACE PROCEDURE p_pragma IS
PRAGMA AUTONOMOUS_TRANSACTION;  
BEGIN  
      INSERT INTO t_pragma VALUES ('pragma value');  
      COMMIT;  
END p_pragma;  
/ 
show err

CREATE OR REPLACE PROCEDURE p_main IS  
BEGIN  
     INSERT INTO t_pragma VALUES ('main value');  
      
      p_pragma; --execute 自治事务 
      
      ROLLBACK;  
END p_main;  
/ 
show err
     
rem run the main procedure  
exec p_main  
      
rem check the results  
SELECT * FROM t_pragma;  
STRING
---------------------------------------------------------------------------
pragma value

此条目发表在PLSQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s