Analysis of nested transaction call for Oracle Data Integrity

This article analyzes the many possibilities of a problem. Readers can determine the situation based on the phenomenon to solve the problem in the shortest time.

We have used many methods to protect data integrity, such as the primary key constraint, foreign key constraint, and trigger of a data table. Today we are discussing how to process direct data transactions, integrity of data.

For example, in a stored procedure, Proc_SaveBill saves a document and calls a function Func_GenerateCode to generate a document number, if my function is taken from a table with the largest number and the number is incremented by one, you may think of problems, that is, if I get a number through Func_GenerateCode before saving in Proc_SaveBill, if the subsequent saving fails, what should I do if I lose a number, because one call to Func_GenerateCode will add a number, so in order to make our data complete, we will use the transaction to roll.

Now there is another problem. Will the stored procedure be rolled into the function?

The answer is: if a function has a transaction or a commit statement, it cannot be rolled. Otherwise, it can be rolled because the transaction will be rolled to everything after the previous commit or rollback. So here I will explain some methods to remind you to consider the nested rollback of transactions. However, when a stored procedure calls a stored procedure, if the called stored procedure is not executed incorrectly, it cannot be rolled. To solve this problem, Oracle uses autonomous transactions to handle the above problems. After the stored procedure is \ as, it declares PRAGMA AUTONOMOUS_TRANSACTION; -- autonomous transactions prevent nested commit, make the transaction commit or rollback in your own transaction zone will not affect other transactions.