详解sql与plsql4课-04.之普通函数篇oradev

DATAGURU专业数据分析2014

V1.5详解SQL与PL/SQL

第4周法律【

】

和幻灯片为炼数成金网络课程的教学资料，所有资料只能在课程内使用，不得在课程以外范围散播，违者将可能被法律和经济责任。课程详情炼数成金培训http:

2014

V1.52DATAGURU专业数据分析SQL函数之普通函数SQL函数概述数据类型SQL普通函数NULL21世纪最重要的是什么？2014

V1.53DATAGURU专业数据分析SQL函数概述函数可以带参数，也可以不带参数带参数的函数有点像操作符，对数据进行操作然后返回结果不带参数的函数有点像伪列，不过对每一行数据，伪列通常返回不一样的值，函数通常返回一样的值Oracle提供了丰富的函数，包括单行函数聚合函数分析函数……2014

V1.54DATAGURU专业数据分析SQL函数概述DEMOsele cii('A')

from

dual;select

to_date('2014-07-01

22:15','yyyy-mm-dd

hh24:mi')

from

dual;select

trunc(1.01)

from

dual;select

deptno,

max(sal)from

emp

group

by

deptno;select

max(hiredate)

from

emp;select

empno,

ename,

sal,

sum(sal)over(order

by

empno)

from

emp

order

by

empno;……2014

V1.55DATAGURU专业数据分析数据类型任何数据都有对应的数据类型分类数字型日期型字符型……2014

V1.56DATAGURU专业数据分析数据类型--数字型强大的NUMBER灵活的FLOATBINARY_FLOAT和BINARY_DOUBLE2014

V1.57DATAGURU专业数据分析数据类型--数字型强大的NUMBER格式：NUMBER(p,s)p:precision，可省略s:scale，可省略，但不可只有s而没有p精度：38位有效数字记数范畴：1.0

x

power(10,-130)<=x<1.0x

power(10,

126)以十进制方式记数：需要1~22个字节2014

V1.58DATAGURU专业数据分析数据类型--数字型灵活的FLOAT小数点可以任意浮动，是number的子类型，也是ANSI定义的标准数据类型格式：float(n)n：1~126与NUMBER精度的转换n*

0.30103=pp*

3.32193=n以二进制方式记数：需要1~22个字节2014

V1.59DATAGURU专业数据分析数据类型--数字型BINARY_FLOAT和BINARY_DOUBLEBINARY_FLOAT：单精度，4字节BINARY_DOUBLE：双精度，8字节与其他编程语言中的float、double分别对应2014

V1.510DATAGURU专业数据分析数据类型--数字型CAST函数将某常量或变量的类型强制设为指定类型例如：select

cast('1234'

as

number(9))

from

dual;select

cast(12

as

number(2))

from

dual;select

cast(12

as

number(9))

from

dual;select

cast(1.2345e4

as

number(9))

from

dual;2014

V1.511DATAGURU专业数据分析数据类型--数字型NUMBER

DEMOselect

cast(1.2345e8

as

number(8))

from

dual;select

cast(1.2345e8

as

number(9,4))

from

dual;select

cast(1.2345e8

as

number(9))

from

dual;select

cast(1.2345e8

as

number(15))

from

dual;select

cast(1.2345e8

as

number)

from

dual;select

cast(1.2345e8

as

float)

from

dual;select

cast(1.2345e8

as

float(3))

from

dual;select

cast(1.2345e8

as

float(4))

from

dual;select

cast(1.2345e8

as

float(7))

from

dual;select

cast(1.2345e8

as

float(11))

from

dual;2014

V1.512DATAGURU专业数据分析数据类型--数字型使用NUMBER–

数据库

十进制精度NUMBER类型变量，而使用二进制精度FLOAT类型变量所有的二进制数都可以用对应的十进制数去表示不是所有的十进制数都可以用二进制数来表示二进制：0.11十进制：0.75十进制：0.6二进制：0.1001……(无限不循环小数，无法准确记录)2014

V1.513DATAGURU专业数据分析数据类型--日期型DATETIMESTAMP2014

V1.514DATAGURU专业数据分析数据类型--日期型DATE记录了年、月、日、时、分、秒默认值：年：同SYSDATE里的年月：同SYSDATE里的月日：1号时分秒：均为02014

V1.515DATAGURU专业数据分析数据类型--日期型表示DATE的方法–

to_date('2013-02-09

23:59:59','yyyy-mm-dd

hh24:mi:ss')–

date'2013-02-09'to_date中的分隔符可以更换，date中的分隔符必须是-date只可以表示日期，不可以表示时间2014

V1.516DATAGURU专业数据分析数据类型--日期型DATE

DEMOSCOTT@lw.lw2>

select

to_date('56','ss')

from

dual;TO_DATE('56','SS')•2013-04-0100:00:56SCOTT@lw.lw2>

select

to_date('56','yy')

from

dual;TO_DATE('56','YY')•2056-04-01

00:00:00SCOTT@lw.lw2>

select

date'2016-04-05'

fromdual;DATE'2016-04-05'•2016-04-05

00:00:002014

V1.517DATAGURU专业数据分析数据类型--日期型TIMESTAMP记录了年、月、日、时、分、秒和纳秒默认值：年：同SYSDATE里的年月：同SYSDATE里的月日：1号时分秒和纳秒：均为02014

V1.518DATAGURU专业数据分析数据类型--日期型表示TIMESTAMP的方法–

to_timestamp('2013-02-09

23:59:59.000','yyyy-mm-dd

hh24:mi:ss.ff')–

timestamp

'2013-04-05

13:48:00.123456789'to_timestamp中的分隔符可以更换，timestamp中的日期分隔符必须是-，时间必须是:，秒后面必须跟上.timestamp可以精确表示到毫秒、微秒甚至纳秒级别2014

V1.519DATAGURU专业数据分析数据类型--日期型TIMESTAMP

DEMOSCOTT@lw.lw2>

alter

session

set

nls_timestamp_format='yyyy-mm-ddhh24:mi:ss.ff';会话已更改。SCOTT@lw.lw2>

select

to_timestamp('05

13:48:22.778','DD

HH24:MI:SS.FF5')

fromdual;TO_TIMESTAMP('0513:48:22.778','DDHH24:MI:SS.FF')•2013-04-05

13:48:22.778000000SCOTT@lw.lw2>

select

to_timestamp('05

13:48','YY

HH24:MI')

from

dual;TO_TIMESTAMP('0513:48','YYHH24:MI')•2005-04-01

13:48:00.0000000002014

V1.520DATAGURU专业数据分析数据类型--日期型TIMESTAMP

DEMOSCOTT@lw.lw2>

select

to_timestamp('05

13:48:22.778','DD

HH24:MI:SS.FF2')

fromdual;第1行出现错误:ORA-01880:零点几秒必须在0和999999999之间SCOTT@lw.lw2>

select

systimestamp

from

dual;SYSTIMESTAMP•17-4月-13056000下午+08:002014

V1.521DATAGURU专业数据分析数据类型--日期型SYSTIMESTAMP返回的是TIMESTAMP

WITH

TIME

ZONE类型的数据SCOTT@lw.lw2>

alter

session

set

nls_timestamp_tz_format='yyyy-mm-ddhh24:mi:ss.ff

tzh:tzm';SCOTT@lw.lw2>

select

systimestamp

from

dual;SYSTIMESTAMP•2013-04-17

13:32:41.605000

+08:00TIME

ZONE(时区)的概念，请自行翻阅相关资料2014

V1.522DATAGURU专业数据分析数据类型--日期型如何正确表示一个时间范围？判断一个变量X表示的时间是否是2013-04-05的任意的一个时间点？SCOTT@lw.lw2>

select

'TRUE'

from

dual

where

to_date('2013-04-05

13:48:22','YYYY-MM-DDHH24:MI:SS')

between

date'2013-04-05'

and

date'2013-04-06'-1/86400;SCOTT@lw.lw2>

select

'TRUE'

from

dual

where

to_date('&X','YYYY-MM-DD

HH24:MI:SS')between

date'2013-04-05'

and

date'2013-04-06'-1/86400;SCOTT@lw.lw2>

select

'TRUE'

from

dual

where

to_date('&X','YYYY-MM-DD

HH24:MI:SS')

>=date'2013-04-05'

and

to_date('&X','YYYY-MM-DD

HH24:MI:SS')<date'2013-04-06';SCOTT@lw.lw2>

select

'TRUE'

from

dual

where

to_char(to_date('&X','YYYY-MM-DDHH24:MI:SS'),'YYYY-MM-DD')='2013-04-05';86400？–

1天=24小时=24*60*60=86400秒2014

V1.523DATAGURU专业数据分析数据类型--日期型如何显示"

"这样的日期？SCOTT@lw.lw2>select

to_char(sysdate,'YYYY"年"MM"月"DD"日"')from

dual;•SCOTT@lw.lw2>select

to_char(sysdate,'YYYY"年"MM"月"DD"日"HH24"点"MM"分"')from

dual;13点38分2014

V1.524DATAGURU专业数据分析数据类型--字符型CHARVARCHAR22014

V1.525DATAGURU专业数据分析数据类型--字符型CHAR定长最小为1，最大为2000字节定义方式：char(10)–

char(10

byte)char(11

byte)char(12

char)2014

V1.526DATAGURU专业数据分析数据类型--字符型VARCHAR2变长最小为1，最大为4000字节定义方式：varchar2(10)varchar2(11

byte)varchar2(12

char)VARCHAR目前和VARCHAR2是一样的，但Oracle建议不要使用这个类型，因为将来另有他用。2014

V1.527DATAGURU专业数据分析数据类型--字符型多字节字符和单字节字符几乎无论什么字符集，英文总是单字节字符，即一个字符只占一个字节

、韩、俄、

等文字，会占据至少两个字节，并在不同的字符集下，占据的字节数不完全一样可以用lengthb来测试字符所占据的字节数2014

V1.528DATAGURU专业数据分析SQL普通函数单行函数聚合函数2014

V1.529DATAGURU专业数据分析SQL普通函数--单行函数在一个被查询的表或视图中，此类函数为每一行返回单独的一个值此类函数可出现在select后的字段列表、WHERE、START

WITH、CONNECT

BY、GROUP

BY和HAVING子句中分类数字类日期类字符类通用类转换类NULL相关类……2014

V1.530DATAGURU专业数据分析SQL普通函数--数字类单行函数精度大部分可以达到38位精度，即NUMBER类数据的最大精度COS,COSH,EXP,LN,LOG,SIN,SINH,

SQRT,TAN以及TANH，36位精度ACOS,ASIN,ATAN和ATAN2，30位精度（反三角函数）类型正负值类舍入类求余类幂指类三角类……2014

V1.531DATAGURU专业数据分析SQL普通函数--数字类单行函数正负值类的函数ABS绝对值，返回值一定大于等于0SIGN返回值只有1（正数）、-1（负数）和0（零）2014

V1.532DATAGURU专业数据分析SQL普通函数--数字类单行函数舍入类的函数CEIL，向上取整ceil(1.06)=2,

ceil(1.9)=2FLOOR，向下取整floor(1.06)=1,

floor(1.9)=1ROUND，四舍五入round(1.06)=1,

round(1.9)=2,

round(15.06,1)=15.1,

round(15.06,-1)=20TRUNC，截断trunc(1.06)=1,

trunc(1.9)=1,

trunc(15.16,1)=15.1,

trunc(15.16,

-1)=102014

V1.533DATAGURU专业数据分析SQL普通函数--数字类单行函数求余类的函数MOD，求余经典：mod(n2,n1)=n2-n1

*

FLOOR(n2/n1)oracle：mod(n2,n1)=sign(n2)*(abs(n2)

-

abs(n1)

*

FLOOR(abs(n2)/abs(n1)))n2为正数：mod(13,5)=3,mod(13.12,5)=3.12,mod(13.12,5.1)=2.92,mod(13.12,-5)=3.12n2为负数：mod(-13,5)=-3,mod(-13.12,5.1)=-2.92Oracle里的mod求余和传统意义上的mod函数不一样REMAINDER，求余2经典：remainder(n2,n1)=n2-n1*

ROUND(n2/n1)oracle：

remainder(n2,n1)=n2

-

n1

*

(case

when

ABS(n2/n1)-0.5=TRUNC(ABS(n2/n1))

andmod(ROUND(ABS(n2/n1)),2)=1

then

ROUND(n2/n1)-SIGN(n2/n1)

else

ROUND(n2/n1)

end)n2为正数：remainder(13,5)＝-2,remainder(11,5)=1n2为负数：remainder(-13,5)＝2,remainder(-13.12,-5.1)＝2.18通常

使用MOD，而不使用REMAINDER2014

V1.534DATAGURU专业数据分析SQL普通函数--数字类单行函数幂指类函数幂函数：POWERpower(2,10)=1024指数函数：EXPexp(1)＝2.718281828459045.……对数函数：LN、LOGln(2.718281828459045)=1log(2,

8)=3,

log(2,1024)=102014

V1.535DATAGURU专业数据分析SQL普通函数--数字类单行函数三角类函数三角函数：COS、SIN、TAN……反三角函数：ACOS、ASIN、ATAN……双曲函数：SINH、COSH、TANH……2014

V1.536DATAGURU专业数据分析SQL普通函数--日期类单行函数类型常规日期函数取日期值的某一部分日期加减日期舍入……时区类日期函数2014

V1.537DATAGURU专业数据分析SQL普通函数--日期类单行函数取日期值的某一部分TO_CHAR函数假定sysdate为2013-04-13

13:06:28to_char(sysdate,

'yyyy')=2013,

to_char(sysdate,

'ss')=28EXTRACT函数extract(MONTH

from

date'2013-01-31')=1extract(SECOND

fromtimestamp'2013-08-04

09:00:05')=5YEAR,

MONTH,

DAY,

HOUR,

MINUTE,

SECOND2014

V1.538DATAGURU专业数据分析SQL普通函数--日期类单行函数计算日期加法直接加上一个数字，表示在当前日期上增加的时长date'2013-01-01'+30=date'2013-01-31'date'2013-01-01'+29+1/12=to_date('2013-01-30

02',

'yyyy-mm-dd

hh24')ADD_MONTHS函数add_months(date'2010-02-27',24)＝date'2012-02-27'add_months(date'2012-02-29',24)＝add_months(date'2012-02-28',24)add_months(date'2010-02-28',24)=？2014

V1.539DATAGURU专业数据分析SQL普通函数--日期类单行函数计算日期加法INTERVAL表示法date'1900-01-01'

+interval

'113-2'

year(3)

to

month=date'2013-03-01'date'2013-04-17'

+

interval

'1

23'

day

to

hour=to_date('

2013-04-18

23',

'yyyy-mm-dd

hh24')NUMTOYMINTERVAL和NUMTODSINTERVALdate'2013-01-28'

+

numtodsinterval(31,'DAY')=date'2013-02-28'date'2013-01-31'

+

numtodsinterval(28,'DAY')=date'2013-02-28'date'2013-01-28'

+

numtoyminterval(1,'month')=date'2013-02-28'date'2013-01-31'

+

numtoyminterval(1,'month')＝?2014

V1.540DATAGURU专业数据分析SQL普通函数--日期类单行函数计算日期差值直接相减，得天数date'2013-01-31'-to_date('2013-01-01

12:00:00','yyyy-mm-dd

hh24:mi:ss')=29.5MONTHS_BETWEEN函数months_between(date'2013-01-31',to_date('2010-11-01

12','yyyy-mm-dd

hh24'))=

26.9516129SCOTT@lw.lw2>

select

29.5/31

from

dual;–

29.5/31––

.9516129032014

V1.541DATAGURU专业数据分析SQL普通函数--日期类单行函数日期舍入TRUNC函数trunc(to_date('12:35:35','hh24:mi:ss'),'MI')=to_date('12:35:00','hh24:mi:ss')trunc(to_date('12:35:35','hh24:mi:ss'),'HH')=to_date('12:00','hh24:mi')ROUND函数round(date'2013-04-17','MM')=date'2013-05-01'round(to_date('12:35:25','hh24:mi:ss'),'MI')=to_date('12:35:00','hh24:mi:ss')round(to_date('12:35:35','hh24:mi:ss'),'MI')=to_date('12:36:00','hh24:mi:ss')2014

V1.542DATAGURU专业数据分析SQL普通函数--日期类单行函数其他常规函数NEXT_DAYLAST_DAY以上两个函数请时区类日期函数通常在

应用中才会用到SCOTT@lw.lw2>

desc

V$TIMEZONE_NAMES;名称 是否为空?

类型•TZNAMETZABBREV–

有需要请VARCHAR2(64)VARCHAR2(64)2014

V1.543DATAGURU专业数据分析SQL普通函数--字符类单行函数大小写转换字符与ASCII码转换字符串连接字符串填补字符串修剪取子串查找替换2014

V1.544DATAGURU专业数据分析SQL普通函数--字符类单行函数大小写转换INITCAPLOWERUPPER字符与ASCII码转换ASCIICHR2014

V1.545DATAGURU专业数据分析SQL普通函数--字符类单行函数字符串连接CONCAT函数，同||操作符SCOTT@lw.lw2>

set

heading

offSCOTT@lw.lw2>

select

'last'||'winner'||''

from

dual;lastwinnerSCOTT@lw.lw2>

select

concat(concat('last','winner'),'')

from

dual;lastwinner字符串填补LPAD、RPAD函数lpad('1234',9,'5')='555551234'lpad('1234',3,'5')='123'lpad('1234',9,'56')='565651234'lpad('1234',9,'数')='数数1234'2014

V1.546DATAGURU专业数据分析SQL普通函数--字符类单行函数字符串修剪LTRIM和RTRIM函数默认从左或右去掉空格从左或右去掉第一个参数中的字符，直到该字符不等于第二个参数中的任意字符ltrim('aabdwecab',

'abcd')='wecab'rtrim('Today

is

a

good

day.

')=

'Today

is

a

good

day.'TRIM函数默认去掉两端的空格从左或/和右边去掉第二个参数指定的字符，仅一个SCOTT@lw.lw2>

select

trim('abcd'from

'aabdwecab')

from

dual;第1行出现错误:ORA-30001:截取集仅能有一个字符SCOTT@lw.lw2>

select

trim('a'

from

'aabdwecaba')

from

dual;bdwecab2014

V1.547DATAGURU专业数据分析SQL普通函数--字符类单行函数取子串SUBSTR函数SCOTT@lw.lw2>

select

substr('lastwinner@dataguru',5)

from

dual;winner@dataguruSCOTT@lw.lw2>

select

substr('lastwinner@dataguru',5,6)

from

dual;winnerSCOTT@lw.lw2>

select

substr('lastwinner@dataguru',-5,6)

from

dual;aguruSCOTT@lw.lw2>

select

substr('lastwinner@dataguru',-5,3)

fromdual;agu2014

V1.548DATAGURU专业数据分析SQL普通函数--字符类单行函数查找替换INSTR函数SCOTT@lw.lw2>

select

instr('lastwinner@dataguru','win')

from

dual;–

5REPLACE函数replace('Today

isa

good

day.

','

')='Todayisagoodday.'replace('Today

is

a

good

day.

','

','##')='

Today##is##a##good##day.####'TRANSLATE函数translate('Today

is

a

good

day.

','

','##')='

Today#is#a#good#day.##'translate('( -)

','0(+)-','0')=008601068345678translate('1last34winn00er97是剑2破53冰45山4的5作5者之4453一45','l0123456789','l')=?2014

V1.549DATAGURU专业数据分析SQL普通函数--通用类单行函数GREATEST可跟若干参数（类型可为数字、字符、日期等），返回其中的最大值，若参数中有一个NULL，则返回NULL以第一个参数的类型为准对其他参数做可能的隐式类型转换注意不要和MAX搞混SCOTT@lw.lw2>select

greatest(sysdate,

date'2013-12-31',to_date('13:12','mi:hh')+interval

'1'

year)

from

dual;GREATEST(SYSDATE,DA•2014-04-01

12:13:00LEAST基本同上，只不过求的是最小值，并且不要和min搞混2014

V1.550DATAGURU专业数据分析SQL普通函数--转换类单行函数CASTTO_CHARTO_DATETO_NUMBERTO_TIMESTAMPDECODE2014

V1.551DATAGURU专业数据分析SQL普通函数--NULL类单行函数NVLnvl(a,b)if

(a==null)

return

b;

else

return

a;NVL2nvl2(a,b,c)

if

(