DECODE函数

最新推荐文章于 2025-05-23 16:19:58 发布

weixin_34260991

最新推荐文章于 2025-05-23 16:19:58 发布

阅读量4.7k

点赞数

文章标签： java

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

利用DECODE函数求出基于不同职位的(job)每个员工加薪后的工资值。涉及的表名:emp
SQL> select ename "Name",job,sal "Salary",
  2         DECODE(job,'SALESMAN',sal*1.15,
  3                'CLERK', sal*1.20,
  4                'ANALYST',sal*1.25,
  5                         sal*1.40) "New Salary"
  6   from emp
  7  order by job;

Name             JOB                Salary New Salary
-------   ---------        ---------- ----------
SCOTT             ANALYST              3000     3750
FORD             ANALYST              3000     3750
MILLER           CLERK                1300     1560
JAMES             CLERK                950       1140
SMITH             CLERK                800       960
ADAMS             CLERK                1100     1320
BLAKE             MANAGER              2850     3990
JONES             MANAGER              2975     4165
CLARK             MANAGER              2450     3430
KING             PRESIDENT            5000     7000
TURNER           SALESMAN              1500     1725
MARTIN           SALESMAN              1250   1437.5
WARD             SALESMAN              1250   1437.5
ALLEN             SALESMAN              1600     1840

已选择14行。

在上面例子查询语句中，DECODE函数执行的步骤如下：
(1)、当job为SALESMAN时DECODE函数返回表达式sal*1.5的值，否则，执行(2)；
(2)、当job为CLERK时DECODE函数返回表达式sal*1.20的值，否则，执行(3)；
(3)、当job为ANALYST时DECODE函数返回表达式sal*1.25的值，否则，执行(4)；
(4)、DECODE函数返回表达式sal*1.40的值。

最近10天中每天日志切换的量(即可分析10天的波度，又可分析24小时内，可很容易看出异常情况)
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, 
       COUNT(*) TOTAL 
FROM v$log_history  a  
   where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) 
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;