Oracle中decode函數用法詳解

一、Oracle數據庫decode函數

Oracle數據庫提供了decode函數，該函數可以在條件成立時返回一個表達式，否則返回另一個表達式。其語法如下：

decode(expr, search1, result1 [, search2, result2, ...,] [, default])

其中，expr表示待比較的值，可以是任意數據類型，search1, search2, …是待匹配的值，result1, result2, …是匹配成功時返回的結果。如果沒有匹配成功，可以使用可選參數default來設置默認返回結果。需要注意的是，decode函數的參數個數必須滿足如下條件：

• 如果有default參數，則參數個數必須為偶數
• 如果沒有default參數，則參數個數必須為奇數

下面給出一個示例，用於說明該函數的基本用法：

SELECT ename, hiredate, 
    decode(job ,'CLERK', '辦事員', 'SALESMAN', '銷售員', 'MANAGER', '經理', 'PRESIDENT', '總裁', '未知') AS job_zh 
FROM emp;

上述SQL語句將查詢emp表中每個員工的姓名、入職時間和職位名稱的中文翻譯，結果如下：

ENAME       HIREDATE   JOB_ZH    
----------  --------- ---------
SMITH       17-DEC-80  辦事員   
ALLEN       20-FEB-81  銷售員   
WARD        22-FEB-81  銷售員   
JONES       02-APR-81  經理     
MARTIN      28-SEP-81  銷售員   
BLAKE       01-MAY-81  經理     
CLARK       09-JUN-81  經理     
KING        17-NOV-81  總裁     
TURNER      08-SEP-81  銷售員   
JAMES       03-DEC-81  辦事員   
FORD        03-DEC-81  經理     
MILLER      23-JAN-82  辦事員   

二、Oracle decode函數判斷條件空

如果需要對NULL值進行判斷，可以使用decode函數。例如，下面的SQL語句將查詢emp表中每個員工的姓名、薪水和是否超過平均薪水的標識：

SELECT ename, sal, 
    decode(sign(sal - avg_sal), -1, '低於平均薪水', 0, '等於平均薪水', 1, '高於平均薪水', '未知') AS sal_flag 
FROM emp, (SELECT round(avg(sal)) AS avg_sal FROM emp);

上述SQL語句中使用了sign函數來比較薪水和平均薪水的大小，並將結果傳遞給decode函數進行判斷，結果如下：

ENAME       SAL   SAL_FLAG     
----------  ----  ------------
SMITH        800   低於平均薪水
ALLEN       1600   高於平均薪水
WARD        1250   等於平均薪水
JONES       2975   高於平均薪水
MARTIN      1250   等於平均薪水
BLAKE       2850   高於平均薪水
CLARK       2450   高於平均薪水
KING       10000   高於平均薪水
TURNER      1500   高於平均薪水
JAMES        950   低於平均薪水
FORD       3000   高於平均薪水
MILLER      1300   等於平均薪水

三、Oracle中decode函數

如果需要在多個條件下返回不同的值，可以使用decode函數的嵌套。例如，下面的SQL語句將查詢emp表中每個員工的姓名、薪水和是否是高薪者的標識：

SELECT ename, sal, 
    decode(sign(sal - 3000), -1, '普通員工', 
        decode(sign(sal - 5000), -1, '中層幹部', 
            decode(sign(sal - 8000), -1, '高級管理人員', '未知'))) AS sal_type
FROM emp;

上述SQL語句中使用了嵌套的decode函數，在三個條件下分別返回不同的值，結果如下：

ENAME       SAL     SAL_TYPE          
----------  ------  -----------------
SMITH        800    普通員工          
ALLEN       1600    普通員工          
WARD        1250    普通員工          
JONES       2975    普通員工          
MARTIN      1250    普通員工          
BLAKE       2850    普通員工          
CLARK       2450    普通員工          
KING       10000    高級管理人員      
TURNER      1500    普通員工          
JAMES        950    普通員工          
FORD       3000    中層幹部          
MILLER      1300    普通員工          

四、Oracle decode函數的用法

除了以上的用法之外，還可以在decode函數中使用其它表達式。例如，下面的SQL語句將查詢emp表中每個員工的姓名、薪水和是否是高薪者的標識：

SELECT ename, sal, 
    decode(sign(sal - 3000), -1, '普通員工', 
        decode(sign(sal - 5000), -1, '中層幹部', 
            decode(sign(sal - 8000), -1, '高級管理人員', 
                decode(sal, 10000, '總裁', '未知')))) AS sal_type
FROM emp;

上述SQL語句中，第四個參數使用了一個簡單的條件判斷，結果如下：

ENAME       SAL     SAL_TYPE          
----------  ------  -----------------
SMITH        800    普通員工          
ALLEN       1600    普通員工          
WARD        1250    普通員工          
JONES       2975    普通員工          
MARTIN      1250    普通員工          
BLAKE       2850    普通員工          
CLARK       2450    普通員工          
KING       10000    總裁          
TURNER      1500    普通員工          
JAMES        950    普通員工          
FORD       3000    中層幹部          
MILLER      1300    普通員工          

五、Oracle decode用法

除了上述用法之外，還有一些高級用法，例如在decode函數中使用子查詢，或者在decode函數中使用字符串函數。例如，下面的SQL語句將查詢emp表中每個員工姓名和入職日期，並根據入職月份返回季度的名稱：

SELECT ename, hiredate, 
    decode(to_char(hiredate, 'MM'), 
            '01', '第一季度', 
            '02', '第一季度', 
            '03', '第一季度', 
            '04', '第二季度', 
            '05', '第二季度', 
            '06', '第二季度', 
            '07', '第三季度', 
            '08', '第三季度', 
            '09', '第三季度', 
            '10', '第四季度', 
            '11', '第四季度', 
            '12', '第四季度', 
            '未知') AS quarter 
FROM emp;

上述SQL語句中使用了to_char函數將hiredate字段轉換成月份，然後使用decode函數返回季度名稱，結果如下：

ENAME       HIREDATE   QUARTER           
----------  --------- -------------------
SMITH       17-DEC-80 第四季度       
ALLEN       20-FEB-81 第一季度       
WARD        22-FEB-81 第一季度       
JONES       02-APR-81 第二季度 
MARTIN      28-SEP-81 第三季度    
BLAKE       01-MAY-81 第二季度 
CLARK       09-JUN-81 第二季度
KING        17-NOV-81 第四季度
TURNER      08-SEP-81 第三季度
JAMES       03-DEC-81 第四季度
FORD        03-DEC-81 第四季度
MILLER      23-JAN-82 第一季度

六、Oracle decode函數MySQL

MySQL數據庫也提供了類似於Oracle的decode函數，其語法如下：

CASE expr WHEN search1 THEN result1 [WHEN search2 THEN result2 ...] [ELSE default] END

其中，expr表示待比較的值，可以是任意數據類型，search1, search2, …是待匹配的值，result1, result2, …是匹配成功時返回的結果。如果沒有匹配成功，可以使用可選參數default來設置默認返回結果，其使用方法與Oracle的decode函數類似。

七、Oracle數據庫decode函數用法舉例

以下是一個使用decode函數的綜合示例，該示例用於查詢emp表中每個員工的姓名、職位和基本工資，並按照職位和工資的大小返回不同的補貼。示例SQL如下：

SELECT ename, job, sal, 
    decode(job, 'CLERK', 
            decode(sign(sal-2000), -1, 0, 0.15*sal), 
        'SALESMAN',
            decode(sign(sal-3000), -1, 0.05*sal, 0.10*sal), 
        'MANAGER', 
            decode(sign(sal-4000), -1, 0.25*sal, 0.30*sal), 0) AS allowance 
FROM emp;

上述SQL語句中，第一個decode函數用於根據職位進行分類。對於『CLERK』職位，再根據基本工資進行分類，並返回相應的補貼比例；對於『SALESMAN』職位，也根據基本工資進行分類，並返回相應的補貼比例；對於『MANAGER』職位，同樣按照基本工資進行分類，並返回相應的補貼比例；默認值為0。結果如下：

ENAME       JOB        SAL     ALLOWANCE          
----------  --------- ------ -------------------
SMITH       CLERK       800                  0  
ALLEN       SALESMAN   1600                80  
WARD        SALESMAN   1250                62.5
JONES       MANAGER    2975            892.5  
MARTIN      SALESMAN   1250                62.5
BLAKE       MANAGER    2850            855   
CLARK       MANAGER    2450            735   
KING        PRESIDENT 10000            3000  
TURNER      SALESMAN   1500                75  
JAMES       CLERK       950                  0  
FORD        ANALYST    3000                  0  
MILLER      CLERK      1300                  0  

八、Hive中decode函數的用法

Hive中也提供了decode函數，其語法與Oracle類似，如下：

CASE expr WHEN search1 THEN result1 [WHEN search2 THEN result2 ...] [ELSE default] END

與Oracle不同的是