All for Joomla All for Webmasters

PL/SQL Exit Loop (Basic Loop)

PL/SQL exit loop is used when a set of statements is to be executed at least once before the termination of the loop. There must be an EXIT condition specified in the loop, otherwise the loop will get into an infinite number of iterations. After the occurrence of EXIT condition, the process exits the loop.

Syntax of basic loop:

  1. LOOP
  2.   Sequence of statements;
  3. END LOOP;

Syntax of exit loop:

  1. LOOP
  2.    statements;
  3.    EXIT;
  4.    {or EXIT WHEN condition;}
  5. END LOOP;

Example of PL/SQL EXIT Loop

Let’s take a simple example to explain it well:

  1. DECLARE
  2. i NUMBER := 1;
  3. BEGIN
  4. LOOP
  5. EXIT WHEN i>10;
  6. DBMS_OUTPUT.PUT_LINE(i);
  7. i := i+1;
  8. END LOOP;
  9. END;

After the execution of the above code, you will get the following result:

1
2
3
4
5
6
7
8
9
10

Note: You must follow these steps while using PL/SQL Exit Loop.

  • Initialize a variable before the loop body
  • Increment the variable in the loop.
  • You should use EXIT WHEN statement to exit from the Loop. Otherwise the EXIT statement without WHEN condition, the statements in the Loop is executed only once.

PL/SQL EXIT Loop Example 2

  1. DECLARE
  2. VAR1 NUMBER;
  3. VAR2 NUMBER;
  4. BEGIN
  5. VAR1:=100;
  6. VAR2:=1;
  7. LOOP
  8. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
  9. IF (VAR2=10) THEN
  10. EXIT;
  11. END IF;
  12. VAR2:=VAR2+1;
  13. END LOOP;
  14. END;

Output:

100
200
300
400
500
600
700
800
900
1000
PinIt
submit to reddit

Leave a Reply

Top