PL/SQL Control Statements in Oracle

Control Statements

  • Control statements are most important in PL/SQL.
  • Control Statements are elements in a program that control the flow of program execution.
  • The syntax of control statements are very similar to regular English and are very similar to choices that we make every day.
  • Branching statements are as follows:

    • If statement
    • If – THEN – ELSE
    • Nested IF
    • Branching with logical connectivity
    • While
    • For Loop

If statement

Syntax:

   IF condition THEN

         Statements;

   END IF;

  • “IF..THEN” is a reserved word and “END IF” is reserved phrase that indicates end of “IF..THEN”
  • IF..THEN is executed, A condition is either TRUE or FALSE
Example

IF price of book is less then I will buy the book.
    1. DECLARE  
    2.   
    3.    no1 NUMBER :=20;    
    4.    no2 NUMBER;  
    5.   
    6. BEGIN  
    7.   
    8.    IF no1=20 THEN  
    9.   
    10.    no2:=20;  
    11.   
    12.    DBMS_OUTPUT.PUT_LINE('IF condition is TRUE');  
    13.   
    14.    DBMS_OUTPUT.PUT_LINE('');  
    15.   
    16.    DBMS_OUTPUT.PUT_LINE('Value of no2 is :' || no2);  
    17.   
    18. End If;  
    19. END;  

    Output:

    QUERY OUTPUT

If Then – Else

Syntax:

    IF condition THEN

          Statements;

    ELSE

          Statements;

    END IF;

  • There are two groups first is evaluated when the condition evaluates to TRUE
  • Next group is evaluated when the condition evaluates to FALSE.

Example

  1. If ticket of Hollywood movie is less THEN I will go to Hollywood movie Else I will go to Marathi movie.
    1. DECLARE  
    2.   
    3.    no1 number:=24;  
    4.   
    5.    no2 number:=26;  
    6.   
    7.    no3 number;  
    8.   
    9. BEGIN  
    10.   
    11.    DBMS_OUTPUT.PUT_LINE(‘first number :’|| no1);  
    12.   
    13.    DBMS_OUTPUT.PUT_LINE(‘second number :’|| no2);  
    14.   
    15.    DBMS_OUTPUT.PUT_LINE(‘’);  
    16.   
    17.    If no1 < no2 THEN  
    18.   
    19.    DBMS_OUTPUT.PUT_LINE(‘first number is less’);  
    20.   
    21. ELSE  
    22.   
    23.    DBMS_OUTPUT.PUT_LINE(‘second number is less’);  
    24.   
    25. END IF;  
    26.   
    27. END;  

Output

SQL Query output

Nested If

  • If statements can be nested into one another depending on the programmer's requirements.
  • A nested IF is a condition in which an IF follows another IF immediately for every true state of the IF condition.
  • Every IF block needs to be properly closed with an END IF.
Syntax

If Condition THEN

   If Condition THEN

      If Condition THEN

            Statement1;

      ELSE

            Statement2;

      END IF;

   ELSE

      Statement3;

    END IF;

ELSE

   Statement4;

END IF;

Example

The following is a program to determine whether or not a year is a Leap Year.
  1. DECLARE  

  2.    year NUMBER:=2012;  

  3. BEGIN 
     
  4.    If MOD(year, 4)= 0 THEN  
  5.    IF MOD(year, 100) <> 0 THEN  
  6.    DBMS_OUTPUT.PUT_LINE(year ||' year is leap year');  

  7. ELSE  

  8.    If MOD(year, 400)= 0 THEN  
  9.    DBMS_OUTPUT.PUT_LINE(year ||' year is leap year');  

  10. ELSE  

  11.    DBMS_OUTPUT.PUT_LINE(year ||' year is not leap year'); 
     
  12. END IF;  
  13. END IF; 
     
  14. ELSE  

  15.    DBMS_OUTPUT.PUT_LINE(year ||' year is not leap year');  

  16. END IF;   
  17. END;  
Output

statement process 2

Branching with logical connectivity
  • In some conditions one IF block is associated with a collection of conditions, either a logical “AND” or a logical “OR” operator.

    • IF Condition1 AND Condition2 THEN

            State1;
            State2;

      ELSE

            State3;
            State4;

      END IF;

    • IF Condition1 OR Condition2 THEN

            State1;
            State2;

      ELSE

            State3;
            State4;

      END IF;

    • IF Condition1 AND Condition2 OR Condition3 THEN

            State1;
            State2;

      ELSE

            State3;
            State4;

      END IF;
Example
  1. DECLARE  
  2.   
  3.    my_name VARCHAR(20) :='Rupesh Kahane';  
  4.    degree CHAR(1) :='Y';  
  5.    passport CHAR(1) :='Y';  

  6. BEGIN  

  7.    If degree = 'Y' AND passport = 'Y' THEN  
  8.    DBMS_OUTPUT.PUT_LINE(my_name ||' can apply for passport');  

  9. ELSE  

  10.    DBMS_OUTPUT.PUT_LINE(my_name ||' can not apply for passport');  

  11. END IF;  
  12. END;   
Output

example output


While

Syntax:

WHILE Condition

      LOOP

            State1;
            State2;

      END LOOP;

Example
  1. DECLARE  

  2.    no1 NUMBER(2):=1;  

  3. output VARCHAR2(100);  

  4. BEGIN  

  5.    WHILE no1 <= 15  
  6.    LOOP  

  7. output := output || ' ' || no1;  

  8.    no1 := no1 + 1;  

  9. END LOOP;  

  10.    DBMS_OUTPUT.PUT_LINE(output);  

  11. END;  
Output

statement process

FOR LOOP

Syntax:

FOR Counter IN [REVERSE] LowerBound...UpperBound

   LOOP

         State1;
         State2;

   END LOOP;

Example

Program to Print first 20 numbers
  1. DECLARE  

  2.    StartRange NUMBER:=1;  
  3.    EndRange NUMBER :=20;  

  4. output VARCHAR2(500);  

  5. BEGIN  

  6.    DBMS_OUTPUT.PUT_LINE('');  

  7. FOR myIndex IN StartRange..EndRange  
  8. LOOP  

  9. output := output||' ' ||StartRange; 
     
  10.    StartRange := StartRange +1;  

  11. END LOOP;  

  12.    DBMS_OUTPUT.PUT_LINE(output);  

  13. END;  
Output

statement process 2