The differences between case and decode statements are listed below:
- DECODE can work with only scalar values but CASE can work with logical operators, predicates and search-able sub queries.
- CASE can work as a PL/SQL construct but DECODE is used only in SQL statements.CASE can be used as parameter of a function/procedure.
- CASE expects data type consistency, DECODE does not.
- CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
- CASE executes faster in the optimizer than does DECODE.
- CASE is a statement while DECODE is a function.
The search-able case statement is similar to the decode function. This is shown below:
Case dept_id when 10