7/25/12

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;


SHARE THIS POST:

Related Posts:

  • SQL Data Definition Language (DDL) Tutorial After writing a post on Data Manipulation Language (DML), here is another tutorial in SQL series on DDL i.e. Data Definition Language. DDL (Data Definition Language) statements are used to build and modify the structur… Read More
  • Variables and Constants in PL/SQL This is out first of many tutorial in the PL/SQL series. Today we will be discussing about variables and coonstants in PL/SQL. PL/SQL stands for Procedural Language to Structured Query Language. We can use PL/SQL in the … Read More
  • GROUP BY vs DISTINCT in SQL This question has to come my mind many times and also I have been asked the same question many time. That what should be used GROUP BY or DISTINCT, as both perform the same thing. So, after researching and using both stat… Read More
  • 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  … Read More
  • SQL Tutorial on Data Manipulation Language (DML) After writing a tutorial on SQL Baiscs, long back, here I am writing again on SQL, but this time on DML i.e. Data Manipulation Language. By the full form of DML we understand that with the help of this we will be able… Read More

0 comments:

Post a Comment