Udemy logo

sql loopSQL 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

  1. Simple Loop
  2. While Loop
  3. For Loop

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

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.

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.

Page Last Updated: June 2014

Top courses in SQL

SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.8 (2,701)
Highest Rated
SQL - The Complete Developer's Guide (MySQL, PostgreSQL)
Academind by Maximilian Schwarzmüller, Maximilian Schwarzmüller, Manuel Lorenz
4.8 (110)
Hot & New
The Complete SQL Bootcamp 2022: Go from Zero to Hero
Jose Portilla
4.7 (115,017)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (61,457)
Bestseller
SQL & PostgreSQL for Beginners: Become an SQL Expert
Jon Avis - SQL Instructor
4.6 (3,956)
Bestseller
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.4 (1,890)
Bestseller
SQL Programming Basics
Global Academy
4.5 (844)
Master SQL For Data Science
Imtiaz Ahmad
4.6 (8,859)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo