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.

A	B
A < 10	1
10 >= A < 20	2
225	3
All other values	4

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.

Search Case Statement	IF Statement
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;