PL/SQL Control Statement


IF Statement

The IF statement executes a sequence of statements depending on the value of a condition.

There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

Using the IF-THEN Statement:

SET SERVEROUTPUT ON;
DECLARE
 A NUMBER := 100;
 B NUMBER := 200;
BEGIN 
 IF A < B THEN
  DBMS_OUTPUT.PUT_LINE('a is less than b');
 END IF;
END;
/

Using the IF-THEN-ELSE Statement:

SET SERVEROUTPUT ON;
DECLARE
 A NUMBER := 200;
 B NUMBER := 100;
BEGIN 
 IF A < B THEN
  DBMS_OUTPUT.PUT_LINE('a is less than b');
 ELSE
  DBMS_OUTPUT.PUT_LINE('a is greater than b');
 END IF;
END;
/

Nested IF-THEN-ELSE Statement:

SET SERVEROUTPUT ON;
DECLARE
 A NUMBER := 100;
 B NUMBER := 200;
 C NUMBER := 300;
BEGIN 
 IF A < B THEN
   IF B < C THEN
    DBMS_OUTPUT.PUT_LINE('a is less than c');
   ELSE
    DBMS_OUTPUT.PUT_LINE('c is greater than b');
   END IF;
 ELSE
  DBMS_OUTPUT.PUT_LINE('a is greater than b');
 END IF;
END;
/

Using the IF-THEN-ELSIF Statement:

SET SERVEROUTPUT ON;
DECLARE
 A NUMBER := 400;
 B NUMBER := 350;
 C NUMBER := 300;
BEGIN 
  IF A < B THEN
    DBMS_OUTPUT.PUT_LINE('a is less than b');
  ELSIF B < C THEN
    DBMS_OUTPUT.PUT_LINE('b is less than c');
  ELSE
   DBMS_OUTPUT.PUT_LINE('a is greater than b');
  END IF;
END;
/

CASE Statement

Like the IF statement, the CASE statement selects one sequence of statements to execute.

However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.

Using the CASE Statement:

SET SERVEROUTPUT ON;
DECLARE
    COLOR CHAR(1);
BEGIN
  COLOR := 'B';
  CASE COLOR
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Amber');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Blue');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Red');
   WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Yellow');
   WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Green');
   ELSE DBMS_OUTPUT.PUT_LINE('No such color');
 END CASE;
END;
/

Using the Searched CASE Statement:

SET SERVEROUTPUT ON;
DECLARE
    COLOR CHAR(1);
BEGIN
  COLOR := 'G';
  CASE
    WHEN COLOR = 'A' THEN DBMS_OUTPUT.PUT_LINE('Amber');
    WHEN COLOR = 'B' THEN DBMS_OUTPUT.PUT_LINE('Blue');
    WHEN COLOR = 'C' THEN DBMS_OUTPUT.PUT_LINE('Red');
   WHEN COLOR = 'D' THEN DBMS_OUTPUT.PUT_LINE('Yellow');
   WHEN COLOR = 'F' THEN DBMS_OUTPUT.PUT_LINE('Green');
   ELSE DBMS_OUTPUT.PUT_LINE('No such color');
 END CASE;
END;
/

Using EXCEPTION Instead of ELSE Clause in CASE Statement:

SET SERVEROUTPUT ON;
DECLARE
    COLOR CHAR(1);
BEGIN
  COLOR := 'G';
  CASE
    WHEN COLOR = 'A' THEN DBMS_OUTPUT.PUT_LINE('Amber');
    WHEN COLOR = 'B' THEN DBMS_OUTPUT.PUT_LINE('Blue');
    WHEN COLOR = 'C' THEN DBMS_OUTPUT.PUT_LINE('Red');
   WHEN COLOR = 'D' THEN DBMS_OUTPUT.PUT_LINE('Yellow');
   WHEN COLOR = 'F' THEN DBMS_OUTPUT.PUT_LINE('Green');
 END CASE;
EXCEPTION
 WHEN CASE_NOT_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('No such color');
END;
/

Loop Statement

A LOOP statement executes a sequence of statements multiple times. PL/SQL provides the following loop statements:

  • Basic LOOP
  • WHILE LOOP
  • FOR LOOP
  • Cursor FOR LOOP
To exit a loop, PL/SQL provides the following statements:
  • EXIT
  • EXIT-WHEN
To exit the current iteration of a loop, PL/SQL provides the following statements:
  • CONTINUE
  • CONTINUE-WHEN

Using the Basic LOOP Statement:

LOOP
  sequence_of_statements
END LOOP;

Using the LOOP with EXIT Statement:

SET SERVEROUTPUT ON;
DECLARE
    num NUMBER := 0;
BEGIN
  LOOP
   DBMS_OUTPUT.PUT_LINE('Num: '||  num);
    num := num + 1;
    IF num > 5 THEN
     EXIT;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exit here.');
END;
/
Output: 
anonymous block completed
Num: 0
Num: 1
Num: 2
Num: 3
Loop exit here.

Using the EXIT-WHEN Statement:

SET SERVEROUTPUT ON;
DECLARE
    num NUMBER := 0;
BEGIN
  LOOP
   DBMS_OUTPUT.PUT_LINE('Num: '||  num);
    num := num + 1;
     EXIT WHEN num > 3;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exit here.');
END;
/
Output: 
anonymous block completed
Num: 0
Num: 1
Num: 2
Num: 3
Loop exit here.

Using the CONTINUE Statement:

SET SERVEROUTPUT ON;
DECLARE
    num NUMBER := 0;
BEGIN
  LOOP  -- After CONTINUE statement, control resumes here
   DBMS_OUTPUT.PUT_LINE('Num: '||  num);
    num := num + 1;
      IF num < 3 THEN
         CONTINUE;
      END IF;
       DBMS_OUTPUT.PUT_LINE('Inside loop when num >= 3 .');
     EXIT WHEN num > 4;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exit here.');
END;
/
Output:
anonymous block completed
Num: 0
Num: 1
Num: 2
Inside loop when num >= 3 .
Num: 3
Inside loop when num >= 3 .
Num: 4
Inside loop when num >= 3 .
Loop exit here.

Using the CONTINUE-WHEN Statement:

SET SERVEROUTPUT ON;
DECLARE
    num NUMBER := 0;
BEGIN
  LOOP  -- After CONTINUE statement, control resumes here
   DBMS_OUTPUT.PUT_LINE('Num: '||  num);
    num := num + 1;
     CONTINUE WHEN num < 3;
       DBMS_OUTPUT.PUT_LINE('Inside loop when num >= 3 .');
     EXIT WHEN num > 4;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Loop exit here.');
END;
/
Output:
anonymous block completed
Num: 0
Num: 1
Num: 2
Inside loop when num >= 3 .
Num: 3
Inside loop when num >= 3 .
Num: 4
Inside loop when num >= 3 .
Loop exit here.

Labeling a PL/SQL Loop:

SET SERVEROUTPUT ON;
DECLARE
      s  PLS_INTEGER := 0;
      i  PLS_INTEGER := 0;
      j  PLS_INTEGER;
  BEGIN
    <>
    LOOP
      i := i + 1;
      j := 0;
     <>
       LOOP
         j := j + 1;
         s := s + i * j; -- Sum several products
         EXIT inner_loop WHEN (j > 5);
         EXIT outer_loop WHEN ((i * j) > 15);
       END LOOP inner_loop;
     END LOOP outer_loop;
     DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
 END;
 /
Output:
anonymous block completed
The sum of products equals: 166

Using the WHILE-LOOP Statement:

Basic structure of WHILE loop: 
WHILE condition LOOP
  sequence_of_statements
END LOOP;

Example:
SET SERVEROUTPUT ON;
DECLARE
      a  PLS_INTEGER := 1;
      b  PLS_INTEGER := 3;
  BEGIN
    WHILE A < B LOOP
      A := A + 1;
       DBMS_OUTPUT.PUT_LINE('The value of a is: ' || TO_CHAR(a));
    END LOOP;
     DBMS_OUTPUT.PUT_LINE('a is equals to b');
 END;
 /
Output:
anonymous block completed
The value of a is: 2
The value of a is: 3
a is equals to b

To ensure that a WHILE loop executes at least once, use an initialized Boolean variable in the condition, as follows:

SET SERVEROUTPUT ON;
DECLARE
      var Varchar2(10);
      done BOOLEAN := false;
      
  BEGIN
    WHILE NOT DONE LOOP
       DBMS_OUTPUT.PUT_LINE('Loop executes util flag is false.');
       done := true;
    END LOOP;
     DBMS_OUTPUT.PUT_LINE('Loop exit when flag is true.');
 END;
 /

Output:
anonymous block completed
Loop executes util flag is false.
Loop exit when flag is true.

FOR-LOOP Statement

Simple FOR loops iterate over a specified range of integers (lower_bound .. upper_bound). The number of iterations is known before the loop is entered. The range is evaluated when the FOR loop is first entered and is never re-evaluated. If lower_bound equals upper_bound, the loop body is executed once.

Simple FOR-LOOP Statement:

SET SERVEROUTPUT ON;
  BEGIN
   FOR I IN 1..3 LOOP 
       DBMS_OUTPUT.PUT_LINE('for loop iterrating i:' || i);
    END LOOP;
 END;
 /
Output:
anonymous block completed
for loop iterrating i:1
for loop iterrating i:2
for loop iterrating i:3

Reverse FOR-LOOP Statement:

SET SERVEROUTPUT ON;
  BEGIN
   FOR I IN REVERSE 1..3 LOOP 
       DBMS_OUTPUT.PUT_LINE('for loop iterrating i:' || i);
    END LOOP;
 END;
 /
Output:
anonymous block completed
for loop iterrating i:3
for loop iterrating i:2
for loop iterrating i:1

Reverse FOR-LOOP Statement:

SET SERVEROUTPUT ON;
 DECLARE
  first  INTEGER := 1;
  last  INTEGER := 3;
  BEGIN
   FOR i IN REVERSE first..last LOOP 
       DBMS_OUTPUT.PUT_LINE('for loop iterrating i:' || i);
    END LOOP;
 END;
 /
Output:
anonymous block completed
for loop iterrating i:3
for loop iterrating i:2
for loop iterrating i:1

Note: Inside a FOR loop, the counter can be read but cannot be changed.

FOR-LOOP with Lower Bound > Upper Bound:

CREATE OR REPLACE PROCEDURE p
  (limit IN INTEGER) IS
BEGIN
  FOR i IN 2..limit LOOP
    DBMS_OUTPUT.PUT_LINE('Inside loop, limit is ' || i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Outside loop, limit is ' || TO_CHAR(limit));
END;
/
   
SET SERVEROUTPUT ON;
BEGIN
  P(2);
  P(1);
END;

Output:
anonymous block completed
Inside loop, limit is 2
Outside loop, limit is 2
Outside loop, limit is 1

Note: The loop counter is defined only within the loop. You cannot reference that variable name outside the loop. After the loop exits, the loop counter is undefined.

Using Existing Variable as Loop Variable:

SET SERVEROUTPUT ON;
DECLARE
 i number := 5;
BEGIN
  FOR i IN 2..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Inside loop, i is ' || TO_CHAR(i));
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Outside loop, i is ' || TO_CHAR(i));
END;
/
OUTPUT:
anonymous block completed
Inside loop, i is 2
Inside loop, i is 3
Outside loop, i is 5

Usage of EXIT and Label in Loop statement:

BEGIN
<>
  FOR i IN 1..10 LOOP
   -- Process data here
   FOR J IN 1..10 LOOP
     EXIT outer_loop WHEN j > 5;
     -- Process data here
   END LOOP;
 END LOOP outer_loop;
END;
/

Sequential Control (GOTO and NULL Statements)

Unlike the IF and LOOP statements, the GOTO and NULL statements are not crucial to PL/SQL programming. The GOTO statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The NULL statement can improve readability by making the meaning and action of conditional statements clear

Using the GOTO Statement:

SET SERVEROUTPUT ON;
DECLARE
  p  VARCHAR2(30);
  n  PLS_INTEGER := 37;
BEGIN
  FOR j in 2..ROUND(SQRT(n)) LOOP
    IF n MOD j = 0 THEN
      p := ' is not a prime number';
      GOTO print_now;
    END IF;
 END LOOP;

 p := ' is a prime number';

 <>
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/
Output:
anonymous block completed
37 is a prime number

Note: A label can appear only before a block or before a statement , not within a statement. See below Example.

Using a NULL Statement to Allow a GOTO to a Label:

SET SERVEROUTPUT ON;
DECLARE
  done  BOOLEAN;
BEGIN
 FOR i IN 1..50 LOOP
    IF done THEN
      GOTO end_loop;
    END IF;
    <>
    null;   --if you comment this line, it will give error, try it.
  END LOOP;
END;
/

GOTO Statement Cannot Branch into IF Statement:

SET SERVEROUTPUT ON;
DECLARE
  valid BOOLEAN := TRUE;
BEGIN
  GOTO update_row;

  IF valid THEN
    <>
    NULL;
  END IF;
END;
/
Error report:
line 4, column 3:
illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW'
line 6, column 12:

GOTO Statement Restrictions

  • A GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or sub-block.
  • A GOTO statement cannot branch from one IF statement clause to another, or from one CASE statement WHEN clause to another.
  • A GOTO statement cannot branch from an outer block into a sub-block (that is, an inner BEGIN-END block).
  • A GOTO statement cannot branch out of a subprogram. To end a subprogram early, either use the RETURN statement or have GOTO branch to a place right before the end of the subprogram.
  • A GOTO statement cannot branch from an exception handler back into the current BEGIN-END block. However, a GOTO statement can branch from an exception handler into an enclosing.
    • block.

Next Article: PL/SQL Collections and Records


For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)

No comments:

Post a Comment

Subscribe to: Comments (Atom)