How to differentiate exit and ruturn in Stored Procedures

Step 1: Test the stored procedure using exit

Create or replace procedure p_test_return_exit is

I number: = 1;
J number: = 1;
Begin

For I in 1 .. 20 Loop

Dbms_output.put_line ('output DATA = '| I );
If (I> 10) then
Exit;
End if;

End loop;

For J in 1 .. 20 Loop

Dbms_output.put_line ('output DATA = '| j );
End loop;

End p_test_return_exit;

Test results:

Output Data = 1
Output Data = 2
Output Data = 3
Output Data = 4
Output Data = 5
Output Data = 6
Output Data = 7
Output Data = 8
Output Data = 9
Output Data = 10
Output Data = 11
Output Data = 1
Output Data = 2
Output Data = 3
Output Data = 4
Output Data = 5
Output Data = 6
Output Data = 7
Output Data = 8
Output Data = 9
Output Data = 10
Output Data = 11
Output Data = 12
Output Data = 13
Output Data = 14
Output Data = 15
Output Data = 16
Output Data = 17
Output Data = 18
Output Data = 19
Output Data = 20

 

 

Step 2: Test the use of return in the Stored Procedure

Create or replace procedure p_test_return_exit is

I number: = 1;
J number: = 1;
Begin

For I in 1 .. 20 Loop

Dbms_output.put_line ('output DATA = '| I );
If (I> 10) then
Return;
End if;

End loop;

For J in 1 .. 20 Loop

Dbms_output.put_line ('output DATA = '| j );
End loop;

End p_test_return_exit;

Test results:

Output Data = 1
Output Data = 2
Output Data = 3
Output Data = 4
Output Data = 5
Output Data = 6
Output Data = 7
Output Data = 8
Output Data = 9
Output Data = 10
Output Data = 11

 

Conclusion:

Exit in the loop of the stored procedure to exit this loopProgramBut will continue to execute other program blocks of the stored procedure.

 

In the loop of the stored procedure, return exits from the current loop and exits the entire stored procedure. Other program blocks are not executed.