Oracle Foundation stored procedures and transactions

I. Transactions and stored Procedures

How transactions are used in a stored procedure. When you need to execute multiple additions, modifications, and deletions of SQL statements in a stored procedure, we need to use transactions to ensure data integrity. The usage is very similar to that in Pl-sql, but there are some differences.

--stored procedures with transactionsCREATE OR REPLACE PROCEDUREAccount_zhuanzhang (Fromuser Number, Touser Number, Money  Number) isBEGIN    UPDATEAccountSETBalance=Balance-  Money WHEREId=Fromuser; UPDATEAccountSETBalance=Balance+  Money WHEREId=Touser; COMMIT; EXCEPTION whenOTHERS ThenDbms_output.put_line ('Transfer failed'); ROLLBACK;ENDAccount_zhuanzhang;

Invoking a transaction

SELECT *  fromAccount ;DECLAREFromuser Number:= 1; Touser Number:= 2;  Money  Number:= &m;BEGINAccount_zhuanzhang (Fromuser,touser, Money);END;

Second, the Independent affairs processing:

Autonomous transaction: is an independent transaction initiated by 01 transactions. Autonomous transactions can suspend primary transactions, that is, transactions within their own stored procedures, and the primary transaction is resumed when the autonomous transaction has finished processing.

　　PRAGMA autonomous_transaction; --defined as autonomous transactions, not being committed by other transactions, impact of rollback

Cases:

--Self-Service: Add department information with parameters, and finally use rollbackCREATE OR REPLACE PROCEDUREPro_dept_add (DEPTNO Number, dnameVARCHAR2, LOCVARCHAR2) asPRAGMA autonomous_transaction; --defined as autonomous transactions, not committed by other transactions, rollback impactBEGIN  INSERT  intoDEPT (DEPTNO, dname, LOC)VALUES(DEPTNO, Dname, LOC); ROLLBACK;--the self-service rollback operation does not affect the primary transaction. END;

--The master transaction, adding the department information, and invoking the autonomous transaction with parameters, commits itselfCREATE OR REPLACE PROCEDUREPro_dept_add2 asBEGIN  INSERT  intoDEPT (DEPTNO, dname, LOC)VALUES( -,'test1','test2'); Pro_dept_add ( -,'Test','Test');--if the calling transaction is rolled back, the data that is inserted in the current stored procedure is rolled back if it is not a self-contained transaction. However, self-transactional commits do not affect the current stored procedure after a self-directed transaction has been added  COMMIT;END;

To invoke the main transaction:

BEGIN       pro_dept_add2 ();   -- after the call is complete, the 60 department is inserted successfully, but the department information for 70 is rolled back. Only one insert succeeded!  END;

　　Summary of self-service:

1. The change of the result of the autonomic transaction processing does not depend on the state or final configuration of the primary transaction.

2. When a self-service transaction commits or rolls back, it does not affect the results of the master transaction.

3, the self-service submission once submitted, the results of the autonomy transaction changes for other transactions is the courseware. This means that you can access the updated information without waiting for the primary transaction to commit.

4. Autonomous transaction processing can initiate other autonomous transaction processing.

Oracle underlying stored procedures and transactions