Loops In Pl-Sql

Iterative Statements in PL/SQL

An iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times. These are similar to those in

There are three types of loops in PL/SQL:
• Simple Loop
• While Loop
• For Loop

1) Simple Loop

A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

The General Syntax to write a Simple Loop is:

LOOP 

   statements; 

   EXIT; 

   {or EXIT WHEN condition;}

END LOOP; 

These are the important steps to be followed while using Simple Loop.

1) Initialise a variable before the loop body.
2) Increment the variable in the loop.
3) Use a EXIT WHEN statement to exit from the Loop. If you use a EXIT statement without WHEN condition, the statements in the loop is executed only once.

DECLARE

     counter INTEGER := 2;

 BEGIN

     counter := 0;

     WHILE counter < 6 LOOP

       counter := counter + 1;

       DBMS_OUTPUT.PUT_LINE(counter);

     END LOOP;

 END;

Output :-

1

2

3

4

5

6

The PL/SQL WHILE Loop

The WHILE loop, also called a conditional loop, evaluates a condition before each loop executes, and if false, the loop is terminated.  If the expression is false when the program reaches the WHILE loop, the loop code is jumped and never executed.  Use a WHILE loop when the condition test is required at the start of the loop.  The next example contains three WHILE loops.

DECLARE

  CURSOR emp_cursor IS

   SELECT employee_id, last_name FROM employees

   WHERE department_id =30;

  empno employees.employee_id%TYPE;

  lname employees.last_name%TYPE;

BEGIN

  OPEN emp_cursor;

  FETCH emp_cursor INTO empno, lname;

  WHILE  emp_cursor%FOUND LOOP

    DBMS_OUTPUT.PUT_LINE( empno ||'  '||lname);

    FETCH emp_cursor INTO empno, lname;

  END LOOP;

END;

/

Output:-

114  Raphaely

115  Khoo

116  Baida

117  Tobias

118  Himuro

119  Colmenares

FOR Loop

A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reachs the value of the end integer.
The General Syntax to write a FOR LOOP is:

FOR counter IN val1..val2 

  LOOP statements; 

END LOOP; 

• val1 - Start integer value.
• val2 - End integer value.

begin

    for idx in 2..5 loop

      dbms_output.put_line (idx);

    end loop;

  end;

Output :-

2

3

4

5