Like all major programming languages, PL/SQL supports loops which allow a set of instructions to be repeated until a certain condition is met.
In this tutorial, we will explore the use of the LOOP statement in PL/SQL to repeatedly execute a block of code.
The PL/SQL language supports the various loop constructs such as “for” loop and “while” loops. We can express the syntax of a generic loop in Oracle as shown in the following:
END LOOP loop_label;
This contains the LOOP keyword and the body of the loop to execute and is enclosed with the END LOOP keyword.
The LOOP block runs the defined actions and returns the control to the top loop clause after completion. This is usually enclosed in an EXIT or EXIT WHEN clause to terminate the loop after a specific condition is met. This helps prevent the infinite loops.
Oracle For Loop Syntax:
The following shows the syntax of a “for” loop in PL/SQL:
FOR loop_variable IN [lower_bound..upper_bound] LOOP
-- code to be executed in the loop
The loop_variable allows you to define a loop which is used to control the loop and the lower and upper bounds. The upper and lower bounds specify the range values where the loop evaluation occurs.
Oracle While Loops
We can also define the “while” loops in PL/SQL as demonstrated by the following syntax:
WHILE condition LOOP
-- loop action
In a “while” loop, the loop action is executed repeatedly while the condition is true.
Oracle Cursor For Loops
Oracle also supports the “cursor for” loops which enables us to execute a set of instructions for every row in a given result set. The syntax can be expressed as follows:
FOR loop_variable IN (SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition) LOOP
-- loop action
Example Loops in Oracle
The following examples cover the basics of working with for, while, and for cursor loops in Oracle.
Oracle For Loop Example:
This example demonstrates how to use a “for” loop to print the numbers from 1 to 10.
FOR i IN 1..10 LOOP
While Loop Example:
We can perform a similar action using a while loop as shown in the following:
i NUMBER := 1;
WHILE i <= 10 LOOP
i := i + 1;
This should perform a similar action to the “for” loop.
Cursor For Loop Example:
The following shows how to use the “cursor for” loop to get a customer’s full name:
FOR employee IN (SELECT first_name, last_name
FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(employee.first_name || ' ' || employee.last_name);
In this post, you encountered three main types of loops in Oracle PL/SQL language. You can check the documentation to explore further.