Using Oracle's decode function for multivalued judgment _oracle

The syntax structure of the DECODE function is as follows:

Copy Code code 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 decode function compares expressions and search words, returns the result if it matches, returns a default value if it does not match, and returns a null value if no default value is defined.
The following is a simple test to illustrate the use of the Decode function:

Copy Code code as follows:

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.