Rollback to savepoint in exception handler : Transaction Rollback Commit « Table « Oracle PL / SQL

1. Oracle PL / SQL
2. Table
3. Transaction Rollback Commit

Rollback to savepoint in exception handler

    

SQL>
SQL> create table myTable2 ( cnt int );

Table created.

SQL>
SQL> insert into myTable2 values ( 0 );

1 row created.

SQL>
SQL> create table t ( x int check ( x>0 ) );

Table created.

SQL>
SQL> create trigger t_trigger before insert or delete on t for each row
  2  begin
  3     if ( inserting ) then
  4          update myTable2 set cnt = cnt +1;
  5     else
  6          update myTable2 set cnt = cnt -1;
  7     end if;
  8     dbms_output.put_line( 'fired and updated '  || sql%rowcount || ' rows' );
  9  end;
 10  /

Trigger created.

SQL>
SQL> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          insert into t values (-1 );
  6  end;
  7  /

Procedure created.

SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL> begin
  2          p;
  3  end;
  4  /
fired and updated 1 rows
fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (JAVA2S.SYS_C0010265) violated
ORA-06512: at "JAVA2S.P", line 5
ORA-06512: at line 2


SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL>
SQL>
SQL> begin
  2      p;
  3  exception
  4      when others then null;
  5  end;
  6  /
fired and updated 1 rows
fired and updated 1 rows

PL/SQL procedure successfully completed.

SQL> select * from t;

         X
----------
         1

SQL> select * from myTable2;

       CNT
----------
         1

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> begin
  2      savepoint sp;
  3      p;
  4  exception
  5      when others then
  6          rollback to sp;
  7  end;
  8  /
fired and updated 1 rows
fired and updated 1 rows

PL/SQL procedure successfully completed.

SQL> select * from t;

no rows selected

SQL> select * from myTable2;

       CNT
----------
         0

SQL>
SQL>
SQL>
SQL> drop table t;

Table dropped.

SQL> drop table myTable2;

Table dropped.

   
    
    
    
  

Related examples in the same category