DATAGURU專業(yè)數(shù)據(jù)分析2014

V1.5詳解SQL與PL/SQL

第4周法律【

】

和幻燈片為煉數(shù)成金網(wǎng)絡(luò)課程的教學(xué)資料，所有資料只能在課程內(nèi)使用，不得在課程以外范圍散播，違者將可能被法律和經(jīng)濟(jì)責(zé)任。課程詳情煉數(shù)成金培訓(xùn)http:

2014

V1.52DATAGURU專業(yè)數(shù)據(jù)分析SQL函數(shù)之普通函數(shù)SQL函數(shù)概述數(shù)據(jù)類型SQL普通函數(shù)NULL21世紀(jì)最重要的是什么？2014

V1.53DATAGURU專業(yè)數(shù)據(jù)分析SQL函數(shù)概述函數(shù)可以帶參數(shù)，也可以不帶參數(shù)帶參數(shù)的函數(shù)有點(diǎn)像操作符，對(duì)數(shù)據(jù)進(jìn)行操作然后返回結(jié)果不帶參數(shù)的函數(shù)有點(diǎn)像偽列，不過對(duì)每一行數(shù)據(jù)，偽列通常返回不一樣的值，函數(shù)通常返回一樣的值Oracle提供了豐富的函數(shù)，包括單行函數(shù)聚合函數(shù)分析函數(shù)……2014

V1.54DATAGURU專業(yè)數(shù)據(jù)分析SQL函數(shù)概述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專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型任何數(shù)據(jù)都有對(duì)應(yīng)的數(shù)據(jù)類型分類數(shù)字型日期型字符型……2014

V1.56DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型強(qiáng)大的NUMBER靈活的FLOATBINARY_FLOAT和BINARY_DOUBLE2014

V1.57DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型強(qiáng)大的NUMBER格式：NUMBER(p,s)p:precision，可省略s:scale，可省略，但不可只有s而沒有p精度：38位有效數(shù)字記數(shù)范疇：1.0

x

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

power(10,

126)以十進(jìn)制方式記數(shù)：需要1~22個(gè)字節(jié)2014

V1.58DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型靈活的FLOAT小數(shù)點(diǎn)可以任意浮動(dòng)，是number的子類型，也是ANSI定義的標(biāo)準(zhǔn)數(shù)據(jù)類型格式：float(n)n：1~126與NUMBER精度的轉(zhuǎn)換n*

0.30103=pp*

3.32193=n以二進(jìn)制方式記數(shù)：需要1~22個(gè)字節(jié)2014

V1.59DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型BINARY_FLOAT和BINARY_DOUBLEBINARY_FLOAT：單精度，4字節(jié)BINARY_DOUBLE：雙精度，8字節(jié)與其他編程語言中的float、double分別對(duì)應(yīng)2014

V1.510DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型CAST函數(shù)將某常量或變量的類型強(qiáng)制設(shè)為指定類型例如：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專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型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專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--數(shù)字型使用NUMBER–

數(shù)據(jù)庫

十進(jìn)制精度NUMBER類型變量，而使用二進(jìn)制精度FLOAT類型變量所有的二進(jìn)制數(shù)都可以用對(duì)應(yīng)的十進(jìn)制數(shù)去表示不是所有的十進(jìn)制數(shù)都可以用二進(jìn)制數(shù)來表示二進(jìn)制：0.11十進(jìn)制：0.75十進(jìn)制：0.6二進(jìn)制：0.1001……(無限不循環(huán)小數(shù)，無法準(zhǔn)確記錄)2014

V1.513DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型DATETIMESTAMP2014

V1.514DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型DATE記錄了年、月、日、時(shí)、分、秒默認(rèn)值：年：同SYSDATE里的年月：同SYSDATE里的月日：1號(hào)時(shí)分秒：均為02014

V1.515DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型表示DATE的方法–

to_date('2013-02-09

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

hh24:mi:ss')–

date'2013-02-09'to_date中的分隔符可以更換，date中的分隔符必須是-date只可以表示日期，不可以表示時(shí)間2014

V1.516DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型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專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型TIMESTAMP記錄了年、月、日、時(shí)、分、秒和納秒默認(rèn)值：年：同SYSDATE里的年月：同SYSDATE里的月日：1號(hào)時(shí)分秒和納秒：均為02014

V1.518DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型表示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中的日期分隔符必須是-，時(shí)間必須是:，秒后面必須跟上.timestamp可以精確表示到毫秒、微秒甚至納秒級(jí)別2014

V1.519DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型TIMESTAMP

DEMOSCOTT@lw.lw2>

alter

session

set

nls_timestamp_format='yyyy-mm-ddhh24:mi:ss.ff';會(huì)話已更改。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專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型TIMESTAMP

DEMOSCOTT@lw.lw2>

select

to_timestamp('05

13:48:22.778','DD

HH24:MI:SS.FF2')

fromdual;第1行出現(xiàn)錯(cuò)誤:ORA-01880:零點(diǎn)幾秒必須在0和999999999之間SCOTT@lw.lw2>

select

systimestamp

from

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

V1.521DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型SYSTIMESTAMP返回的是TIMESTAMP

WITH

TIME

ZONE類型的數(shù)據(jù)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(時(shí)區(qū))的概念，請(qǐng)自行翻閱相關(guān)資料2014

V1.522DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型如何正確表示一個(gè)時(shí)間范圍？判斷一個(gè)變量X表示的時(shí)間是否是2013-04-05的任意的一個(gè)時(shí)間點(diǎn)？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小時(shí)=24*60*60=86400秒2014

V1.523DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--日期型如何顯示"

"這樣的日期？SCOTT@lw.lw2>select

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

dual;?SCOTT@lw.lw2>select

to_char(sysdate,'YYYY"年"MM"月"DD"日"HH24"點(diǎn)"MM"分"')from

dual;13點(diǎn)38分2014

V1.524DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--字符型CHARVARCHAR22014

V1.525DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--字符型CHAR定長最小為1，最大為2000字節(jié)定義方式：char(10)–

char(10

byte)char(11

byte)char(12

char)2014

V1.526DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--字符型VARCHAR2變長最小為1，最大為4000字節(jié)定義方式：varchar2(10)varchar2(11

byte)varchar2(12

char)VARCHAR目前和VARCHAR2是一樣的，但Oracle建議不要使用這個(gè)類型，因?yàn)閷砹碛兴谩?014

V1.527DATAGURU專業(yè)數(shù)據(jù)分析數(shù)據(jù)類型--字符型多字節(jié)字符和單字節(jié)字符幾乎無論什么字符集，英文總是單字節(jié)字符，即一個(gè)字符只占一個(gè)字節(jié)

、韓、俄、

等文字，會(huì)占據(jù)至少兩個(gè)字節(jié)，并在不同的字符集下，占據(jù)的字節(jié)數(shù)不完全一樣可以用lengthb來測試字符所占據(jù)的字節(jié)數(shù)2014

V1.528DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)單行函數(shù)聚合函數(shù)2014

V1.529DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--單行函數(shù)在一個(gè)被查詢的表或視圖中，此類函數(shù)為每一行返回單獨(dú)的一個(gè)值此類函數(shù)可出現(xiàn)在select后的字段列表、WHERE、START

WITH、CONNECT

BY、GROUP

BY和HAVING子句中分類數(shù)字類日期類字符類通用類轉(zhuǎn)換類NULL相關(guān)類……2014

V1.530DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)精度大部分可以達(dá)到38位精度，即NUMBER類數(shù)據(jù)的最大精度COS,COSH,EXP,LN,LOG,SIN,SINH,

SQRT,TAN以及TANH，36位精度ACOS,ASIN,ATAN和ATAN2，30位精度（反三角函數(shù)）類型正負(fù)值類舍入類求余類冪指類三角類……2014

V1.531DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)正負(fù)值類的函數(shù)ABS絕對(duì)值，返回值一定大于等于0SIGN返回值只有1（正數(shù)）、-1（負(fù)數(shù)）和0（零）2014

V1.532DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)舍入類的函數(shù)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，截?cái)鄑runc(1.06)=1,

trunc(1.9)=1,

trunc(15.16,1)=15.1,

trunc(15.16,

-1)=102014

V1.533DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)求余類的函數(shù)MOD，求余經(jīng)典：mod(n2,n1)=n2-n1

*

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

-

abs(n1)

*

FLOOR(abs(n2)/abs(n1)))n2為正數(shù)：mod(13,5)=3,mod(13.12,5)=3.12,mod(13.12,5.1)=2.92,mod(13.12,-5)=3.12n2為負(fù)數(shù)：mod(-13,5)=-3,mod(-13.12,5.1)=-2.92Oracle里的mod求余和傳統(tǒng)意義上的mod函數(shù)不一樣REMAINDER，求余2經(jīng)典：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為正數(shù)：remainder(13,5)＝-2,remainder(11,5)=1n2為負(fù)數(shù)：remainder(-13,5)＝2,remainder(-13.12,-5.1)＝2.18通常

使用MOD，而不使用REMAINDER2014

V1.534DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)冪指類函數(shù)冪函數(shù)：POWERpower(2,10)=1024指數(shù)函數(shù)：EXPexp(1)＝2.718281828459045.……對(duì)數(shù)函數(shù)：LN、LOGln(2.718281828459045)=1log(2,

8)=3,

log(2,1024)=102014

V1.535DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--數(shù)字類單行函數(shù)三角類函數(shù)三角函數(shù)：COS、SIN、TAN……反三角函數(shù)：ACOS、ASIN、ATAN……雙曲函數(shù)：SINH、COSH、TANH……2014

V1.536DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)類型常規(guī)日期函數(shù)取日期值的某一部分日期加減日期舍入……時(shí)區(qū)類日期函數(shù)2014

V1.537DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)取日期值的某一部分TO_CHAR函數(shù)假定sysdate為2013-04-13

13:06:28to_char(sysdate,

'yyyy')=2013,

to_char(sysdate,

'ss')=28EXTRACT函數(shù)extract(MONTH

from

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

fromtimestamp'2013-08-04

09:00:05')=5YEAR,

MONTH,

DAY,

HOUR,

MINUTE,

SECOND2014

V1.538DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)計(jì)算日期加法直接加上一個(gè)數(shù)字，表示在當(dāng)前日期上增加的時(shí)長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函數(shù)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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)計(jì)算日期加法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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)計(jì)算日期差值直接相減，得天數(shù)date'2013-01-31'-to_date('2013-01-01

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

hh24:mi:ss')=29.5MONTHS_BETWEEN函數(shù)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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)日期舍入TRUNC函數(shù)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函數(shù)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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--日期類單行函數(shù)其他常規(guī)函數(shù)NEXT_DAYLAST_DAY以上兩個(gè)函數(shù)請(qǐng)時(shí)區(qū)類日期函數(shù)通常在

應(yīng)用中才會(huì)用到SCOTT@lw.lw2>

desc

V$TIMEZONE_NAMES;名稱 是否為空?

類型?TZNAMETZABBREV–

有需要請(qǐng)VARCHAR2(64)VARCHAR2(64)2014

V1.543DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)大小寫轉(zhuǎn)換字符與ASCII碼轉(zhuǎn)換字符串連接字符串填補(bǔ)字符串修剪取子串查找替換2014

V1.544DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)大小寫轉(zhuǎn)換INITCAPLOWERUPPER字符與ASCII碼轉(zhuǎn)換ASCIICHR2014

V1.545DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)字符串連接CONCAT函數(shù)，同||操作符SCOTT@lw.lw2>

set

heading

offSCOTT@lw.lw2>

select

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

from

dual;lastwinnerSCOTT@lw.lw2>

select

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

from

dual;lastwinner字符串填補(bǔ)LPAD、RPAD函數(shù)lpad('1234',9,'5')='555551234'lpad('1234',3,'5')='123'lpad('1234',9,'56')='565651234'lpad('1234',9,'數(shù)')='數(shù)數(shù)1234'2014

V1.546DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)字符串修剪LTRIM和RTRIM函數(shù)默認(rèn)從左或右去掉空格從左或右去掉第一個(gè)參數(shù)中的字符，直到該字符不等于第二個(gè)參數(shù)中的任意字符ltrim('aabdwecab',

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

is

a

good

day.

')=

'Today

is

a

good

day.'TRIM函數(shù)默認(rèn)去掉兩端的空格從左或/和右邊去掉第二個(gè)參數(shù)指定的字符，僅一個(gè)SCOTT@lw.lw2>

select

trim('abcd'from

'aabdwecab')

from

dual;第1行出現(xiàn)錯(cuò)誤:ORA-30001:截取集僅能有一個(gè)字符SCOTT@lw.lw2>

select

trim('a'

from

'aabdwecaba')

from

dual;bdwecab2014

V1.547DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)取子串SUBSTR函數(shù)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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--字符類單行函數(shù)查找替換INSTR函數(shù)SCOTT@lw.lw2>

select

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

from

dual;–

5REPLACE函數(shù)replace('Today

isa

good

day.

','

')='Todayisagoodday.'replace('Today

is

a

good

day.

','

','##')='

Today##is##a##good##day.####'TRANSLATE函數(shù)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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--通用類單行函數(shù)GREATEST可跟若干參數(shù)（類型可為數(shù)字、字符、日期等），返回其中的最大值，若參數(shù)中有一個(gè)NULL，則返回NULL以第一個(gè)參數(shù)的類型為準(zhǔn)對(duì)其他參數(shù)做可能的隱式類型轉(zhuǎn)換注意不要和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專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--轉(zhuǎn)換類單行函數(shù)CASTTO_CHARTO_DATETO_NUMBERTO_TIMESTAMPDECODE2014

V1.551DATAGURU專業(yè)數(shù)據(jù)分析SQL普通函數(shù)--NULL類單行函數(shù)NVLnvl(a,b)if

(a==null)

return

b;

else

return

a;NVL2nvl2(a,b,c)

if

(