Oracle Self-service real-world use cases

As below, create a new two stored procedure:

In the master autonomy transaction, we insert a record, then in the autonomous transaction, we look at the number of rows in the table, then try to insert three records, view the number of rows, finally rollback the number of rows, and finally return to the main transaction to see the number of rows.

1, the following code:

--Mastertransaction PROCEDURE P_TEST_AT_AND_MT is CNT number:= -1; BEGIN INSERT into msg VALUES ('Father Record'); SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of rows after the primary transaction is inserted into a row:'||CNT);    P_test_at_and_mt_son; Dbms_output.put_line ('number of rows for the primary transaction after the child autonomy transaction is run:'||CNT); --COMMIT;  END P_TEST_AT_AND_MT; --test the autonomous transaction (sub-autonomous) PROCEDURE P_test_at_and_mt_son is PRAGMA autonomous_transaction; CNT Number:= -1; BEGIN SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines before inserting three records:'||CNT);      ROLLBACK; INSERT into msg VALUES ('son Record1'); INSERT into msg VALUES ('son Record2'); INSERT into msg VALUES ('son Record3'); SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines after inserting three records:'||CNT);      ROLLBACK; SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('after rollback, the number of child autonomy transaction lines:'||CNT); --COMMIT; END P_test_at_and_mt_son;

Test the main transaction stored procedure with the following results:

Number of rows after the primary transaction is inserted: 1 number of child autonomy transactions before inserting three records: 0 number of child autonomy transactions after inserting three records: 3Rollback, number of child autonomy transactions: 0 Number of rows for the primary transaction after the child autonomy transaction is run: 1

you can see that there is no committed transaction running from the primary transaction, which is not visible in the child autonomy transaction. However, there is no commit in the main transaction itself and can see that a record has been inserted.

2, when we will be the second stored procedure, make a point modification, if the following coloring part two lines, and submit, we run the main transaction, see what reflects.

PROCEDURE P_test_at_and_mt_son is PRAGMA autonomous_transaction; CNT Number:= -1; BEGIN SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines before inserting three records:'||CNT);      ROLLBACK; INSERT into msg VALUES ('son Record1'); INSERT into msg VALUES ('son Record2'); INSERT into msg VALUES ('son Record3'); SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines after inserting three records:'||CNT);      ROLLBACK; SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('after rollback, the number of child autonomy transaction lines:'||CNT); INSERT into msg VALUES (  'son Record4'); SELECT COUNT (*) into CNT from MSG; --The  child transaction inserts the last COMMIT; END P_test_at_and_mt_son;

Operation Result:

Number of rows after a primary transaction is inserted:1 number of child autonomy transactions before inserting three records:0 number of child autonomy transactions after inserting three records:after3rollback, Number of child autonomy transactions:0 number of rows for the primary transaction after the child autonomy transaction is run:1

Exactly the same as the first, but we in the sub-autonomy affairs clearly commit ah, according to if not add the following statement, then we can be sure, see is 2 (last)

PRAGMA autonomous_transaction;

Autonomy, that's what it means.



3, in making some changes, such as the next, directly insert three records, commit we run the main transaction again, see the result:

PROCEDURE P_test_at_and_mt_son is PRAGMA autonomous_transaction; CNT Number:= -1; BEGIN SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines before inserting three records:'||CNT);      ROLLBACK; INSERT into msg VALUES ('son Record1'); INSERT into msg VALUES ('son Record2'); INSERT into msg VALUES ('son Record3'); SELECT COUNT (*) into CNT from MSG; Dbms_output.put_line ('number of child autonomous transaction lines after inserting three records:'||CNT);  COMMIT; END P_test_at_and_mt_son;

The results are as follows:

Number of rows after a primary transaction is inserted: 1 number of child autonomy transactions before inserting three records: 0 number of child autonomy transactions after inserting three records: 3 Number of rows for the primary transaction after the child autonomy transaction is run: 1

It is believed to be here that the master transaction, without seeing the child transaction, commits the rollback operation, namely: they are independent of each other.

Because, we are in the main transaction, there is no commit, you can debug during the run time, manually press the rollback button, you can see. The transaction processing of the background transaction is committed to the database.

In short: mutual independence, does not affect, does not disturb, sees the data also.

Share Progress!

Thank you for reading!

Oracle Self-service real-world use cases