Oracle PL/SQL - Searched CASE Expression

Introduction

A searched CASE expression has this syntax:

CASE 
WHEN boolean_expression_1 THEN result_1 
WHEN boolean_expression_2 THEN result_2 
 ... 
WHEN boolean_expression_n THEN result_n 
 [ ELSE 
  else_result ] 
END]

The searched CASE expression returns the first result for which boolean_expression is TRUE.

Remaining expressions are not evaluated in the other branches.

If no boolean_expression is TRUE, the CASE expression returns else_result if it exists and NULL otherwise.

Demo

SQL>
SQL> DECLARE-- from  w  ww .  jav  a 2  s .  c o m
  2    final_mark CHAR(1);
  3  BEGIN
  4    final_mark := 'B';
  5
  6    CASE
  7      WHEN final_mark = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  8      WHEN final_mark = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  9      WHEN final_mark = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
 10      WHEN final_mark = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
 11      WHEN final_mark = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
 12      ELSE DBMS_OUTPUT.PUT_LINE('No such final_mark');
 13    END CASE;
 14  END;
 15  /
Very Good

PL/SQL procedure successfully completed.

SQL>

The following code assigns the value of a searched CASE expression to the variable mark_description.

Demo

SQL>
SQL> Create or replace FUNCTION always_false (id NUMBER)
SQL>     RETURN BOOLEAN IS-- from  ww w  . j  av  a 2s. c o  m
SQL> BEGIN
  2      RETURN TRUE;
  3  END;
  4  /

SQL>
SQL> DECLARE
  2    final_mark      CHAR(1) := 'B';
  3    mark_description  VARCHAR2(120);
  4    id         NUMBER  := 1001;
  5    attendance NUMBER := 150;
  6    min_days   CONSTANT NUMBER := 200;
  7
  8
  9  BEGIN
 10    mark_description :=
 11    CASE
 12      WHEN always_false(id) = FALSE
 13        THEN 'Student not enrolled'
 14      WHEN final_mark = 'F' OR attendance < min_days
 15        THEN 'Poor final_mark and missing classes'
 16      WHEN final_mark = 'A' THEN 'Excellent'
 17      WHEN final_mark = 'B' THEN 'Very Good'
 18      WHEN final_mark = 'C' THEN 'Good'
 19      WHEN final_mark = 'D' THEN 'Fair'
 20      ELSE 'No such final_mark'
 21    END;
 22    DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || mark_description);
 23  END;
 24  /

Oracle PL/SQL - Searched CASE Expression

Introduction

Demo

Demo

Related Topics