SQL Loop : Use Loops to Iterate through Code Effectively

Udemy Editor

SQL stands for Structured Query Language. It is specifically designed to retrieve and manipulate data from relational databases. PL/SQL is a procedural extension of SQL and is well integrated with the latter. This language contains important programming features which are combined with SQL to create useful data driven applications. PL/SQL code can be called from the SQL command line. It supports several data types and incorporates many useful programming structures.PL/SQL can be used for both structured and object oriented programming.

This tutorial requires a basic understanding of PL/SQL. If you’re new to SQL, you may want to first take this course on SQL to get started.

PL/SQL Loop Statements

A loop is a program structure that executes statements repeatedly. This avoids duplication of program code as we may not know how many times the relevant statements should be executed. Normally, it is recommended to use an exit condition to terminate the loop. Otherwise, the loop may iterate infinitely.

There are three main types of loops in PL/SQL

Simple Loop
While Loop
For Loop

There are a few things that you need to take into consideration while using loop statement. They are listed below.

Initializing of variables- this should be done before the LOOP statement.
Incrementing of variables- This is done inside the loop.
EXIT statement- If used alone, it will result in the executions of the statements only once.
EXIT WHEN statement- This is used to exit from the LOOP.

Learn more about loops in SQL with this course.

First type of PL/SQL Loop: Simple Loop

This is used when statements need to be executed at least once. A simple loop must have an EXIT condition. This is to avoid getting into infinite executions. If the EXIT condition is satisfied, the loop terminates. Take a look at the syntax

LOOP

  Sequence of statements;

END LOOP;

You can also have the following version

LOOP

   statements;

   EXIT;

   {or EXIT WHEN condition;}

END LOOP;

Let’s understand the concept of loop better with help of the following examples.

Example 1a: Loop Statement with EXIT

DECLARE counter NUMBER := 0;

BEGIN

  LOOP

    counter := counter + 1;

    DBMS_OUTPUT.PUT_LINE(counter);

    IF counter = 10 THEN

      EXIT;

    END IF;

  END LOOP;

END;

In the above example, the variable counter is initialized to 0. Then we begin the loop by incrementing the counter value by 1. When the value of the counter becomes 10, the IF condition is satisfied and the exit statement is executed. This results in termination of the loop. Note that this loop iterates 10 times. Take this hands on course to learn more about how IF condition is used in PL/SQL.

Example 1b: Loop Statement using EXIT WHEN

 DECLARE counter NUMBER := 0;

BEGIN

  LOOP

    counter := counter + 1;

    DBMS_OUTPUT.PUT_LINE(counter);

    EXIT WHEN counter = 10;

  END LOOP;

END;

This is similar to the above program. However, this can be used as the substitute for IF THEN and EXIT statements together.

Second type of PL/SQL Loop: While Loop

In the WHILE loop the condition is checked at the beginning. If false, the loop terminates without a single execution of the statement. If true, the statements within the loop are executed. The loop will repeat until, the condition becomes false.

Note that you should use the WHILE loop, when you do not know how many times an individual or sequence of statements need to be executed.

WHILE <condition>

 LOOP statements;

END LOOP;

Note that EXIT and EXIT WHEN statements can be used in this type of loop but it is not a very common practice.

Example 2: Using WHILE LOOP to execute database transactions

 DECLARE

    salary  INTEGER;

    mgr_num  INTEGER;

    last_name VARCHAR2;

    starting_empno  CONSTANT NUMBER(4) := 100;

BEGIN

    SELECT sal, mgr INTO salary, mgr_num FROM employee

        WHERE empno = starting_empno;

    WHILE salary < 10000 LOOP

        SELECT sal, mgr, ename INTO salary, mgr_num, last_name

            FROM emp

            WHERE empno = mgr_num;

    END LOOP;

    INSERT INTO temp VALUES (NULL, salary, last_name);

    COMMIT;

END;

Here while the salary variable value is less than 10000, the loop executes. When the condition is false, the loop terminates. Then the insert query is executed and the database transactions are made permanent using the COMMIT command.

Third type of PL/SQL Loop: FOR Loop

When you know the number of iterations in advance, it is advisable to use the FOR loop. The syntax is as follows.

FOR counter IN [REVERSE] lower_bound..higher_bound

LOOP

   statements;

END LOOP;

Let’s examine this syntax in detail.

PL/SQL creates a local variable counter with integer data type automatically. Note that the scope of the variable is confined within the loop itself. Here after each iteration, the counter value is automatically incremented by 1. Also, its value should be within the given range defined by the lower and upper bound values. When the value is not in the range, the loop terminates. In each iteration of the loop the given statements are executed. There is another way to use FOR loop.
REVERSE keyword- Note that this is optional. When included, the counter variable is initialized to the higher bound value. And in each iteration, the variable value is decremented by 1. When the variable value is not in range, the loop terminates and control goes to the next statement following the loop.
Executable statements- It is mandatory to have at least 1 executable statement with in the loop.

Example 3: Program to insert records in table using the FOR loop

DECLARE

    a  NUMBER := 100;

BEGIN

    FOR i IN 1..20 LOOP

     IF MOD(i,2) = 0 THEN

         INSERT INTO temp VALUES (i, a, ‘Even number');

     ELSE

         INSERT INTO temp VALUES (i, a, 'Odd number');

     END IF;

      a := a + 100;

    END LOOP;

    COMMIT;

END;

The variable i in the for loop is implicitly declared as integer type and has scope only within the for loop. The MOD() function determines whether the value of the variable is perfectly divisible by 2. if true, then the insert command inserts a record into the temp table with the values mentioned above. If false, then the other insert command is executed. After each iteration, i is incremented by one.

Do study the examples and try to create your own code. That’s the most effective way to master programming concepts. If you’d like to learn more about loops take this advanced course on PL/SQL loops.