PL/SQL Control Statements - (IF-ELSE, CASE, LOOP, FOR LOOP, WHILE)

byNikhil Ranjan •
1
In previous post we discussed about variable declarations and operators in Pl/SQL.Here we will discuss about various control statements available in PL/SQL. Control statements means - PL/SQL construct which can be used for conditional program execution (IF-ELSE,CASE), iteration of data source/result set retrieved from database (LOOP, FOR LOOP, WHILE) and sequential control statements like GOTO and NULL.Conditional statement is broadly classified in three category :
1. Conditional statements
2. Loop statements
3.Sequential control statements

Conditional selection statements:

IF statement is used mostly for conditional execution of statements or block of statements. IF has three different form:
1. IF THEN statement - condition is evaluated to true then statements are executed else does nothing.
IF condition THEN
     executable statements  -- if condition is true, statements is executed
END IF;
2. IF THEN ELSE statement- condition is evaluated true then statements are executed otherwise else_statements are executed. ONLY one of them statements and else_statements will be executed.  
IF condition THEN
  executable statements -- if condition is true, statements is executed
ELSE
  else_statements -- -- if condition is false,NULL else_statements is executed
END IF;
3. IF THEN ELSIF statement - If want to execute code blocks base on multiple condition execution , then we use IF THEN ELSIF. If condition_1 is evaluated true then statement_1 is executed and control reaches to END IF, otherwise condition_2 is evaluated, if true statement_2 is executed and control executed in similar fashion until it reaches to ELSE.   
IF condition_1 THEN
  executable statements_1 -- if condition_1 is true, statements is executed
ELSIF condition_2 THEN
  executable statements_2 -- if condition_2 is true, statements is executed
[ ELSIF condition_3 THEN  -- optional 
   executable statements_3
]
[ ELSE    --optional
    else_statements
]
END IF;
Below is the sample PL/SQL program to demonstrate the conditional statements.
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Excellent');
  ELSIF grade = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('Very Good');
  ELSIF grade = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('Good');
  ELSIF grade = 'D' THEN
    DBMS_OUTPUT. PUT_LINE('Fair');
  ELSIF grade = 'F' THEN
    DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END IF;
END;
When we execute this PL/SQL program, "Very Good" is displayed, since grade value is  B and first IF comparison (grade = 'A' ) resolves to false , so control goes to  to next ELSIF statement and it is true so its DBMS_OUTPUT prints Very Good and control reaches to END IF.

CASE Statement is used in PL/SQL for execution of statements based on selector value match and condition match. CASE statement based on selector match is called simple case and syntax for this type is as follows. Here selector value is matched with selector_1, then statement_1 is executed otherwise it matches with other selector and finally if does not matches any selector ELSE is executed, if available.  
CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]
Below is the sample program to demonstrate simple case statement:- Here variable grade is the selector of case statement and it's value is 'B'. When program is executed, grade is matched with first selector_value_1 and it returns false ('A' = 'B'). Next selector_value_2 is compared with grade  and it returns true('B' = 'B') so, Very Good is the result of the following program.  
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';

  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;

and CASE statement defined based on condition match is termed as searched case and syntax of this is follows.Here we do not have selector, one by one condition_1, condition_2 is executed and which one gets evaluated true first corresponding statement is executed, and remaining conditions are not evaluated.  If none of conditions  is evaluated to true then else is executed , if available. 
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]
Here is sample program which demonstrate searched case statement, similar to the IF statement.
DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';
  
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
Here grade is initialized with value 'B', when program executed first condition is evaluated and it returns false, so next condition is evaluated and it returns true, Very Good is obtained as output.

LOOP Statements

Loop statements are used to execute a PL/SQL statement/s multiple times base on certain condition. Loop statements can be of different types and its features are as follows :
  1.   Different varieties of Loop are : Basic LOOP, FOR LOOP, CURSOR LOOP, WHILE LOOP
  2.  Loop cannot be run forever , it need some exit or terminating statements which are :
    EXIT,  EXIT THEN
  3.  Some times we need to stop current iteration/skip current iteration and continue next iteration, in such case we use CONTINUE and CONTINUE WHEN statement. 
  4. LOOP statements can be labeled, and LOOP statements can be nested.It is mandatory to match label in the END LOOP statement to the label at the beginning of the same loop statement (the compiler does not check). 
  5. Assigning a label to LOOP is optional and it is recommended to add it in nested looping to improve readability. <<LABEL_NAME>> is used to label LOOP(both <<  and >> are part of syntax.)
Basic LOOP syntax is as follows. Label is optional and if use label we must end loop with label name. It is programmer's responsibility to provide termination condition for Basic Loop. 
[ label ] LOOP
  statements
END LOOP [ label ];
Below is the sample program using Basic LOOP - print all even number less than equal to  10 (Hint: EXIT loop after that ). Here we have created a LOOP with label My_Loop and ending with the same. For checking Number is even or not REMAINDER function. is used and in inner IF we check for upper limit and once it reaches to 10 loop terminates because of EXIT statement.  
DECLARE 
event_count NUMBER(10);
BEGIN
event_count := 1;
  DBMS_OUTPUT.PUT_LINE ('Print even number from 1 to 10');
  <<My_Loop>> -- Label loop with name My_Loop 
  LOOP 
   IF (REMAINDER(event_count, 2) = 0) THEN 
      DBMS_OUTPUT.PUT_LINE(event_count);
      event_count := event_count + 1; 
      IF(event_count >= 10) THEN --EXIT WHEN can be used instead of IF statement.
         DBMS_OUTPUT.PUT_LINE('Exiting loop now, reached count up to 10');
         EXIT;
      END IF;  
   ELSE  
        event_count := event_count+1;
    END IF; 
  END  LOOP My_Loop; 
END;
========Output of above program========
Print even number from 1 to 10
2
4
6
8
10
Exiting loop now, reached count up to 10
==================================
We can modify above program with EXIT WHEN statement and it length shorten and becomes more readable. Just comment inner IF statement (form IF to END IF) and replace it with
EXIT WHEN  even_Count >=10
CONTINUE and CONTINUE WHEN can also be use in PL/SQL. Below is the sample program to print multiple of 5 between 1 to 30 and it should not be even number.
DECLARE 
multiple_of_5_count NUMBER(10);
div_by_2_check NUMBER(10);
BEGIN
multiple_of_5_count := 1;
div_by_2_check := 0;
  DBMS_OUTPUT.PUT_LINE ('Print non even number which is multiple of 5');
  <<My_Loop>> --Label loop with name My_Loop 
  LOOP 
   IF (REMAINDER(multiple_of_5_count, 5) = 0) THEN 
      div_by_2_check := multiple_of_5_count; -- temporary store current value 
      multiple_of_5_count := multiple_of_5_count + 1; 
      CONTINUE WHEN REMAINDER(div_by_2_check, 2) = 0;
      DBMS_OUTPUT.PUT_LINE(div_by_2_check);
   ELSE 
        multiple_of_5_count := multiple_of_5_count+1; 
        EXIT WHEN multiple_of_5_count >= 30;
    END IF; 
  END  LOOP My_Loop; 
END;
In above program, we starts from 1, check  divisibility by 5 using REMAINDER function. If it is divisible by 5 then check for divisibility by 2. If  given number is divisible by 2 then CONTINUE WHEN returns control back to LOOP and start same process with incremented value.
========Sample output=================
Print non even number which is multiple of 5
5
15
25
 ==================================

FOR LOOP Statement executes statements until the loop index is in a specified range. Consider the FOR LOOP syntax and sample program to illustrate structure of the syntax.
[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ label ];
In FOR loop label and REVERSE are optional and index is just a variable (any valid identifier).
Notes: Some important about index and lower/upper bound in FOR LOOP: 
  1. index is just a variable (any valid identifier) and sometimes index is also termed as loop counter. 
  2. The index of a FOR LOOP statement is implicitly declared as a variable of type PLS_INTEGER that is local to the loop.
  3. The statements in the loop can read the value of the index, but cannot modify it. 
  4. FOR LOOP Statement Index with Same Name as Variable : If index  name is same as a variable declared in DECLARE block then local declaration hides the global declaration. 
  5. Lower Bound and Upper Bound  value should be of  numeric type(literal, variable) or numeric expressions, otherwise PL/SQL will throw VALUE_ERROR exception.
  6.  Accessing references Variable with Same Name as Index: Using TO_CHAR function
    TO_CHAR(<block_name>.i)  -- Qualify reference with block label.
    7. <<main>>  -- Label block.
DECLARE
  i NUMBER := 5;
BEGIN
  FOR i IN 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE (
      'local: ' || TO_CHAR(i) || ', global: ' ||
      TO_CHAR(main.i)  -- Qualify reference with block label.
    );
  END LOOP;
END main;
    In above program, global i is accessed via block_name[dot]variable name.
When FOR loop execution starts- lower_bound value is assigned to index_variable and increments until it reaches to upper_bound and repeat the loop statements execution for each iteration. IF REVERSE is enabled then loop starts from upper _bound and decrements on each iteration and continue until reaches to lower_bound. Consider the following sample program: Print odd numbers between 1 to 10 in reverse order. (Result should be: 9,7,....) 
DECLARE 
lower_bound NUMBER(2) := 1;
upper_bound NUMBER(2) := 10;
ouput_string VARCHAR2(20) := NULL;
BEGIN 
  DBMS_OUTPUT.PUT_LINE ('Print odd number between 1 to 10 in reverse order');
-- REVERSE enabled so looping starts from upper_bound and reaches 1.
  FOR loop_var IN REVERSE  lower_bound..upper_bound LOOP 
    IF(REMAINDER(loop_var,2) != 0) THEN
        --NUMBER converted to string using TO_CHAR(NUMBER) and appended.
        ouput_string := ouput_string || TO_CHAR(loop_var)|| ', ' ;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(ouput_string);
END;
=========Sample output================
Print odd number between 1 to 10 in reverse order
9, 7, 5, 3, 1,
====================================
In FOR LOOP also  EXIT,EXIT WHEN, CONTINUE and CONTINUE WHEN can be used as we saw in Basic LOOP earlier.

FOR LOOP is most commonly used with cursor type, for sake of completeness I am writing sample code for the same, it can be ignored as of now, we will revisit it again.  
DECLARE
  v_locations LOCATIONS%ROWTYPE; --%ROWTYPE refers ROW of LOCATIONS TABLE 
  -- result set is referred by cursor variable 
  CURSOR c1 is SELECT * FROM LOCATIONS where COUNTRY_ID = 'US'; 
BEGIN
  OPEN c1;
  -- Fetch entire row into v_employees record:
  FOR i IN 1..3 LOOP
    FETCH c1 INTO v_locations;
    EXIT WHEN c1%NOTFOUND; -- Exit when no record found
    DBMS_OUTPUT.PUT_LINE (i||' City is '|| v_locations.CITY);
  END LOOP;
  CLOSE c1;
END;
=======Sample output======
 1 City is Southlake
2 City is South San Francisco
3 City is South Brunswick
=======================
WHILE LOOP Statement runs one or more statements while a condition is true. It has following structure: 
[ label ] WHILE condition LOOP
  statements
END LOOP [ label ];
Like other loops discussed above, EXIT, EXIT WHEN, CONTINUE, or CONTINUE WHEN can cause the loop or the current iteration of the loop to end early.

 Sequential Control Statements

GOTO Statement and NULL Statement belong to this category. It is recommended not to use GOTO statements even tough it makes life easy, it degrade readability.
GOTO Statement - The GOTO statement transfers control to a label unconditionally.
NULL Statement - It just passes control to next statement and termed as no operation statements. It is used to : To create placeholders and stub subprograms and To improve readability by making the meaning and action of conditional statements clear.
Invalid anonymous PL/SQL block - Body cannot be empty Valid anonymous PL/SQL block - NULL; makes it valid
DECLARE
   BEGIN   -- nothing is body,
END; 		DECLARE
     BEGIN
     NULL; -- No-op,makes program valid one,
END;

In next post we will learn PL/SQL Composite data type - Collections & Record and followed by reference types - cursors.

Previous: PL/SQL- Language fundamentals and Data Types Next: PL/SQL Composite data type
Tags:

1 Comments

Previous Post Next Post