DECODE函数是ORACLE数据库直ウ用强除夜的函数之一，古晨借只要ORACLE公司的SQL供给了此函数，其他数据库厂商的SQL函数挚暂时出有此服从。DECODE有甚么用处呢？先机闭一个例子，假定我们念给公司员工减人为，其尺度是：工字在8000元以下的将减20％＝瑜字在8000元以上的减15％，一样平常的做法是，先选出挤素中的工孜仓段值？select salary into var-salary fromemployee，然后对变量var-salary用if-then-else或choosecase之类的流节制语句遏制审定。假定操做DECODE函数，那么我们便可以或许把那些流节制语句省略，经过进程SQL语句便可以或许直接完成。以下：selectdecode(sign(salary - 8000),1,salary*115,-1,salary*12,salary fromemployee 是没有是是很细练呢。DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,,else)，暗示假定value即是if1时，DECODE函数的成果返回then1,,假定没有即是任何一个if值，则返回else。泰尔维亭超级p57初看一下，DECODE只能做即是测试，但适才也看到了，我们经过进程一些函数或钾葡交流value，是可使DECODE函数具有除夜于、小于或即是服从。

DECODE(条件，值1，翻译值1，值2，翻译值2，值n，翻译值n，缺省值)

DECODE(字段，比较1，值1，比较2，值2，，比较n，值n缺省值) 

IF 条件=值1 THEN

该函数的含义以下：

　　　　RETURN(翻译值1)

ELSIF 条件=值2 THEN

　　　　RETURN(翻译值2)

　　　　

ELSIF 条件=值n THEN

　　　　RETURN(翻译值n)

ELSE

　　　　RETURN(缺省值)

END IF

decode()函数操做技术本领

1、Windows Server2003+ORACLE ；

2、ORACLE安拆路子为:C:/Oracle。

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

sign()函数按照某个值是0、正数借是负数，告别返回0、1、-1

比方:

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码成果为“变量1”，到达了与较小值的方针。

竖坐测试表sale:

create table sale

(

smonth char(6),        --月份

sell number(10,2)      --月收卖金额

)

插进测试数据：

insert into sale values( '',1100);

insert into sale values( '',1080);

insert into sale values( '',1200);

insert into sale values( '',1400);

insert into sale values( '',1300);

insert into sale values( '',1500);

insert into sale values( '',1700);

insert into sale values( '',1600);

insert into sale values( '',1800);

insert into sale values( '',1900);

insert into sale values( '',2000);

year　　　char(4)　　　　　 --年份

month1　　number(10,2)　　　--1月收卖金额

mit;

  念要转化为以下挨算的数据:

month2　　number(10,2)　　　--2月收卖金额

month3　　number(10,2)　　　--3月收卖金额

month4　　number(10,2)　　　--4月收卖金额

month5　　number(10,2)　　　--5月收卖金额

month7　　number(10,2)　　　--7月收卖金额

month6　　number(10,2)　　　--6月收卖金额

month9　　number(10,2)　　　--9月收卖金额

month10　　number(10,2)　　 --10月收卖金额

month8　　number(10,2)　　　--8月收卖金额

挨算转化的SQL语句为:

month11　　number(10,2)　　 --11月收卖金额

month12　　number(10,2)　　 --12月收卖金额

create or replace viewv_sale(yeay,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

as 

 select substrb(smonth,1,4),

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

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

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

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

sum(decode(substrb(smonth,5,2),'05',sell,0)),

sum(decode(substrb(smonth,5,2),'06',sell,0)),

sum(decode(substrb(smonth,5,2),'07',sell,0)),

sum(decode(substrb(smonth,5,2),'08',sell,0)),

sum(decode(substrb(smonth,5,2),'09',sell,0)),

sum(decode(substrb(smonth,5,2),'11',sell,0)),

sum(decode(substrb(smonth,5,2),'10',sell,0)),

sum(decode(substrb(smonth,5,2),'12',sell,0))

 from sale group by smonth;

查询成果：

select * form v_sale;

"YEAY","MONTH1","MONTH2","MONTH3","MONTH4","MONTH5","MONTH6","MONTH7","MONTH8","MONTH9","MONTH10","MONTH11","MONTH12"

---------------------------------------------------------------------------------------------------------------------

"2012","1080","0","0","0","0","0","0","0","0","0","0","0"

"2012","0","1100","0","0","0","0","0","0","0","0","0","0"

"2012","0","0","1200","0","0","0","0","0","0","0","0","0"

"2012","0","0","0","1300","0","0","0","0","0","0","0","0"

"2012","0","0","0","0","1400","0","0","0","0","0","0","0"

"2012","0","0","0","0","0","1500","0","0","0","0","0","0"

"2012","0","0","0","0","0","0","1600","0","0","0","0","0"

"2012","0","0","0","0","0","0","0","1700","0","0","0","0"

"2012","0","0","0","0","0","0","0","0","1800","0","0","0"

"2012","0","0","0","0","0","0","0","0","0","1900","0","0"

"2012","0","0","0","0","0","0","0","0","0","0","2000","0"

补偿示例1：

假定student的编号为id,成绩为score,那么：

有教天死绩表student,目下现古要用decode函数真现以下寂服从Ｒ∩绩>85,隐现劣秀；>70隐现超卓；>60及格；可则是没有及格。

select id,decode(sign(score-85),1,'劣秀',0,'劣秀',-1, 

decode(sign(score-60),1,'及格',0,'及格',-1,'没有及格'))) 

decode(sign(score-70),1,'超卓',0,'超卓',-1, 

from student；

======================================================

补偿示例2：

decode (expression, search_1, result_1, search_2,result_2)

Decode函数的语法则划以下:

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2,, search_n, result_n)

decode (expression, search_1, result_1, search_2, result_2,default)

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2,, search_n, result_n, default)

以下是一个简朴测试，苏玛素效果怎么样左旋瘦咔燃脂黑咖啡用于申明Decode函数的用法:

decode函数比较表达式战搜刮字，假订婚配，返回成果；假定没有婚配，返回default值；假定已界讲default值，则返回空值。

SQL> create table t as selectusername,default_tablespace,lock_date from dba_users;

Table created

SQL> select * from t;

USERNAME                    DEFAULT_TABLESPACE           LOCK_DATE

SYS                         SYSTEM

SYSTEM                      SYSTEM

------------------------------ ---------------------------------------

CSMIG                       SYSTEM

OUTLN                       SYSTEM

SCOTT                       SYSTEM

SQL> selectusername,decode(lock_date,null,'unlocked','locked') status fromt;

WMSYS                       locked

WMSYS                       SYSTEM                      20-OCT-04

EYGLE                       USERS

DBSNMP                      SYSTEM

8 rows selected

USERNAME                    STATUS

------------------------------ --------

SYS                         unlocked

SYSTEM                      unlocked

OUTLN                       unlocked

CSMIG                       unlocked

SCOTT                       unlocked

EYGLE                       unlocked

DBSNMP                      unlocked

8 rows selected

SQL> select username,decode(lock_date,null,'unlocked')status from t;

WMSYS