PL/SQL Conditional Statements

PL/SQL supports the conditional statements statements like other programming languages such as C++, java etc.

The conditional selection statements, IF and CASE, run different statements for different data values.

The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms:

  • IF THEN
  • IF THEN ELSE
  • IF THEN ELSIF

The CASE statement chooses from a sequence of conditions, and runs the corresponding statement. The CASE statement has these forms:

  • Simple, which evaluates a single expression and compares it to several potential values.
  • Searched, which evaluates multiple conditions and chooses the first one that is true.

IF THEN Statement

IF THEN statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is true, the statements get executed and if the condition is false or NULL then IF statement does nothing.

Syntax :

IF condition THEN
  statements
 END IF;

Example :

DECLARE
a number(3) :=100;
b number(3) :=150;
BEGIN
if a < b then
dbms_output.put_line ('b is the highest number');
END IF;
END;

b is the highest number

PL/SQL procedure successfully completed.

IF THEN ELSE

IF statment adds the keyword ELSE followed by an alternative sequence of statements. If the condition is false or NULL, then alternative sequenceof statements get executed. It ensure that either of sequence of statements are executed.

Sytax :

IF condition THEN
  statements
 ELSE
  else_statements
 END IF;
DECLARE
a number(3) :=100;
b number(3) :=150;
BEGIN
if a > b then
dbms_output.put_line ('a is the highest number');
ELSE
dbms_output.put_line ('b is the highest number');
END IF;
END;

b is the highest number

PL/SQL procedure successfully completed.

IF THEN ELSIF

The IF THEN ELSIF statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the else_statements run, if they exist; otherwise, the IF THEN ELSIF statement does nothing.

Syntax :

IF condition_1 THEN
  statements_1
 ELSIF condition_2 THEN
  statements_2
 [ ELSIF condition_3 THEN
  statements_3
 ]...
 [ ELSE
  else_statements
 ]
 END IF;

Example :

DECLARE
 grade CHAR(1);
BEGIN
 grade := 'C';
 
 IF grade = 'A' THEN
 DBMS_OUTPUT.PUT_LINE('Excellent');
 ELSIF grade = 'B' THEN
 DBMS_OUTPUT.PUT_LINE('Very Good');
 ELSIF grade = 'C' THEN
 DBMS_OUTPUT.PUT_LINE('Good');
 ELSIF grade = 'D' THEN
 DBMS_OUTPUT. PUT_LINE('Fair');
 ELSIF grade = 'F' THEN
 DBMS_OUTPUT.PUT_LINE('Poor');
 ELSE
 DBMS_OUTPUT.PUT_LINE('No such grade');
 END IF;
END;
/


Good

PL/SQL procedure successfully completed.

Simple CASE Statement

The simple CASE statement runs the first statements for which selector_value equals selector. Remaining conditions are not evaluated. If no selector_value equals selector, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Syntax :

CASE selector
 WHEN selector_value_1 THEN statements_1
 WHEN selector_value_2 THEN statements_2
 ...
 WHEN selector_value_n THEN statements_n
 [ ELSE
  else_statements ]
 END CASE;]
DECLARE
 grade CHAR(1);
BEGIN
 grade := 'B';

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

Very Good

PL/SQL procedure successfully completed.

Searched CASE Statement

The searched CASE statement runs the first statements for which condition is true. Remaining conditions are not evaluated. If no condition is true, the CASE statement runs else_statements if they exist and raises the predefined exception CASE_NOT_FOUND otherwise.

Syntax :

CASE
 WHEN condition_1 THEN statements_1
 WHEN condition_2 THEN statements_2
 ...
 WHEN condition_n THEN statements_n
 [ ELSE
  else_statements ]
 END CASE;]
DECLARE
 grade CHAR(1);
BEGIN
 grade := 'C';
 
 CASE
 WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
 WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
 WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
 END CASE;
END;
/

Good

PL/SQL procedure successfully completed.
Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s