Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

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;

Related

Категории