第14章Oracle中的函數(shù)與表達(dá)式課件_第1頁(yè)
第14章Oracle中的函數(shù)與表達(dá)式課件_第2頁(yè)
第14章Oracle中的函數(shù)與表達(dá)式課件_第3頁(yè)
第14章Oracle中的函數(shù)與表達(dá)式課件_第4頁(yè)
第14章Oracle中的函數(shù)與表達(dá)式課件_第5頁(yè)
已閱讀5頁(yè),還剩133頁(yè)未讀, 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

第14章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ù)與表達(dá)式Oracle中提供了第14章Oracle中的函數(shù)與表達(dá)式Oracle中的聚合函數(shù);Oracle中的運(yùn)算表達(dá)式;Oracle中的特殊判式;Oracle中的高級(jí)函數(shù)——分析函數(shù)與窗口函數(shù)。第14章Oracle中的函數(shù)與表達(dá)式Oracle中的聚合14.1Oracle中的字符串函數(shù)Oracle提供了豐富的字符串函數(shù),本小節(jié)將通過(guò)實(shí)例講述Oracle中各字符串函數(shù)的使用。14.1Oracle中的字符串函數(shù)Oracle提供了豐富14.1.1lpad()函數(shù)lpad()函數(shù)用于左補(bǔ)全字符串。在某些情況下,預(yù)期的字符串為固定長(zhǎng)度,而且格式統(tǒng)一,此時(shí)可以考慮使用lpad()函數(shù)。例如,深市股票代碼都以0開(kāi)頭,并且都為6位,可以利用lpad格式化股票代碼,以保證股票代碼的格式。selectlpad('21',6,'0')stock_codefromdual;需要注意的是,當(dāng)原字符串的長(zhǎng)度大于預(yù)期長(zhǎng)度時(shí),實(shí)際進(jìn)行的是截取字符串操作。selectlpad('1234567',6,'0')stock_codefromdual;14.1.1lpad()函數(shù)lpad()函數(shù)用于左補(bǔ)全字14.1.2rpad()函數(shù)與lpad()函數(shù)相反,rpad()函數(shù)從右端補(bǔ)齊字符串。selectrpad('abc',10,'*')fromdual;注意與說(shuō)明:lpad()和rpad()都用于填充字符串,lpad()從左端進(jìn)行填充,而rpad()從右端進(jìn)行填充,但是,二者在最終截取字符串時(shí),都是從左端開(kāi)始截取。selectrpad('abcdefg',6,'*')fromdual;14.1.2rpad()函數(shù)與lpad()函數(shù)相反,rp14.1.3lower()函數(shù)——返回小寫(xiě)字符串lower()函數(shù)用于返回字符串的小寫(xiě)形式。lower()函數(shù)在查詢語(yǔ)句中經(jīng)常扮演重要角色。例如,對(duì)于用戶名和密碼的校驗(yàn)來(lái)說(shuō),用戶名一般并不區(qū)分大小寫(xiě),用戶無(wú)論輸入了大寫(xiě)還是小寫(xiě)形式,都被認(rèn)為是合法用戶。因此,在數(shù)據(jù)庫(kù)查詢時(shí),應(yīng)該將數(shù)據(jù)庫(kù)中用戶名與用戶輸入的用戶名進(jìn)行統(tǒng)一。selectuser_id,user_namefromt_userswherelower(user_name)=lower('Alex');14.1.3lower()函數(shù)——返回小寫(xiě)字符串lowe14.1.4upper()函數(shù)——返回大寫(xiě)字符串upper()函數(shù)用于返回字符串的大寫(xiě)形式。與lower()函數(shù)類似,upper()函數(shù)也可以用在查詢語(yǔ)句中,以統(tǒng)一數(shù)據(jù)庫(kù)和查詢條件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper('ALEX');注意與說(shuō)明:upper()函數(shù)和lower()函數(shù)只針對(duì)英文字符其作用,因?yàn)橹挥杏⑽淖址庞写笮?xiě)之分。14.1.4upper()函數(shù)——返回大寫(xiě)字符串uppe14.1.5initcap()函數(shù)——單詞首字母大寫(xiě)initcap()函數(shù)將單詞的首字母大寫(xiě)。selectinitcap('big')fromdual;需要注意的是,initcap()函數(shù)不能自動(dòng)識(shí)別單詞

selectinitcap('bigbigtiger')fromdual;initcap()函數(shù)會(huì)將參數(shù)中的非單詞字符作為單詞分隔符selectinitcap('big_big_tiger')fromdual;selectinitcap('big/big/tiger')fromdual;selectinitcap('bigbigtiger')fromdual;14.1.5initcap()函數(shù)——單詞首字母大寫(xiě)i14.1.6length()函數(shù)——返回字符串長(zhǎng)度length()函數(shù)用于返回字符串的長(zhǎng)度。selectlength('abcd')fromdual;空字符串的長(zhǎng)度不是0,而是null。因?yàn)榭兆址灰曌鱪ull,所以,length(null)返回的仍然是null。selectlength('')fromdual;對(duì)其其他數(shù)據(jù)類型,照樣可以通過(guò)length()函數(shù)來(lái)獲得其長(zhǎng)度。length()函數(shù)會(huì)首先將參數(shù)轉(zhuǎn)換為字符串,然后計(jì)算其長(zhǎng)度。selectlength(12.51)fromdual;14.1.6length()函數(shù)——返回字符串長(zhǎng)度len14.1.7substr()函數(shù)——截取字符串substr()函數(shù)用于截取字符串。該函數(shù)可以指定截取的起始位置,截取長(zhǎng)度,可以實(shí)現(xiàn)靈活的截取操作,因此,成為字符串操作中最常用的函數(shù)之一。例如,對(duì)于字符串“1234567890”,現(xiàn)欲截取自第5位開(kāi)始的4個(gè)字符。selectsubstr('1234567890',5,4)fromdual;需要注意的是,Oracle中字符位置從1開(kāi)始,而不是像某些編程語(yǔ)言(如Java)那樣從0開(kāi)始。

如果不指定長(zhǎng)度,那么substr()函數(shù)將獲取起始位置參數(shù)至字符串結(jié)尾處的所有字符。

selectsubstr('1234567890',5)fromdual;14.1.7substr()函數(shù)——截取字符串subst14.1.8instr()函數(shù)——獲得字符串出現(xiàn)的位置instr()函數(shù)用于獲得子字符串在父字符串中出現(xiàn)的位置。selectinstr('bigbigtiger','big')fromdual;可以指定額外的參數(shù),以命令該函數(shù)從指定位置開(kāi)始搜索。selectinstr('bigbigtiger','big',2)fromdual;還可以指定出現(xiàn)次數(shù)參數(shù),以指定是第幾次搜索到子字符串。selectinstr('bigbigtiger','big',2,2)fromdual;14.1.8instr()函數(shù)——獲得字符串出現(xiàn)的位置i14.1.9ltrim()函數(shù)——?jiǎng)h除字符串首部空格ltrim()中的l代表left。該函數(shù)用于刪除字符串左端的空白符。selectltrim('abc')fromdual;需要注意的是,空白符不僅僅包括了空格符,還包括TAB鍵、回車符和換行符。14.1.9ltrim()函數(shù)——?jiǎng)h除字符串首部空格lt14.1.10rtrim()函數(shù)——?jiǎng)h除字符串尾部空格rtrim()中的r代表right。該函數(shù)用于刪除字符串右端空白符。刪除字符串首尾空白符可以結(jié)合使用ltrm()和rtrim()函數(shù)。selectrtrim(ltrim('abc'))fromdual;14.1.10rtrim()函數(shù)——?jiǎng)h除字符串尾部空格r14.1.11trim()函數(shù)——?jiǎng)h除字符串首尾空格trim()函數(shù)可用于刪除首尾空格,相當(dāng)于ltrim()和rtrim()的組合。selecttrim('abc')fromdual;14.1.11trim()函數(shù)——?jiǎng)h除字符串首尾空格tr14.1.12to_char()函數(shù)——將其他類型轉(zhuǎn)換為字符類型to_char()函數(shù)用于將其他數(shù)據(jù)類型的數(shù)據(jù)轉(zhuǎn)換為字符型,這些類型主要包括數(shù)值型、日期型。1.將數(shù)值型轉(zhuǎn)換為字符串selectto_char(120,'99999')resultfromdual;selectto_char(0.96,'9.99')resultfromdual;selectto_char(0.96,'0.00')resultfromdual;selectto_char(5897.098,'999,999,999.000')resultfromdual;selectto_char(5987.098,'$999,999,999.000')resultfromdual;2.將日期型轉(zhuǎn)換為字符串selectto_char(sysdate,'yyyy-mm-dd')resultfromdual;selectto_char(sysdate,'YYYY-MON-DD')fromdual;

14.1.12to_char()函數(shù)——將其他類型轉(zhuǎn)換為14.1.13chr()函數(shù)——將ascii碼轉(zhuǎn)換為字符串chr()函數(shù)用于將ascii碼轉(zhuǎn)換為字符串。通過(guò)chr()函數(shù),可以對(duì)不宜直接輸入的字符進(jìn)行操作。例如,將回車換行符插入到數(shù)據(jù)中。insertintotest_datavalues(6,'周林'||chr(13)||chr(10)|'梁軍',20);select*fromtest_datawhereid=6;14.1.13chr()函數(shù)——將ascii碼轉(zhuǎn)換為字符14.1.14translate()函數(shù)——替換字符translate()函數(shù)用于替換字符串。替換的規(guī)則類似于翻譯的過(guò)程。selecttranslate('56338','1234567890','avlihemoqr')resultfromdual;需要注意的是,當(dāng)字符不能被成功“翻譯”,那么,Oracle將使用空字符替換它。利用此特性,可以使用translate()函數(shù)來(lái)刪除一個(gè)含有數(shù)字和英文字母的字符串中的所有字母:selecttranslate('21343yuioioizf899dasiwpe58595oda0j098','#abcdefghijklmnopqrstuvwxyz','')reulstfromdual;14.1.14translate()函數(shù)——替換字符tr14.2Oracle中的數(shù)學(xué)函數(shù)Oracle提供的數(shù)學(xué)函數(shù)可以處理日常使用到的大多數(shù)數(shù)學(xué)運(yùn)算。本小節(jié)將講述Oracle中常用的幾種數(shù)學(xué)函數(shù)。14.2Oracle中的數(shù)學(xué)函數(shù)Oracle提供的數(shù)學(xué)函14.2.1abs()函數(shù)——返回?cái)?shù)字的絕對(duì)值abs()函數(shù)的參數(shù)只能是數(shù)值型,該參數(shù)用于返回參數(shù)的絕對(duì)值。selectabs(-2.1)fromdual;14.2.1abs()函數(shù)——返回?cái)?shù)字的絕對(duì)值abs(14.2.2round()函數(shù)——返回?cái)?shù)字的“四舍五入”值round()函數(shù)用于返回某個(gè)數(shù)字的四舍五入值。為了使用該函數(shù),除了提供原始值之外,還應(yīng)提供精確到的位數(shù)。精確位數(shù)可以為正整數(shù)、0和負(fù)整數(shù)。selectround(2745.173,2)resultfromdual;如果不使用第二個(gè)參數(shù),那么,相當(dāng)于使用了參數(shù)0,即精確到整數(shù)。selectround(2745.173)resultfromdual;如果第二個(gè)參數(shù)為負(fù)數(shù),那么,相當(dāng)于將數(shù)值精確到小數(shù)點(diǎn)之前的位數(shù)。

selectround(2745,-1)resultfromdual;14.2.2round()函數(shù)——返回?cái)?shù)字的“四舍五入14.2.3ceil()函數(shù)——向上取整ceil()函數(shù)只能有一個(gè)參數(shù)。該函數(shù)將參數(shù)向上取整,以獲得大于等于該參數(shù)的最小整數(shù)。selectceil(21.897)resultfromdual;需要注意的是該函數(shù)針對(duì)負(fù)數(shù)的運(yùn)算:

selectceil(-21.897)resultfromdual;因?yàn)閏eil()函數(shù)返回的是大于等于參數(shù)的最小整數(shù),所以,該函數(shù)返回的并非-22,而是-21。14.2.3ceil()函數(shù)——向上取整ceil()函數(shù)14.2.4floor()函數(shù)——向下取整與ceil函數(shù)相反,floor()函數(shù)用于返回小于等于某個(gè)數(shù)值的最大整數(shù)。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;14.2.4floor()函數(shù)——向下取整與ceil函數(shù)14.2.5mod()函數(shù)——取模操作mod()函數(shù)有兩個(gè)參數(shù),第一個(gè)參數(shù)為被除數(shù),第二個(gè)參數(shù)為除數(shù)。mod()函數(shù)的實(shí)際功能為獲得兩數(shù)相除之后的余數(shù)。selectmod(5,2)resultfromdual;14.2.5mod()函數(shù)——取模操作mod()函數(shù)有14.2.6sign()函數(shù)——返回?cái)?shù)字的正負(fù)性sign()函數(shù)只有一個(gè)參數(shù)。該函數(shù)將返回參數(shù)的正負(fù)性。若返回值為1,表示該參數(shù)大于0;若返回值為-1,表示該參數(shù)小于0;若返回值為0,表示該參數(shù)等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函數(shù)為判斷兩個(gè)數(shù)值的大小關(guān)系提供了方便。因?yàn)樵趏racle中,利用類似ifelse的結(jié)構(gòu)來(lái)判斷兩個(gè)數(shù)值之間的大小關(guān)系,并不像編程語(yǔ)言中那樣方便,而且極易造成代碼的復(fù)雜化。14.2.6sign()函數(shù)——返回?cái)?shù)字的正負(fù)性sign14.2.7sqrt()函數(shù)——返回?cái)?shù)字的平方根sqrt()函數(shù)也只有一個(gè)參數(shù)。該函數(shù)用于返回參數(shù)的平方根??梢岳胷ound()函數(shù)和sqrt()函數(shù)返回某個(gè)數(shù)值的近似平方根。selectround(sqrt(2),3)resultfromdual;14.2.7sqrt()函數(shù)——返回?cái)?shù)字的平方根sqrt14.2.8power()函數(shù)——乘方運(yùn)算power()函數(shù)有兩個(gè)參數(shù)。該函數(shù)用于實(shí)現(xiàn)數(shù)值的乘方運(yùn)算。selectpower(6,2)resultfromdual;14.2.8power()函數(shù)——乘方運(yùn)算power()14.2.9trunc()函數(shù)——截取數(shù)字trunc()函數(shù)用于截取部分?jǐn)?shù)字。其工作機(jī)制非常類似于round()函數(shù)。與round()函數(shù)不同的是,該函數(shù)不對(duì)數(shù)值做四舍五入處理,而是直接截取。selecttrunc(2745.173,2)resultfromdual;保留位數(shù)的值可以為0,當(dāng)該參數(shù)的值為0時(shí),將保留到整數(shù)。selecttrunc(2745.173)resultfromdual;當(dāng)保留位數(shù)小于0時(shí),表示保留到小數(shù)點(diǎn)之前的位數(shù)。

selecttrunc(2745.173,-1)resultfromdual;14.2.9trunc()函數(shù)——截取數(shù)字trunc()14.2.10vsize()函數(shù)——返回?cái)?shù)據(jù)的存儲(chǔ)空間vsize()函數(shù)根據(jù)數(shù)據(jù)庫(kù)的存儲(chǔ)格式,來(lái)返回其所占用的存儲(chǔ)空間的字節(jié)數(shù)。selectvsize('abc123')fromdual;注意與說(shuō)明:vsize()函數(shù)在返回的是Oracle實(shí)際存儲(chǔ)數(shù)據(jù)的字節(jié)數(shù),在實(shí)際開(kāi)發(fā)中使用的幾率也較小。讀者可以不必了解Oracle本身的存儲(chǔ)機(jī)制。14.2.10vsize()函數(shù)——返回?cái)?shù)據(jù)的存儲(chǔ)空間v14.2.11to_number()函數(shù)——將字符串轉(zhuǎn)換為數(shù)值類型to_number()函數(shù)可以將字符串轉(zhuǎn)換為數(shù)值型。selectto_number('257.90')resultfromdual;需要注意的是,被轉(zhuǎn)換的字符串必須符合數(shù)值類型格式。如果被轉(zhuǎn)換的字符串不符合數(shù)值型格式,Oracle將拋出錯(cuò)誤提示。selectto_number('a')resultfromdual;14.2.11to_number()函數(shù)——將字符串轉(zhuǎn)換14.3Oracle中的日期函數(shù)Oracle提供了豐富的日期函數(shù)。利用日期函數(shù)可以靈活的對(duì)日期進(jìn)行運(yùn)算。14.3Oracle中的日期函數(shù)Oracle提供了豐富的14.3.1to_date()函數(shù)——將字符串轉(zhuǎn)換為日期型to_date()函數(shù)用于將字符串轉(zhuǎn)換為日期。被轉(zhuǎn)換的字符串必須符合特定的日期格式。selectto_date('12/02/09','mm/dd/yy')resultfromdual;14.3.1to_date()函數(shù)——將字符串轉(zhuǎn)換為日期14.3.2add_months()函數(shù)——為日期加上特定月份add_months()函數(shù)將為日期添加特定月份,并獲得新的日期。selectto_char(add_months(sysdate,2),'yyyy-mm-dd')resultfromdual;14.3.2add_months()函數(shù)——為日期加上特14.3.3last_day()函數(shù)——返回特定日期所在月的最后一天last_day()函數(shù)將接受一個(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.3last_day()函數(shù)——返回特定日期所在14.3.4months_between()函數(shù)——返回兩個(gè)日期所差的月數(shù)months_between()函數(shù)用于獲取兩個(gè)日期所間隔的月數(shù)。該函數(shù)的返回值是一個(gè)實(shí)數(shù)。selectmonths_between(sysdate,to_date('2009-02-08','yyyy-mm-dd'))resultfromdual;當(dāng)?shù)谝粋€(gè)日期早于第二個(gè)日期,那么返回值將是負(fù)值。selectmonths_between(to_date('2009-02-08','yyyy-mm-dd'),to_date('2009-03-08','yyyy-mm-dd'))resultfromdual;14.3.4months_between()函數(shù)——返14.3.5current_date()函數(shù)——返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前日期current_date()函數(shù)用于返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前日期。selectsessiontimezone,to_char(current_date,'yyyy-mm-ddhh:mi:ss')resultfromdual;注意與說(shuō)明:current_date等無(wú)參數(shù)函數(shù)作為Oracle的關(guān)鍵字存在。在使用時(shí),不能為其添加小括號(hào)。即selectcurrent_date()fromdual是錯(cuò)誤的SQL語(yǔ)句。14.3.5current_date()函數(shù)——返回當(dāng)前14.3.6current_timestamp()函數(shù)——返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前時(shí)間戳current_timestamp()函數(shù)用于返回當(dāng)前會(huì)話時(shí)的區(qū)時(shí)間戳??梢越Y(jié)合sessiontimezone來(lái)查看其用法。selectsessiontimezone,current_timestampfromdual;14.3.6current_timestamp()函數(shù)—14.3.7extract()函數(shù)——返回日期的某個(gè)域日期由若干域組成,例如年、月、日、小時(shí)等等。extract()函數(shù)可以返回這些域的具體值。為了使用該函數(shù),除了要指定原日期外,還應(yīng)該指定要返回的域名。selectextract(yearfromsysdate)resultfromdual;需要注意的是,year、month、day域只能從日期(如sysdate)中獲得,而hour、minute、second只能從時(shí)間型(如systimestamp)中獲得。

14.3.7extract()函數(shù)——返回日期的某個(gè)域日14.4Oracle中的聚合函數(shù)所謂聚合函數(shù)是指針對(duì)多條記錄的函數(shù)。Oracle最常用的聚合函數(shù)包括,max()、min()、avg()、sum()和count()函數(shù)。本節(jié)將講述這些函數(shù)的用法。14.4Oracle中的聚合函數(shù)所謂聚合函數(shù)是指針對(duì)多條14.4.1max()函數(shù)——求最大值max()函數(shù)用于獲得記錄集在某列的最大值。例如,為了返回員工最高工資,可以利用max()函數(shù)。selectmax(salary)max_salaryfromt_salary;需要注意的是,聚合函數(shù)往往是返回記錄集的統(tǒng)計(jì)值,因此,不能與其中的單條記錄同時(shí)出現(xiàn)。例如,不能將max(salary)與具體列一起查詢。selectemployee_id,max(salary)max_salaryfromt_salary;selectdistincte.employee_name,s.salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_idands.salary=(selectmax(salary)fromt_salary)14.4.1max()函數(shù)——求最大值max()函數(shù)用于14.4.2min()函數(shù)——求最小值min()函數(shù)可以用來(lái)獲得記錄集在某列上的最小值,其功能與max()函數(shù)相反。selectdistincte.employee_name,s.salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_idands.salary=(selectmin(salary)fromt_salary)14.4.2min()函數(shù)——求最小值min()函數(shù)可以14.4.3avg()函數(shù)——求平均值avg()函數(shù)用于獲得記錄集在某列上的平均值。selecte.employee_name,avg(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.4.3avg()函數(shù)——求平均值avg()函數(shù)用于14.4.4sum()函數(shù)——求和sum()函數(shù)用于獲得結(jié)果集上某列值的和。selecte.employee_name,sum(salary)fromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.4.4sum()函數(shù)——求和sum()函數(shù)用于獲得14.4.5count()函數(shù)——獲得記錄數(shù)count()函數(shù)的作用對(duì)象同樣為記錄集。與其他聚合函數(shù)不同的是,count()函數(shù)可以有三種方式來(lái)進(jìn)行計(jì)數(shù):count(*)——計(jì)算行數(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ō),利用count(1)進(jìn)行計(jì)數(shù)的速度最快,但是特別注意的是,預(yù)期的結(jié)果是針對(duì)整行數(shù)據(jù),還是某列的數(shù)據(jù)。14.4.5count()函數(shù)——獲得記錄數(shù)count(14.5Oracle中的其他函數(shù)除了數(shù)值函數(shù)、字符串函數(shù)、日期函數(shù)和聚合函數(shù)外,Oracle還提供了其他功能性更強(qiáng)的函數(shù)。本節(jié)將介紹decode()、nvl()和cast()函數(shù)。14.5Oracle中的其他函數(shù)除了數(shù)值函數(shù)、字符串函數(shù)14.5.1decode()函數(shù)——多值判斷decode()函數(shù)用于多值判斷。其執(zhí)行過(guò)程類似于解碼操作。該函數(shù)最常見(jiàn)的應(yīng)用為,實(shí)現(xiàn)類似ifelse的功能。例如,可以利用decode()函數(shù)為員工工資添加標(biāo)識(shí),工資大于6000者為高收入,其余的為一般收入。selecte.employee_id,e.employee_name,decode(sign(avg(s.salary)-6000),1,'高收入','一般收入')incommingfromt_employeese,t_salaryswheree.employee_id=s.employee_idgroupbye.employee_id,e.employee_name14.5.1decode()函數(shù)——多值判斷decode14.5.2nvl()函數(shù)——為空值重新賦值nvl()函數(shù)用于處理某列的值。該函數(shù)有兩個(gè)參數(shù),第一個(gè)參數(shù)為要處理的列。如果其值為空,則返回第二個(gè)參數(shù)的值,否則,將返回列值。selectemployee_id,nvl(employee_name,'未知')employee_namefromt_employees;nvl()函數(shù)更常見(jiàn)的用途為判斷數(shù)值是否為空。因?yàn)閟um()等函數(shù)往往會(huì)返回null,例如,表示匯率的列一旦為null,那么最終的貨幣結(jié)算額度也為null,所以,必須對(duì)匯率列進(jìn)行nvl()的處理。在統(tǒng)計(jì)員工工資時(shí),null同樣是不受歡迎的結(jié)果,那么可以利用nvl()函數(shù)進(jìn)行處理。selecte.employee_id,nvl(e.employee_name,'未知')employee_name,nvl(sum(s.salary),0)salaryfromt_employeese,t_salaryswheree.employee_id=s.employee_id(+)groupbye.employee_id,e.employee_name14.5.2nvl()函數(shù)——為空值重新賦值nvl()函14.5.3cast()函數(shù)——強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型cast()函數(shù)用于強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型。Oracle會(huì)根據(jù)操作符來(lái)自動(dòng)進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)換,例如:select'123'+200resultfromdual;Oracle會(huì)根據(jù)運(yùn)算符“+”將‘123’轉(zhuǎn)換為數(shù)值型123。select'123'||200resultfromdual;Oracle會(huì)根據(jù)運(yùn)算符“||”將數(shù)字200轉(zhuǎn)換為字符串‘200’。cast()函數(shù)最常用的場(chǎng)景是轉(zhuǎn)換列的數(shù)據(jù)類型,以創(chuàng)建新表createtabletmp_salaryasselectcast(salary_idasvarchar2(20))salary_id,cast(employee_idasvarchar2(20))employee_id,cast(monthasvarchar2(20))month,cast(salaryasvarchar2(20))salaryfromt_salarydesctmp_salary;14.5.3cast()函數(shù)——強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型cast14.6Oracle中的運(yùn)算表達(dá)式Oracle中的常用運(yùn)算包括:數(shù)學(xué)運(yùn)算、邏輯運(yùn)算和按位運(yùn)算。本節(jié)將通過(guò)范例著重講述這三種運(yùn)算的常用運(yùn)算符和運(yùn)算規(guī)則。14.6Oracle中的運(yùn)算表達(dá)式Oracle中的常用運(yùn)14.6.1數(shù)學(xué)運(yùn)算數(shù)學(xué)運(yùn)算是最常用的運(yùn)算方式,Oracle中的數(shù)學(xué)運(yùn)算符包括:+、-、*、/,分別代表了加、減、乘除運(yùn)算。在使用數(shù)學(xué)運(yùn)算時(shí),Oracle會(huì)自動(dòng)將其他數(shù)據(jù)類型轉(zhuǎn)換為數(shù)值型,然后再參與運(yùn)算。select5+3resultfromdual;select5-3resultfromdual;select5*2resultfromdual;select5/2resultfromdual;需要注意的是,任何一種運(yùn)算符與null的運(yùn)算結(jié)果均為null。

select5+nullresultfromdual;select5-nullresultfromdual;select5*nullresultfromdual;select5/nullresultfromdual;14.6.1數(shù)學(xué)運(yùn)算數(shù)學(xué)運(yùn)算是最常用的運(yùn)算方式,Orac14.6.2邏輯運(yùn)算Oracle中的邏輯運(yùn)算包括:>:大于運(yùn)算,可用于數(shù)值型、日期型和字符串類型;>=:大于等于運(yùn)算,可用于數(shù)值型、日期型和字符串類型;<:小于運(yùn)算,可用于數(shù)值型、日期型和字符串類型;<=:大于等于運(yùn)算,可用于數(shù)值型、日期型和字符串類型;=:等于,可用于數(shù)值型、日期型和字符串類型;<>:不等于,可用于數(shù)值型、日期型和字符串類型;!=:與<>用法相同;NOT:取反操作;AND:布爾值的與操作;OR:布爾值的或操作。14.6.2邏輯運(yùn)算Oracle中的邏輯運(yùn)算包括:14.6.2邏輯運(yùn)算需要注意的是,Oracle中的邏輯運(yùn)算符只能作為條件判斷,并不返回值。為了查詢工資在5000-7000之間的記錄,可以利用邏輯運(yùn)算符來(lái)組合查詢條件。select*fromt_salarywheresalary>=5000andsalary<=7000;對(duì)于null值,需要特別注意的是,無(wú)論使用哪種運(yùn)算符,結(jié)果都會(huì)返回null。當(dāng)比較的結(jié)果為null,并作為條件出現(xiàn)時(shí),Oracle都會(huì)將其解釋為false。select1resultfromdualwhere1=null;select1resultfromdualwhere1<>null;select1resultfromdualwherenull=null;select1resultfromdualwherenull<>null;14.6.2邏輯運(yùn)算需要注意的是,Oracle中的邏輯運(yùn)14.6.3位運(yùn)算

從Oracle8i開(kāi)始,系統(tǒng)已經(jīng)提供了位運(yùn)算符。最常用的莫過(guò)于bitand運(yùn)算符。selectbitand(192,100)resultfromdual;14.6.3位運(yùn)算從Oracle8i開(kāi)始,系統(tǒng)14.7Oracle中的特殊判式除了邏輯運(yùn)算之外,Oracle提供了一些特殊判式。這些判式可以用來(lái)生成更加復(fù)雜和靈活的查詢條件。本節(jié)將著重介紹以下幾種判式。Between:取值范圍。In:集合成員測(cè)試。Like:模式匹配。isnull:空值判斷。all,some,any:數(shù)量判斷。exists:存在性判斷。14.7Oracle中的特殊判式除了邏輯運(yùn)算之外,Ora14.7.1between——范圍測(cè)試between判式,用于判斷某個(gè)值是否在另外兩個(gè)值之間。這些值可以為數(shù)值型、字符串和日期型。使用betwwen判式來(lái)獲得ID號(hào)在1-5之間的員工信息。select*fromt_employeeswhereemployee_idbetween1and5;betwwen判式同樣可以應(yīng)用于字符串和日期型。字符串是按照字母表的順序進(jìn)行比較,而日期型是按照日期的先后順序進(jìn)行比較。select*fromt_employeeswhere'b'between'b'and'c';select*fromt_employeeswhere'b'between'bc'and'c';注意與說(shuō)明:between判式與>=、<=的組合是等價(jià)關(guān)系。但是,效率上要比后者差。14.7.1between——范圍測(cè)試between判式14.7.2in——集合成員測(cè)試in用于判斷某個(gè)值是否一個(gè)集合的成員。select*fromt_employeeswherestatusin('NEW','ACT');值得注意的是,in判式中的集合的成員的數(shù)據(jù)類型可以不一致,例如,select*fromt_employeeswherestatusin('NEW','ACT',sysdate,1)中的數(shù)據(jù)類型包含了字符串、日期型和數(shù)值型。14.7.2in——集合成員測(cè)試in用于判斷某個(gè)值是否一14.7.3like——模式匹配like判式的最大特點(diǎn)在于,可以使用通配符。其通常的應(yīng)用場(chǎng)景為處理模糊查詢。select*fromt_employeeswhereemployee_namelike'鐘%';如果要求字符串中含有原義字符“%”,例如,含有百分比的字符串。那么,like判式應(yīng)寫(xiě)作:like'鐘\%'escape'\'。Oracle會(huì)首先解釋escape關(guān)鍵字,并將其后的字符“\”解釋為轉(zhuǎn)義字符。那么在“鐘\%”中的“%”不再表示通配符,而是表示原義字符“%”。“_”(下劃線)是可用于like判式的另一個(gè)通配符,該通配符表示一個(gè)任意的字符。14.7.3like——模式匹配like判式的最大特點(diǎn)在14.7.4isnull——空值判斷在邏輯判斷中,對(duì)于列值為空的判斷,不能使用=或者<>。oracle對(duì)與空值的判斷提供了專門(mén)的判式——isnull。例如,為了獲取表t_employees中員工信息不全的記錄,可以利用如下所示的查詢語(yǔ)句。select*fromt_employeeswhereemployee_idisnulloremployee_nameisnullorwork_yearsisnullorstatusisnull;14.7.4isnull——空值判斷在邏輯判斷中,對(duì)于14.7.5exists——存在性判斷in判式用于判斷表的列值是否存在于列表(集合)中。而exists判式則可用于判斷查詢結(jié)果集合是否為空。例如,為了查詢出表t_employees所存儲(chǔ)的員工信息中,哪些員工存在于工資表中,即可利用exists判式。select*fromt_employeesewhereexists(select*fromt_salarywhereemployee_id=e.employee_id);14.7.5exists——存在性判斷in判式用于判斷表14.7.6all,some,any——數(shù)量判斷all,some和any判式的作用對(duì)象為記錄集合。all表示,記錄集中的所有記錄,some表示其中的一些記錄,any判式則表示其中的任意記錄。例如,在員工工資表t_salary中,為了查找高于id為4和5的工資信息,即可使用all判式。select*fromt_salarywhereemployee_id=4oremployee_id=5;select*fromt_salarywheresalary>all(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);select*fromt_salarywheresalary>some(selectdistinctsalaryfromt_salarywhereemployee_id=4oremployee_id=5);此時(shí)的some判式實(shí)際相當(dāng)于邏輯運(yùn)算中的or運(yùn)算,即salary>6000orsalary>7000。此時(shí),使用any判式,將返回同樣的結(jié)果。

14.7.6all,some,any——數(shù)量判斷all,14.8Oracle高級(jí)函數(shù)——分析函數(shù)與窗口函數(shù)Oracle中的分析函數(shù)具有非常強(qiáng)大的功能。分析函數(shù)往往與另一類函數(shù)——窗口函數(shù)同時(shí)使用。窗口函數(shù)總是為查詢過(guò)程中的當(dāng)前記錄提供一個(gè)相關(guān)記錄集,而且隨著當(dāng)前記錄的推移,相應(yīng)的記錄集也會(huì)隨之改變,這非常類似于“滑動(dòng)窗”的概念。分析函數(shù)的操作對(duì)象即為“滑動(dòng)窗”所指定的記錄集合。本節(jié)將通過(guò)實(shí)例來(lái)講述分析函數(shù)和窗口函數(shù)的使用。14.8Oracle高級(jí)函數(shù)——分析函數(shù)與窗口函數(shù)Ora14.8.1排名分析函數(shù)中的排名函數(shù)可以針對(duì)窗口中的記錄生成排序序號(hào)。常用的排名函數(shù)有rank()、dense_rank()和row_number()。rank()函數(shù)用于返回當(dāng)前記錄在窗口函數(shù)所指定的記錄集中的排名。rank()函數(shù)在排名過(guò)程中,具有跳躍的特點(diǎn)。select*fromstudents;selectstudent_name,rank()over(orderbystudent_age)positionfromstudents;selectstudent_name,dense_rank()over(orderbystudent_age)positionfromstudents;selectstudent_name,row_number()over(orderbystudent_age)positionfromstudents;14.8.1排名分析函數(shù)中的排名函數(shù)可以針對(duì)窗口中的記錄14.8.2分區(qū)窗口對(duì)于窗口函數(shù),利用partitionby關(guān)鍵字可以指定分區(qū)窗口?,F(xiàn)欲統(tǒng)計(jì)各員工的工資在各自部門(mén)的高低情況,則可以利用partitionby進(jìn)行分區(qū),然后利用分析函數(shù)對(duì)分區(qū)內(nèi)的記錄進(jìn)行統(tǒng)計(jì)

selectt.*,dense_rank()over(partitionbydepartmentorderbysalary)positionfromsalarytorderbyt.employee_id另外一種常見(jiàn)需求為,在獲得員工工資的同時(shí),也需要部門(mén)所有員工的工資總額

selectt.*,sum(salary)over(partitionbydepartment)total_salary,round(avg(salary)over(partitionbydepartment))average_salaryfromsalarytorderbyemployee_id注意,avg(salary)over(partitionbydepartment)是不可分割的一個(gè)整體。對(duì)于數(shù)據(jù)表salary中每條記錄都會(huì)返回單個(gè)值,因此,當(dāng)使用round()函數(shù),函數(shù)的作用對(duì)象應(yīng)為avg(salary)over(partitionbydepartment)這個(gè)整體,而不能使用諸如round(avg(salary))over(partitionbydepartment)等形式。另外,利用partitionby進(jìn)行分區(qū)之后,當(dāng)前記錄總是處于某個(gè)分區(qū)中,此時(shí)的窗口即為該分區(qū)。14.8.2分區(qū)窗口對(duì)于窗口函數(shù),利用partition14.8.3窗口子句對(duì)于每條記錄,一旦使用了窗口函數(shù),都會(huì)為其產(chǎn)生一個(gè)可操作的記錄集合。而對(duì)于該記錄集,可以使用窗口子句,來(lái)進(jìn)一步限制窗口范圍。常用的窗口子句包括兩類:利用rows子句的行方式進(jìn)行限制;利用range子句的值方式進(jìn)行限制。rows子句selectemployee_id,employee_name,sum(salary)over(orderbyemployee_idrowsbetween1precedingand1following)three_totalfromsalaryrows子句因?yàn)楹臀恢孟嚓P(guān),因此,在窗口函數(shù)中必須含有排序子句orderby。如果未使用orderby子句,而直接使用rows子句,Oracle將拋出錯(cuò)誤提示,14.8.3窗口子句對(duì)于每條記錄,一旦使用了窗口函數(shù),都14.8.3窗口子句注意,rowsbetwwen1precedingand1following不一定返回3條記錄。例如,對(duì)于employee_id為1的記錄,排序之后,該記錄為第一條記錄,不存在前一條記錄,因此只返回兩條記錄,而求和操作返回的實(shí)際為employee_id為1和2的員工的工資總和10500。2.range子句range子句按照列值進(jìn)行窗口的進(jìn)一步限制。selectemployee_id,employee_name,count(1)over(partitionbydepartmentorderbysalaryrangebetween500precedingand500following)asemployee_countfromsalaryorderbyemployee_id3.unbounded和currentrow在rows和range子句中,除了使用具體的數(shù)值來(lái)決定窗口的大小之外,還可以使用關(guān)鍵字unbounded和currentrow。unbounded可以直接代替數(shù)值,表示沒(méi)有任何限制;currentrow則直接代表當(dāng)前行。

14.8.3窗口子句注意,rowsbetwwen114.8.4主要的分析函數(shù)分析函數(shù)作用對(duì)象為窗口函數(shù)所捕獲的記錄集,因此,分析函數(shù)具有聚合函數(shù)的特點(diǎn),大多數(shù)的聚合函數(shù),如sum()、count()、max()等都能作為分析函數(shù)出現(xiàn)。Oracle還提供了專門(mén)針對(duì)窗口函數(shù)的分析函數(shù),本小節(jié)著重講述常用的幾種——fist_value()、last_value()、lag()和lead()。1.fist_value()函數(shù)的使用selectdistinctdepartment,first_value(employee_name)over(partitionbydepartmentorderbysalary)employee_name,first_value(salary)over(partitionbydepartmentorderbysalary)salaryfromsalary2.last_value()函數(shù)的使用3.lead()函數(shù)的使用selectemployee_id,employee_name,salary,lead(employee_name,1,'N/A')over(partitionbydepartmentorderbysalary)prev_namefromsalaryorderbyemployee_id4.lag()函數(shù)的使用14.8.4主要的分析函數(shù)分析函數(shù)作用對(duì)象為窗口函數(shù)所捕14.9本章實(shí)例Oracle雖然內(nèi)置了很多函數(shù),但是并不能滿足日常開(kāi)發(fā)中的應(yīng)用。此時(shí),需要開(kāi)發(fā)者自定義函數(shù),而內(nèi)置函數(shù)往往成為自定函數(shù)的基石。createorreplacefunctionis_date(paramvarchar2)returnvarchar2isvaldate;beginval:=to_date(nvl(param,''),'yyyy-mm-ddhh24:mi:ss');return'Y';exceptionwhenothersthenreturn'N';end;selectis_date('abc')fromdual;selectis_date('2009-09-01')fromdual;14.9本章實(shí)例Oracle雖然內(nèi)置了很多函數(shù),但是并不14.10本章小結(jié)本章通過(guò)詳盡的實(shí)例講述了Oracle中常用的內(nèi)置函數(shù)、表達(dá)式以及特殊判式。對(duì)于內(nèi)置函數(shù),特別需要注意的是聚合函數(shù)的使用。聚合函數(shù)最常用的場(chǎng)景為分組查詢;聚合函數(shù)不能與單條記錄的列并列作為查詢結(jié)果。對(duì)于特殊判式,尤其應(yīng)該注意的是like判式的使用,like判式使用中,通配符只有“%”和“_”兩種,要注意通配符和正則表達(dá)式的區(qū)別。14.10本章小結(jié)本章通過(guò)詳盡的實(shí)例講述了Oracle中14.10本章小結(jié)在Oracle高級(jí)函數(shù)中,重點(diǎn)介紹了分析函數(shù)和窗口函數(shù),這兩個(gè)函數(shù)總是結(jié)合使用,為數(shù)據(jù)表中單條記錄提供新的結(jié)果集的方法。對(duì)于窗口函數(shù),要重點(diǎn)理解分區(qū)和排序的工作流程,尤其需要注意的是,對(duì)于排序中,具有相同列值的記錄的處理。相較之下,分析函數(shù)非常類似于聚合函數(shù),比較容易理解和掌握。在統(tǒng)計(jì)和生成復(fù)雜報(bào)表時(shí),分析函數(shù)和窗口函數(shù)有著廣泛的應(yīng)用,尤其對(duì)于復(fù)雜統(tǒng)計(jì),利用這兩種函數(shù)往往可以起到事半功倍的效果。14.10本章小結(jié)在Oracle高級(jí)函數(shù)中,重點(diǎn)介紹了分14.11習(xí)題1.簡(jiǎn)述null作為函數(shù)參數(shù)時(shí)的特點(diǎn)。2.簡(jiǎn)述like判式的使用方法。3.簡(jiǎn)述isnull判式的意義。4.簡(jiǎn)述窗口函數(shù)的特點(diǎn)。14.11習(xí)題1.簡(jiǎn)述null作為函數(shù)參數(shù)時(shí)的特點(diǎn)。第14章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ù)與表達(dá)式Oracle中提供了第14章Oracle中的函數(shù)與表達(dá)式Oracle中的聚合函數(shù);Oracle中的運(yùn)算表達(dá)式;Oracle中的特殊判式;Oracle中的高級(jí)函數(shù)——分析函數(shù)與窗口函數(shù)。第14章Oracle中的函數(shù)與表達(dá)式Oracle中的聚合14.1Oracle中的字符串函數(shù)Oracle提供了豐富的字符串函數(shù),本小節(jié)將通過(guò)實(shí)例講述Oracle中各字符串函數(shù)的使用。14.1Oracle中的字符串函數(shù)Oracle提供了豐富14.1.1lpad()函數(shù)lpad()函數(shù)用于左補(bǔ)全字符串。在某些情況下,預(yù)期的字符串為固定長(zhǎng)度,而且格式統(tǒng)一,此時(shí)可以考慮使用lpad()函數(shù)。例如,深市股票代碼都以0開(kāi)頭,并且都為6位,可以利用lpad格式化股票代碼,以保證股票代碼的格式。selectlpad('21',6,'0')stock_codefromdual;需要注意的是,當(dāng)原字符串的長(zhǎng)度大于預(yù)期長(zhǎng)度時(shí),實(shí)際進(jìn)行的是截取字符串操作。selectlpad('1234567',6,'0')stock_codefromdual;14.1.1lpad()函數(shù)lpad()函數(shù)用于左補(bǔ)全字14.1.2rpad()函數(shù)與lpad()函數(shù)相反,rpad()函數(shù)從右端補(bǔ)齊字符串。selectrpad('abc',10,'*')fromdual;注意與說(shuō)明:lpad()和rpad()都用于填充字符串,lpad()從左端進(jìn)行填充,而rpad()從右端進(jìn)行填充,但是,二者在最終截取字符串時(shí),都是從左端開(kāi)始截取。selectrpad('abcdefg',6,'*')fromdual;14.1.2rpad()函數(shù)與lpad()函數(shù)相反,rp14.1.3lower()函數(shù)——返回小寫(xiě)字符串lower()函數(shù)用于返回字符串的小寫(xiě)形式。lower()函數(shù)在查詢語(yǔ)句中經(jīng)常扮演重要角色。例如,對(duì)于用戶名和密碼的校驗(yàn)來(lái)說(shuō),用戶名一般并不區(qū)分大小寫(xiě),用戶無(wú)論輸入了大寫(xiě)還是小寫(xiě)形式,都被認(rèn)為是合法用戶。因此,在數(shù)據(jù)庫(kù)查詢時(shí),應(yīng)該將數(shù)據(jù)庫(kù)中用戶名與用戶輸入的用戶名進(jìn)行統(tǒng)一。selectuser_id,user_namefromt_userswherelower(user_name)=lower('Alex');14.1.3lower()函數(shù)——返回小寫(xiě)字符串lowe14.1.4upper()函數(shù)——返回大寫(xiě)字符串upper()函數(shù)用于返回字符串的大寫(xiě)形式。與lower()函數(shù)類似,upper()函數(shù)也可以用在查詢語(yǔ)句中,以統(tǒng)一數(shù)據(jù)庫(kù)和查詢條件的一致性。selectuser_id,user_namefromt_userswhereupper(user_name)=upper('ALEX');注意與說(shuō)明:upper()函數(shù)和lower()函數(shù)只針對(duì)英文字符其作用,因?yàn)橹挥杏⑽淖址庞写笮?xiě)之分。14.1.4upper()函數(shù)——返回大寫(xiě)字符串uppe14.1.5initcap()函數(shù)——單詞首字母大寫(xiě)initcap()函數(shù)將單詞的首字母大寫(xiě)。selectinitcap('big')fromdual;需要注意的是,initcap()函數(shù)不能自動(dòng)識(shí)別單詞

selectinitcap('bigbigtiger')fromdual;initcap()函數(shù)會(huì)將參數(shù)中的非單詞字符作為單詞分隔符selectinitcap('big_big_tiger')fromdual;selectinitcap('big/big/tiger')fromdual;selectinitcap('bigbigtiger')fromdual;14.1.5initcap()函數(shù)——單詞首字母大寫(xiě)i14.1.6length()函數(shù)——返回字符串長(zhǎng)度length()函數(shù)用于返回字符串的長(zhǎng)度。selectlength('abcd')fromdual;空字符串的長(zhǎng)度不是0,而是null。因?yàn)榭兆址灰曌鱪ull,所以,length(null)返回的仍然是null。selectlength('')fromdual;對(duì)其其他數(shù)據(jù)類型,照樣可以通過(guò)length()函數(shù)來(lái)獲得其長(zhǎng)度。length()函數(shù)會(huì)首先將參數(shù)轉(zhuǎn)換為字符串,然后計(jì)算其長(zhǎng)度。selectlength(12.51)fromdual;14.1.6length()函數(shù)——返回字符串長(zhǎng)度len14.1.7substr()函數(shù)——截取字符串substr()函數(shù)用于截取字符串。該函數(shù)可以指定截取的起始位置,截取長(zhǎng)度,可以實(shí)現(xiàn)靈活的截取操作,因此,成為字符串操作中最常用的函數(shù)之一。例如,對(duì)于字符串“1234567890”,現(xiàn)欲截取自第5位開(kāi)始的4個(gè)字符。selectsubstr('1234567890',5,4)fromdual;需要注意的是,Oracle中字符位置從1開(kāi)始,而不是像某些編程語(yǔ)言(如Java)那樣從0開(kāi)始。

如果不指定長(zhǎng)度,那么substr()函數(shù)將獲取起始位置參數(shù)至字符串結(jié)尾處的所有字符。

selectsubstr('1234567890',5)fromdual;14.1.7substr()函數(shù)——截取字符串subst14.1.8instr()函數(shù)——獲得字符串出現(xiàn)的位置instr()函數(shù)用于獲得子字符串在父字符串中出現(xiàn)的位置。selectinstr('bigbigtiger','big')fromdual;可以指定額外的參數(shù),以命令該函數(shù)從指定位置開(kāi)始搜索。selectinstr('bigbigtiger','big',2)fromdual;還可以指定出現(xiàn)次數(shù)參數(shù),以指定是第幾次搜索到子字符串。selectinstr('bigbigtiger','big',2,2)fromdual;14.1.8instr()函數(shù)——獲得字符串出現(xiàn)的位置i14.1.9ltrim()函數(shù)——?jiǎng)h除字符串首部空格ltrim()中的l代表left。該函數(shù)用于刪除字符串左端的空白符。selectltrim('abc')fromdual;需要注意的是,空白符不僅僅包括了空格符,還包括TAB鍵、回車符和換行符。14.1.9ltrim()函數(shù)——?jiǎng)h除字符串首部空格lt14.1.10rtrim()函數(shù)——?jiǎng)h除字符串尾部空格rtrim()中的r代表right。該函數(shù)用于刪除字符串右端空白符。刪除字符串首尾空白符可以結(jié)合使用ltrm()和rtrim()函數(shù)。selectrtrim(ltrim('abc'))fromdual;14.1.10rtrim()函數(shù)——?jiǎng)h除字符串尾部空格r14.1.11trim()函數(shù)——?jiǎng)h除字符串首尾空格trim()函數(shù)可用于刪除首尾空格,相當(dāng)于ltrim()和rtrim()的組合。selecttrim('abc')fromdual;14.1.11trim()函數(shù)——?jiǎng)h除字符串首尾空格tr14.1.12to_char()函數(shù)——將其他類型轉(zhuǎn)換為字符類型to_char()函數(shù)用于將其他數(shù)據(jù)類型的數(shù)據(jù)轉(zhuǎn)換為字符型,這些類型主要包括數(shù)值型、日期型。1.將數(shù)值型轉(zhuǎn)換為字符串selectto_char(120,'99999')resultfromdual;selectto_char(0.96,'9.99')resultfromdual;selectto_char(0.96,'0.00')resultfromdual;selectto_char(5897.098,'999,999,999.000')resultfromdual;selectto_char(5987.098,'$999,999,999.000')resultfromdual;2.將日期型轉(zhuǎn)換為字符串selectto_char(sysdate,'yyyy-mm-dd')resultfromdual;selectto_char(sysdate,'YYYY-MON-DD')fromdual;

14.1.12to_char()函數(shù)——將其他類型轉(zhuǎn)換為14.1.13chr()函數(shù)——將ascii碼轉(zhuǎn)換為字符串chr()函數(shù)用于將ascii碼轉(zhuǎn)換為字符串。通過(guò)chr()函數(shù),可以對(duì)不宜直接輸入的字符進(jìn)行操作。例如,將回車換行符插入到數(shù)據(jù)中。insertintotest_datavalues(6,'周林'||chr(13)||chr(10)|'梁軍',20);select*fromtest_datawhereid=6;14.1.13chr()函數(shù)——將ascii碼轉(zhuǎn)換為字符14.1.14translate()函數(shù)——替換字符translate()函數(shù)用于替換字符串。替換的規(guī)則類似于翻譯的過(guò)程。selecttranslate('56338','1234567890','avlihemoqr')resultfromdual;需要注意的是,當(dāng)字符不能被成功“翻譯”,那么,Oracle將使用空字符替換它。利用此特性,可以使用translate()函數(shù)來(lái)刪除一個(gè)含有數(shù)字和英文字母的字符串中的所有字母:selecttranslate('21343yuioioizf899dasiwpe58595oda0j098','#abcdefghijklmnopqrstuvwxyz','')reulstfromdual;14.1.14translate()函數(shù)——替換字符tr14.2Oracle中的數(shù)學(xué)函數(shù)Oracle提供的數(shù)學(xué)函數(shù)可以處理日常使用到的大多數(shù)數(shù)學(xué)運(yùn)算。本小節(jié)將講述Oracle中常用的幾種數(shù)學(xué)函數(shù)。14.2Oracle中的數(shù)學(xué)函數(shù)Oracle提供的數(shù)學(xué)函14.2.1abs()函數(shù)——返回?cái)?shù)字的絕對(duì)值abs()函數(shù)的參數(shù)只能是數(shù)值型,該參數(shù)用于返回參數(shù)的絕對(duì)值。selectabs(-2.1)fromdual;14.2.1abs()函數(shù)——返回?cái)?shù)字的絕對(duì)值abs(14.2.2round()函數(shù)——返回?cái)?shù)字的“四舍五入”值round()函數(shù)用于返回某個(gè)數(shù)字的四舍五入值。為了使用該函數(shù),除了提供原始值之外,還應(yīng)提供精確到的位數(shù)。精確位數(shù)可以為正整數(shù)、0和負(fù)整數(shù)。selectround(2745.173,2)resultfromdual;如果不使用第二個(gè)參數(shù),那么,相當(dāng)于使用了參數(shù)0,即精確到整數(shù)。selectround(2745.173)resultfromdual;如果第二個(gè)參數(shù)為負(fù)數(shù),那么,相當(dāng)于將數(shù)值精確到小數(shù)點(diǎn)之前的位數(shù)。

selectround(2745,-1)resultfromdual;14.2.2round()函數(shù)——返回?cái)?shù)字的“四舍五入14.2.3ceil()函數(shù)——向上取整ceil()函數(shù)只能有一個(gè)參數(shù)。該函數(shù)將參數(shù)向上取整,以獲得大于等于該參數(shù)的最小整數(shù)。selectceil(21.897)resultfromdual;需要注意的是該函數(shù)針對(duì)負(fù)數(shù)的運(yùn)算:

selectceil(-21.897)resultfromdual;因?yàn)閏eil()函數(shù)返回的是大于等于參數(shù)的最小整數(shù),所以,該函數(shù)返回的并非-22,而是-21。14.2.3ceil()函數(shù)——向上取整ceil()函數(shù)14.2.4floor()函數(shù)——向下取整與ceil函數(shù)相反,floor()函數(shù)用于返回小于等于某個(gè)數(shù)值的最大整數(shù)。selectfloor(21.897)resultfromdual;selectfloor(-21.897)resultfromdual;14.2.4floor()函數(shù)——向下取整與ceil函數(shù)14.2.5mod()函數(shù)——取模操作mod()函數(shù)有兩個(gè)參數(shù),第一個(gè)參數(shù)為被除數(shù),第二個(gè)參數(shù)為除數(shù)。mod()函數(shù)的實(shí)際功能為獲得兩數(shù)相除之后的余數(shù)。selectmod(5,2)resultfromdual;14.2.5mod()函數(shù)——取模操作mod()函數(shù)有14.2.6sign()函數(shù)——返回?cái)?shù)字的正負(fù)性sign()函數(shù)只有一個(gè)參數(shù)。該函數(shù)將返回參數(shù)的正負(fù)性。若返回值為1,表示該參數(shù)大于0;若返回值為-1,表示該參數(shù)小于0;若返回值為0,表示該參數(shù)等于0。selectsign(8)resultfromdual;selectsign(-8)resultfromdual;selectsign(0)resultfromdual;sign()函數(shù)為判斷兩個(gè)數(shù)值的大小關(guān)系提供了方便。因?yàn)樵趏racle中,利用類似ifelse的結(jié)構(gòu)來(lái)判斷兩個(gè)數(shù)值之間的大小關(guān)系,并不像編程語(yǔ)言中那樣方便,而且極易造成代碼的復(fù)雜化。14.2.6sign()函數(shù)——返回?cái)?shù)字的正負(fù)性sign14.2.7sqrt()函數(shù)——返回?cái)?shù)字的平方根sqrt()函數(shù)也只有一個(gè)參數(shù)。該函數(shù)用于返回參數(shù)的平方根??梢岳胷ound()函數(shù)和sqrt()函數(shù)返回某個(gè)數(shù)值的近似平方根。selectround(sqrt(2),3)resultfromdual;14.2.7sqrt()函數(shù)——返回?cái)?shù)字的平方根sqrt14.2.8power()函數(shù)——乘方運(yùn)算power()函數(shù)有兩個(gè)參數(shù)。該函數(shù)用于實(shí)現(xiàn)數(shù)值的乘方運(yùn)算。selectpower(6,2)resultfromdual;14.2.8power()函數(shù)——乘方運(yùn)算power()14.2.9trunc()函數(shù)——截取數(shù)字trunc()函數(shù)用于截取部分?jǐn)?shù)字。其工作機(jī)制非常類似于round()函數(shù)。與round()函數(shù)不同的是,該函數(shù)不對(duì)數(shù)值做四舍五入處理,而是直接截取。selecttrunc(2745.173,2)resultfromdual;保留位數(shù)的值可以為0,當(dāng)該參數(shù)的值為0時(shí),將保留到整數(shù)。selecttrunc(2745.173)resultfromdual;當(dāng)保留位數(shù)小于0時(shí),表示保留到小數(shù)點(diǎn)之前的位數(shù)。

selecttrunc(2745.173,-1)resultfromdual;14.2.9trunc()函數(shù)——截取數(shù)字trunc()14.2.10vsize()函數(shù)——返回?cái)?shù)據(jù)的存儲(chǔ)空間vsize()函數(shù)根據(jù)數(shù)據(jù)庫(kù)的存儲(chǔ)格式,來(lái)返回其所占用的存儲(chǔ)空間的字節(jié)數(shù)。selectvsize('abc123')fromdual;注意與說(shuō)明:vsize()函數(shù)在返回的是Oracle實(shí)際存儲(chǔ)數(shù)據(jù)的字節(jié)數(shù),在實(shí)際開(kāi)發(fā)中使用的幾率也較小。讀者可以不必了解Oracle本身的存儲(chǔ)機(jī)制。14.2.10vsize()函數(shù)——返回?cái)?shù)據(jù)的存儲(chǔ)空間v14.2.11to_number()函數(shù)——將字符串轉(zhuǎn)換為數(shù)值類型to_number()函數(shù)可以將字符串轉(zhuǎn)換為數(shù)值型。selectto_number('257.90')resultfromdual;需要注意的是,被轉(zhuǎn)換的字符串必須符合數(shù)值類型格式。如果被轉(zhuǎn)換的字符串不符合數(shù)值型格式,Oracle將拋出錯(cuò)誤提示。selectto_number('a')resultfromdual;14.2.11to_number()函數(shù)——將字符串轉(zhuǎn)換14.3Oracle中的日期函數(shù)Oracle提供了豐富的日期函數(shù)。利用日期函數(shù)可以靈活的對(duì)日期進(jìn)行運(yùn)算。14.3Oracle中的日期函數(shù)Oracle提供了豐富的14.3.1to_date()函數(shù)——將字符串轉(zhuǎn)換為日期型to_date()函數(shù)用于將字符串轉(zhuǎn)換為日期。被轉(zhuǎn)換的字符串必須符合特定的日期格式。selectto_date('12/02/09','mm/dd/yy')resultfromdual;14.3.1to_date()函數(shù)——將字符串轉(zhuǎn)換為日期14.3.2add_months()函數(shù)——為日期加上特定月份add_months()函數(shù)將為日期添加特定月份,并獲得新的日期。selectto_char(add_months(sysdate,2),'yyyy-mm-dd')resultfromdual;14.3.2add_months()函數(shù)——為日期加上特14.3.3last_day()函數(shù)——返回特定日期所在月的最后一天last_day()函數(shù)將接受一個(gè)日期參數(shù)

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論