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

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

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

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