Is there rollback in oracle?

There is rollback in oracle. Rollback is used to undo the current transaction or problematic transaction. The rollback method can be used to complete the rollback of the transaction. The syntax is "ROLLBACK [Implicit keyword] [TO [SAVEPOINT] ] savepoint_name | FORCE 'string'};".

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

Is there rollback in oracle?

1. Concept

In Oracle, the usage of rollback is the opposite of commit, which is used to undo the current transaction or Problematic transactions.

1.1 Syntax

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name | FORCE 'string'};

Example:

• work - Optional. Implicit keywords can be written or not.

• to savepoint savepoint_name - Optional. The rollback statement undoes all changes for the current session up to the savepoint specified by savepoint_name. If this clause is omitted, all changes will be undone.

• force 'string - optional'. Used to force a rollback of potentially corrupted or problematic transactions. Using this clause, you can specify the transaction id in single quotes as a string. The transaction identifier named dba_2pc_pengding, v$corrupt_xid_list can be found in the system view (there is no way to roll back the problematic transaction to the save point.)

1.1 Data preparation

DROP TABLE stu; -- if exists
CREATE TABLE stu (
  s_id NUMBER,
  s_xm VARCHAR2(30)
);
ALTER TABLE stu ADD CONSTRAINTS pk_stu_id PRIMARY KEY(s_id);

2. savepoint

Roll back to the latest pointsql

DECLARE
   
BEGIN
   INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
   SAVEPOINT ps1;
   
   INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
   SAVEPOINT ps2; -- 若也是 ps1，则回滚至此处（最近）
   
   INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
   
   COMMIT;
EXCEPTION 
  WHEN OTHERS THEN
     ROLLBACK TO ps1;
     dbms_output.put_line(SQLCODE ||' : '||SQLERRM);
     dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

Run result:

Recommended Tutorial: "Oracle Video Tutorial"

The above is the detailed content of Is there rollback in oracle?. For more information, please follow other related articles on the PHP Chinese website!