Oracle中的Case與Sql的Case對比

文章目錄

• Basic syntax
• Examples
• COALESCE and NULLIF functions
• Conclusion

區別在與Case後的expression和End後面的as運算式最明顯

--oracle的

   select t_CX_Orders.*,
   case when FIsSendMail=1 then FFILEPATH
                    else '.'
                    end  DownLoadPath
    from t_CX_Orders 
    where FUserID='AAA613' order by FInnerCode desc

--sql的

SELECT au_fname, au_lname, 
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname

DECODE is considered the most powerful function in Oracle. Oracle 8i release introduced the CASE expression. The CASE expression can do all that DECODE does plus lot of other things including IF-THEN analysis, use of any comparison operator and checking multiple conditions, all in a SQL query itself. Moreover, using the CASE function, multiple conditions provided in separate SQL queries can be combined into one, thus avoiding multiple statements on the same table (example given below). The function is available from Oracle 8i onwards.

Basic syntax

CASE expression syntax is similar to an IF-THEN-ELSE statement. Oracle checks each condition starting from the first condition (left to right). When a particular condition is satisfied (WHEN part) the expression returns the tagged value (THEN part). If none of the conditions are matched, the value mentioned in the ELSE part is returned. The ELSE part of the expression is not mandatory-- CASE expression will return null if nothing is satisfied.

case when <condition> then <value>when <condition> then <value>...else <value>end 

Examples

The following examples will make the use of CASE expression more clear.

E.g.: Returning categories based on the salary of the employee.

select sal, case when sal < 2000 then 'category 1'                  when sal < 3000 then 'category 2'                  when sal < 4000 then 'category 3'                  else 'category 4'             end from emp; 

E.g.: The requirement is to find out the count of employees for various conditions as given below. There are multiple ways of getting this output. Five different statements can be written to find the count of employees based on salary and commission conditions, or a single select having column-level selects could be written.

select count(1) from   emp where  sal < 2000 and    comm is not null;  select count(1) from   emp where  sal < 2000 and    comm is null; select count(1) from   emp where  sal < 5000 and    comm is not null; select count(1) from   emp where  sal < 5000 and    comm is null; select count(1) from   emp where  sal > 5000; 

(or)

select (select count(1)        from   emp        where  sal < 2000        and    comm is not null) a,       (select count(1)        from   emp        where  sal < 2000        and    comm is null) b,       (select count(1)        from   emp        where  sal < 5000        and    comm is not null) c,       (select count(1)        from   emp        where  sal < 5000        and    comm is null) d,(select count(1)from   empwhere  sal > 5000) efrom dual

With CASE expression, the above multiple statements on the same table can be avoided.

select count(case when sal < 2000 and comm is not null then 1                   else null              end),        count(case when sal < 2000 and comm is null then 1                   else null              end),        count(case when sal < 5000 and comm is not null then 1                   else null              end),        count(case when sal < 5000 and comm is null then 1                   else null              end),        count(case when sal > 5000 then 1                   else null              end) from emp; 

(or)

select count(case when sal < 2000 and comm is not null then 1              end) cnt1,        count(case when sal < 2000 and comm is null then 1              end) cnt2,        count(case when sal < 5000 and comm is not null then 1              end) cnt3,        count(case when sal < 5000 and comm is null then 1              end) cnt4,        count(case when sal > 5000 then 1              end) cnt5 from emp;

E.g.: CASE expression can also be nested.

select (case when qty_less6months < 0 and qty_6to12months < 0 then                            (case when season_code in ('0', '1', '2', '3', '4') then 'value is negative'                                  else 'No stock'                             end)             when qty_1to2years < 0 and qty_2to3years < 0 then                            (case when season_code in ('A', 'B', 'C', 'D', 'E') then 'value is negative'                                  else 'No stock'                             end)             else 'Stock Available'        end) stock_checkfrom   jnc_lots_ageing_mexx_asofwhere  rownum < 20and    qty_less6months < 0 and qty_6to12months < 0

E.g.: The data types of the returned values should be the same. In the example below, one argument is assigned a numeric value resulting in an error.

SQL> select sal, case when sal < 2000 then 'category 1'  2                   when sal < 3000 then 0  3                   when sal < 4000 then 'category 3'  4                   else 'category 4'  5              end  6  from emp;                 when sal < 3000 then 0                                      *ERROR at line 2:
COALESCE and NULLIF functions
Oracle provides two more functions that carry out a functionality that is similar to the CASE expression in certain scenarios. We can use these in conjunction with or as a variety of the CASE expression. 
COALESCE returns the first not null value in a given list of values.
E.g.: Returning the first not null value available in four columns present in a table.
select coalesce(col1, col2, col3, col4)from am25;
E.g.: The above example will return the same result as the below statement with the CASE expression.
select case when col1 is not null then col1            when col2 is not null then col2            when col3 is not null then col3            when col4 is not null then col4            else null       end VALfrom   am25;
The NULLIF function compares two values and does the following.
 

  
Returns null if both values are the same. 
  
Returns the first value if both values are different. 
 
E.g.: Returning the credits available for customers. The query below will return null if the TOTAL_CREDITS column is equal to the CREDITS_USED column for a customer, else it will return the TOTAL_CREDITS value.
select customer_name,  nullif(total_credit, credits_used)from   customer_credits;
E.g.: The above example will return the same result as the statement below with CASE expression. 
select customer_name, case when total_credits = credits_used then null                            else total_credits                      endfrom    customer_credits;
Conclusion
The maximum number of arguments that can be specified is 255, each WHEN ... THEN pair is counted as two arguments. To avoid this limitation the CASE function can be nested.
This functionality is supported in PL/SQL from Oracle 9i. The CASE expression will make it easy for developers to get more information based on analysis in a single query.