How to Use the Decode function of Oracle for multi-value judgment

Author: eygle | English Version [indicate the source and author information and this statement in hyperlink form during reprinting]
Link: http://www.eygle.com/archives/2005/10/oracle_howto_use_decode_function.html

The syntax structure of the Decode function is as follows:

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)

The comparison expression and search word of the decode function. If the expression matches, the return result is returned. If the expression does not match, the default value is returned. If the default value is not defined, the return value is null.
The following is a simple test to Decode the function usage:

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.