PL/SQL Loops

Loops are one of the primary building blocks in programming. We use loops to create the manageable code blocks that can be executed multiple times when a certain condition is true. They are the fundamental bone when automating the repetitive tasks without re-writing a block of code multiple times.

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.

Oracle Loops

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:

<<label>> LOOP
loop_statement;
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:

BEGIN
FOR loop_variable IN [lower_bound..upper_bound] LOOP
-- code to be executed in the loop
END LOOP;
END;

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:

BEGIN
WHILE condition LOOP
-- loop action
END LOOP;
END;

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:

BEGIN
FOR loop_variable IN (SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition) LOOP
-- loop action
END LOOP;
END;

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.

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

While Loop Example:

We can perform a similar action using a while loop as shown in the following:

BEGIN
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;
END;

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:

BEGIN
FOR employee IN (SELECT first_name, last_name
FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE(employee.first_name || ' ' || employee.last_name);
END LOOP;
END;

Example Output:

Conclusion

In this post, you encountered three main types of loops in Oracle PL/SQL language. You can check the documentation to explore further.