Oracle PL/SQL: IF-THEN-ELSIF Statement vs. Case statement , CASE Expression vs. Decode Function

Decode Function

Decode is a very useful and handy function for Oracle queries. It replaces the complex If-Then-Else logic, which is used to display different things based on different values in a column. This is usually used for pivoting purposes.

Syntax is like:

DECODE(expr, search, result
             [, search, result ]...
       [, default ]
      )

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.

The search, result, and default values can be derived from expressions. Oracle Database uses short-circuit evaluation. That is, the database evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

An Example:

An Example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
       decode(supplier_id, 10000, 'IBM',
                           10001, 'Microsoft',
                           10002, 'Hewlett Packard',
              'Gateway') result
FROM suppliers; 

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
     result := 'IBM';

ELSIF supplier_id = 10001 THEN
    result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
    result := 'Hewlett Packard';

ELSE
    result := 'Gateway';

END IF;

The decode function will compare each supplier_id value, one by one.

Searched CASE Statement

PL/SQL also provides a searched CASE statement, which has the form:

[<<label_name>>]
CASE
   WHEN search_condition1 THEN sequence_of_statements1;
   WHEN search_condition2 THEN sequence_of_statements2;
   ...
   WHEN search_conditionN THEN sequence_of_statementsN;
  [ELSE sequence_of_statementsN+1;]
END CASE [label_name];

The searched CASE statement has no selector. Also, its WHEN clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:

CASE
   WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
   WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
   WHEN grade = 'C' THEN dbms_output.put_line('Good');
   WHEN grade = 'D' THEN dbms_output.put_line('Fair');
   WHEN grade = 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;

The search conditions are evaluated sequentially. The Boolean value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. If any WHEN clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.

If none of the search conditions yields TRUE, the ELSE clause is executed. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;

Exceptions raised during the execution of a searched CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

DECODE Function vs. CASE Expression

The DECODE fucntion can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN.

The traditional CASE expression can compare discrete values, the searched CASE expression can evaluate any expression and allowes the use of operators like > and BETWEEN (eliminating most of the contortions) and different values to be compared in different branches of the statement (eliminating most nesting).

Example:

select                         
   decode (      
      status,   
     'A','Active',
     'T','Terminated',
     'Unknow'  
   ) status_desc       
from            
   customer;    

The equivelance case statement is:

select
 (case status
 when 'A' then 'Active'
 when 'T' then 'Terminated'
 else 'Unknow'
 end) status_desc                             
from            
   customer;

or

select
 (case
 when status = 'A' then 'Active'
 when status = 'T' then 'Terminated'
 else 'Unknow'
 end) status_desc                                 
from            
   customer;