Oracle PL/SQL - PL SQL SQL Statement Transaction Control

Introduction

Explicit commits are fired by using the COMMIT command.

begin
     update ...;                                  
     update ...;                                  
     insert ...                                   
     commit;                                      
end;

Implicit commits fire automatically when you use any DDL commands that create, alter, or delete any database objects.

It doesn't matter if the DDL command fails.

Rolling back changes

Explicit rollbacks occur when you use the command ROLLBACK. The rollback may be full or partial.

declare
     v_errors_yn VARCHAR2(1) := 'N';
begin
     update emp
        set sal = sal * 1.1                                           --5
     where deptNo = 10;
     update emp
        set sal = sal * .9
     where deptNo = 20;                                               --9
     --...
     select decode (count(*),0,'N','Y')
     into v_errors_yn
     from t_logError;
     If v_errors_yn = 'Y' then                                      --17
       rollback;
     end if;
end;

If the T_LOGERROR table contains any records, changes made by both UPDATE statements will be discarded.

Related Topic

• COMMIT Statement with COMMENT and WRITE Clauses

• Previous
• Next