Comments

Wednesday, July 25, 2012

Conditional Statements in PL/SQL

Posted by on Wednesday, July 25, 2012 Read our previous post
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;


© 2010 Code 2 Learn