To the point guide to PL/SQL

In some situation a block of statements need to be executing several times this is achieved by using loop.

There are four types

• Basic Loop
• For Loop
• While Loop

Basic Loop

The basic loop structure has the LOOP and END LOOP keywords encloses the set of statements.

Syntax:

LOOP 
   statement(s);
END LOOP;

Example

DECLARE
   num NUMBER := 3;
BEGIN
	LOOP
	  DBMS_OUTPUT.PUT_LINE ('The value:  num = ' || num);
    	num := num -1;
    	IF num = 0 THEN
      		EXIT;
    	END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Loop Outside');   
END;
/

Result:

The value: num = 3
The value: num = 2
The value: num = 1
Loop Outside 
PL/SQL procedure successfully completed.

For Loop

This loop allows us to execute a block of code for a specific number of times.

Syntax

FOR counter IN initial_value.. final_value LOOP
   statements;
END LOOP;

Here are couple of points that you should be aware of with regards to For loop

• The counter increment (or decrement) must be 1 hence initial_value need not to be 1.
• The initial_value and final_value of the loop variable or expressions but must evaluate to numbers.

Example:

BEGIN
      FOR num IN 1.. 3 LOOP
      DBMS_OUTPUT.PUT_LINE('Value of num : ' || num);
     END LOOP;
END;
/

Result:

Value of num : 1
Value of num : 2
Value of num : 3
PL/SQL procedure successfully completed.

FOR Loop  REVERSE statement

Usually iterations takes place from the lower to the higher bound here REVERSE is optional keyword introduce to iteration is proceed from higher to lower bound. 

Example:

BEGIN
     FOR num IN REVERSE 3.. 1 LOOP
     DBMS_OUTPUT.PUT_LINE('Value of num : ' || num);
     END LOOP;
END;
/

Result:

Value of num : 3
Value of num : 2
Value of num : 1
PL/SQL procedure successfully completed.

While Loop

This loop provides condition, to iteratespecific statements based on condition.

If condition fails/not met then statements will not execute inside the while loop. 

Syntax:

WHILE condition LOOP 
           statement;
END LOOP;
Example:
DECLARE
   num NUMBER := 0;
BEGIN
    WHILE no <=3 LOOP
    dbms_output.put_line('value of number: ' || num);
     num := num + 1;
     END LOOP;
  END;
/

Result:

value of number: 0
value of number: 1
value of number: 2
value of number: 3
PL/SQL procedure successfully completed.

Loop Control Statements

PL/SQL Loop Control Statements are used to control iteration loop, The following Loop Control Statements are:

EXIT Statement

EXIT statement is used to exit from the loop immediately and control goes to the next statement inside the program.

Syntax:

LOOP 
   statement;
   EXIT; 
END LOOP;
Example:
DECLARE
   num NUMBER := 3;
BEGIN
    LOOP
          DBMS_OUTPUT.PUT_LINE ('Inside Loop the num value is = ' || num);
          num := num -1;
          IF num = 0 THEN
       	EXIT;
    	END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Next line after the END LOOP');   -- The control transfer jump to this statement
END;
/

Result:

Inside Loop the num value is = 3
Inside Loop the num value is = 2
Inside Loop the num value is = 1
Next line after the END LOOP
PL/SQL procedure successfully completed.

EXIT WHEN Statement

EXIT WHEN statement is used to exit from the loop immediately when WHEN clause condition becomes true.

Syntax:

LOOP 
   statements;
   EXIT WHEN condition;
END LOOP;

Example:
DECLARE
	x number;
BEGIN
	LOOP 
	 dbms_output.put_line('PL/SQL');
	 x:=x+1;
	EXIT WHEN x>4;
	END LOOP; 
END;
/

Result:

PL/SQL
PL/SQL
PL/SQL
PL/SQL procedure successfully completed.

CONTINUE Statement

Continue is keyword used to unconditionally skip the current iteration and execute the next iteration. Syntax: CONTINUE; Example:

DECLARE
   num NUMBER := 0;
BEGIN
	FOR num IN 1.. 4
                LOOP
	    IF i = 3 THEN
	    CONTINUE;
	    END IF;
	    DBMS_OUTPUT.PUT_LINE('The value of num is : ' || num);
  	END LOOP;
END;
/

Result:

The value of num is : 1
The value of num is : 2
The value of num is : 4

PL/SQL procedure successfully completed.

CONTINUE WHEN Statement

Continue is keyword used to unconditionally skip the current iteration when WHEN clause condition becomes true.

Syntax:

CONTINUE WHEN condition;
	statement;
Example:
DECLARE
   num NUMBER := 0;
BEGIN
	FOR no IN 1.. 4 LOOP
	    DBMS_OUTPUT.PUT_LINE('The value of num is:  ' || num);
	    CONTINUE WHEN no = 3
	    DBMS_OUTPUT.PUT_LINE('Execution CONTINUE  When iteration: ' || num);
  	END LOOP;
END;
/

Result:

The value of num is: 1
The value of num is: 2
'Execution CONTINUE When iteration: 3
The value of num is: 4
PL/SQL procedure successfully completed.

GOTO Statement

This keyword unconditionally transfers control of program.

Syntax:

GOTO codename

Example

BEGIN
FOR x IN 1..3 LOOP
	dbms_output.put_line(x);
	IF x=2 THEN
	GOTO print;
	END IF;	
END LOOP; 
<>
DBMS_OUTPUT.PUT_LINE(' Inside print and its value 2!!! ');
END;
/

Result:

1
2
Inside print and its value 2!!!
PL/SQL procedure successfully completed.