PL/SQL Control Statements in Oracle

Control Statements

• Control statements are most important in PL/SQL.
• Control Statements are elements in a program that controls the flow of program execution.
• The syntax of control statements is very similar to regular English and is very similar to the 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 and THEN are reserved words and END IF is a reserved phrase that indicates the end of IF and THEN
• IF and THEN are executed only when a condition is either TRUE or FALSE

Example If the price of the book is less then I will buy the book.

    DECLARE  
       no1 NUMBER :=20;    
       no2 NUMBER;  
    BEGIN  
       IF no1=20 THEN  
       no2:=20;  
       DBMS_OUTPUT.PUT_LINE('IF condition is TRUE');  
       DBMS_OUTPUT.PUT_LINE('');  
       DBMS_OUTPUT.PUT_LINE('Value of no2 is :' || no2);  
    End If;  
    END;  

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
• The next group is evaluated when the condition evaluates to FALSE.

Example

If the ticket for a Hollywood movie is less THEN I will go to a Hollywood movie Else I will go to a Marathi movie.

    DECLARE  
       no1 number:=24;  
       no2 number:=26;  
       no3 number;  
    BEGIN  
       DBMS_OUTPUT.PUT_LINE(‘first number :’|| no1);  
       DBMS_OUTPUT.PUT_LINE(‘second number :’|| no2);  
       DBMS_OUTPUT.PUT_LINE(‘’);  
       If no1 < no2 THEN  
       DBMS_OUTPUT.PUT_LINE(‘first number is less’);  
    ELSE  
       DBMS_OUTPUT.PUT_LINE(‘second number is less’);  
    END IF;  
    END;  

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.

    DECLARE  
       year NUMBER:=2012;  
    BEGIN 
       If MOD(year, 4)= 0 THEN  
       IF MOD(year, 100) <> 0 THEN  
       DBMS_OUTPUT.PUT_LINE(year ||' year is leap year');  
    ELSE  
       If MOD(year, 400)= 0 THEN  
       DBMS_OUTPUT.PUT_LINE(year ||' year is leap year');  
    ELSE  
       DBMS_OUTPUT.PUT_LINE(year ||' year is not leap year'); 
    END IF;  
    END IF; 
    ELSE  
       DBMS_OUTPUT.PUT_LINE(year ||' year is not leap year');  
    END IF;   
    END;  

Output

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
          Statement1;
          Statement2;
    ELSE
          Statement3;
          Statement4;
    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

    DECLARE  
       my_name VARCHAR(20) :='Rupesh Kahane';  
       degree CHAR(1) :='Y';  
       passport CHAR(1) :='Y';  
    BEGIN  
       If degree = 'Y' AND passport = 'Y' THEN  
       DBMS_OUTPUT.PUT_LINE(my_name ||' can apply for passport'); 
    ELSE  
       DBMS_OUTPUT.PUT_LINE(my_name ||' can not apply for passport');  
    END IF;  
    END;   

Output

WHILE LOOP

Syntax

    WHILE Condition
          LOOP
                State1;
                State2;
          END LOOP;

Example

    DECLARE  
       no1 NUMBER(2):=1;  
    output VARCHAR2(100);  
    BEGIN  
       WHILE no1 <= 15  
       LOOP  
    output := output || ' ' || no1;  
       no1 := no1 + 1;  
    END LOOP;  
       DBMS_OUTPUT.PUT_LINE(output);  
    END;  

Output

FOR LOOP

Syntax

    FOR Counter IN [REVERSE] LowerBound...UpperBound
       LOOP
             State1;
             State2;
       END LOOP;

Example Program to Print first 20 numbers

    DECLARE  
       StartRange NUMBER:=1;  
       EndRange NUMBER :=20;  
    output VARCHAR2(500);  
    BEGIN  
       DBMS_OUTPUT.PUT_LINE('');  
    FOR myIndex IN StartRange..EndRange  
    LOOP  
    output := output||' ' ||StartRange; 
       StartRange := StartRange +1;  
    END LOOP;  
       DBMS_OUTPUT.PUT_LINE(output);  
    END;  

Output