What is the use of transactions in oracle stored procedures?

In Oracle stored procedures, transactions are used to ensure data consistency and consist of a group of related DML statements. The operations performed by this group of DML statements are either all confirmed or all canceled. When a transaction operation is performed, locks are placed on the tables and rows affected to prevent other users from changing the table structure, or other transactions from performing DML operations on the corresponding rows.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Transaction processing of oracle stored procedures

1. Transactions are used to ensure data consistency and consist of a set of related DML statements. The operations performed by this group of DML statements are either all confirmed or all canceled.

2. When executing a transaction operation (DML), Oracle will lock the affected table to prevent other users from changing the table structure. It will also add row locks to the affected rows to prevent other users from changing the table structure. Prevent other transactions from performing DML operations on the corresponding rows.

3. When executing transaction commit or transaction rollback, Oracle will confirm the transaction changes or roll back the transaction, end the transaction, delete the save point, and release the lock.

4. Commit the transaction (COMMIT) to confirm the transaction changes, end the current transaction, delete the save point, and release the lock, making all pending data in the current transaction permanently changed.

5. Save point (SAVEPOINT) In the current transaction, mark the save point of the transaction.

6. Rollback transaction (ROLLBACK) rolls back the entire transaction, deletes all save points defined by the transaction, releases the lock, and discards all pending data changes.

7. Roll back the transaction to the specified save point (ROLLBACK TO SAVEPOINT). Roll back the current transaction to the specified save point, discard any changes after the save point was created, and release the lock.

8. When executing DDL, DCL statements, or exiting SQL*PLUS, the transaction will be automatically submitted;

9. Interaction with users should be avoided during the transaction;

10. During data query, try not to start a transaction;

11. Try to keep the transaction as short as possible;

12. Access the smallest amount of data in the transaction

Case:

create or replace procedure stu_proc
(
  v_id in number,
  v_name in varchar2,
  v_age in number,
  v_msg out varchar2
) as
begin
  insert into student(id, sname, age) values (v_id, v_name, v_age);
  commit;
  v_msg:='添加成功';
exception
  when others then
    rollback;
    v_msg:='失败成功';
    RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');
end;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What is the use of transactions in oracle stored procedures?. For more information, please follow other related articles on the PHP Chinese website!