oracle中decode函數與abs、sign、trunc、substr函數的混合使用

decode()函數是ORACLE PL/SQL是功能強大的函數之一，目前還只有ORACLE公司的SQL提供了此函數，其他資料庫廠商的SQL實現還沒有此功能。（但其不是標準SQL函數，不過這也正是他的優點，其他資料庫中提供不了如此強大的函數。）

功能：

1、 流程式控制制，相當於IF-THEN-ELSE功能。

 用法如下：decode( expression , search , result [, search , result]... [, default] )

expression 要比較的運算式，search 要與expression 比較的欄位。.

result 如果expression 與search 一樣的話，返回該結果。.

default 此參數可選，如果沒有與expression 匹配上的search . 就返回此結果，如果此參數沒有設定，當沒有與expression匹配上的search時，返回null。

search 和 result可成對出現多次，代表各種要匹配的情況。

適用於9i以上版本，如：Oracle 9i, Oracle 10g, Oracle 11g

例子：Select supplier_name,decode(supplier_id,1000,'IBM',10001,'Microsoft','1002','Hewlett Packard','Gateway') result from suppliers;

其相當於：

IF supplier_id = 10000 THEN

     result := 'IBM';

ELSIF supplier_id = 10001 THEN

    result := 'Microsoft';

ELSIF supplier_id = 10002 THEN

    result := 'Hewlett Packard';

ELSE

    result := 'Gateway';

END IF;

再一個例子，和order by一塊使用，放在後面。

select * from table_subject order by decode(subject_name, '語文', 1, '數學', 2, , '外語',3)

2、 比較大小,此時經常同sign、trunc兩個函數配合使用。

a、配合sign使用

select decode(sign(變數1-變數2),-1,變數1,變數2) from dual; --取較小值
sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1

如果date1 > date2, decode 函數返回date2. 否則decode函數返回 date1.

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

abs函數求絕對值，上式中如果date1-date2大於0，得正值，假設結果為2，則abs(date1-date2)也為正，取2 。兩者之差得0，返回0，取返回日期小的值，date2,反之。取data1

如果配合sign函數，運算式就簡單的多了：

DECODE(SIGN(date1-date2), 1, date2, date1)

又如：

SIGN/DECODE 聯合對於有關銷售紅利等數字方面的比較是非常有用的。

DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

假設我們想給智星職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加15％

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee

b、配合trunc求範圍

雖然不可以用decode函數來確定數位範圍. 可是我們可以試著建立一個運算式，這個運算式可以得一個數字指定的範圍, 下一個數字對應下一個指定的範圍, 以此類推.

我想寫一個decode函數，要求如下:

如果 yrs_of_service < 1 返回 0.04

如果 yrs_of_service >= 1 and < 5 返回0.04

如果 yrs_of_service > 5 返回 0.06

則其運算式可如下寫：

select emp_name,decode(trunc((yrs_of_service+3)/4),0,0.04,1,0.04,0.06) as perc_value from employees;

註：decode函數的最大參數個數為255個,包括expression, search, and result。超過這個範圍將報"ORA-00939: too many arguments for function".錯誤

3、 表、視圖結構的轉化

現有一個商品銷售表sale，表結構為:

month char(6) --月份

sell number(10,2) --月銷售金額

現有資料為:

200001 1000

200002 1100

200003 1200

200004 1300

200005 1400

200006 1500

200007 1600

200101 1100

200202 1200

200301 1300

想要轉化為以下結構的資料:

year char(4) --年份

month1 number(10,2) --1月銷售金額

month2 number(10,2) --2月銷售金額

month3 number(10,2) --3月銷售金額

month4 number(10,2) --4月銷售金額

month5 number(10,2) --5月銷售金額

month6 number(10,2) --6月銷售金額

month7 number(10,2) --7月銷售金額

month8 number(10,2) --8月銷售金額

month9 number(10,2) --9月銷售金額

month10 number(10,2) --10月銷售金額

month11 number(10,2) --11月銷售金額

month12 number(10,2) --12月銷售金額

結構轉化的SQL語句為:

create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as

select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)),

sum(decode(substrb(month,5,2),'02',sell,0)),

sum(decode(substrb(month,5,2),'03',sell,0)),

sum(decode(substrb(month,5,2),'04',sell,0)),

上面的例子中又用到一個函數substrb。取值的部分資料的函數。

下面我們將上面用到的除decode外的函數歸納下：

abs 數學函數，求絕對值，這個就沒什麼好說的了。

Sign是一個符號函數，sign(x) ，其中x可以是一個值，也可以是一個傳回值的運算式。

sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1

trunc截取函數，主要用於日期或數值的截取，這裡有兩個例子，便於理解：

TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am') ='24-Nov-1999 12:00:00 am' 　　TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') ='24-Nov-1999 08:00:00 am' 　　trunc(sysdate,'yyyy') --返回當年第一天. 　　trunc(sysdate,'mm') --返回當月第一天. 　　trunc(sysdate,'d') --返回當前星期的第一天. 　　trunc(sysdate,'dd')--返回當前年月日

 

　TRUNC（number[,decimals]） 　　其中： 　　number 待做截取處理的數值 　　decimals 指明需保留小數點後面的位元。可選項，忽略它則截去所有的小數部分 　　下面是該函數的使用方式： 　　TRUNC（89.985，2）=89.98 　　TRUNC（89.985）=89 　　TRUNC（89.985，-1）=80

注意：第二個參數可以為負數，表示為小數點左邊指定位元後面的部分截去，即均以0記。與取整類似，比如參數為1即取整到十分位，如果是-1，則是取整到十位，以此類推。

substr　　substr() 函數返回字串的一部分。 　　文法：substr(string,start,length) 　　必需。規定在字串的何處開始。 　　正數 - 在字串的指定位置開始 　　負數 - 在從字串結尾的指定位置開始 　　0 - 在字串中的第一個字元處開始