Simple CASE vs. Searched CASE

转载自:http://www.oratable.com/simple-case-searched-case/

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE.

Let’s have a closer look to compare them in structure and functionality.

Structural Differences

The simple CASE has the following structure:

1

2

3

4

5

casen

          when1 thenAction1

          when2 thenAction2

          else       ActionOther

        endcase;

The searched CASE has the following structure:

1

2

3

4

5

6

case

  whenn = 1               thenAction1;

  whenn = 2               thenAction2;

   when( n > 2 andn < 6) thenAction3through5;

  else                         ActionOther;

endcase;

Functional Differences

The simple CASE performs a simple equality check of "n" against each of the "when" options.

The searched CASE evaluates the conditions independently under each of the "when" options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.

A searched CASE can combine multiple tests using several columns, comparisons and AND/OR operators.

Note that in both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match is found. So, suppose more than one condition is true, only the first action is considered.

Example using the searched case:

1

2

3

4

5

6

7

case

  whenempno in('KING','CLARK')andsal > 5000

              thenprocess_highpaid(empno);

  whenempno in('CLARK','SMITH')

              thenprocess_manager(empno);

  else   process_general(empno);

endcase;

In the above, though employee CLARK may match two conditions, only process_highpaid(empno) will be executed.

Historical Difference

Not that it matters but for the trivia buffs (and also if you’re working on an old version of Oracle) – the simple CASE expression was introduced in Oracle 9i. The searched CASE expression is the Oracle 8i variant.

Exercise for you:

In the examples under the topic The Difference Between DECODE and CASE, identify which use simple CASE and which use searched CASE.

For further reading:

Oracle documentation on CASE Expressions

Related Posts:

1. The Difference Between DECODE and CASE
2. CASE_NOT_FOUND Exception
3. IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!
4. Hierarchical Queries: A QuickStart Guide

Spon