如何使用Oracle的Decode函數進行多值判斷

作者：eygle | English Version 【轉載時請以超連結形式標明文章出處和作者資訊及本聲明】
連結：http://www.eygle.com/archives/2005/10/oracle_howto_use_decode_function.html

Decode函數的文法結構如下:

decode (expression, search_1, result_1)decode (expression, search_1, result_1, search_2, result_2)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)decode (expression, search_1, result_1, default)decode (expression, search_1, result_1, search_2, result_2, default)decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函數比較運算式和搜尋字，如果匹配，返回結果；如果不匹配，返回default值；如果未定義default值，則返回空值。
以下是一個簡單測試，用於說明Decode函數的用法:

SQL> create table t as select username,default_tablespace,lock_date from dba_users;Table created.SQL> select * from t;USERNAME DEFAULT_TABLESPACE LOCK_DATE------------------------------ ------------------------------ ---------SYS SYSTEMSYSTEM SYSTEMOUTLN SYSTEMCSMIG SYSTEMSCOTT SYSTEMEYGLE USERSDBSNMP SYSTEMWMSYS SYSTEM 20-OCT-048 rows selected.SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;USERNAME STATUS------------------------------ --------SYS unlockedSYSTEM unlockedOUTLN unlockedCSMIG unlockedSCOTT unlockedEYGLE unlockedDBSNMP unlockedWMSYS locked8 rows selected.SQL> select username,decode(lock_date,null,'unlocked') status from t;USERNAME STATUS------------------------------ --------SYS unlockedSYSTEM unlockedOUTLN unlockedCSMIG unlockedSCOTT unlockedEYGLE unlockedDBSNMP unlockedWMSYS8 rows selected.