It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Simple CASE vs. 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:

case n
          when 1 then Action1
          when 2 then Action2
          else        ActionOther
        end case; 

The searched CASE has the following structure:

  when n = 1               then Action1;
  when n = 2               then Action2;
   when ( n > 2 and n < 6) then Action3through5;
  else                          ActionOther;
end case;

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:

  when empno in ('KING', 'CLARK') and sal > 5000
              then process_highpaid(empno);
  when empno in ('CLARK', 'SMITH') 
              then process_manager(empno);
  else    process_general(empno);
end case;

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


You Might Also Like

Related Posts with Thumbnails