Loops in Oracle
Let’s delve into the world of PL/SQL loops, which are essential for controlling the flow of commands within your Oracle database programs. PL/SQL, Oracle Corporation’s procedural extension for SQL, allows you to codify business rules, trigger database events, and add programming logic to SQL commands. PL/SQL code is structured into blocks, which can be anonymous or named (like stored procedures or packages). Each block typically has a Declarations section, an Executable Commands section, and an optional Exception Handling section. Loops are a core part of the Executable Commands section, enabling repetitive execution of code until a certain condition is met.
For displaying output from PL/SQL blocks, remember to execute SET SERVEROUTPUT ON; in your SQL*Plus or SQL Developer session.
Understanding the PL/SQL Block Structure
Since all loop types are found in the Executable Commands section, it’s useful to review the fundamental structure of a PL/SQL block before delving into loops:
DECLARE
-- Optional: Declarations section to define variables, cursors, etc.
-- Example: pi constant NUMBER(9,7) := 3.1415927;
-- Example: radius INTEGER(5);
BEGIN
-- Mandatory: Executable Commands section where program logic resides
-- This is where loops and conditional statements are used
EXCEPTION
-- Optional: Exception Handling section to manage errors
END;
/ -- The slash (/) executes the PL/SQL block Simple Loops
The simplest type of loop in PL/SQL is called a simple loop. The fundamental characteristic of a simple loop is that it repeats until an EXIT or EXIT WHEN statement is met within the loop’s body. It is distinguished by its simple form, which starts with the LOOP keyword and ends with END LOOP. A basic loop is guaranteed to run its body (or at least a portion of it) at least once because the termination condition is tested inside the loop. This makes it helpful when you want to make sure initial processing happens but aren’t sure how many iterations are required.
The EXIT WHEN statement, a more popular variation, incorporates an IF-THEN condition straight into the exit logic, making the code more compact for single termination conditions. The EXIT statement unconditionally ends the loop, passing control to the statement that comes right after END LOOP.
Syntax:
LOOP
-- Block of code to be repeated
-- ...
EXIT WHEN condition; -- Condition to exit the loop
-- ...
END LOOP; Creating Table and Inserting Values: Let’s create a table named AREAS to store the calculated areas of circles.
-- Create the AREAS table if it doesn't exist
CREATE TABLE AREAS (
RADIUS NUMBER(5),
AREA NUMBER(14,2)
);Code Example: This example demonstrates a simple loop that calculates the area of circles, starting with a radius of 3 and incrementing by 1 in each iteration. The loop continues to insert records into the AREAS table until the calculated AREA exceeds 100.
SET SERVEROUTPUT ON;
DELETE FROM AREAS;
COMMIT; -- Clear previous data for a fresh run
DECLARE
pi CONSTANT NUMBER(9,7) := 3.1415927; -- Declare constant for PI
radius INTEGER(5); -- Declare variable for radius
area NUMBER(14,2); -- Declare variable for area
BEGIN
radius := 3; -- Initialize radius
LOOP
area := pi * POWER(radius, 2); -- Calculate area
INSERT INTO AREAS VALUES (radius, area); -- Insert values into AREAS table
radius := radius + 1; -- Increment radius
EXIT WHEN area > 100; -- Exit condition: loop terminates when area exceeds 100
END LOOP;
DBMS_OUTPUT.PUT_LINE('Simple loop completed. Check the AREAS table for results.');
END;
/Output:
PL/SQL procedure successfully completed.
-- Verify the inserted data
SELECT * FROM AREAS ORDER BY RADIUS;
RADIUS AREA
---------- ----------
3 28.27
4 50.27
5 78.54
6 113.10The loop processed radius values 3, 4, 5, and 6, as you can see. The EXIT WHEN condition was satisfied when the radius was 6 because the area (113.10) was more than 100, and no additional data were processed after that iteration.
WHILE Loops
The definition of a WHILE loop is a conditional loop that keeps running as long as the Boolean condition that is stated at the loop border evaluates to TRUE. The fact that the condition is tested at the start of each iteration is a crucial feature of the WHILE loop. This implies that the body of the loop won’t run at all if the condition is set to FALSE or NULL at the beginning. Because of this, the WHILE loop is appropriate in situations where you are unsure of the precise number of iterations and the loop may not even need to run.
The termination criteria are specified in the WHILE statement itself. The loop is stopped when the radius value violates the WHILE condition.
Syntax:
WHILE condition LOOP -- Condition evaluated at the start of each iteration
-- Block of code to be repeated
-- ...
END LOOP; Reusing Table: We will reuse the AREAS table for this example.
Code Example: This WHILE loop also calculates and inserts areas into the AREAS table, but it explicitly checks if radius is less than or equal to 7 before each iteration. The loop terminates once radius becomes greater than 7.
SET SERVEROUTPUT ON;
DELETE FROM AREAS;
COMMIT; -- Clear previous data
DECLARE
pi CONSTANT NUMBER(9,7) := 3.1415927; -- Declare constant for PI
radius INTEGER(5); -- Declare variable for radius
area NUMBER(14,2); -- Declare variable for area
BEGIN
radius := 3; -- Initialize radius
WHILE radius <= 7 LOOP -- Loop continues as long as radius is 7 or less
area := pi * POWER(radius, 2); -- Calculate area
INSERT INTO AREAS VALUES (radius, area); -- Insert values into AREAS table
radius := radius + 1; -- Increment radius
END LOOP;
DBMS_OUTPUT.PUT_LINE('WHILE loop completed. Check the AREAS table for results.');
END;
/Output:
PL/SQL procedure successfully completed.
-- Verify the inserted data
SELECT * FROM AREAS ORDER BY RADIUS;
RADIUS AREA
---------- ----------
3 28.27
4 50.27
5 78.54
6 113.10
7 153.94For radius values ranging from 3 to 7, inclusive, the loop ran. The loop ended when radius reached 8, making the condition radius <= 7 false.
Numeric FOR Loops
To put it simply, a Numeric FOR loop iterates a predetermined number of times, making it a “counted” loop. A start..end range indicates the number of iterations, which is known at the beginning of the loop. Because PL/SQL implicitly declares the loop counter (or index) as an INTEGER, its scope is restricted to the loop. This means that PL/SQL takes care of this automatically, so you don’t have to declare the counter variable in the DECLARE section or manually increment it. When the loop starts, the range expressions (start and end) are only evaluated once.
The REVERSE keyword, which counts down from the end value to the start value, can also be used to iterate in reverse order.
Syntax:
FOR counter IN [REVERSE] start_value .. end_value LOOP -- `counter` implicitly declared
-- Block of code to be repeated
-- ...
END LOOP; Reusing Table: We will reuse the AREAS table for this example.
Code Example: This Numeric FOR loop calculates and inserts areas for radius values ranging from 1 through 7, inclusive.
SET SERVEROUTPUT ON;
DELETE FROM AREAS;
COMMIT; -- Clear previous data
DECLARE
pi CONSTANT NUMBER(9,7) := 3.1415927; -- Declare constant for PI
-- radius INTEGER(5); -- Not explicitly declared, handled by FOR loop
area NUMBER(14,2); -- Declare variable for area
BEGIN
-- The 'radius' variable is implicitly declared and iterates from 1 to 7
FOR radius IN 1..7 LOOP
area := pi * POWER(radius, 2); -- Calculate area
INSERT INTO AREAS VALUES (radius, area); -- Insert values into AREAS table
END LOOP;
DBMS_OUTPUT.PUT_LINE('Numeric FOR loop completed. Check the AREAS table for results.');
-- Example with REVERSE keyword
DBMS_OUTPUT.PUT_LINE('Numeric FOR loop with REVERSE:');
FOR counter IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Current iteration of loop is ' || counter);
END LOOP;
END;
/Output:
PL/SQL procedure successfully completed.
-- Verify the inserted data from the first loop
SELECT * FROM AREAS ORDER BY RADIUS;
RADIUS AREA
---------- ----------
1 3.14
2 12.57
3 28.27
4 50.27
5 78.54
6 113.10
7 153.94
7 rows selected.
Numeric FOR loop with REVERSE:
Current iteration of loop is 5
Current iteration of loop is 4
Current iteration of loop is 3
Current iteration of loop is 2
Current iteration of loop is 1
PL/SQL procedure successfully completed.As the radius counter iterated from 1 to 7, the first loop was able to correctly insert 7 records. By printing the counter from 5 down to 1, the second loop illustrated the REVERSE keyword.
Cursor FOR Loops
Within a PL/SQL block, a Cursor FOR loop offers a very practical means of iterating over the output of a SQL query. Its main benefit is that it greatly simplifies the code by handling the cursor’s OPEN, FETCH, and CLOSE instructions implicitly. This lowers the amount of code needed and removes a frequent cause of mistakes, such as failing to close a cursor.
PL/SQL automatically creates a record variable (with the %ROWTYPE attribute) that corresponds to the structure of the query’s result set when a Cursor FOR loop is used. The column values of every fetched row are then accessed using this record variable. When every record from the related cursor has been retrieved, the loop ends unconditionally. The body of the loop won’t run if the query yields no rows.
Syntax:
FOR record_name IN (SELECT_statement) LOOP -- `record_name` is implicitly declared
-- Block of code to process each row
-- Access columns using record_name.column_name
END LOOP; Alternatively, you can define a named cursor in the DECLARE section and reference it:
CURSOR cursor_name IS SELECT_statement;
-- ...
FOR record_name IN cursor_name LOOP
-- ...
END LOOP;Creating Table and Inserting Values: First, let’s ensure our RADIUS_VALS table is set up with some data, and clear the AREAS table.
-- Create the RADIUS_VALS table
CREATE TABLE RADIUS_VALS (
RADIUS NUMBER(5)
);
-- Insert values into RADIUS_VALS
INSERT INTO RADIUS_VALS VALUES (3);
INSERT INTO RADIUS_VALS VALUES (4);
INSERT INTO RADIUS_VALS VALUES (10);
COMMIT; -- Commit the inserts
-- Verify RADIUS_VALS content
SELECT * FROM RADIUS_VALS ORDER BY RADIUS;Output (RADIUS_VALS setup):
Table RADIUS_VALS created.
1 row created.
1 row created.
1 row created.
Commit complete.
RADIUS
----------
3
4
10Code Example: This Cursor FOR loop queries the RADIUS_VALS table and, for each record found, calculates the area and inserts it into the AREAS table.
SET SERVEROUTPUT ON;
DELETE FROM AREAS;
COMMIT; -- Clear previous data
DECLARE
pi CONSTANT NUMBER(9,7) := 3.1415927; -- Declare constant for PI
area NUMBER(14,2); -- Declare variable for area
-- Define a named cursor for RADIUS_VALS
CURSOR rad_cursor IS
SELECT radius FROM RADIUS_VALS;
BEGIN
-- The 'rad_val' record is implicitly declared and populated by the cursor FOR loop
FOR rad_val IN rad_cursor LOOP
area := pi * POWER(rad_val.radius, 2); -- Calculate area using fetched radius
INSERT INTO AREAS VALUES (rad_val.radius, area); -- Insert into AREAS table
END LOOP;
DBMS_OUTPUT.PUT_LINE('Cursor FOR loop completed. Check the AREAS table for results.');
END;
/Output:
PL/SQL procedure successfully completed.
-- Verify the inserted data
SELECT * FROM AREAS ORDER BY RADIUS;
RADIUS AREA
---------- ----------
3 28.27
4 50.27
10 314.16Without the need for explicit OPEN, FETCH, or CLOSE instructions, the loop processed each entry from RADIUS_VALS, calculating and adding the associated areas into the AREAS table.
CONTINUE Statement
The CONTINUE statement, which was added in Oracle Database 11g, allows you to bypass any leftover statements in the current loop iteration and go straight to the next one. This can make intricate conditional logic inside a loop body simpler.
Two types exist:
CONTINUE;: Jumps straight to the following iteration.CONTINUE WHEN condition;: If the given condition isTRUE, it moves on to the following iteration.
For more precise control over loop execution, you can also use CONTINUE to give a loop label that will tell which loop (in nested loops) should move on to its next iteration.
Syntax:
LOOP_LABEL: -- Optional loop label
FOR counter IN start..end LOOP
-- ...
CONTINUE [LOOP_LABEL] [WHEN condition]; -- Jump to next iteration
-- ...
END LOOP;Code Example: This example uses a Numeric FOR loop and the CONTINUE WHEN statement to skip even numbers. Only odd numbers will be displayed.
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('Demonstrating CONTINUE WHEN:');
FOR l_index IN 1 .. 10 LOOP
CONTINUE WHEN MOD(l_index, 2) = 0; -- If l_index is even, skip to next iteration [65]
DBMS_OUTPUT.PUT_LINE('Loop index = ' || TO_CHAR(l_index));
END LOOP;
DBMS_OUTPUT.PUT_LINE('CONTINUE WHEN example completed.');
DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Demonstrating CONTINUE with nested loops:'); -- CHR(10) for newline
<<outer_loop>>
FOR outer_idx IN 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE('Outer index = ' || TO_CHAR(outer_idx));
<<inner_loop>>
FOR inner_idx IN 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(' Inner index = ' || TO_CHAR(inner_idx));
CONTINUE outer_loop WHEN inner_idx = 1; -- Skip to next outer_loop iteration if inner_idx is 1
DBMS_OUTPUT.PUT_LINE(' This line will only show if inner_idx > 1 and after outer_loop continue.');
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE('CONTINUE with nested loops example completed.');
END;
/Output:
PL/SQL procedure successfully completed.
Demonstrating CONTINUE WHEN:
Loop index = 1
Loop index = 3
Loop index = 5
Loop index = 7
Loop index = 9
CONTINUE WHEN example completed.
Demonstrating CONTINUE with nested loops:
Outer index = 1
Inner index = 1
Outer index = 2
Inner index = 1
Outer index = 3
Inner index = 1
CONTINUE with nested loops example completed.
PL/SQL procedure successfully completed.In the first part of the example, CONTINUE WHEN MOD(l_index, 2) = 0 caused the DBMS_OUTPUT.PUT_LINE for even numbers to be skipped, displaying only odd numbers. In the second part, CONTINUE outer_loop WHEN inner_idx = 1 immediately caused the outer_loop to proceed to its next iteration as soon as inner_idx was 1, effectively preventing the rest of the inner loop’s body from executing and resetting the inner loop for the next outer iteration.
This includes thorough descriptions and illustrations of PL/SQL’s Simple Loops, WHILE Loops, Numeric FOR Loops, Cursor FOR Loops, and CONTINUE statement.