PL SQL Case Statement

PL/SQL Case Statement -Learn use of case statements in pl-sql programming with simple explanation and example.

PL/SQL Case Statement

• CASE Statement similar to SWITCH statement in C and C#.
• When a particular search condition evaluates to TRUE, the group of statements associated with this condition are executed.

Syntax

CASE test_var
WHEN value1 THEN
     Sequence_of_statements1;
WHEN value2 THEN
    sequence_of_statements2;

........
WHEN valueN THEN
--sequence of statements N

ELSE
   --else_Sequence statements

END CASE;

NOTES

• CASE marks the Beginning of the CASE statement. It is a reserved keyword
• The selected value decide which WHEN case should be executed.
• Each WHEN clause contains a expression and one or more executable statements.
• The ELSE case is optional
• each CASE statement is marked with “END CASE”.
• test_var is the variable or expression to be tested, value1 to valuen are the comparison values
• if none of the values are equal, then else_sequence will be executed.

Example program to print the DAYS NAMES in the week.

DECLARE 
D NUMBER:=&NUM1;
BEGIN
CASE D
  WHEN 1 THEN
     DBMS_OUTPUT.PUT_LINE('SUNDAY');
  WHEN 2 THEN
     DBMS_OUTPUT.PUT_LINE('MONDAY');
  WHEN 3 THEN
     DBMS_OUTPUT.PUT_LINE('TUESDAY');
  WHEN 4 THEN
     DBMS_OUTPUT.PUT_LINE('WEDNESDAY');
  WHEN 5 THEN
     DBMS_OUTPUT.PUT_LINE('THURSDAY');
  WHEN 6 THEN
     DBMS_OUTPUT.PUT_LINE('FRIDAY');
  WHEN 7 THEN
     DBMS_OUTPUT.PUT_LINE('SATURDAY');
  ELSE
     DBMS_OUTPUT.PUT_LINE('INVALID DAY');
END CASE;

END;

INPUT

NUM1=4

OUTPUT

WEDNESDAY

LABELED CASE STATEMENTS

• A CASE statement can be optionally labeled like a PL/SQL block
• If a CASE statement is Labelled, then the label also can appear after the END CASE clause.

Syntax

<<MYCASE>>
CASE test_var
WHEN value1 THEN
     Sequence_of_statements1;
WHEN value2 THEN
    sequence_of_statements2;

........
WHEN valueN THEN
--sequence of statements N

ELSE
   --else_Sequence statements

END CASE MYCASE;

Example program to print the DAYS NAMES in the week.

DECLARE
D NUMBER:=&NUM1;
BEGIN
<<WEEKDAYS>>
CASE D
  WHEN 1 THEN
     DBMS_OUTPUT.PUT_LINE('SUNDAY');
  WHEN 2 THEN
     DBMS_OUTPUT.PUT_LINE('MONDAY');
  WHEN 3 THEN
     DBMS_OUTPUT.PUT_LINE('TUESDAY');
  WHEN 4 THEN
     DBMS_OUTPUT.PUT_LINE('WEDNESDAY');
  WHEN 5 THEN
     DBMS_OUTPUT.PUT_LINE('THURSDAY');
  WHEN 6 THEN
     DBMS_OUTPUT.PUT_LINE('FRIDAY');
  WHEN 7 THEN
     DBMS_OUTPUT.PUT_LINE('SATURDAY');
  ELSE
     DBMS_OUTPUT.PUT_LINE('INVALID DAY');
END CASE WEEKDAYS;
END;

INPUT

NUM1=4

OUTPUT
WEDNESDAY