CASE statements in Oracle PL/SQL

CASE Statement

The CASE statement is similar to an IF-THEN-ELSE statement, but helps in better readability than the later. The CASE statement is also a block with a header and a footer. The header section starts the block with the CASE keyword followed by a series of conditional checks and the footer section ends with an END CASE keyword. There is a mandatory executable statement needed in each of the conditional check section to make the unit free from compilation errors. The CASE statement has optional n number of WHEN-THEN with one or more conditional checks separating each other by conjoining operators (AND) or by include operators (OR), and an ELSE section for the default result output if none of the conditions are satisfied.

The conditional checks in the WHEN-THEN phrases can be of Relational (<, >, <=, >=, <> or !=), Boolean (TRUE, FALSE, NULL)or NULL(IS NULL, IS NOT NULL) comparisons.

% Note: Static variables or values cannot be compared to a column value from a select statement directly in the WHEN-THEN conditional check, instead the column value has to be assigned to a variable prior to the CASE statement and the two variables can be compared.

There are two types of CASE statements available in PL/SQL and they are,

1.       Simple CASE: A simple case takes a scalar variable as an expression and compares it with a list of scalar values.

2.       Searched CASE: A searched case takes a Boolean variable as an expression and compares the Boolean state of the comparison made in the when clause. The default Boolean value is true if not mentioned.

The below prototype defines the basic structure of the simple CASE statement.

CASE <Scalar Variable>

WHEN <Scalar value1> then

<Executable statement>;

WHEN <Scalar value2> then

<Executable statement>;

…

ELSE

<Executable statement>;

END CASE;

Simple CASE Example

1.  SET SERVEROUTPUT ON SIZE 200000

2.  DECLARE

3.  l_n_var1 NUMBER:=:value;

4.  BEGIN

5.  CASE l_n_var1

6.  WHEN 1 THEN

7.  dbms_output.put_line('The value is 1');

8.  WHEN 2 THEN

9.  dbms_output.put_line('The value is 2');

10. WHEN 3 THEN

11. dbms_output.put_line('The value is 3');

12. ELSE

13. dbms_output.put_line('The value is not 1 or 2 or 3');

14. END CASE;

15. END;

16. /

Script Explanation

Line no.	Description
1	This environment variable opens up an output buffer of size limit of 200000
2	Start of the DECLARE section of the block
3	A local variable of the number data type is declared and assigned to a bind variable
4	Start of the Execution section
5	The CASE statement begins with the scalar variable l_n_var1 as its expression
6	The scalar variable's value is checked with the value 1 in the WHEN clause
7	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
8	If the above conditional check at line 6 does not match, the control shifts to this WHEN section checking the variable with the value 2
9	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
10	If the above conditional check at line 8 does not match, the control shifts to this WHEN section checking the variable with the value 3
11	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
12,13	If none of the above conditions are matched, the program control is passed on to the ELSE section and prints the default result in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
14	End of the CASE statement
15,16	End of the execution section

The below prototype defines the basic structure of the simple CASE statement.

CASE <Boolean Variable>

WHEN <variable1> = <variable2> then

<Executable statement>;

WHEN <variable1> > <variable2> then

<Executable statement>;

ELSE

<Executable statement>;

END CASE;

Searched CASE Example

1.  SET SERVEROUTPUT ON SIZE 200000

2.  DECLARE

3.  l_n_var1 NUMBER:=:value;

4.  BEGIN

5.  CASE true

6.  WHEN l_n_var1=1 THEN

7.  dbms_output.put_line('The value is 1');

8.  WHEN l_n_var1=2 THEN

9.  dbms_output.put_line('The value is 2');

10. WHEN l_n_var1=3 THEN

11. dbms_output.put_line('The value is 3');

12. ELSE

13. dbms_output.put_line('The value is not 1 or 2 or 3');

14. END CASE;

15. END;

16. /

Script Explanation

Line no.	Description
1	This environment variable opens up an output buffer of size limit of 200000
2	Start of the DECLARE section of the block
3	A local variable of the number data type is declared and assigned to a bind variable
4	Start of the Execution section
5	The CASE statement begins with the Boolean value True as its expression
6	The scalar variable's value is equated with the value 1 in the WHEN clause
7	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
8	If the above conditional check at line 6 does not match, the control shifts to this WHEN section equating the variable with the value 2
9	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
10	If the above conditional check at line 8 does not match, the control shifts to this WHEN section checking the variable with the value 3
11	If this conditional check is a match, then the result is printed in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
12,13	If none of the above conditions are matched, the program control is passed on to the ELSE section and prints the default result in the form of a printable text using the DBMS_OUTPUT.PUT_LINE procedure
14	End of the CASE statement
15,16	End of the execution section

Thank you,

Boobal Ganesan