Oracle Transaction Management Guide

Transaction management in Oracle can be achieved by utilizing the following methods:

1. Begin and end statements are used to specify the beginning and ending of a transaction. SQL statements that require transaction management are executed between the begin and end statements.
2. Use the COMMIT statement to finalize the transaction. Once the COMMIT statement is executed, all operations within the transaction will be permanently saved to the database.
3. To rollback a transaction, use the ROLLBACK statement. This can be done if there is an error during the transaction or if you need to undo previous operations.
4. Use the SAVEPOINT statement to create a savepoint. A savepoint is a marker within a transaction that allows you to rollback to that point using the ROLLBACK TO SAVEPOINT statement later on.

The sample code is shown below:

BEGIN
  -- 开始事务
  INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2');
  UPDATE table2 SET column1 = 'new_value' WHERE column2 = 'old_value';
  SAVEPOINT savepoint1;
  DELETE FROM table3 WHERE column1 = 'value3';
  
  IF condition THEN
    -- 如果条件满足，提交事务
    COMMIT;
  ELSE
    -- 如果条件不满足，回滚到保存点
    ROLLBACK TO SAVEPOINT savepoint1;
  END IF;
  
EXCEPTION
  WHEN others THEN
    -- 处理异常，回滚事务
    ROLLBACK;
END;

In the examples above, we used BEGIN and END statements to define a transaction, then executed insert, update, and delete operations within the transaction. If the conditions are met, we commit the transaction; if the conditions are not met, we rollback to the save point. Finally, in the exception handling, we rollback the entire transaction.