11.11. CASE Statement

Case statements implement the same construct model as IF-THEN-ELSIF with ELSE constructs. The CASE statement is easier to read and has improved performance over complex IF statements. There are two forms to the CASE statement:

• Searched Case Statement

• Case with Selector

11.11.1. Searched CASE Statement

The searched CASE statement evaluates a sequence of test conditions. When a test evaluates to TRUE, code is executed and the construct is complete—no further conditions are examined.

Execution falls to the ELSE clause if no prior conditions are met. The ELSE clause can be any group of statements or the NULL statement. NULL statements refer to the PL/SQL statement, NULL.

If no condition is met and there is no ELSE clause, an exception is raised. The error is:

ORA-06592: CASE not found while executing CASE statement.

This error can be captured with the exception:

CASE_NOT_FOUND

This is illustrated later. The Searched Case Statement evaluates an expression with each WHEN clause. The syntax is the following:

CASE
    WHEN expression THEN action;
    WHEN expression THEN action;
    WHEN expression THEN action;
    [ELSE action;]
END CASE;

The following example defines a rule for setting a value to B based on the value of A. We implement this rule with a CASE statement.

The CASE statement for this is:

CASE
    WHEN (A < 10)              THEN B := 1;
    WHEN (A >= 10 AND A < 20)  THEN B := 2;
    WHEN (A = 225)             THEN B := 3;
    ELSE                            B := 4;
END CASE;

The ELSE clause is optional. The following CASE statement is valid; however, this code raises an exception because no condition is TRUE. The exception handler catches the error and prints the value A.

DECLARE
    A INTEGER := 300;
BEGIN
    CASE
        WHEN (A < 10)              THEN B := 1;
        WHEN (A >= 10 AND A < 20)  THEN B := 2;
        WHEN (A = 225)             THEN B := 3;
    END CASE;
EXCEPTION
    WHEN CASE_NOT_FOUND THEN
        dbms_output.put_line('A = '||a);
END;

The CASE statement is easier to read in the code. Compare the following CASE logic with the same IF logic.

CASE
WHEN (A<=100)  THEN B := 1;
WHEN (A<=200)  THEN B := 2;
WHEN (A<=300)  THEN B := 3;
ELSE                B := 4;
END CASE;

IF (A <= 100) THEN
B := 1;
ELSIF (A <= 200) THEN
B := 2;
ELSIF (A <= 300) THEN
B := 3;
ELSE
B := 4;
END IF;

11.11.2. CASE with Selector

The CASE with Selector also raises an exception if no conditions are true and there is no ELSE clause. The syntax for this CASE statement is:

CASE selector
    WHEN value THEN action;
    WHEN value THEN action;
    WHEN value THEN action;
    [ELSE action;]
END CASE;

The following example prints a two-character string for each college major. An exception handler is not necessary because there is an ELSE clause.

DECLARE
    college_major major_lookup.major_desc%TYPE;

    PROCEDURE p(s VARCHAR2) IS
    BEGIN
        dbms_output.put_line(s);
    END;

BEGIN
    college_major := 'Biology';

    CASE college_major
        WHEN 'Undeclared'   THEN p('A1'),
        WHEN 'Biology'      THEN p('A2'),
        WHEN 'Math/Science' THEN p('A3'),
        WHEN 'History'      THEN p('A4'),
        WHEN 'English'      THEN p('A5'),
        ELSE                     p('none'),
    END CASE;
END;

11.11.3. Using CASE within the SELECT

Use SQL CASE expressions in SQL query statements first. A more lengthy procedure will use a basic SQL SELECT statement that immediately transforms the data using a Searched Case Statement. The following illustrates the difference.

We create a TEMP table. The code under Version 1 executes a SELECT statement and then transforms the data. Version 2 uses CASE within the SELECT statement. This data transform occurs in the SQL engine. The logic of a procedure following Version 2 will be simpler.

CREATE TABLE TEMP (A NUMBER(2));
INSERT INTO TEMP VALUES (11);

VERSION 1

DECLARE
    A INTEGER;
    B INTEGER;
BEGIN
    SELECT A INTO A FROM TEMP;
    CASE
        WHEN (A<=100)  THEN B := 1;
        WHEN (A<=200)  THEN B := 2;
        WHEN (A<=300)  THEN B := 3;
        ELSE                B := 4;
    END CASE;
    dbms_output.put_line(B);
END;

VERSION 2

DECLARE
    B INTEGER;
BEGIN
    SELECT CASE
            WHEN A < 10             THEN 1
            WHEN A >= 10 AND A < 20 THEN 2
            WHEN A = 225            THEN 3 END
    INTO B FROM TEMP;
END;

For the Version 1 and Version 2 examples, the total lines of code are not that different; however, for larger applications, procedures following the style in Version 2 will be easier to read and maintain.

11.11.4. Using DECODE within the SELECT

Use SQL DECODE expressions in the SELECT, rather than a SQL SELECT statement, that immediately transforms the data using a Searched With Selector.

The following illustrates the difference. The code in Version 1 selects a college major and then uses CASE to transform the major into a two-character string. This transformation occurs in the PL/SQL. The Version 2 code performs the transformation in the query using DECODE.

						VERSION 1

DECLARE
    college_major major_lookup.major_desc%TYPE;
    code VARCHAr2(4);
BEGIN
    SELECT major_desc INTO college_major
    FROM major_lookup WHERE ROWNUM = 1;

    -- for example, college_major := 'Biology';

    CASE college_major
        WHEN 'Undeclared'   THEN code := 'A1'
        WHEN 'Biology'      THEN code := 'A2'),
        WHEN 'Math/Science' THEN code := 'A3'),
        WHEN 'History'      THEN code := 'A4'),
        WHEN 'English'      THEN code := 'A5'),
        ELSE                     code := 'none';
    END CASE;
END;

VERSION 2

DECLARE
    code VARCHAR2(4);
BEGIN
    SELECT DECODE
       ( major_desc,
        'Undeclared'   , 'A1',
        'Biology'      , 'A2',
        'Math/Science' , 'A3',
        'History'      , 'A4',
        'English'      , 'A5') major
    FROM major_lookup WHERE ROWNUM = 1;
END;