OracleのDecode関数を使って多値判定を行います。

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.