This article contains information about PL/SQL Loops such as WHILE, FOR and necessary statements for these loops like GOTO, Continue, EXIT.
What is loop in PL SQL?
PL/SQL Loops are structures used to run commands again until a certain condition is met.
What are Oracle loops?
LOOP in Oracle
It is used to run the commands in the block continuously.
|
1 2 3 |
LOOP -- Commands END LOOP; |
NOTE: If the loop is not terminated with the EXIT keyword it will run continuously-infinitely. Example LOOP usage is as follows.
|
1 2 3 4 5 6 7 |
SET SERVEROUTPUT ON; BEGIN LOOP DBMS_OUTPUT.put_line('Hello Oracle!'); EXIT; END LOOP; END; |
Example LOOP WHEN EXIT usage is as follows.
|
1 2 3 4 5 6 7 8 9 10 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN LOOP DBMS_OUTPUT.put_line(v_number); v_number := v_number + 1; EXIT WHEN v_number > 10; END LOOP; END; |
WHILE LOOP in PL/SQL
It is used to run the commands in the block until the condition is met.
|
1 2 3 |
WHILE Your_Condition LOOP -- Commands END LOOP; |
Example WHILE usage is as follows.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN WHILE v_number < 10 LOOP DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); v_number := v_number + 1; END LOOP; END; |
FOR LOOP in PL/SQL
It is used to run commands in the block as much as predetermined number.
|
1 2 3 |
FOR Counter IN Start .. Finish LOOP -- Commands END LOOP; |
Example FOR usage is as follows.
|
1 2 3 4 5 6 7 8 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); END LOOP; END; |
The number range can be a custom range.
|
1 2 3 4 5 6 7 8 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 100 .. 110 LOOP DBMS_OUTPUT.put_line(v_number); END LOOP; END; |
To reverse the start and end in the FOR loop
|
1 2 3 4 5 6 7 8 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN REVERSE 100 .. 110 LOOP DBMS_OUTPUT.put_line(v_number); END LOOP; END; |
GOTO Statement in PL/SQL
It is used to direct the command flow to a predetermined tag.
|
1 |
GOTO tag |
Example GOTO usage is as follows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BEGIN GOTO second_command; <<first_command>> DBMS_OUTPUT.put_line('first_command'); GOTO last_command; <<second_command>> DBMS_OUTPUT.put_line('second_command'); GOTO first_command; <<last_command>> DBMS_OUTPUT.PUT_LINE('last_command'); END; |
With GOTO, the loop can be created as follows.
|
1 2 3 4 5 6 7 8 9 10 11 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN <<my_loop>> IF v_number < 10 THEN DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); v_number := v_number +1; GOTO my_loop; END IF; END; |
NOTE: It is not recommended to use GOTO. Because GOTO complicates the command flow.
EXIT Statement in PL/SQL
It is used to exit the loop.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); EXIT; END LOOP; END; |
The WHEN keyword can be used to determine the condition.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); EXIT WHEN v_number > 4; END LOOP; END; |
Continue Statement in PL/SQL
Its used to skip loop steps.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP CONTINUE; DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); END LOOP; END; |
The WHEN keyword can be used to determine the condition.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP CONTINUE WHEN v_number > 3; DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); END LOOP; END; |
Various operators can be used for a certain range.
|
1 2 3 4 5 6 7 8 9 |
SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 1; BEGIN FOR v_number IN 1 .. 10 LOOP CONTINUE WHEN v_number < 3 OR v_number > 5 ; DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.'); END LOOP; END; |
NOTE: The keyword CONTINUE must be added at the beginning of the commands. Otherwise, the step is skipped after the commands are executed.
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about loops at docs.oracle.com
