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 SYSTEM
- SYSTEM SYSTEM
- OUTLN SYSTEM
- CSMIG SYSTEM
- SCOTT SYSTEM
- EYGLE USERS
- DBSNMP SYSTEM
- WMSYS SYSTEM 20-OCT-04
- 8 rows selected.
- SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;
- USERNAME STATUS
- ------------------------------ --------
- SYS unlocked
- SYSTEM unlocked
- OUTLN unlocked
- CSMIG unlocked
- SCOTT unlocked
- EYGLE unlocked
- DBSNMP unlocked
- WMSYS locked
- 8 rows selected.
- SQL> select username,decode(lock_date,null,'unlocked') status from t;
- USERNAME STATUS
- ------------------------------ --------
- SYS unlocked
- SYSTEM unlocked
- OUTLN unlocked
- CSMIG unlocked
- SCOTT unlocked
- EYGLE unlocked
- DBSNMP unlocked
- WMSYS
- 8 rows selected.