??? 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 emp7 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;

?

轉載于:https://www.cnblogs.com/Richardzhu/p/3598005.html

總結

以上是生活随笔為你收集整理的DECODE函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。