Using the CASE Statement

The CASE statement chooses from a sequence of conditions, and executes the corresponding statement.

The simple CASE statement evaluates a single expression and compares it to several potential values. It has this syntax:

CASE expression
WHEN value THEN statement
[ WHEN value THEN statement ]...
[ ELSE statement [, statement ]... ]
END CASE;

The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE. For information about the searched CASE statement, see Oracle Database PL/SQL Language Reference.

Tip:

When you can use either a CASE statement or nested IF statements, use a CASE statement—it is both more readable and more efficient.

Suppose that, if an employee is evaluated only once a year, you want the eval_frequency function to suggest a salary increase, which depends on the JOB_ID.

Add the CASE statement shown in the following example to the eval_frequency function. (For information about the procedure that prints the strings, DBMS_OUTPUT.PUT_LINE, see Oracle Database PL/SQL Packages and Types Reference.)

CASE Statement that Determines Which String to Print

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
  j_id       EMPLOYEES.JOB_ID%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    CASE j_id
       WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 8% salary increase for employee # ' || emp_id);
       WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 7% salary increase for employee # ' || emp_id);
       WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 6% salary increase for employee # ' || emp_id);
       WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 4% salary increase for employee # ' || emp_id);
       ELSE DBMS_OUTPUT.PUT_LINE(
         'Nothing to do for employee #' || emp_id);
    END CASE;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

Using the CASE Statement

Related Topics