分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
Autonomous transactions allow you to leave the context of the callingtransaction, perform an independant transaction, and return to thecalling transaction without affecting it's state. The autonomoustransaction has no link to the calling transaction, so only commiteddata can be shared by both transactions.
自治事务允许你离开调用的事务上下文,执行一个独立的事务,然后返回调用的事务而不会影响到调用事务的状态。自治事务和调用事务不同,只有提交的事务才会在事务见共享。
The following types of PL/SQL blocks can be defined as autonomous transactions:
以下的PL/SQL代码块可以定义为自治事务。
* Stored procedures and functions. 存储过程和函数
* Local procedures and functions defined in a PL/SQL declaration block. 定义在声明块里的本地存储过程和函数
* Packaged procedures and functions. 打包的存储过程和函数
* Type methods. 类型方法
* Top-level anonymous blocks. 顶层的匿名块
The easiest way to understand autonomous transactions is to seethem in action. To do this, we create a test table and populate it withtwo rows. Notice that the data is not commited.
最简单的理解自治事务的方法是查看他们的行为。我们创建一个测试表格,然后放入2行数据,注意数据没有提交。
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8 rows using an anonymous block declared asan autonomous transaction, which contains a commit statement.
下一步,我们使用匿名的自治事务块插入另外8行数据,同时提交。
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
和预想的一样,我们得到了10行数据。如果我们执行一个回滚(rollback)语句,我们得到了如下的结果
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction) have beenrolled back, while the rows inserted by the autonomous transactionsremain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compilerdirective made the anonymous block run in its own transaction, so theinternal commit statement did not affect the calling session. As aresult rollback was still able to affect the DML issued by the currentstatement.
被我们当前事务插入的2行数据被回滚了,而被自治事务插入的数据继续存在。编译描述符 PRAGMA AUTONOMOUS_TRANSACTION 使得自治块在自己的事务里运行,所以内部的提交语句不会影响调用方的事务。
Autonomous transactions are commonly used by error loggingroutines, where the error messages must be preserved, regardless of thethe commit/rollback status of the transaction. For example, thefollowing table holds basic error messages.
自治事务一般用于日常的错误日志,错误信息必须保留,无论事务是提交还是回滚。例如下面的表保存了基本的错误信息。
CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
log_timestamp TIMESTAMP NOT NULL,
error_message VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);
CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
我们定义了一个自治事务的存储过程来记录错误信息
CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
下面的代码强制产生一个错误,被捕获且记录到日志
BEGIN
INSERT INTO at_test (id, description)
VALUES (998, 'Description for 998');
-- Force invalid insert.
INSERT INTO at_test (id, description)
VALUES (999, NULL);
EXCEPTION
WHEN OTHERS THEN
log_errors (p_error_message => SQLERRM);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test WHERE id >= 998;
no rows selected
SELECT * FROM error_logs;
ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")
1 row selected.
SQL>
From this we can see that the LOG_ERRORS transaction was separateto the anonymous block. If it weren't, we would expect the first insertin the anonymous block to be preserved by the commit statement in theLOG_ERRORS procedure.
由此可知,LOG_ERRORS事务被分割为自治块。如果不是,我们可以期待在自治块插入的第一个数据被LOG_ERRORS存储过程的提交语句后保留。
Be careful how you use autonomous transactions. If they are usedindiscriminately they can lead to deadlocks, and cause confusion whenanalyzing session trace. To hammer this point home, here's a quote fromTom Kyte posted on my blog (here):
小心你使用自治存储过程的方式。如果你胡乱使用,可能会引起死锁,同时在分析跟踪事务时引起冲突。下面是Tom Kyte在我的博客里提供的一些建议:
... in 999 times out of 1000, if you find yourself "forced" touse an autonomous transaction - it likely means you have a serious dataintegrity issue you haven't thought about.
Where do people try to use them?
* in that trigger that calls a procedure that commits (notan error logging routine). Ouch, that has to hurt when you rollback.
* in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*
Error logging - OK.
Almost everything else - not OK.
自治事务允许你离开调用的事务上下文,执行一个独立的事务,然后返回调用的事务而不会影响到调用事务的状态。自治事务和调用事务不同,只有提交的事务才会在事务见共享。
The following types of PL/SQL blocks can be defined as autonomous transactions:
以下的PL/SQL代码块可以定义为自治事务。
* Stored procedures and functions. 存储过程和函数
* Local procedures and functions defined in a PL/SQL declaration block. 定义在声明块里的本地存储过程和函数
* Packaged procedures and functions. 打包的存储过程和函数
* Type methods. 类型方法
* Top-level anonymous blocks. 顶层的匿名块
The easiest way to understand autonomous transactions is to seethem in action. To do this, we create a test table and populate it withtwo rows. Notice that the data is not commited.
最简单的理解自治事务的方法是查看他们的行为。我们创建一个测试表格,然后放入2行数据,注意数据没有提交。
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8 rows using an anonymous block declared asan autonomous transaction, which contains a commit statement.
下一步,我们使用匿名的自治事务块插入另外8行数据,同时提交。
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
和预想的一样,我们得到了10行数据。如果我们执行一个回滚(rollback)语句,我们得到了如下的结果
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction) have beenrolled back, while the rows inserted by the autonomous transactionsremain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compilerdirective made the anonymous block run in its own transaction, so theinternal commit statement did not affect the calling session. As aresult rollback was still able to affect the DML issued by the currentstatement.
被我们当前事务插入的2行数据被回滚了,而被自治事务插入的数据继续存在。编译描述符 PRAGMA AUTONOMOUS_TRANSACTION 使得自治块在自己的事务里运行,所以内部的提交语句不会影响调用方的事务。
Autonomous transactions are commonly used by error loggingroutines, where the error messages must be preserved, regardless of thethe commit/rollback status of the transaction. For example, thefollowing table holds basic error messages.
自治事务一般用于日常的错误日志,错误信息必须保留,无论事务是提交还是回滚。例如下面的表保存了基本的错误信息。
CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
log_timestamp TIMESTAMP NOT NULL,
error_message VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);
CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
我们定义了一个自治事务的存储过程来记录错误信息
CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
下面的代码强制产生一个错误,被捕获且记录到日志
BEGIN
INSERT INTO at_test (id, description)
VALUES (998, 'Description for 998');
-- Force invalid insert.
INSERT INTO at_test (id, description)
VALUES (999, NULL);
EXCEPTION
WHEN OTHERS THEN
log_errors (p_error_message => SQLERRM);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test WHERE id >= 998;
no rows selected
SELECT * FROM error_logs;
ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")
1 row selected.
SQL>
From this we can see that the LOG_ERRORS transaction was separateto the anonymous block. If it weren't, we would expect the first insertin the anonymous block to be preserved by the commit statement in theLOG_ERRORS procedure.
由此可知,LOG_ERRORS事务被分割为自治块。如果不是,我们可以期待在自治块插入的第一个数据被LOG_ERRORS存储过程的提交语句后保留。
Be careful how you use autonomous transactions. If they are usedindiscriminately they can lead to deadlocks, and cause confusion whenanalyzing session trace. To hammer this point home, here's a quote fromTom Kyte posted on my blog (here):
小心你使用自治存储过程的方式。如果你胡乱使用,可能会引起死锁,同时在分析跟踪事务时引起冲突。下面是Tom Kyte在我的博客里提供的一些建议:
... in 999 times out of 1000, if you find yourself "forced" touse an autonomous transaction - it likely means you have a serious dataintegrity issue you haven't thought about.
Where do people try to use them?
* in that trigger that calls a procedure that commits (notan error logging routine). Ouch, that has to hurt when you rollback.
* in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*
Error logging - OK.
Almost everything else - not OK.
给我老师的人工智能教程打call!http://blog.csdn.net/jiangjunshow
![这里写图片描述](https://i-blog.csdnimg.cn/blog_migrate/2e19ffb3303a490d20ea5d00dd397156.gif)