decode函数吗 jsp_DECODE函数

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> selectename "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 fromemp7 order byjob;

Name JOB Salary New Salary------- --------- ---------- ----------

SCOTT ANALYST 3000 3750FORD ANALYST3000 3750MILLER CLERK1300 1560JAMES CLERK950 1140SMITH CLERK800 960ADAMS CLERK1100 1320BLAKE MANAGER2850 3990JONES MANAGER2975 4165CLARK MANAGER2450 3430KING PRESIDENT5000 7000TURNER SALESMAN1500 1725MARTIN SALESMAN1250 1437.5WARD SALESMAN1250 1437.5ALLEN SALESMAN1600 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(*) TOTALFROMv$log_history awhere 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;