Oracle函數(shù)與表達(dá)式

1、Oracle函數(shù)與表達(dá)式Oracle中提供了大量的內(nèi)置函數(shù)，以處理各種形式的運(yùn)算。這些函數(shù)涵蓋了字符串運(yùn)算、數(shù)值運(yùn)算、日期運(yùn)算等方面。同樣，Oracle允許使用數(shù)值運(yùn)算、邏輯運(yùn)算等基本的表達(dá)式運(yùn)算，另外，提供了SQL標(biāo)準(zhǔn)所規(guī)定的特殊判式。Oracle中的字符串函數(shù)；Oracle中的數(shù)學(xué)函數(shù)；Oracle中的日期函數(shù)；第14章Oracle中的函數(shù)數(shù)與表達(dá)達(dá)式Oracle中的聚合合函數(shù)；Oracle中的運(yùn)算算表達(dá)式式；Oracle中的特殊殊判式；Oracle中的高級(jí)級(jí)函數(shù)分析函數(shù)數(shù)與窗口口函數(shù)。14.1Oracle中的字符符串函數(shù)數(shù)Oracle提供了豐豐富的字字符串函函數(shù)，本本小節(jié)將將通過(guò)實(shí)實(shí)例講

2、述述Oracle中各字符符串函數(shù)數(shù)的使用用。14.1.1lpad()函數(shù)lpad()函數(shù)用于于左補(bǔ)全全字符串串。在某某些情況況下，預(yù)預(yù)期的字字符串為為固定長(zhǎng)長(zhǎng)度，而而且格式式統(tǒng)一，此時(shí)可可以考慮慮使用lpad()函數(shù)。例例如，深深市股票票代碼都都以0開頭，并并且都為為6位，可以以利用lpad格式化股股票代碼碼，以保保證股票票代碼的的格式。selectlpad(21,6,0)stock_code fromdual;需要注意意的是，當(dāng)原字字符串的的長(zhǎng)度大大于預(yù)期期長(zhǎng)度時(shí)時(shí)，實(shí)際際進(jìn)行的的是截取取字符串串操作。selectlpad(1234567,6,0) stock_codefromdual;14

3、.1.2rpad()函數(shù)與lpad()函數(shù)相反反，rpad()函數(shù)從右右端補(bǔ)齊齊字符串串。selectrpad(abc,10, *)fromdual;注意與說(shuō)說(shuō)明：lpad()和rpad()都用于填填充字符符串，lpad()從左端進(jìn)進(jìn)行填充充，而rpad()從右端進(jìn)進(jìn)行填充充，但是是，二者者在最終終截取字字符串時(shí)時(shí)，都是是從左端端開始截截取。selectrpad(abcdefg, 6, *)fromdual;14.1.3lower()函數(shù)返回小寫寫字符串串lower()函數(shù)用于于返回字字符串的的小寫形形式。lower()函數(shù)在查查詢語(yǔ)句句中經(jīng)常常扮演重重要角色色。例如如，對(duì)于于用戶名名和密碼碼

4、的校驗(yàn)驗(yàn)來(lái)說(shuō)，用戶名名一般并并不區(qū)分分大小寫寫，用戶戶無(wú)論輸輸入了大大寫還是是小寫形形式，都都被認(rèn)為為是合法法用戶。因此，在數(shù)據(jù)據(jù)庫(kù)查詢?cè)儠r(shí)，應(yīng)應(yīng)該將數(shù)數(shù)據(jù)庫(kù)中中用戶名名與用戶戶輸入的的用戶名名進(jìn)行統(tǒng)統(tǒng)一。selectuser_id,user_name fromt_userswherelower(user_name) =lower(Alex);14.1.4upper()函數(shù)返回大寫寫字符串串upper()函數(shù)用于于返回字字符串的的大寫形形式。與與lower()函數(shù)類似似，upper()函數(shù)也可可以用在在查詢語(yǔ)語(yǔ)句中，以統(tǒng)一一數(shù)據(jù)庫(kù)庫(kù)和查詢?cè)儣l件的的一致性性。selectuser_id,user

5、_name fromt_userswhereupper(user_name) =upper(ALEX);注意與說(shuō)說(shuō)明：upper()函數(shù)和lower()函數(shù)只針針對(duì)英文文字符其其作用，因?yàn)橹恢挥杏⑽奈淖址挪庞写笮⌒懼址帧?4.1.5initcap()函數(shù)單詞首字字母大寫寫initcap()函數(shù)將單單詞的首首字母大大寫。selectinitcap(big)fromdual;需要注意意的是，initcap()函數(shù)不能能自動(dòng)識(shí)識(shí)別單詞詞selectinitcap(bigbigtiger)from dual;initcap()函數(shù)會(huì)將將參數(shù)中中的非單單詞字符符作為單單詞分隔隔符selectini

6、tcap(big_big_tiger) fromdual;selectinitcap(big/big/tiger) fromdual;selectinitcap(big bigtiger) fromdual;14.1.6length()函數(shù)返回字符符串長(zhǎng)度度length()函數(shù)用于于返回字字符串的的長(zhǎng)度。selectlength(abcd) fromdual;空字符串串的長(zhǎng)度度不是0，而是null。因?yàn)榭湛兆址灰曌髯鱪ull，所以，length(null)返回的仍仍然是null。selectlength()from dual;對(duì)其其他他數(shù)據(jù)類類型，照照樣可以以通過(guò)length()函數(shù)來(lái)獲

7、獲得其長(zhǎng)長(zhǎng)度。length()函數(shù)會(huì)首首先將參參數(shù)轉(zhuǎn)換換為字符符串，然然后計(jì)算算其長(zhǎng)度度。selectlength(12.51)from dual;14.1.7substr()函數(shù)截取字符符串substr()函數(shù)用于于截取字字符串。該函數(shù)數(shù)可以指指定截取取的起始始位置，截取長(zhǎng)長(zhǎng)度，可可以實(shí)現(xiàn)現(xiàn)靈活的的截取操操作，因因此，成成為字符符串操作作中最常常用的函函數(shù)之一一。例如，對(duì)對(duì)于字符符串“1234567890”，現(xiàn)欲截截取自第第5位開始的的4個(gè)字符。selectsubstr(1234567890,5,4)fromdual;需要注意意的是，Oracle中字符位位置從1開始，而而不是像像某些編編程語(yǔ)

8、言言（如Java）那樣從從0開始。如果不指指定長(zhǎng)度度，那么么substr()函數(shù)將獲獲取起始始位置參參數(shù)至字字符串結(jié)結(jié)尾處的的所有字字符。selectsubstr(1234567890,5)fromdual;14.1.8instr()函數(shù)獲得字符符串出現(xiàn)現(xiàn)的位置置instr()函數(shù)用于于獲得子子字符串串在父字字符串中中出現(xiàn)的的位置。selectinstr(bigbigtiger,big)fromdual;可以指定定額外的的參數(shù)，以命令令該函數(shù)數(shù)從指定定位置開開始搜索索。selectinstr(bigbigtiger,big,2)fromdual;還可以指指定出現(xiàn)現(xiàn)次數(shù)參參數(shù)，以以指定是是第幾次

9、次搜索到到子字符符串。selectinstr(bigbigtiger,big,2,2)fromdual;14.1.9ltrim()函數(shù)刪除字符符串首部部空格ltrim()中的l代表left。該函數(shù)數(shù)用于刪刪除字符符串左端端的空白白符。selectltrim(abc)fromdual;需要注意意的是，空白符符不僅僅僅包括了了空格符符，還包包括TAB鍵、回車車符和換換行符。14.1.10rtrim()函數(shù)刪除字符符串尾部部空格rtrim()中的r代表right。該函數(shù)數(shù)用于刪刪除字符符串右端端空白符符。刪除除字符串串首尾空空白符可可以結(jié)合合使用ltrm()和rtrim()函數(shù)。selectrtri

10、m(ltrim(abc)from dual;14.1.11trim()函數(shù)刪除字符符串首尾尾空格trim()函數(shù)可用用于刪除除首尾空空格，相相當(dāng)于ltrim()和rtrim()的組合。selecttrim(abc)fromdual;14.1.12to_char()函數(shù)將其他類類型轉(zhuǎn)換換為字符符類型to_char()函數(shù)用于于將其他他數(shù)據(jù)類類型的數(shù)數(shù)據(jù)轉(zhuǎn)換換為字符符型，這這些類型型主要包包括數(shù)值值型、日日期型。1.將數(shù)值型型轉(zhuǎn)換為為字符串串selectto_char(120, 99999) resultfrom dual;selectto_char(0.96,9.99) resultfrom d

11、ual;selectto_char(0.96,0.00) resultfrom dual;selectto_char(5897.098,999,999,999.000) resultfrom dual;selectto_char(5987.098,$999,999,999.000)resultfromdual;2.將日期型型轉(zhuǎn)換為為字符串串selectto_char(sysdate,yyyy-mm-dd) resultfrom dual;selectto_char(sysdate,YYYY-MON-DD)from dual;14.1.13chr()函數(shù)將ascii碼轉(zhuǎn)換為為字符串串chr()函

12、數(shù)用于于將ascii碼轉(zhuǎn)換為為字符串串。通過(guò)過(guò)chr()函數(shù)，可可以對(duì)不不宜直接接輸入的的字符進(jìn)進(jìn)行操作作。例如如，將回回車換行行符插入入到數(shù)據(jù)據(jù)中。insertintotest_datavalues (6,周林|chr(13)|chr(10)|梁軍,20);select*from test_datawhereid= 6;14.1.14translate()函數(shù)替換字符符translate()函數(shù)用于于替換字字符串。替換的的規(guī)則類類似于翻翻譯的過(guò)過(guò)程。selecttranslate(56338, 1234567890,avlihemoqr)resultfromdual;需要注意意的是，當(dāng)字符符

13、不能被被成功“翻譯”，那么么，Oracle將使用空空字符替替換它。利用此此特性，可以使使用translate()函數(shù)來(lái)刪刪除一個(gè)個(gè)含有數(shù)數(shù)字和英英文字母母的字符符串中的的所有字字母：selecttranslate(21343yuioioizf899dasiwpe58595oda0j098,#abcdefghijklmnopqrstuvwxyz,)reulstfrom dual;14.2Oracle中的數(shù)學(xué)學(xué)函數(shù)Oracle提供的數(shù)數(shù)學(xué)函數(shù)數(shù)可以處處理日常常使用到到的大多多數(shù)數(shù)學(xué)學(xué)運(yùn)算。本小節(jié)節(jié)將講述述Oracle中常用的的幾種數(shù)數(shù)學(xué)函數(shù)數(shù)。14.2.1abs()函數(shù)返回?cái)?shù)字字的絕對(duì)對(duì)值abs(

14、)函數(shù)的參參數(shù)只能能是數(shù)值值型，該該參數(shù)用用于返回回參數(shù)的的絕對(duì)值值。selectabs(-2.1) fromdual;14.2.2round ()函數(shù)返回?cái)?shù)字字的“四舍五入入”值round()函數(shù)用于于返回某某個(gè)數(shù)字字的四舍舍五入值值。為了了使用該該函數(shù)，除了提提供原始始值之外外，還應(yīng)應(yīng)提供精精確到的的位數(shù)。精確位位數(shù)可以以為正整整數(shù)、0和負(fù)整數(shù)數(shù)。selectround(2745.173, 2) resultfrom dual;如果不使使用第二二個(gè)參數(shù)數(shù)，那么么，相當(dāng)當(dāng)于使用用了參數(shù)數(shù)0，即精確確到整數(shù)數(shù)。selectround(2745.173) resultfrom dual;如果第二

15、二個(gè)參數(shù)數(shù)為負(fù)數(shù)數(shù)，那么么，相當(dāng)當(dāng)于將數(shù)數(shù)值精確確到小數(shù)數(shù)點(diǎn)之前前的位數(shù)數(shù)。selectround(2745,-1) resultfrom dual;14.2.3ceil()函數(shù)向上取整整ceil()函數(shù)只能能有一個(gè)個(gè)參數(shù)。該函數(shù)數(shù)將參數(shù)數(shù)向上取取整，以以獲得大大于等于于該參數(shù)數(shù)的最小小整數(shù)。selectceil(21.897) resultfrom dual;需要注意意的是該該函數(shù)針針對(duì)負(fù)數(shù)數(shù)的運(yùn)算算：selectceil(-21.897)resultfromdual;因?yàn)閏eil()函數(shù)返回回的是大大于等于于參數(shù)的的最小整整數(shù)，所所以，該該函數(shù)返返回的并并非-22，而是-21。14.2.4

16、floor()函數(shù)向下取整整與ceil函數(shù)相反反，floor()函數(shù)用于于返回小小于等于于某個(gè)數(shù)數(shù)值的最最大整數(shù)數(shù)。selectfloor(21.897)resultfromdual;selectfloor(-21.897)result fromdual;14.2.5mod()函數(shù)取模操作作mod()函數(shù)有兩兩個(gè)參數(shù)數(shù)，第一一個(gè)參數(shù)數(shù)為被除除數(shù)，第第二個(gè)參參數(shù)為除除數(shù)。mod()函數(shù)的實(shí)實(shí)際功能能為獲得得兩數(shù)相相除之后后的余數(shù)數(shù)。selectmod(5,2)result fromdual;14.2.6sign()函數(shù)返回?cái)?shù)字字的正負(fù)負(fù)性sign()函數(shù)只有有一個(gè)參參數(shù)。該該函數(shù)將將返回參參數(shù)的

17、正正負(fù)性。若返回回值為1，表示該該參數(shù)大大于0；若返回回值為-1，表示該該參數(shù)小小于0；若返回回值為0，表示該該參數(shù)等等于0。selectsign(8)resultfromdual;selectsign(-8)result fromdual;selectsign(0)resultfromdual;sign()函數(shù)為判判斷兩個(gè)個(gè)數(shù)值的的大小關(guān)關(guān)系提供供了方便便。因?yàn)闉樵趏racle中，利用用類似ifelse的結(jié)構(gòu)來(lái)來(lái)判斷兩兩個(gè)數(shù)值值之間的的大小關(guān)關(guān)系，并并不像編編程語(yǔ)言言中那樣樣方便，而且極極易造成成代碼的的復(fù)雜化化。14.2.7sqrt()函數(shù)返回?cái)?shù)字字的平方方根sqrt()函數(shù)也只只有一個(gè)個(gè)

18、參數(shù)。該函數(shù)數(shù)用于返返回參數(shù)數(shù)的平方方根?？煽梢岳糜胷ound()函數(shù)和sqrt()函數(shù)返回回某個(gè)數(shù)數(shù)值的近近似平方方根。selectround(sqrt(2),3)result fromdual;14.2.8power()函數(shù)乘方運(yùn)算算power()函數(shù)有兩兩個(gè)參數(shù)數(shù)。該函函數(shù)用于于實(shí)現(xiàn)數(shù)數(shù)值的乘乘方運(yùn)算算。selectpower(6,2)result fromdual;14.2.9trunc()函數(shù)截取數(shù)字字trunc()函數(shù)用于于截取部部分?jǐn)?shù)字字。其工工作機(jī)制制非常類類似于round()函數(shù)。與與round()函數(shù)不同同的是，該函數(shù)數(shù)不對(duì)數(shù)數(shù)值做四四舍五入入處理，而是直直接截取取。se

19、lecttrunc(2745.173, 2) resultfrom dual;保留位數(shù)數(shù)的值可可以為0，當(dāng)該參參數(shù)的值值為0時(shí)，將保保留到整整數(shù)。selecttrunc(2745.173) resultfrom dual;當(dāng)保留位位數(shù)小于于0時(shí)，表示示保留到到小數(shù)點(diǎn)點(diǎn)之前的的位數(shù)。selecttrunc(2745.173, -1)resultfromdual;14.2.10vsize()函數(shù)返回?cái)?shù)據(jù)據(jù)的存儲(chǔ)儲(chǔ)空間vsize()函數(shù)根據(jù)據(jù)數(shù)據(jù)庫(kù)庫(kù)的存儲(chǔ)儲(chǔ)格式，來(lái)返回回其所占占用的存存儲(chǔ)空間間的字節(jié)節(jié)數(shù)。selectvsize(abc123) fromdual;注意與說(shuō)說(shuō)明：vsize()函數(shù)在返

20、返回的是是Oracle實(shí)際存儲(chǔ)儲(chǔ)數(shù)據(jù)的的字節(jié)數(shù)數(shù)，在實(shí)實(shí)際開發(fā)發(fā)中使用用的幾率率也較小小。讀者者可以不不必了解解Oracle本身的存存儲(chǔ)機(jī)制制。14.2.11to_number()函數(shù)將字符串串轉(zhuǎn)換為為數(shù)值類類型to_number()函數(shù)可以以將字符符串轉(zhuǎn)換換為數(shù)值值型。selectto_number(257.90)resultfromdual;需要注意意的是，被轉(zhuǎn)換換的字符符串必須須符合數(shù)數(shù)值類型型格式。如果被被轉(zhuǎn)換的的字符串串不符合合數(shù)值型型格式，Oracle將拋出錯(cuò)錯(cuò)誤提示示。selectto_number(a)result fromdual;14.3Oracle中的日期期函數(shù)Oracl

21、e提供了豐豐富的日日期函數(shù)數(shù)。利用用日期函函數(shù)可以以靈活的的對(duì)日期期進(jìn)行運(yùn)運(yùn)算。14.3.1to_date()函數(shù)將字符串串轉(zhuǎn)換為為日期型型to_date()函數(shù)用于于將字符符串轉(zhuǎn)換換為日期期。被轉(zhuǎn)轉(zhuǎn)換的字字符串必必須符合合特定的的日期格格式。selectto_date(12/02/09,mm/dd/yy)resultfromdual;14.3.2add_months()函數(shù)為日期加加上特定定月份add_months()函數(shù)將為為日期添添加特定定月份，并獲得得新的日日期。selectto_char(add_months(sysdate, 2),yyyy-mm-dd) resultfrom du

22、al;14.3.3last_day()函數(shù)返回特定定日期所所在月的的最后一一天last_day()函數(shù)將接接受一個(gè)個(gè)日期參參數(shù)。該該函數(shù)首首先獲得得日期參參數(shù)所在在月的信信息，然然后獲得得該月最最后一天天的日期期。selectto_char(last_day(sysdate),yyyy-mm-dd)resultfromdual;可以綜合合利用add_months()函數(shù)來(lái)獲獲得若干干月之后后的月份份的最后后一天。selectto_char(last_day(add_months(sysdate, 3),yyyy-mm-dd)resultfromdual;14.3.4months_between

23、 ()函數(shù)返回兩個(gè)個(gè)日期所所差的月月數(shù)months_between()函數(shù)用于于獲取兩兩個(gè)日期期所間隔隔的月數(shù)數(shù)。該函函數(shù)的返返回值是是一個(gè)實(shí)實(shí)數(shù)。selectmonths_between(sysdate,to_date(2009-02-08, yyyy-mm-dd) resultfrom dual;當(dāng)?shù)谝粋€(gè)個(gè)日期早早于第二二個(gè)日期期，那么么返回值值將是負(fù)負(fù)值。selectmonths_between(to_date(2009-02-08, yyyy-mm-dd), to_date(2009-03-08,yyyy-mm-dd)resultfromdual;14.3.5current_date(

24、)函數(shù)返回當(dāng)前前會(huì)話時(shí)時(shí)區(qū)的當(dāng)當(dāng)前日期期current_date()函數(shù)用于于返回當(dāng)當(dāng)前會(huì)話話時(shí)區(qū)的的當(dāng)前日日期。selectsessiontimezone,to_char(current_date,yyyy-mm-ddhh:mi:ss)resultfromdual;注意與說(shuō)說(shuō)明：current_date等無(wú)參數(shù)數(shù)函數(shù)作作為Oracle的關(guān)鍵字字存在。在使用用時(shí)，不不能為其其添加小小括號(hào)。即selectcurrent_date()fromdual是錯(cuò)誤的的SQL語(yǔ)句。14.3.6current_timestamp()函數(shù)返回當(dāng)前前會(huì)話時(shí)時(shí)區(qū)的當(dāng)當(dāng)前時(shí)間間戳current_timestamp()

25、函數(shù)用于于返回當(dāng)當(dāng)前會(huì)話話時(shí)的區(qū)區(qū)時(shí)間戳戳?？梢砸越Y(jié)合sessiontimezone來(lái)查看其其用法。selectsessiontimezone,current_timestampfrom dual;14.3.7extract()函數(shù)返回日期期的某個(gè)個(gè)域日期由若若干域組組成，例例如年、月、日日、小時(shí)時(shí)等等。extract()函數(shù)可以以返回這這些域的的具體值值。為了了使用該該函數(shù)，除了要要指定原原日期外外，還應(yīng)應(yīng)該指定定要返回回的域名名。selectextract(year fromsysdate)result fromdual;需要注意意的是，year、month、day域只能從從日期（如sys

26、date）中獲得得，而hour、minute、second只能從時(shí)時(shí)間型（如systimestamp）中獲得得。14.4Oracle中的聚合合函數(shù)所謂聚合合函數(shù)是是指針對(duì)對(duì)多條記記錄的函函數(shù)。Oracle最常用的的聚合函函數(shù)包括括，max()、min()、avg()、sum()和count()函數(shù)。本本節(jié)將講講述這些些函數(shù)的的用法。14.4.1max()函數(shù)求最大值值max()函數(shù)用于于獲得記記錄集在在某列的的最大值值。例如如，為了了返回員員工最高高工資，可以利利用max()函數(shù)。selectmax(salary)max_salaryfrom t_salary;需要注意意的是，聚合函函數(shù)往往往

27、是返回回記錄集集的統(tǒng)計(jì)計(jì)值，因因此，不不能與其其中的單單條記錄錄同時(shí)出出現(xiàn)。例例如，不不能將max(salary)與具體列列一起查查詢。selectemployee_id, max(salary) max_salaryfromt_salary;selectdistincte.employee_name,s.salaryfrom t_employees e, t_salary swheree.employee_id =s.employee_idands.salary=(select max(salary) fromt_salary)14.4.2min()函數(shù)求最小值值min()函數(shù)可以以用來(lái)獲獲

28、得記錄錄集在某某列上的的最小值值，其功功能與max()函數(shù)相反反。selectdistincte.employee_name,s.salaryfrom t_employees e, t_salary swheree.employee_id =s.employee_idands.salary=(select min(salary) fromt_salary)14.4.3avg()函數(shù)求平均值值avg()函數(shù)用于于獲得記記錄集在在某列上上的平均均值。selecte.employee_name,avg(salary)from t_employees e, t_salary swheree.emplo

29、yee_id =s.employee_idgroupbye.employee_id,e.employee_name14.4.4sum()函數(shù)求和sum()函數(shù)用于于獲得結(jié)結(jié)果集上上某列值值的和。selecte.employee_name,sum(salary)from t_employees e, t_salary swheree.employee_id =s.employee_idgroupbye.employee_id,e.employee_name14.4.5count()函數(shù)獲得記錄錄數(shù)count()函數(shù)的作作用對(duì)象象同樣為為記錄集集。與其其他聚合合函數(shù)不不同的是是，count()函數(shù)

30、可以以有三種種方式來(lái)來(lái)進(jìn)行計(jì)計(jì)數(shù)：count(*)計(jì)算行數(shù)數(shù)、count(column)計(jì)算某列列和count(1)累加1。insertintot_employeesvalues(16,null,null,null);selectcount(*)fromt_employees;selectcount(employee_id) fromt_employees;selectcount(employee_name)fromt_employees;selectcount(1)fromt_employees;一般來(lái)說(shuō)說(shuō)，利用用count(1)進(jìn)行計(jì)數(shù)數(shù)的速度度最快，但是特特別注意意的是，預(yù)期的的結(jié)果是是

31、針對(duì)整整行數(shù)據(jù)據(jù)，還是是某列的的數(shù)據(jù)。14.5Oracle中的其他他函數(shù)除了數(shù)值值函數(shù)、字符串串函數(shù)、日期函函數(shù)和聚聚合函數(shù)數(shù)外，Oracle還提供了了其他功功能性更更強(qiáng)的函函數(shù)。本本節(jié)將介介紹decode()、nvl()和cast()函數(shù)。14.5.1decode()函數(shù)多值判斷斷decode()函數(shù)用于于多值判判斷。其其執(zhí)行過(guò)過(guò)程類似似于解碼碼操作。該函數(shù)數(shù)最常見(jiàn)見(jiàn)的應(yīng)用用為，實(shí)實(shí)現(xiàn)類似似ifelse的功能。例如，可以利利用decode()函數(shù)為員員工工資資添加標(biāo)標(biāo)識(shí)，工工資大于于6000者為高收收入，其其余的為為一般收收入。selecte.employee_id,e.employee_n

32、ame,decode(sign(avg(s.salary) -6000),1,高收入,一般收入入)incommingfrom t_employees e, t_salary swheree.employee_id =s.employee_idgroupbye.employee_id,e.employee_name14.5.2nvl()函數(shù)為空值重重新賦值值nvl()函數(shù)用于于處理某某列的值值。該函函數(shù)有兩兩個(gè)參數(shù)數(shù)，第一一個(gè)參數(shù)數(shù)為要處處理的列列。如果果其值為為空，則則返回第第二個(gè)參參數(shù)的值值，否則則，將返返回列值值。selectemployee_id, nvl(employee_name,未

33、知)employee_name fromt_employees;nvl()函數(shù)更常常見(jiàn)的用用途為判判斷數(shù)值值是否為為空。因因?yàn)閟um()等函數(shù)往往往會(huì)返返回null，例如，表示匯匯率的列列一旦為為null，那么最最終的貨貨幣結(jié)算算額度也也為null，所以，必須對(duì)對(duì)匯率列列進(jìn)行nvl()的處理。在統(tǒng)計(jì)計(jì)員工工工資時(shí)，null同樣是不不受歡迎迎的結(jié)果果，那么么可以利利用nvl()函數(shù)進(jìn)行行處理。selecte.employee_id,nvl(e.employee_name,未知)employee_name,nvl(sum(s.salary),0)salaryfrom t_employees e,

34、t_salary swheree.employee_id =s.employee_id(+)groupbye.employee_id,e.employee_name14.5.3cast()函數(shù)強(qiáng)制轉(zhuǎn)換換數(shù)據(jù)類類型cast()函數(shù)用于于強(qiáng)制轉(zhuǎn)轉(zhuǎn)換數(shù)據(jù)據(jù)類型。Oracle會(huì)根據(jù)操操作符來(lái)來(lái)自動(dòng)進(jìn)進(jìn)行數(shù)據(jù)據(jù)類型的的轉(zhuǎn)換，例如：select123+200result fromdual;Oracle會(huì)根據(jù)運(yùn)運(yùn)算符“+”將123轉(zhuǎn)換為數(shù)數(shù)值型123。select123|200 resultfrom dual;Oracle會(huì)根據(jù)運(yùn)運(yùn)算符“|”將數(shù)字200轉(zhuǎn)換為字字符串200。cast()函數(shù)最常常用的場(chǎng)場(chǎng)景

35、是轉(zhuǎn)轉(zhuǎn)換列的的數(shù)據(jù)類類型，以以創(chuàng)建新新表createtabletmp_salaryasselectcast(salary_idasvarchar2(20)salary_id,cast(employee_id as varchar2(20)employee_id,cast(month as varchar2(20)month,cast(salaryasvarchar2(20) salaryfrom t_salarydesc tmp_salary;14.6Oracle中的運(yùn)算算表達(dá)式式Oracle中的常用用運(yùn)算包包括：數(shù)數(shù)學(xué)運(yùn)算算、邏輯輯運(yùn)算和和按位運(yùn)運(yùn)算。本本節(jié)將通通過(guò)范例例著重講講述這三三種運(yùn)

36、算算的常用用運(yùn)算符符和運(yùn)算算規(guī)則。14.6.1數(shù)學(xué)運(yùn)算算數(shù)學(xué)運(yùn)算算是最常常用的運(yùn)運(yùn)算方式式，Oracle中的數(shù)學(xué)學(xué)運(yùn)算符符包括：+、-、*、/，分別代代表了加加、減、乘除運(yùn)運(yùn)算。在在使用數(shù)數(shù)學(xué)運(yùn)算算時(shí)，Oracle會(huì)自動(dòng)將將其他數(shù)數(shù)據(jù)類型型轉(zhuǎn)換為為數(shù)值型型，然后后再參與與運(yùn)算。select5+3 resultfrom dual;select5-3 resultfrom dual;select5*2resultfromdual;select5/2 resultfrom dual;需要注意意的是，任何一一種運(yùn)算算符與null的運(yùn)算結(jié)結(jié)果均為為null。select5+null resultfrom

37、 dual;select5-null resultfrom dual;select5*null resultfrom dual;select5/null resultfrom dual;14.6.2邏輯運(yùn)算算Oracle中的邏輯輯運(yùn)算包包括：大于運(yùn)運(yùn)算，可可用于數(shù)數(shù)值型、日期型型和字符符串類型型；=：大于等等于運(yùn)算算，可用用于數(shù)值值型、日日期型和和字符串串類型；：小于運(yùn)運(yùn)算，可可用于數(shù)數(shù)值型、日期型型和字符符串類型型；=：大于等等于運(yùn)算算，可用用于數(shù)值值型、日日期型和和字符串串類型；=：等于，可用于于數(shù)值型型、日期期型和字字符串類類型；：不等于于，可用用于數(shù)值值型、日日期型和和字符串串類型；!

38、=：與用法相同同；NOT：取反操操作；AND：布爾值值的與操操作；OR：布爾值值的或操操作。14.6.2邏輯運(yùn)算算需要注意意的是，Oracle中的邏輯輯運(yùn)算符符只能作作為條件件判斷，并不返返回值。為了查查詢工資資在5000-7000之間的記記錄，可可以利用用邏輯運(yùn)運(yùn)算符來(lái)來(lái)組合查查詢條件件。select*from t_salary where salary=5000andsalary=7000;對(duì)于null值，需要要特別注注意的是是，無(wú)論論使用哪哪種運(yùn)算算符，結(jié)結(jié)果都會(huì)會(huì)返回null。當(dāng)比較較的結(jié)果果為null，并作為為條件出出現(xiàn)時(shí)，Oracle都會(huì)將其其解釋為為false。select1re

39、sultfromdual where 1=null;select1resultfromdual where 1null;select1resultfromdual where null=null;select1resultfromdual where nullnull;14.6.3位運(yùn)算從Oracle8i開始，系系統(tǒng)已經(jīng)經(jīng)提供了了位運(yùn)算算符。最最常用的的莫過(guò)于于bitand運(yùn)算符。selectbitand(192,100)resultfromdual;14.7Oracle中的特殊殊判式除了邏輯輯運(yùn)算之之外，Oracle提供了一一些特殊殊判式。這些判判式可以以用來(lái)生生成更加加復(fù)雜和和靈活的的查詢

40、條條件。本本節(jié)將著著重介紹紹以下幾幾種判式式。Between：取值范范圍。In：集合成成員測(cè)試試。Like：模式匹匹配。isnull：空值判判斷。all，some，any：數(shù)量判判斷。exists：存在性性判斷。14.7.1between范圍測(cè)試試between判式，用用于判斷斷某個(gè)值值是否在在另外兩兩個(gè)值之之間。這這些值可可以為數(shù)數(shù)值型、字符串串和日期期型。使使用betwwen判式來(lái)獲獲得ID號(hào)在1-5之間的員員工信息息。select*from t_employees where employee_id between1and5;betwwen判式同樣樣可以應(yīng)應(yīng)用于字字符串和和日期型型。字符

41、符串是按按照字母母表的順順序進(jìn)行行比較，而日期期型是按按照日期期的先后后順序進(jìn)進(jìn)行比較較。select*from t_employees where bbetween bandc;select*from t_employees where bbetween bcand c;注意與說(shuō)說(shuō)明：between判式與=、=的組合是是等價(jià)關(guān)關(guān)系。但但是，效效率上要要比后者者差。14.7.2in集合成員員測(cè)試in用于判斷斷某個(gè)值值是否一一個(gè)集合合的成員員。select*from t_employees where statusin(NEW,ACT);值得注意意的是，in判式中的的集合的的成員的的數(shù)據(jù)類類型可

42、以以不一致致，例如如，select*from t_employees where statusin(NEW,ACT, sysdate, 1)中的數(shù)據(jù)據(jù)類型包包含了字字符串、日期型型和數(shù)值值型。14.7.3like模式匹配配like判式的最最大特點(diǎn)點(diǎn)在于，可以使使用通配配符。其其通常的的應(yīng)用場(chǎng)場(chǎng)景為處處理模糊糊查詢。select*from t_employees where employee_namelike鐘%;如果要求求字符串串中含有有原義字字符“%”，例如，含有百百分比的的字符串串。那么么，like判式應(yīng)寫寫作：like 鐘%escape 。Oracle會(huì)首先解解釋escape關(guān)鍵字，并將

43、其其后的字字符“”解釋為轉(zhuǎn)轉(zhuǎn)義字符符。那么么在“鐘鐘%”中的“%”不再表示示通配符符，而是是表示原原義字符符“%”?！癬”（下劃線線）是可可用于like判式的另另一個(gè)通通配符，該通配配符表示示一個(gè)任任意的字字符。14.7.4is null空值判斷斷在邏輯判判斷中，對(duì)于列列值為空空的判斷斷，不能能使用=或者。oracle對(duì)與空值值的判斷斷提供了了專門的的判式is null。例如，為了獲獲取表t_employees中員工信信息不全全的記錄錄，可以以利用如如下所示示的查詢?cè)冋Z(yǔ)句。select*from t_employeeswhereemployee_idisnulloremployee_name

44、is nullorwork_years is nullorstatusisnull;14.7.5exists存在性判判斷in判式用于于判斷表表的列值值是否存存在于列列表（集集合）中中。而exists判式則可可用于判判斷查詢?cè)兘Y(jié)果集集合是否否為空。例如，為了查查詢出表表t_employees所存儲(chǔ)的的員工信信息中，哪些員員工存在在于工資資表中，即可利利用exists判式。select*from t_employees ewhereexists(select *fromt_salarywhereemployee_id= e.employee_id);14.7.6all，some，any數(shù)量判斷斷a

45、ll，some和any判式的作作用對(duì)象象為記錄錄集合。all表示，記記錄集中中的所有有記錄，some表示其中中的一些些記錄，any判式則表表示其中中的任意意記錄。例如，在員工工工資表表t_salary中，為了了查找高高于id為4和5的工資信信息，即即可使用用all判式。select*from t_salary where employee_id =4oremployee_id= 5;select*from t_salary where salary all(selectdistinctsalary fromt_salarywhereemployee_id=4 or employee_id =5

46、);select*from t_salary where salary some(select distinct salaryfrom t_salary where employee_id =4oremployee_id= 5);此時(shí)的some判式實(shí)際際相當(dāng)于于邏輯運(yùn)運(yùn)算中的的or運(yùn)算，即即salary6000orsalary7000。此時(shí)，使用any判式，將將返回同同樣的結(jié)結(jié)果。14.8Oracle高級(jí)函數(shù)數(shù)分析函數(shù)數(shù)與窗口口函數(shù)Oracle中的分析析函數(shù)具具有非常常強(qiáng)大的的功能。分析函函數(shù)往往往與另一一類函數(shù)數(shù)窗口函數(shù)數(shù)同時(shí)使使用。窗窗口函數(shù)數(shù)總是為為查詢過(guò)過(guò)程中的的當(dāng)前記記錄提供供一個(gè)相

47、相關(guān)記錄錄集，而而且隨著著當(dāng)前記記錄的推推移，相相應(yīng)的記記錄集也也會(huì)隨之之改變，這非常常類似于于“滑動(dòng)動(dòng)窗”的的概念。分析函函數(shù)的操操作對(duì)象象即為“滑動(dòng)窗窗”所指指定的記記錄集合合。本節(jié)節(jié)將通過(guò)過(guò)實(shí)例來(lái)來(lái)講述分分析函數(shù)數(shù)和窗口口函數(shù)的的使用。14.8.1排名分析函數(shù)數(shù)中的排排名函數(shù)數(shù)可以針針對(duì)窗口口中的記記錄生成成排序序序號(hào)。常常用的排排名函數(shù)數(shù)有rank()、dense_rank()和row_number()。rank()函數(shù)用于于返回當(dāng)當(dāng)前記錄錄在窗口口函數(shù)所所指定的的記錄集集中的排排名。rank()函數(shù)在排排名過(guò)程程中，具具有跳躍躍的特點(diǎn)點(diǎn)。select*fromstudents;sel

48、ectstudent_name,rank()over(orderbystudent_age)positionfromstudents;selectstudent_name,dense_rank()over(orderbystudent_age)positionfromstudents;selectstudent_name,row_number()over(orderbystudent_age)positionfromstudents;14.8.2分區(qū)窗口口對(duì)于窗口口函數(shù)，利用partitionby關(guān)鍵字可可以指定定分區(qū)窗窗口。現(xiàn)欲統(tǒng)計(jì)計(jì)各員工工的工資資在各自自部門的的高低情情況，則則可以利利用

49、partitionby進(jìn)行分區(qū)區(qū)，然后后利用分分析函數(shù)數(shù)對(duì)分區(qū)區(qū)內(nèi)的記記錄進(jìn)行行統(tǒng)計(jì)selectt.*,dense_rank()over(partitionbydepartmentorderbysalary) position fromsalary torderbyt.employee_id另外一種種常見(jiàn)需需求為，在獲得得員工工工資的同同時(shí)，也也需要部部門所有有員工的的工資總總額selectt.*,sum(salary)over(partitionbydepartment)total_salary,round(avg(salary)over(partitionbydepartment)aver

50、age_salaryfromsalary torderbyemployee_id注意，avg(salary)over(partitionbydepartment)是不可分分割的一一個(gè)整體體。對(duì)于于數(shù)據(jù)表表salary中每條記記錄都會(huì)會(huì)返回單單個(gè)值，因此，當(dāng)使用用round()函數(shù)，函函數(shù)的作作用對(duì)象象應(yīng)為avg(salary)over(partitionbydepartment)這個(gè)整體體，而不不能使用用諸如round(avg(salary)over(partitionbydepartment)等形式。另外，利用partitionby進(jìn)行分區(qū)區(qū)之后，當(dāng)前記記錄總是是處于某某個(gè)分區(qū)區(qū)中，此此時(shí)的

51、窗窗口即為為該分區(qū)區(qū)。14.8.3窗口子句句對(duì)于每條條記錄，一旦使使用了窗窗口函數(shù)數(shù)，都會(huì)會(huì)為其產(chǎn)產(chǎn)生一個(gè)個(gè)可操作作的記錄錄集合。而對(duì)于于該記錄錄集，可可以使用用窗口子子句，來(lái)來(lái)進(jìn)一步步限制窗窗口范圍圍。常用用的窗口口子句包包括兩類類：利用rows子句的行行方式進(jìn)進(jìn)行限制制；利用range子句的值值方式進(jìn)進(jìn)行限制制。rows子句selectemployee_id, employee_name,sum(salary)over(order by employee_id rowsbetween1 precedingand1following) three_total fromsalaryrows子句

52、因?yàn)闉楹臀恢弥孟嚓P(guān)，因此，在窗口口函數(shù)中中必須含含有排序序子句orderby。如果未未使用orderby子句，而而直接使使用rows子句，Oracle將拋出錯(cuò)錯(cuò)誤提示示，14.8.3窗口子句句注意，rows betwwen1precedingand 1following不一定返返回3條記錄。例如，對(duì)于employee_id為1的記錄，排序之之后，該該記錄為為第一條條記錄，不存在在前一條條記錄，因此只只返回兩兩條記錄錄，而求求和操作作返回的的實(shí)際為為employee_id為1和2的員工的的工資總總和10500。2.range子句range子句按照照列值進(jìn)進(jìn)行窗口口的進(jìn)一一步限制制。selectemployee_id, employee_name,count(1)over(parti