Conditional Statements in PL/SQL

Conditional Statements in PL/SQL are same as conditional statements available in java, c++, c or any other language. PL/SQL allows processing of data using the following conditional statements:

    * IF

    * CASE

IF Statement Construct

The IF statement can be used for the conditional processing of statements. There are three forms of IF statements :

* IF-THEN-END IF
* IF-THEN-ELSE-END IF
* IF-THEN-ELSIF-END IF

Syntax for IF-THEN-ELSE Clause

IF<condition_1> THEN
      <statement_1>
ELSE<condition_2> THEN
      <statement_2>
ELSE
      <statement_3>
      END IF;

The ELSE clause is optional, but if it is used then it should be accompanied with an IF clause. Similarly, the ELSIF Clause is also optional. If the first condition evaluates to FALSE the ELSIF tests another condition. The IF statement can have any number of ELSIF clause.

EXAMPLE 

IF age < minimum_age THEN
          allow:=FALSE;
ELSE
          allow:=TRUE;
END IF;

CASE Statement Construct

CASE statement is like the switch case in most of the programming language. Therefore, CASE performs statement based on multiple conditions.

The CASE statement begins with the keyword CASE followed by an expression.  

The CASE statement has a selector, which is an expression whose value is used to select one of the alternatives.

Syntax to use CASE

CASE <expr_1> 
WHEN <expr_1> THEN
     sequence_of_statements;
WHEN <expr_2> THEN
     sequence_of_statements;
[ELSE
      sequence_of_statements;]
      END CASE;

To select the sequence, the CASE statement uses a selector. The value it yields can have any PL/SQL data type other than BLOB, BFILE, an object type, a PL/SQL record.

The ELSE clause is optional and if it is omitted, PL/SQL adds the following implicit ELSE clause :

ELSE RAISE CASE_NOT_FOUND;

EXAMPLE

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('Failed');
ELSE
     DBMS_OUTPUT.PUT_LINE('No such Grade');
END CASE;