Oracle Decode函数

DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.
其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);
其中：
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果
value  是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应
result  是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时，函数返回的默认值
下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;

 

 

 

 

 

Note: Decode and Case are very similar in their appearance but can produce very different results.
Demo Tables & Data Airplanes Table Locations Table All other tables
 
DECODE (overload 1) standard.decode(expr NUMBER, pat NUMBER, res NUMBER) RETURN NUMBER;
DECODE (overload 2) standard.decode(
expr NUMBER,
pat  NUMBER,
res  VARCHAR2 CHARACTER SET ANY_CS)
return VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 3) standard.decode(expr NUMBER, pat NUMBER, res DATE) RETURN DATE;
DECODE (overload 4) standard.decode(
expr VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  NUMBER)
RETURN NUMBER;
DECODE (overload 5) standard.decode(
expr VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 6) standard.decode(
expr VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  DATE)
RETURN DATE;
DECODE (overload 7) standard.decode(expr DATE, pat DATE, res NUMBER) RETURN NUMBER;
DECODE (overload 8) standard.decode(
expr DATE,
pat  DATE,
res  VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 9) standard.decode( expr DATE, pat DATE, res DATE) RETURN DATE;
DECODE (overload 10) standard.decode(expr OBJECT, pat  OBJECT, res OBJECT) RETURN OBJECT;
DECODE (overload 11) standard.decode(expr UNDEFINED, pat UNDEFINED, res UNDEFINED)
RETURN UNDEFINED;
 
Decode Built-in Function

Simple DECODE  SELECT DECODE (value, <if this value>, <return this value>)
FROM dual; 
SELECT program_id,
  DECODE(customer_id, 'AAL', 'American Airlines') AIRLINE,
  delivered_date
FROM airplanes
WHERE ROWNUM < 11;

More Complex DECODE SELECT DECODE (value,<if this value>,<return this value>,
                     < if this value>,<return this value>,
                      ....)
FROM dual;
SELECT program_id,
       DECODE(customer_id,
              'AAL', 'American Airlines',
              'ILC', 'Intl. Leasing Corp.',
              'NWO', 'Northwest Orient',
              'SAL', 'Southwest Airlines',
              'SWA', 'Sweptwing Airlines',
              'USAF', 'U.S. Air Force') AIRLINE,
       delivered_date
FROM airplanes
WHERE ROWNUM < 11;

DECODE with DEFAULT  SELECT DECODE (value,<if this value>,<return this value>,
                     <if this value>,<return this value>,
                     ....
                     <otherwise this value>)
FROM dual; 
SELECT program_id,
       DECODE(customer_id,
             'AAL', 'American Airlines',
             'ILC', 'Intl. Leasing Corp.',
             'NWO', 'Northwest Orient',
             'SAL', 'Southwest Airlines',
   &n