How to Control submission and rollback of stored procedures in Oracle

Create table test1 (id number, name varchar2 (20 ));
Create table test2 (id number, name varchar2 (20 ));
Create table test3 (id number, name varchar2 (20 ));

1. No commit is displayed in t1;

Create or replace procedure t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
End loop;
End;

No commit is displayed in t1;

After exec t1, if the session is not exited, it will not be submitted. At this time, if rollback is used, it will be rolled back. If commit is used, it will be submitted automatically if disconn is used;

 

2. commit is displayed in t1;

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
Commit;
End loop;
End;
/

When commit is displayed in t1:

After exec t1, it will be submitted directly;

3.

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
Commit;
If I = 20 then
Exit;
End if;
End loop;
End;
/

Commit is displayed in the loop, and commit is submitted before exit.

4. In procedure, both commit and rollback are available. commit is performed before commit, and rollback is performed between commit and rollback.

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
Commit;
If I = 20 then
Rollback;
Exit;
End if;
End loop;
End;
/

4. In procedure, both commit and rollback are available. commit is performed before commit, and rollback is performed between commit and rollback.

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
Commit;
If I = 20 then
Rollback;
Exit;
End if;
End loop;
End;
/

5. Some of procedure's commit files are submitted before commit, but not after commit. rollback is performed if rollback is performed in the session, commit is performed, and automatic commit is exited.

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
If I <20 then
Commit;
End if;
End loop;
End;
/

 

6. No commit and rollback are displayed in procedure. If a program error occurs, the program is forcibly exited and rolled back.

Create or replace procedure SCOTT. t1
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test1 (id) values (I );
If I = 100 then
Select name into var_name from test1 where id = 0; -- simulate an error
End if;
End loop;
End;
/

6. commit is displayed in procedure. If a program error occurs, the commit is submitted before commit, and the rollback between commit and error is forced.
Create or replace procedure SCOTT. t1
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test1 (id) values (I );
If I <20 then
Commit;
End if;

If I = 100 then
Select name into var_name from test1 where id = 0;
End if;
End loop;
End;
/

Result: 19

7. nested errors. The commit is submitted before an error occurs. The program is forcibly exited without commit and rolled back.

Create or replace procedure t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
End loop;
Commit;
T2;
End;

Create or replace procedure SCOTT. t2
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test2 (id) values (I );
If I <20 then
Commit;
End if;

If I = 100 then
Select name into var_name from test1 where id = 0; -- error location.
End if;
End loop;
End;
/

T1: 10000
T2: 19

8. t1 nested t2, t2 commit also takes effect for t1.
Create or replace procedure t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
End loop;
T2;
End;

Create or replace procedure SCOTT. t2
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test2 (id) values (I );
If I <20 then
Commit;
End if;

If I = 100 then
Select name into var_name from test1 where id = 0;
End if;
End loop;
End;
/

T1: 10000
T2: 19

9. t1 nested t2, t2 rollback also takes effect on t1.
Create or replace procedure t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
End loop;
T2;
End;

Create or replace procedure SCOTT. t2
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test2 (id) values (I );
If I <20 then
Commit;
End if;

If I = 100 then
Select name into var_name from test1 where id = 0;
End if;
End loop;
End;
/

T1: 10000
T2: 19

10. t1 nested t2, t2 nested t3, commit of commit before error, uncommitted force rollback.

Create or replace procedure SCOTT. t1
As
Begin
For I in 1 .. 10000 loop
Insert into test1 (id, name) values (I, 'leng' | I );
End loop;
T2;
End;
/

Create or replace procedure SCOTT. t2
As
Begin
For I in 1 .. 10000 loop
Insert into test2 (id, name) values (I, 'leng' | I );
End loop;
T3;
End;
/

Create or replace procedure SCOTT. t3
As
Var_name varchar2 (20 );
Begin
For I in 1 .. 10000 loop
Insert into test3 (id) values (I );
If I <20 then
Commit;
End if;

If I = 100 then
Select name into var_name from test1 where id = 0;
End if;
End loop;
End;
/

T1: 10000
T2: 10000
T3: 19

11. Conclusion
Take all the programs and statements in a procedure as sequential execution. No matter how many layers are nested, The commit takes effect. If an error occurs, the program is forcibly exited from the place where the error occurs, if no error occurs, it is submitted by default when the session is exited.