Different types of Loops in Oracle PL/SQL
In this article, I’ll demonstrate the different types of loops used in Oracle PL/SQL. Loops are used to repeatedly execute code until some condition is met.
We will understand by dividing it into three categories which are discussed in greater detail in the following sections:
- Simple Loops – This is the most basic kind of loop they include LOOP, END LOOP, and EXIT keywords. The basic syntax is as follows:
SET SERVEROUTPUT ON
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Hey there!');
END LOOP;
END;
Here the LOOP keyword marks the beginning of the loop and the END LOOP keyword marks the end of the loop. This piece of code keeps on running and never stops, guess why? Well, this is an infinite loop since we did not tell the loop when to exit. To resolve this dilemma we use the EXIT condition. We can rewrite the last example with a few modifications to allow a specific number of iterations.
DECLARE
v_count PLS_INTEGER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The count is: ' || v_count);
v_count := v_count + 1;
EXIT WHEN v_count = 5;
END LOOP;
END;
/The v_count variable stores the count initially it is initialized as 0 and after each run, the value of this variable is incremented by 1. The loop exits when the value of this variable reaches 5.
- FOR Loop – It has a predefined number of iterations built into the syntax. Here we do not need to create and initialize the variable as we did in a simple for loop. Below is the syntax:
BEGIN
FOR v_count IN 1 .. 5
LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: '||v_count);
END LOOP;
END;
The value of the for loop variable v_count by default starts with 1(not with 0 as in the case of a simple for a loop because there we had initialized it to 0). The loop runs 5 times and prints the statement. We can also print the list in reverse order by using the keyword REVERSE after IN as seen below:
BEGIN
FOR v_count IN REVERSE 1 .. 5
LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: '||v_count);
END LOOP;
END;
We use the numeric FOR loop whenever we need to load mass amounts of data.
- While loop – This type of loop executes only while a certain condition is met. When it no longer meets the condition, the loop ends. The while loop executes as long as the stated expression evaluates to true. Below is an example:
DECLARE
v_count PLS_INTEGER := 1;
BEGIN
WHILE v_count <= 5
LOOP
DBMS_OUTPUT.PUT_LINE('While loop iteration: '||v_count);
v_count := v_count + 1;
END LOOP;
END;
/
WHILE marks the beginning of the loop and END LOOP is the end of the loop. The loop will run until the value of the variable is less than or equal to 5 and for each run, the value of this variable is incremented by 1.
When we have a requirement to run a particular piece of code multiple times, we should use loops. We do not need to write code repeatedly for each iteration, using loops we can re-use code in every iteration which also, in turn, helps us reduce the size of the overall program.
That’s all about this article.
Request you to subscribe to my YouTube channel for regular updates.
Further readings:
What Is an Autonomous Database?
How to create a free tier ATP connection
