oracle decode函数

再列个副标题吧: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、 比较大小,此时经常同signtrunc两个函数配合使用。
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,salary1.15,-1,salary1.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 – 在字符串中的第一个字符处开始

oracle decode函数》有1条评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注