DECODE()使用方法和处理NULL 时候的细节

在查询数据，需要进行条件判断时，一般我们使用CASE...WHEN实现，当判断条件为相等时，除了使用CASE...WHEN实现，还可以使用DECODE函数。若要使用like、>、<等其他判断条件时，就只能使用CASE...WHEN实现了。下面就解释下DECODE()函数的用法。

DECODE()使用方法： 

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

处理null的时候需要将null cast 成相应的数据格式，例如decode （name，CAST(NULL AS VARCHAR(1)), 'zxm', 'bbb')

DECODE()含义说明：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)

ELSE
　　　　RETURN(缺省值)
END IF

附上DBCODE（）官方说明

：

DECODE scalar function<img alt="Read syntax di function is similar to the CASE expression except for the handling of null values:

• A null value of expression1 will match a corresponding null value of expression2.
• If the NULL keyword is used as an argument in the DECODE function, it must be cast to an appropriate data type.

The rules for determining the result type of a DECODE expression are based on the corresponding CASE expression.

Examples:

The DECODE expression:

DECODE (c1, 7, 'a', 6, 'b', 'c')

achieves the same result as the following CASE expression:

CASE c1 WHEN 7 THEN 'a' WHEN 6 THEN 'b' ELSE 'c' END

Similarly, the DECODE expression:

DECODE (c1, var1, 'a', var2, 'b')

where the values of c1, var1, and var2 could be null values, achieves the same result as the following CASE expression:

CASE WHEN c1 = var1 OR (c1 IS NULL AND var1 IS NULL) THEN 'a' WHEN c1 = var2 OR (c1 IS NULL AND var2 IS NULL) THEN 'b' ELSE NULL END

Consider also the following query:

SELECT ID, DECODE(STATUS, 'A', 'Accepted', 'D', 'Denied', CAST(NULL AS VARCHAR(1)), 'Unknown', 'Other') FROM CONTRACTS

Here is the same statement using a CASE expression:

SELECT ID, CASE WHEN STATUS = 'A' THEN 'Accepted' WHEN STATUS = 'D' THEN 'Denied' WHEN STATUS IS NULL THEN 'Unknown' ELSE 'Other' END FROM CONTRACTS