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不同的是