请问SQL中decode函数的用法?

请问SQL中decode函数的用法?,第1张

DECODE函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途呢? 先构造一个例子,假设我们想给智星职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15%,通常的做法是,先选出记录中的工资字段值? select salary into var-salary from employee,然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数,那么我们就可以把这些流控制语句省略,通过SQL语句就可以直接完成。如下:select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁? DECODE的语法:DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。初看一下,DECODE 只能做等于测试,但刚才也看到了,我们通过一些函数或计算替代value,是可以使DECODE函数具备大于、小于或等于功能。

decode()函数使用技巧

·软件环境:

1、Windows NT4.0+ORACLE 8.0.4

2、ORACLE安装路径为:C:\ORANT

·含义解释:

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

· 使用方法:

1、比较大小

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”,达到了取较小值的目的。

2、表、视图结构转化

现有一个商品销售表sale,表结构为:

monthchar(6) --月份

sellnumber(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)),

不是键位。decode是Oracle公司独家提供的功能(psinformix数据库也提供该函数),因此并不是键位。且他是一个功能很强的函数。它虽然不是SQL的标准,但对于性能非常有用。

【二】SQL的基本函数

2.1 关系型数据库SQL命令类别

数据 *** 纵语言:DML: selectinsertdeleteupdatemerge.

数据定义语言:DDL: createalterdroptruncaterenamecomment.

事务控制语言:TCL: commitrollbacksavepoint.

数据控制语言:DCL: grantrevoke.

2.2 单行函数与多行函数

单行函数:指一行数据输入,返回一个值得函数。所以查询一个表时,对选择的每一行数据都会返回一个结果。

SQL>select empno,lower(ename) from emp

多行函数:指多行数据输入,返回一个值得函数。所以对表的群组进行 *** 作,并且每组返回一个结果。(典型的是聚合函数)

SQL>select sum(sal) from emp

2.3 单行函数的几种类型

2.3.1 字符型函数

lower('SQL Course')----->sql course 返回小写

upper('sql course')----->SQL COURSE 返回大学

initcap('SQL course')----->Sql Course 每个单字返回首字母大写

concat('good','string')---->good string 拼接 只能拼接2个字符串

substr('String',1,3)---->Str 从第1位开始截取3位数,

演变:只有两个参数的

substr('String',3) 正数第三位起始,得到后面所有字符

substr('String',-2) 倒数第二位,起始,得到最后所有字符

instr('t#i#m#r#a#n#','#') --->找第一个#字符在那个绝对位置,得到的数值

Instr参数经常作为substr的第二个参数值

演变:Instr参数可有四个之多

如select instr('aunfukk','u',-1,1) from dual倒数第一个u是哪个位置,结果返回5

length('String')---->6 长度,得到的是数值

length参数又经常作为substr的第三个参数

lpad('first',10,'#39)左填充

rpad(676768,10,'*')右填充

replace('JACK and JUE','J','BL')---->BLACK and BLUE

trim('m' from 'mmtimranm')---->timran 两头截,这里的‘m’是截取集,仅能有一个字符

trim( ' timran ')---->timran 作用是两头去空字符

处理字符串时,利用字符型函数的嵌套组合是非常有效的:

create table customers(cust_name varchar2(20))

insert into customers values('Lex De Hann')

insert into customers values('Renske Ladwig')

insert into customers values('Jose Manuel Urman')

insert into customers values('Joson Malin')

select * from customers

CUST_NAME

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

Lex De Hann

Renske Ladwig

Jose Manuel Urman

Joson Malin

一共四条记录,客户有两个名的,也有三个名的,现在想列出仅有三个名的客户,且第一个名字用*号略去

答案之一:

SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"

FROM customers

WHERE INSTR(cust_name,' ',1,2)>0

CUST NAME

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

*** De Hann

**** Manuel Urman

分析:

先用INSTR(cust_name,' ')找出第一个空格的位置,

然后,SUBSTR(cust_name,INSTR(cust_name,' '))从第一个空格开始往后截取字符串到末尾,结果是第一个空格以后所有的字符,

最后,LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原来的长度,不足的部分用*填充,也就是将第一个空格前的位置,用*填充。

where后过滤是否有三个名字,INSTR(cust_name, ' ',1,2)从第一个位置,从左往右,查找第二次出现的空格,如果返回非0值,则说明有第二个空格,则有第三个名字。

2.3.2 数值型函数

round 对指定的值做四舍五入,round(p,s) s为正数时,表示小数点后要保留的位数,s也可以为负数,但意义不大。

round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9

round(45.923, 0),结果,46

round(45.923, -1),结果,50

trunc 对指定的值取整 trunc(p,s)

trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9

trunc(45.923),结果,45

trunc(45.923, -1),结果, 40

mod 返回除法后的余数

SQL>select mod(100,12) from dual

2.3.3 日期型函数

因为日期在oracle里是以数字形式存储的,所以可对它进行加减运算,计算是以天为单位。

缺省格式:DD-MON-RR.

可以表示日期范围:(公元前)4712 至(公元)9999

时间格式

SQL>select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') FROM dual

SQL>select sysdate+2 from dual当前时间+2day

SQL>select sysdate+2/24 from dual当前时间+2hour

SQL>select sysdate+2/1440 from dual当前时间+2分钟

SQL>select (sysdate-hiredate)/7 week from emp两个date类型差,结果是以天为整数位的实数。

①MONTHS_BETWEEN 计算两个日期之间的月数

SQL>select months_between('1994-04-01','1992-04-01') mm from dual

查找emp表中参加工作时间>30年的员工

SQL>select * from emp where months_between(sysdate,hiredate)/12>32

很容易认为单行函数返回的数据类型与函数类型一致,对于数值函数类型而言的确如此,但字符和日期函数可以返回任何数据类型的值。比如instr函数是字符型的,months_between函数是日期型的,但它们返回的都是数值。

②ADD_MONTHS 给日期增加月份

SQL>select hiredate,add_months(hiredate,4) from emp

③LAST_DAY 日期当前月份的最后一天

SQL>select hiredate,last_day(hiredate) from emp

④NEXT_DAY NEXT_DAY的第2个参数可以是数字1-7,分别表示周日--周六(考点)

比如要取下一个星期六,则应该是:

SQL>select next_day(sysdate,7) FROM DUAL

⑤ROUND(p,s),TRUNC(p,s)在日期中的应用,如何舍入要看具体情况,s是MONTH按30天计,应该是15舍16入,s是YEAR则按6舍7入计算。

SQL>SELECT empno, hiredate,round(hiredate,'MONTH') AS round,trunc(hiredate,'MONTH') AS trunc FROM emp

SQL>SELECT empno, hiredate, round(hiredate,'YEAR') AS round,trunc(hiredate,'YEAR') AS trunc FROM emp

2.3.4 几个有用的函数和表达式

1)DECODE函数和CASE表达式:

实现sql语句中的条件判断语句,具有类似高级语言中的if-then语句的功能。

decode函数源自oracle, case表达式源自sql标准,实现功能类似,decode语法更简单些。

decode函数用法:

SQL>SELECT job, sal,

decode(job, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.15,'MANAGER', SAL*1.20, SAL) SALARY FROM emp

decode函数的另几种常见用法:

SQL>select ename,job,decode(job,'MANAGER','中层干部') leader from emp

SQL>select ename,job,comm,decode (comm,null,'nonsale','sale') saleman from emp

注:单一列处理,共四个参数:含义是:comm 如果为null就取'nonsale,否则取'sale'

SQL>select sal,sign(sal-1500) from emp

SQL>select ename,decode (sign(sal-1500), 1, 'NORMAL','LOW') as "LEV" from emp

注:sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1,含义是:工资大于1500,返回1,真取'NORMAL',假取'LOW'

CASE表达式第一种用法:

SQL>SELECT job, sal,

case job when 'ANALYST' then SAL*1.10

when 'CLERK' then SAL*1.15

when 'MANAGER' then SAL*1.20

else sal end SALARY

FROM emp

CASE表达式第二种用法:

SQL>SELECT job, sal, case

when job='ANALYST' then SAL*1.1

when job='CLERK' then SAL*1.15

when job='MANAGER' then SAL*1.20

else sal end SALARY

FROM emp

以上三种写法结果都是一样的

CASE第二种语法比第一种语法增加了搜索功能。形式上第一种when后跟定值,而第二种还可以使用表达式和比较符。

看一个例子

SQL>SELECT ename,sal,case

when sal>=3000 then '高级'

when sal>=2000 then '中级'

else '低级' end 级别

FROM emp

再看一个例子:使用了复杂的表达式

SQL>SELECT AVG(CASE

WHEN sal BETWEEN 500 AND 1000 AND JOB='CLERK'

THEN sal ELSE null END) "CLERK_SAL"

from emp

比较

SQL>select avg(sal) from emp where job='CLERK'

2)DISTINCT(去重)限定词的用法:

distinct貌似多行函数,严格来说它不是函数而是select子句中的一个选项。

SQL>select distinct job from emp消除表行重复值。

SQL>select distinct job,deptno from emp重复值是后面的字段组合起来考虑的

SQL>select distinct * from emp消除重复记录

3)sys_context 获取环境上下文的函数(多用于应用环境)

scott远程登录

SQL>select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual

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

192.168.0.136

SQL>select sys_context('userenv','sid') from dual

SYS_CONTEXT('USERENV','SID')

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

129

SQL>select sys_context('userenv','terminal') from dual

SYS_CONTEXT('USERENV','TERMINAL')

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

TIMRAN-222C75E5

the end !!!

@jackman 共筑美好!


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/6779452.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-28
下一篇 2023-03-28

发表评论

登录后才能评论

评论列表(0条)

保存