PL/SQL CASE Statement

Summary: in this tutorial, you will learn how to use PL/SQL CASE statement to execute a sequence of statements based on a selector.

Introduction to PL/SQL CASE Statement

The PL/SQL CASE statement allows you to execute a sequence of statements based on a selector. A selector can be anything such as variable, function, or expression that the CASE statement evaluates to a Boolean value.

You can use almost any PL/SQL data types as a selector except BLOB, BFILE and composite types.

Unlike the PL/SQL IF statement, PL/SQL CASE statement uses a selector instead of using a combination of multiple Boolean expressions.

The following illustrates the PL/SQL CASE statement syntax:

[<<label_name>>]
CASE [TRUE | selector]
   WHEN expression1 THEN
	sequence_of_statements1;
   WHEN expression2 THEN
	sequence_of_statements2;
   ...
   WHEN expressionN THEN
	sequence_of_statementsN;
  [ELSE sequence_of_statementsN+1;]
END CASE [label_name];

Followed by the keyword CASE is a selector. The PL/SQL CASE statement evaluates the selector only once to decide which sequence of statements to execute.

Followed by the selector is any number of the WHEN clauses. If the selector value is equal to expression in the WHEN clause, the corresponding sequence of statement after the THEN keyword is executed.

If the selector’s value is not one of the choices covered by WHEN clause, the sequence of statements in the ELSE clause will be executed. The ELSE clause is optional so if you omit it. PL/SQL will add the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;

If you use implicit ELSE clause in the PL/SQL CASE statement, an CASE_NOT_FOUND exception is raised and can be handled in the exception handling section of the PL/SQL block as usual.

The END CASE clause are used to terminate the CASE statement.

Example of Using PL/SQL CASE Statement

The following example demonstrates the PL/SQL CASE statement. We’ll use employees table in HR sample data provided by Oracle for the demonstration.

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_pct    employees.commission_pct%TYPE;
  v_eval   varchar2(10);
  n_emp_id employees.employee_id%TYPE := 145;
BEGIN
  -- get commission percentage
  SELECT commission_pct
  INTO n_pct
  FROM employees
  WHERE employee_id = n_emp_id;

  -- evalutate commission percentage
  CASE n_pct
    WHEN 0 THEN
      v_eval := 'N/A';
    WHEN 0.1 THEN
      v_eval := 'Low';
    WHEN 0.4 THEN
      v_eval := 'High';
    ELSE
      v_eval := 'Fair';
  END CASE;
  -- print commission evaluation
  DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || 
                       ' commission ' || TO_CHAR(n_pct) ||
                       ' which is '   || v_eval);
END;
/

PL/SQL searched CASE statement

PL/SQL provides a special CASE statement called searched CASE statement. The syntax of the PL/SQL searched CASE statement is as follows:

[<<label_name>>]
CASE
   WHEN search_condition_1 THEN sequence_of_statements_1;
   WHEN search_condition_2 THEN sequence_of_statements_2;
   ...
   WHEN search_condition_N THEN sequence_of_statements_N;
  [ELSE sequence_of_statements_N+1;]
END CASE [label_name];

The searched CASE statement has no selector. Each WHEN clause in the searched CASE statement contains a search condition that returns a Boolean value.

The search condition is evaluated sequentially from top to bottom. If a search condition evaluates to TRUE, the sequence of statements in the corresponding WHEN clause is executed and  the control is passed to the next statement therefore the subsequent search conditions are ignored.

If no search condition evaluates to TRUE, the sequence of statements in the ELSE clause will be executed.

The following is an example of using PL/SQL searched CASE statement:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_salary EMPLOYEES.SALARY%TYPE;
  n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
  v_msg    VARCHAR(20);
BEGIN
  SELECT salary
  INTO n_salary
  FROM employees
  WHERE employee_id = n_emp_id;

  CASE
    WHEN n_salary < 2000 THEN
      v_msg := 'Low';
    WHEN n_salary >= 2000 and n_salary <=3000 THEN
      v_msg := 'Fair';
    WHEN n_salary >= 3000 THEN
      v_msg := 'High';
  END CASE;
  DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

In this tutorial, you have learned how to use PL/SQL CASE statement and searched CASE to execute a sequence of statements based on conditions.