[轉貼]ORACLE SQL IF ELSE

http://www.orafaq.com/faq/how_does_one_implement_if_then_else_logic_in_a_select_statement

How does one implement IF-THEN-ELSE logic in a SELECT statement?

Submitted by admin on Sat, 2005-11-12 06:38 

Oracle SQL supports several methods of coding conditional IF-THEN-ELSE logic in SQL statements. Here are some:

CASE Expressions

From Oracle 8i one can use CASE statements in SQL. Look at this example:

SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
                   WHEN sal > 1000 THEN 'Over paid'
                   ELSE 'Under paid'
              END AS "Salary Status"
FROM   emp;

DECODE() Function

The Oracle decode function acts like a procedural statement inside an
SQL statement to return different values or columns based on the values of
other columns in the select statement. Examples:

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from   employees;

select a, b, decode( abs(a-b), a-b, 'a > b',
                               0,   'a = b',
                                    'a < b') from  tableX;

Note: The decode function is not ANSI SQL and is rarely implemented
in other RDBMS offerings. It is one of the good things about Oracle,
but use it sparingly if portability is required.

GREATEST() and LEAST() Functions

select decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B', 
                                 'B is greater than A')...

select decode( GREATEST(A,B), 
               A, decode(A, B, 'A NOT GREATER THAN B', 'A GREATER THAN B'), 
               'A NOT GREATER THAN B')...

NVL() and NVL2() Functions

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.

SELECT nvl(ename, 'No Name') 
  FROM emp;

NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.

SELECT nvl2(ename, 'Do have a name', 'No Name') 
  FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:

SELECT 'Dear '||COALESCE(preferred_name, first_name, 'Sir or Madam')
  FROM emp2;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:

SELECT NULLIF(ename, ename)
  FROM emp;