PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
来源:互联网 发布:jackson json忽略空值 编辑:程序博客网 时间:2024/05/17 21:45
http://blog.csdn.net/pan_tian/article/details/7675800
这段时间遇到一个问题,程序里明明插入了一条记录,但在后边的一段Procedure中却查不到刚刚插入的记录,最后发现这个Procedure的定义中加入了PRAGMA AUTONOMOUS_TRANSACTION。
PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”(翻译的还算好理解),对于定义成自治事务的Procedure,实际上相当于一段独立运行的程序段,这段程序不依赖于主程序,也不干涉主程序
自治事务的特点
第一,这段程序不依赖于原有Main程序,比如Main程序中有未提交的数据,那么在自治事务中是查找不到的。
第二,在自治事务中,commit或者rollback只会提交或回滚当前自治事务中的DML,不会影响到Main程序中的DML。
Autonomous Transaction Demo 1
Without Pragma Autonomous Transaction
- CREATE TABLE t (
- test_value VARCHAR2(25));
- CREATE OR REPLACE PROCEDURE child_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Child block insert');
- COMMIT;
- END child_block;
- /
- CREATE OR REPLACE PROCEDURE parent_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Parent block insert');
- child_block;
- ROLLBACK;
- END parent_block;
- /
- -- run the parent procedure
- exec parent_block
- -- check the results
- SELECT * FROM t;
- Output:
- Parent block insert
- Child block insert
- CREATE OR REPLACE PROCEDURE child_block IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Child block insert');
- COMMIT;
- END child_block;
- /
- CREATE OR REPLACE PROCEDURE parent_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Parent block insert');
- child_block;
- ROLLBACK;
- END parent_block;
- /
- -- empty the test table
- TRUNCATE TABLE t;
- -- run the parent procedure
- exec parent_block;
- -- check the results
- SELECT * FROM t;
- Output:
- Child block insert
Autonomous Transaction Demo 2
Without Pragma Autonomous Transaction- DROP TABLE t;
- CREATE TABLE t (testcol NUMBER);
- CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
- i INTEGER;
- BEGIN
- SELECT COUNT(*)
- INTO i
- FROM t;
- RETURN i;
- END howmanyrows;
- /
- CREATE OR REPLACE PROCEDURE testproc IS
- a INTEGER;
- b INTEGER;
- c INTEGER;
- BEGIN
- SELECT COUNT(*)
- INTO a
- FROM t;
- INSERT INTO t VALUES (1);
- COMMIT;
- INSERT INTO t VALUES (2);
- INSERT INTO t VALUES (3);
- b := howmanyrows;
- INSERT INTO t VALUES (4);
- INSERT INTO t VALUES (5);
- INSERT INTO t VALUES (6);
- COMMIT;
- SELECT COUNT(*)
- INTO c
- FROM t;
- dbms_output.put_line(a);
- dbms_output.put_line(b);
- dbms_output.put_line(c);
- END testproc;
- /
- set serveroutput on
- exec testproc
- Output:
- 0
- 3
- 6
- Total execution time 2.782 sec.
- CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
- i INTEGER;
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- SELECT COUNT(*)
- INTO i
- FROM t;
- RETURN i;
- END howmanyrows;
- /
- -- empty the test table
- TRUNCATE TABLE t;
- exec testproc;
- Output:
- 0
- 1
- 6
0 0
- PRAGMA AUTONOMOUS_TRANSACTION自治事务
- 自治事务 PRAGMA AUTONOMOUS_TRANSACTION
- PRAGMA AUTONOMOUS_TRANSACTION自治事务
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
- oracle 自治事务 PRAGMA AUTONOMOUS_TRANSACTION
- PRAGMA AUTONOMOUS_TRANSACTION自治事务应用
- PRAGMA AUTONOMOUS_TRANSACTION oracle 自治事务
- oracle 自治事务 PRAGMA AUTONOMOUS_TRANSACTION
- PRAGMA AUTONOMOUS_TRANSACTION oracle 自治事务
- Oracle 自治事务 pragma autonomous_transaction
- oracle自治事务(PRAGMA AUTONOMOUS_TRANSACTION)
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)【整理】
- PRAGMA AUTONOMOUS_TRANSACTION(oracle自治事务)
- ORACLE中的自治事务 PRAGMA AUTONOMOUS_TRANSACTION
- 关于自治事务pragma autonomous_transaction的转载
- Yii的where方法使用大全
- SQL语句Left join 中On和Where的用法区别
- NBUT 1463 内部收益率 二分
- Sevlet的生命周期
- JEEWX微信企业号管家,开源免费,1.0版本发布
- PRAGMA AUTONOMOUS_TRANSACTION(自治事务)
- solr更新schema.xml后,重新把配置文件注入到zookeeper
- FZU 2125 简单的等式
- ios基础知识整理1
- cntk
- jboss4.2.3漏洞修复
- 字体,截图,获取图库照片
- iOS - OC和网页JS的交互
- nagios插件,通过nrpe远程调用和本地执行结果不一致