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

QUERY_UTPUT

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

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.

    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

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
          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

example 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

statement process

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

statement process 2