oracle自治事务(PRAGMA AUTONOMOUS_TRANSACTION)
2024-10-08 21:42:20
这段时间遇到一个问题,程序里明明插入了一条记录,但在后边的一段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
With Pragma Autonomous Transaction
- 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.
With Pragma Autonomous Transaction
- 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
转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7675800
最新文章
- Alpha、Beta、RC、GA版本的区别 ZT
- iOS开发——UI基础-按钮内边距,图片拉伸
- 泛型、Linq 查询使用
- 32位Windows7上8G内存使用感受+xp 32位下使用8G内存 (转)
- Winform 拦截最小化、最大化、关闭事件【整理】
- ABAP开发基础知识:内表(Internal Table)
- Python学习(七) 流程控制if语句
- USB Mass Storage大容量存储的基本知识
- EF Code First Migrations, 支持MySQL
- VS中为类,函数代码自动添加版权注释信息
- width:100vh有感而发
- html5之range
- 再续session和cookie (网络整理)
- Windows下安装kibana
- 纯js轮播图
- 用eclipse+svn插件,上传新项目到svn服务器
- DH03-单一职责原则
- SQL Server 获取日期是星期周几(默认从周日开始到周六 1-7)
- vue-router原理分析
- 如何查看用pip命令安装的软件的安装路径