再列个副标题吧：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

例子：

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

其相当于：

1IF supplier_id = 10000 THEN
2     result := 'IBM';
3ELSIF supplier_id = 10001 THEN
4    result := 'Microsoft';
5ELSIF supplier_id = 10002 THEN
6    result := 'Hewlett Packard';
7ELSE
8    result := 'Gateway';
9END IF;

再一个例子，和order by一块使用，放在后面。

1select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

2、比较大小

此时经常同sign、trunc两个函数配合使用。

a、配合sign使用

1select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;
2--取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
如果date1 > date2, decode 函数返回date2. 否则decode函数返回 date1.

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

abs函数求绝对值，上式中如果date1-date2大于0，得正值，假设结果为2，则abs(date1-date2)也为正，取2 。两者之差得0，返回0，取返回日期小的值，date2,反之。取data1

如果配合sign函数，表达式就简单的多了：

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

又如：

SIGN/DECODE 联合对于有关销售红利等数字方面的比较是非常有用的。

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

假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％

1select 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

则其表达式可如下写：

1select 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，表结构为:

1month char(6) --月份
2sell number(10,2) --月销售金额

现有数据为:

200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300

想要转化为以下结构的数据:

 1year char(4) --年份
 2month1 number(10,2) --1月销售金额
 3month2 number(10,2) --2月销售金额
 4month3 number(10,2) --3月销售金额
 5month4 number(10,2) --4月销售金额
 6month5 number(10,2) --5月销售金额
 7month6 number(10,2) --6月销售金额
 8month7 number(10,2) --7月销售金额
 9month8 number(10,2) --8月销售金额
10month9 number(10,2) --9月销售金额
11month10 number(10,2) --10月销售金额
12month11 number(10,2) --11月销售金额
13month12 number(10,2) --12月销售金额

结构转化的SQL语句为:

 1create or replace view
 2v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
 3
 4as
 5
 6select
 7substrb(month,1,4),
 8sum(decode(substrb(month,5,2),'01',sell,0)),
 9sum(decode(substrb(month,5,2),'02',sell,0)),
10sum(decode(substrb(month,5,2),'03',sell,0)),
11sum(decode(substrb(month,5,2),'04',sell,0)),

上面的例子中又用到一个函数substrb。取值的部分数据的函数。下面我们将上面用到的除decode外的函数归纳下：

  • abs 数学函数，求绝对值，这个就没什么好说的了。
  • Sign是一个符号函数，sign(x) ，其中x可以是一个值，也可以是一个返回值的表达式。
  • sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
  • trunc截取函数，主要用于日期或数值的截取

这里有两个例子，便于理解：

1TRUNC(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')
2--返回当年第一天.
3
4trunc(sysdate,'mm') --返回当月第一天.
5trunc(sysdate,'d') --返回当前星期的第一天.
6trunc(sysdate,'dd')--返回当前年月日
7TRUNC（number[,decimals]）

其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

下面是该函数的使用情况：

1TRUNC（89.985，2）=89.98
2TRUNC（89.985）=89
3TRUNC（89.985，-1）=80

注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推。

substr
substr() 函数返回字符串的一部分。
语法：substr(string,start,length)

必需。规定在字符串的何处开始。
正数 – 在字符串的指定位置开始
负数 – 在从字符串结尾的指定位置开始
0 – 在字符串中的第一个字符处开始