Using CASE statements and CASE expressions in PL/SQL

You have learned to write PL/SQL blocks containing declarative and executable sections. You have also learned to include expressions and SQL statements in the executable block. In this post, you will learn how to use control structures such as CASE statements in a PL/SQL block.

CASE Expressions

A CASE expression returns a result based on one or more alternatives. To return the result, the CASE expression uses a selector, which is an expression whose value is used to return one of several alternatives. The selector is followed by one or more WHEN clauses that are checked sequentially. The value of the selector determines which result is returned. If the value of the selector equals the value of a WHEN clause expression, that WHEN clause is executed and that result is returned.

CASE selector
   WHEN expression1 THEN result1
   [WHEN expression2 THEN result2
   ...
   WHEN expressionN THEN resultN]
   [ELSE resultN+1]
END;

PL/SQL also provides a searched CASE expression, which has the form:

CASE
  WHEN search_condition1 THEN result1
  [WHEN search_condition2 THEN result2
  ...
  WHEN search_conditionN THEN resultN]
  [ELSE resultN+1]
END;

A searched CASE expression has no selector. Furthermore, the WHEN clauses in CASE expressions contain search conditions that yield a Boolean value rather than expressions that can yield a value of any type.

CASE Expressions: Example

Consider the example shown below:

SET VERIFY OFF
DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  v_appraisal VARCHAR2(20);
BEGIN
  v_appraisal := CASE v_grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      ELSE 'No such grade'
    END;
DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade ||
                  'Appraisal' || v_appraisal);
END;
/

In the example above, the CASE expression uses the value in the v_grade variable as the expression. This value is accepted from the user by using a substitution variable. Based on the value entered by the user, the CASE expression returns the value of the v_appraisal variable based on the value of the v_grade value.

Result
When you enter a or A for v_grade, as shown in the Substitution Variable window, the output of the example is as follows:

anonymous block completed
Grade: A   Appraisal Excellent

Searched CASE Expressions

Consider the example shown below:

DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  v_appraisal VARCHAR2(20);
BEGIN
  v_appraisal := CASE
      WHEN v_grade = 'A' THEN 'Excellent'
      WHEN v_grade IN ('B','C') THEN 'Good'
      ELSE 'No such grade'
    END;
  DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade ||
                     ‘ Appraisal ' || v_appraisal);
END;
/

In the previous example, you saw a single test expression, the v_grade variable. The WHEN clause compares a value against this test expression. In searched CASE statements, you do not have a test expression. Instead, the WHEN clause contains an expression that results in a Boolean value. The same example is rewritten above to show searched CASE statements.

Output
The output of the example is as follows when you enter b or B for v_grade:

anonymous block completed
Grade: B   Appraisal Good

CASE Statement

Conside the example shown below:

DECLARE
  v_deptid NUMBER;
  v_deptname VARCHAR2(20);
  v_emps NUMBER;
  v_mngid NUMBER:= 108;
BEGIN
  CASE v_mngid
    WHEN 108 THEN
      SELECT department_id, department_name
      INTO v_deptid, v_deptname FROM departments
      WHERE manager_id=108;
      SELECT count(*) INTO v_emps FROM employees
      WHERE department_id=v_deptid;
      WHEN 200 THEN
      ...
  END CASE;
DBMS_OUTPUT.PUT_LINE ('You are working in the '|| v_deptname||
' department. There are '||v_emps ||' employees in this department');
END;
/

Recall the use of the IF statement. You may include n number of PL/SQL statements in the THEN clause and also in the ELSE clause. Similarly, you can include statements in the CASE statement, which is more readable compared to multiple IF and ELSIF statements.

How a CASE Expression Differs from a CASE Statement

A CASE expression evaluates the condition and returns a value, whereas a CASE statement evaluates the condition and performs an action. A CASE statement can be a complete PL/SQL block.

– CASE statements end with END CASE;
– CASE expressions end with END;

The output of the example code above is as follows:

anonymous block completed
You are working in the Finance department. There are 6 employees in this department.