【IT168技术】首先介绍一些相关的基本概念
嵌套事务(Nested Transaction):指在一个Parent事务中嵌套的一个或多个Sub Transaction.并且主事务与其相互影响,这种事务就称为嵌套事务。以Commit作为事务的结束。
自治事务(Autonomous Transaction):指在function,procedure等subprograms中对事务进行自治管理,当在别的pl/sql block里去调用这些subprograms的时候这些subprograms并不随着父pl/sql block的失败而回滚,而是自己管自己commit。以Commit作为事务的结束。自治事务常用于写入LOG或TRAC信息便于查找错误。
嵌套事务的运用(Nested Transaction)
预备Create Table:
1.create table TEST_POLICY
2.(
3. POLICY_CODE VARCHAR2(20),
4. POLICY_TYPE CHAR(1)
5.)
2.(
3. POLICY_CODE VARCHAR2(20),
4. POLICY_TYPE CHAR(1)
5.)
创建一个嵌套事务的procedure:
1.Procedure P_Insert_Policy(I_Policy_code varchar2(20),
2. I_Policy_type char(1)) as
3. cnt number :=0;
4. begin
5. select count(1) into cnt from Test_Policy;
6. Dbms_Output.put_line('records of the test_policy is '|| cnt);
7.
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);
9. commit;--commit in nested transaction
10. end P_Insert_Policy;
11.--call procedure used in nested transaction
12. PROCEDURE TEST_PL_SQL_ENTRY(
13. I_POL_ID IN VARCHAR2,
14. O_SUCC_FLG OUT VARCHAR2) AS
15. strSql varchar2(500);
16. cnt number := 0;
17. BEGIN
18. delete from test_policy;
19. commit;
20. insert into test_policy values('2010042101', '1');
21. select count(1) into cnt from Test_Policy;
22. Dbms_Output.put_line('records of the test_policy is '|| cnt);
23. --call nested transaction
24. P_Insert_Policy('2010042102', '2');
25. rollback;--rollback data for all transactions
26. commit;--master transaction commit
27. select count(1) into cnt from Test_Policy;
28. Dbms_Output.put_line('records of the test_policy is '|| cnt);
29. rollback;
30.
31. select count(1) into cnt from Test_Policy;
32. Dbms_Output.put_line('records of the test_policy is '|| cnt);
33.
34.END TEST_PL_SQL_ENTRY;
35.
36.=>run Pl/sql:
37.records of the test_policy is 1 –-主事务中的操作已经commit
38.records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
39.records of the test_policy is 2 –-Nested transaction 已经Commit
40.records of the test_policy is 2 –-Nested transaction对主事务有影响。
2. I_Policy_type char(1)) as
3. cnt number :=0;
4. begin
5. select count(1) into cnt from Test_Policy;
6. Dbms_Output.put_line('records of the test_policy is '|| cnt);
7.
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);
9. commit;--commit in nested transaction
10. end P_Insert_Policy;
11.--call procedure used in nested transaction
12. PROCEDURE TEST_PL_SQL_ENTRY(
13. I_POL_ID IN VARCHAR2,
14. O_SUCC_FLG OUT VARCHAR2) AS
15. strSql varchar2(500);
16. cnt number := 0;
17. BEGIN
18. delete from test_policy;
19. commit;
20. insert into test_policy values('2010042101', '1');
21. select count(1) into cnt from Test_Policy;
22. Dbms_Output.put_line('records of the test_policy is '|| cnt);
23. --call nested transaction
24. P_Insert_Policy('2010042102', '2');
25. rollback;--rollback data for all transactions
26. commit;--master transaction commit
27. select count(1) into cnt from Test_Policy;
28. Dbms_Output.put_line('records of the test_policy is '|| cnt);
29. rollback;
30.
31. select count(1) into cnt from Test_Policy;
32. Dbms_Output.put_line('records of the test_policy is '|| cnt);
33.
34.END TEST_PL_SQL_ENTRY;
35.
36.=>run Pl/sql:
37.records of the test_policy is 1 –-主事务中的操作已经commit
38.records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
39.records of the test_policy is 2 –-Nested transaction 已经Commit
40.records of the test_policy is 2 –-Nested transaction对主事务有影响。
将上面的nested transaction的procedure修改一下,不需要commit:
1.Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,
2. I_Policy_type t_contract_master.policy_type%type) as
3. cnt number :=0;
4. begin
5. select count(1) into cnt from Test_Policy;
6. Dbms_Output.put_line('records of the test_policy is '|| cnt);
7.
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);
9. --commit;
10. end P_Insert_Policy;
11. PROCEDURE TEST_PL_SQL_ENTRY(
12. I_POL_ID IN VARCHAR2,
13. O_SUCC_FLG OUT VARCHAR2) AS
14. strSql varchar2(500);
15. cnt number := 0;
16. BEGIN
17. delete from test_policy;
18. commit;
19. insert into test_policy values('2010042101', '1');
20. select count(1) into cnt from Test_Policy;
21. Dbms_Output.put_line('records of the test_policy is '|| cnt);
22.
23. P_Insert_Policy('2010042102', '2');
24. rollback;
25. commit;
26. select count(1) into cnt from Test_Policy;
27. Dbms_Output.put_line('records of the test_policy is '|| cnt);
28. rollback;
29.
30. select count(1) into cnt from Test_Policy;
31. Dbms_Output.put_line('records of the test_policy is '|| cnt);
32.
33.END TEST_PL_SQL_ENTRY;
34.Run Pl/Sql=>
35.结果是:
36.records of the test_policy is 1 –-主事务中的操作已经commit
37.records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
38.records of the test_policy is 0 –-Nested transaction 的数据被主事务rollback.
39.records of the test_policy is 0
Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,
I_Policy_type t_contract_master.policy_type%type) as
cnt number :=0;
begin
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
Insert into Test_Policy values(I_Policy_code, I_Policy_type);
--commit;
end P_Insert_Policy;
PROCEDURE TEST_PL_SQL_ENTRY(
I_POL_ID IN VARCHAR2,
O_SUCC_FLG OUT VARCHAR2) AS
strSql varchar2(500);
cnt number := 0;
BEGIN
delete from test_policy;
commit;
insert into test_policy values('2010042101', '1');
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
P_Insert_Policy('2010042102', '2');
rollback;
commit;
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
rollback;
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
END TEST_PL_SQL_ENTRY;
Run Pl/Sql=>
结果是:
records of the test_policy is 1 –-主事务中的操作已经commit
records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
records of the test_policy is 0 –-Nested transaction 的数据被主事务rollback.
records of the test_policy is 0
2. I_Policy_type t_contract_master.policy_type%type) as
3. cnt number :=0;
4. begin
5. select count(1) into cnt from Test_Policy;
6. Dbms_Output.put_line('records of the test_policy is '|| cnt);
7.
8. Insert into Test_Policy values(I_Policy_code, I_Policy_type);
9. --commit;
10. end P_Insert_Policy;
11. PROCEDURE TEST_PL_SQL_ENTRY(
12. I_POL_ID IN VARCHAR2,
13. O_SUCC_FLG OUT VARCHAR2) AS
14. strSql varchar2(500);
15. cnt number := 0;
16. BEGIN
17. delete from test_policy;
18. commit;
19. insert into test_policy values('2010042101', '1');
20. select count(1) into cnt from Test_Policy;
21. Dbms_Output.put_line('records of the test_policy is '|| cnt);
22.
23. P_Insert_Policy('2010042102', '2');
24. rollback;
25. commit;
26. select count(1) into cnt from Test_Policy;
27. Dbms_Output.put_line('records of the test_policy is '|| cnt);
28. rollback;
29.
30. select count(1) into cnt from Test_Policy;
31. Dbms_Output.put_line('records of the test_policy is '|| cnt);
32.
33.END TEST_PL_SQL_ENTRY;
34.Run Pl/Sql=>
35.结果是:
36.records of the test_policy is 1 –-主事务中的操作已经commit
37.records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
38.records of the test_policy is 0 –-Nested transaction 的数据被主事务rollback.
39.records of the test_policy is 0
Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,
I_Policy_type t_contract_master.policy_type%type) as
cnt number :=0;
begin
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
Insert into Test_Policy values(I_Policy_code, I_Policy_type);
--commit;
end P_Insert_Policy;
PROCEDURE TEST_PL_SQL_ENTRY(
I_POL_ID IN VARCHAR2,
O_SUCC_FLG OUT VARCHAR2) AS
strSql varchar2(500);
cnt number := 0;
BEGIN
delete from test_policy;
commit;
insert into test_policy values('2010042101', '1');
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
P_Insert_Policy('2010042102', '2');
rollback;
commit;
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
rollback;
select count(1) into cnt from Test_Policy;
Dbms_Output.put_line('records of the test_policy is '|| cnt);
END TEST_PL_SQL_ENTRY;
Run Pl/Sql=>
结果是:
records of the test_policy is 1 –-主事务中的操作已经commit
records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
records of the test_policy is 0 –-Nested transaction 的数据被主事务rollback.
records of the test_policy is 0