Oracle 中条件分歧总结
Oracle 中条件分歧总结：
* Decode
* IF--THEN--ELSIF---END IF
* CASE WHEN---END CASE

1.DECODE介绍
   In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE      statement.

   The syntax for the decode function is:

   decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.result is the value returned, if expression is equal to search.default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

For 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;

Refer to:http://www.techonthenet.com/oracle/functions/index.php

2. IF-THEN-ELSE Statement
There are three different syntaxes for these types of statements.

Syntax #1: IF-THEN

IF condition THEN
         {...statements...}
END IF;


Syntax #2: IF-THEN-ELSE

IF condition THEN
         {...statements...}
ELSE
         {...statements...}
END IF;


Syntax #3: IF-THEN-ELSIF

IF condition THEN
         {...statements...}
ELSIF condition THEN
         {...statements...}
ELSE
         {...statements...}
END IF;


Here is an example of a function that uses the IF-THEN-ELSE statement:

CREATE OR REPLACE Function IncomeLevel
     ( name_in IN varchar2 )
     RETURN varchar2
IS
     monthly_value number(6);
     ILevel varchar2(20);

     cursor c1 is
        select monthly_income
        from employees
        where name = name_in;

  BEGIN

open c1;
fetch c1 into monthly_value;
close c1;

IF monthly_value <= 4000 THEN
     ILevel := 'Low Income';

ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
     ILevel := 'Avg Income';

ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
     ILevel := 'Moderate Income';

ELSE
     ILevel := 'High Income';

END IF;

  RETURN ILevel;

END;

3.Case Statement
Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

The syntax for the case statement is:

CASE  [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN co