1、第第1414章章 OracleOracle中的函數(shù)與表達(dá)式中的函數(shù)與表達(dá)式Oracle中提供了大量的內(nèi)置函數(shù)，以處理各種形式的中提供了大量的內(nèi)置函數(shù)，以處理各種形式的運(yùn)算。這些函數(shù)涵蓋了字符串運(yùn)算、數(shù)值運(yùn)算、日期運(yùn)算等運(yùn)算。這些函數(shù)涵蓋了字符串運(yùn)算、數(shù)值運(yùn)算、日期運(yùn)算等方面。同樣，方面。同樣，Oracle允許使用數(shù)值運(yùn)算、邏輯運(yùn)算等基本的允許使用數(shù)值運(yùn)算、邏輯運(yùn)算等基本的表達(dá)式運(yùn)算，另外，提供了表達(dá)式運(yùn)算，另外，提供了SQL標(biāo)準(zhǔn)所規(guī)定的特殊判式。標(biāo)準(zhǔn)所規(guī)定的特殊判式。lOracle中的字符串函數(shù)；中的字符串函數(shù)；lOracle中的數(shù)學(xué)函數(shù)；中的數(shù)學(xué)函數(shù)；lOracle中的日期函數(shù)；中的日期函數(shù)

2、；第第1414章章 OracleOracle中的函數(shù)與表達(dá)式中的函數(shù)與表達(dá)式lOracle中的聚合函數(shù)；中的聚合函數(shù)；lOracle中的運(yùn)算表達(dá)式；中的運(yùn)算表達(dá)式；lOracle中的特殊判式；中的特殊判式；lOracle中的高級函數(shù)中的高級函數(shù)分析函數(shù)與窗口函數(shù)。分析函數(shù)與窗口函數(shù)。14.1 Oracle14.1 Oracle中的字符串函數(shù)中的字符串函數(shù)Oracle提供了豐富的字符串函數(shù)，本小節(jié)將通過實(shí)例提供了豐富的字符串函數(shù)，本小節(jié)將通過實(shí)例講述講述Oracle中各字符串函數(shù)的使用。中各字符串函數(shù)的使用。14.1.1 lpad14.1.1 lpad() ()函數(shù)函數(shù)lpad()函數(shù)用于左補(bǔ)全

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

4、作。select lpad(1234567,6,0) stock_code from dual;14.1.2 rpad14.1.2 rpad() ()函數(shù)函數(shù)與與lpad()函數(shù)相反，函數(shù)相反，rpad()函數(shù)從右端補(bǔ)齊字符串。函數(shù)從右端補(bǔ)齊字符串。select rpad(abc, 10, *) from dual; 注意與說明：注意與說明：lpad()和和rpad()都用于填充字符串，都用于填充字符串，lpad()從左端進(jìn)行填充，而從左端進(jìn)行填充，而rpad()從右端進(jìn)行填充，但是，從右端進(jìn)行填充，但是，二者在最終截取字符串時(shí)，都是從左端開始截取。二者在最終截取字符串時(shí)，都是從左端開始截取。

5、select rpad(abcdefg, 6, *) from dual;14.1.3 lower()14.1.3 lower()函數(shù)函數(shù)返回小寫字符串返回小寫字符串lower()函數(shù)用于返回字符串的小寫形式。函數(shù)用于返回字符串的小寫形式。lower()函數(shù)函數(shù)在查詢語句中經(jīng)常扮演重要角色。例如，對于用戶名和密碼在查詢語句中經(jīng)常扮演重要角色。例如，對于用戶名和密碼的校驗(yàn)來說，用戶名一般并不區(qū)分大小寫，用戶無論輸入了的校驗(yàn)來說，用戶名一般并不區(qū)分大小寫，用戶無論輸入了大寫還是小寫形式，都被認(rèn)為是合法用戶。因此，在數(shù)據(jù)庫大寫還是小寫形式，都被認(rèn)為是合法用戶。因此，在數(shù)據(jù)庫查詢時(shí)，應(yīng)該將數(shù)據(jù)庫中用戶

6、名與用戶輸入的用戶名進(jìn)行統(tǒng)查詢時(shí)，應(yīng)該將數(shù)據(jù)庫中用戶名與用戶輸入的用戶名進(jìn)行統(tǒng)一。一。select user_id, user_name from t_users where lower(user_name) = lower(Alex);14.1.4 upper()14.1.4 upper()函數(shù)函數(shù)返回大寫字符串返回大寫字符串upper()函數(shù)用于返回字符串的大寫形式。與函數(shù)用于返回字符串的大寫形式。與lower()函函數(shù)類似，數(shù)類似，upper()函數(shù)也可以用在查詢語句中，以統(tǒng)一數(shù)據(jù)函數(shù)也可以用在查詢語句中，以統(tǒng)一數(shù)據(jù)庫和查詢條件的一致性。庫和查詢條件的一致性。select user_id

7、, user_name from t_users where upper(user_name) = upper(ALEX);注意與說明：注意與說明：upper()函數(shù)和函數(shù)和lower()函數(shù)只針對英文字函數(shù)只針對英文字符其作用，因?yàn)橹挥杏⑽淖址庞写笮懼帧７渥饔茫驗(yàn)橹挥杏⑽淖址庞写笮懼帧?4.1.5 initcap14.1.5 initcap () ()函數(shù)函數(shù)單詞首字母大寫單詞首字母大寫initcap()函數(shù)將單詞的首字母大寫。函數(shù)將單詞的首字母大寫。select initcap(big) from dual;需要注意的是，需要注意的是，initcap()函數(shù)不能自動(dòng)識別單詞

8、函數(shù)不能自動(dòng)識別單詞 select initcap(bigbigtiger) from dual;initcap()函數(shù)會(huì)將參數(shù)中的非單詞字符作為單詞分隔符函數(shù)會(huì)將參數(shù)中的非單詞字符作為單詞分隔符select initcap(big_big_tiger) from dual; select initcap(big/big/tiger) from dual; select initcap(big big tiger) from dual; 14.1.6 length()14.1.6 length()函數(shù)函數(shù)返回字符串長度返回字符串長度length()函數(shù)用于返回字符串的長度。函數(shù)用于返回字符串的

9、長度。select length(abcd ) from dual;空字符串的長度不是空字符串的長度不是0，而是，而是null。因?yàn)榭兆址灰暋Ｒ驗(yàn)榭兆址灰曌髯鱪ull，所以，所以，length(null)返回的仍然是返回的仍然是null。select length() from dual;對其其他數(shù)據(jù)類型，照樣可以通過對其其他數(shù)據(jù)類型，照樣可以通過length()函數(shù)來獲得函數(shù)來獲得其長度。其長度。length()函數(shù)會(huì)首先將參數(shù)轉(zhuǎn)換為字符串，然后計(jì)函數(shù)會(huì)首先將參數(shù)轉(zhuǎn)換為字符串，然后計(jì)算其長度。算其長度。select length(12.51) from dual;14.1.7 sub

10、str14.1.7 substr() ()函數(shù)函數(shù)截取字符串截取字符串substr()函數(shù)用于截取字符串。該函數(shù)可以指定截取的函數(shù)用于截取字符串。該函數(shù)可以指定截取的起始位置，截取長度，可以實(shí)現(xiàn)靈活的截取操作，因此，成起始位置，截取長度，可以實(shí)現(xiàn)靈活的截取操作，因此，成為字符串操作中最常用的函數(shù)之一。為字符串操作中最常用的函數(shù)之一。例如，對于字符串例如，對于字符串“1234567890”，現(xiàn)欲截取自第，現(xiàn)欲截取自第5位位開始的開始的4個(gè)字符。個(gè)字符。select substr(1234567890, 5, 4) from dual;需要注意的是，需要注意的是，Oracle中字符位置從中字符位置

11、從1開始，而不是像開始，而不是像某些編程語言（如某些編程語言（如Java）那樣從）那樣從0開始。開始。 如果不指定長度，那么如果不指定長度，那么substr()函數(shù)將獲取起始位置參函數(shù)將獲取起始位置參數(shù)至字符串結(jié)尾處的所有字符。數(shù)至字符串結(jié)尾處的所有字符。 select substr(1234567890, 5) from dual; 14.1.8 instr14.1.8 instr() ()函數(shù)函數(shù)獲得字符串出現(xiàn)的位置獲得字符串出現(xiàn)的位置instr()函數(shù)用于獲得子字符串在父字符串中出現(xiàn)的位置函數(shù)用于獲得子字符串在父字符串中出現(xiàn)的位置。select instr(big big tiger,

12、big) from dual;可以指定額外的參數(shù)，以命令該函數(shù)從指定位置開始可以指定額外的參數(shù)，以命令該函數(shù)從指定位置開始搜索。搜索。 select instr(big big tiger, big, 2) from dual; 還可以指定出現(xiàn)次數(shù)參數(shù)，以指定是第幾次搜索到子還可以指定出現(xiàn)次數(shù)參數(shù)，以指定是第幾次搜索到子字符串。字符串。select instr(big big tiger, big, 2, 2) from dual;14.1.9 ltrim14.1.9 ltrim() ()函數(shù)函數(shù)刪除字符串首部空格刪除字符串首部空格ltrim()中的中的l代表代表left。該函數(shù)用于刪除字符串

13、左端的空。該函數(shù)用于刪除字符串左端的空白符。白符。select ltrim( abc) from dual; 需要注意的是，空白符不僅僅包括了空格符，還包括需要注意的是，空白符不僅僅包括了空格符，還包括TAB鍵、回車符和換行符。鍵、回車符和換行符。14.1.10 rtrim14.1.10 rtrim() ()函數(shù)函數(shù)刪除字符串尾部空格刪除字符串尾部空格rtrim()中的中的r代表代表right。該函數(shù)用于刪除字符串右端空。該函數(shù)用于刪除字符串右端空白符。刪除字符串首尾空白符可以結(jié)合使用白符。刪除字符串首尾空白符可以結(jié)合使用ltrm()和和rtrim()函數(shù)。函數(shù)。select rtrim(lt

14、rim( abc ) from dual;14.1.11 trim()14.1.11 trim()函數(shù)函數(shù)刪除字符串首尾空格刪除字符串首尾空格trim()函數(shù)可用于刪除首尾空格，相當(dāng)于函數(shù)可用于刪除首尾空格，相當(dāng)于ltrim()和和rtrim()的組合。的組合。select trim( abc ) from dual;14.1.12 to_char14.1.12 to_char() ()函數(shù)函數(shù)將其他類型轉(zhuǎn)換為將其他類型轉(zhuǎn)換為字符類型字符類型to_char()函數(shù)用于將其他數(shù)據(jù)類型的數(shù)據(jù)轉(zhuǎn)換為字符函數(shù)用于將其他數(shù)據(jù)類型的數(shù)據(jù)轉(zhuǎn)換為字符型，這些類型主要包括數(shù)值型、日期型。型，這些類型主要包括數(shù)值

15、型、日期型。1. 將數(shù)值型轉(zhuǎn)換為字符串將數(shù)值型轉(zhuǎn)換為字符串select to_char(120, 99999) result from dual; select to_char(0.96, 9.99) result from dual; select to_char(0.96, 0.00) result from dual; select to_char(5897.098, 999,999,999.000) result from dual; select to_char(5987.098, $999,999,999.000) result from dual;2. 將日期型轉(zhuǎn)換為字符串將日期

16、型轉(zhuǎn)換為字符串select to_char(sysdate, yyyy-mm-dd) result from dual; select to_char(sysdate, YYYY-MON-DD) from dual; 14.1.13 chr14.1.13 chr() ()函數(shù)函數(shù)將將asciiascii碼轉(zhuǎn)換為字符串碼轉(zhuǎn)換為字符串chr()函數(shù)用于將函數(shù)用于將ascii碼轉(zhuǎn)換為字符串。通過碼轉(zhuǎn)換為字符串。通過chr()函數(shù)函數(shù)，可以對不宜直接輸入的字符進(jìn)行操作。例如，將回車換行，可以對不宜直接輸入的字符進(jìn)行操作。例如，將回車換行符插入到數(shù)據(jù)中。符插入到數(shù)據(jù)中。insert into test_

17、data values (6, 周林周林|chr(13)|chr(10)|梁軍梁軍, 20); select * from test_data where id = 6; 14.1.14 translate()14.1.14 translate()函數(shù)函數(shù)替換字符替換字符translate()函數(shù)用于替換字符串。替換的規(guī)則類似于翻函數(shù)用于替換字符串。替換的規(guī)則類似于翻譯的過程。譯的過程。 select translate(56338, 1234567890, avlihemoqr) result from dual;需要注意的是，當(dāng)字符不能被成功需要注意的是，當(dāng)字符不能被成功“翻譯翻譯”，那么

18、，那么，Oracle將使用空字符替換它。利用此特性，可以使用將使用空字符替換它。利用此特性，可以使用translate()函數(shù)來刪除一個(gè)含有數(shù)字和英文字母的字符串中函數(shù)來刪除一個(gè)含有數(shù)字和英文字母的字符串中的所有字母：的所有字母：select translate(21343yuioioizf899dasiwpe58595oda0j098, #abcdefghijklmnopqrstuvwxyz, ) reulst from dual; 14.2 Oracle14.2 Oracle中的數(shù)學(xué)函數(shù)中的數(shù)學(xué)函數(shù)Oracle提供的數(shù)學(xué)函數(shù)可以處理日常使用到的大多數(shù)提供的數(shù)學(xué)函數(shù)可以處理日常使用到的大多數(shù)

19、數(shù)學(xué)運(yùn)算。本小節(jié)將講述數(shù)學(xué)運(yùn)算。本小節(jié)將講述Oracle中常用的幾種數(shù)學(xué)函數(shù)。中常用的幾種數(shù)學(xué)函數(shù)。14.2.1 abs ()14.2.1 abs ()函數(shù)函數(shù)返回?cái)?shù)字的絕對值返回?cái)?shù)字的絕對值abs()函數(shù)的參數(shù)只能是數(shù)值型，該參數(shù)用于返回參數(shù)函數(shù)的參數(shù)只能是數(shù)值型，該參數(shù)用于返回參數(shù)的絕對值。的絕對值。select abs(-2.1) from dual; 14.2.2 round ()14.2.2 round ()函數(shù)函數(shù)返回?cái)?shù)字的返回?cái)?shù)字的“ “四舍五入四舍五入” ”值值round()函數(shù)用于返回某個(gè)數(shù)字的四舍五入值。為了使函數(shù)用于返回某個(gè)數(shù)字的四舍五入值。為了使用該函數(shù)，除了提供原始值

20、之外，還應(yīng)提供精確到的位數(shù)。用該函數(shù)，除了提供原始值之外，還應(yīng)提供精確到的位數(shù)。精確位數(shù)可以為正整數(shù)、精確位數(shù)可以為正整數(shù)、0和負(fù)整數(shù)。和負(fù)整數(shù)。select round(2745.173, 2) result from dual; 如果不使用第二個(gè)參數(shù)，那么，相當(dāng)于使用了參數(shù)如果不使用第二個(gè)參數(shù)，那么，相當(dāng)于使用了參數(shù)0，即精確到整數(shù)。即精確到整數(shù)。select round(2745.173) result from dual; 如果第二個(gè)參數(shù)為負(fù)數(shù)，那么，相當(dāng)于將數(shù)值精確到如果第二個(gè)參數(shù)為負(fù)數(shù)，那么，相當(dāng)于將數(shù)值精確到小數(shù)點(diǎn)之前的位數(shù)。小數(shù)點(diǎn)之前的位數(shù)。 select round(2745

21、, -1) result from dual; 14.2.3 ceil()14.2.3 ceil()函數(shù)函數(shù)向上取整向上取整ceil()函數(shù)只能有一個(gè)參數(shù)。該函數(shù)將參數(shù)向上取整，函數(shù)只能有一個(gè)參數(shù)。該函數(shù)將參數(shù)向上取整，以獲得大于等于該參數(shù)的最小整數(shù)。以獲得大于等于該參數(shù)的最小整數(shù)。select ceil(21.897) result from dual; 需要注意的是該函數(shù)針對負(fù)數(shù)的運(yùn)算：需要注意的是該函數(shù)針對負(fù)數(shù)的運(yùn)算： select ceil(-21.897) result from dual; 因?yàn)橐驗(yàn)閏eil()函數(shù)返回的是大于等于參數(shù)的最小整數(shù)，所函數(shù)返回的是大于等于參數(shù)的最小整數(shù)

22、，所以，該函數(shù)返回的并非以，該函數(shù)返回的并非-22，而是，而是-21。14.2.4 floor()14.2.4 floor()函數(shù)函數(shù)向下取整向下取整與與ceil函數(shù)相反，函數(shù)相反，floor()函數(shù)用于返回小于等于某個(gè)數(shù)函數(shù)用于返回小于等于某個(gè)數(shù)值的最大整數(shù)。值的最大整數(shù)。select floor(21.897) result from dual; select floor(-21.897) result from dual;14.2.5 mod ()14.2.5 mod ()函數(shù)函數(shù)取模操作取模操作mod()函數(shù)有兩個(gè)參數(shù)，第一個(gè)參數(shù)為被除數(shù)，第二個(gè)函數(shù)有兩個(gè)參數(shù)，第一個(gè)參數(shù)為被除數(shù)，第二

23、個(gè)參數(shù)為除數(shù)。參數(shù)為除數(shù)。mod()函數(shù)的實(shí)際功能為獲得兩數(shù)相除之后的函數(shù)的實(shí)際功能為獲得兩數(shù)相除之后的余數(shù)。余數(shù)。select mod(5,2) result from dual; 14.2.6 sign()14.2.6 sign()函數(shù)函數(shù)返回?cái)?shù)字的正負(fù)性返回?cái)?shù)字的正負(fù)性sign()函數(shù)只有一個(gè)參數(shù)。該函數(shù)將返回參數(shù)的正負(fù)性函數(shù)只有一個(gè)參數(shù)。該函數(shù)將返回參數(shù)的正負(fù)性。若返回值為。若返回值為1，表示該參數(shù)大于，表示該參數(shù)大于0；若返回值為；若返回值為-1，表示該，表示該參數(shù)小于參數(shù)小于0；若返回值為；若返回值為0，表示該參數(shù)等于，表示該參數(shù)等于0。select sign(8) result

24、from dual; select sign(-8) result from dual; select sign(0) result from dual; sign()函數(shù)為判斷兩個(gè)數(shù)值的大小關(guān)系提供了方便。因函數(shù)為判斷兩個(gè)數(shù)值的大小關(guān)系提供了方便。因?yàn)樵跒樵趏racle中，利用類似中，利用類似if else的結(jié)構(gòu)來判斷兩個(gè)數(shù)值之間的結(jié)構(gòu)來判斷兩個(gè)數(shù)值之間的大小關(guān)系，并不像編程語言中那樣方便，而且極易造成代的大小關(guān)系，并不像編程語言中那樣方便，而且極易造成代碼的復(fù)雜化。碼的復(fù)雜化。14.2.7 sqrt14.2.7 sqrt() ()函數(shù)函數(shù)返回?cái)?shù)字的平方根返回?cái)?shù)字的平方根sqrt()函數(shù)也只

25、有一個(gè)參數(shù)。該函數(shù)用于返回參數(shù)的平函數(shù)也只有一個(gè)參數(shù)。該函數(shù)用于返回參數(shù)的平方根。可以利用方根。可以利用round()函數(shù)和函數(shù)和sqrt()函數(shù)返回某個(gè)數(shù)值的近函數(shù)返回某個(gè)數(shù)值的近似平方根。似平方根。select round(sqrt(2), 3) result from dual; 14.2.8 power()14.2.8 power()函數(shù)函數(shù)乘方運(yùn)算乘方運(yùn)算power()函數(shù)有兩個(gè)參數(shù)。該函數(shù)用于實(shí)現(xiàn)數(shù)值的乘方函數(shù)有兩個(gè)參數(shù)。該函數(shù)用于實(shí)現(xiàn)數(shù)值的乘方運(yùn)算。運(yùn)算。select power(6, 2) result from dual; 14.2.9 trunc14.2.9 trunc()

26、 ()函數(shù)函數(shù)截取數(shù)字截取數(shù)字trunc()函數(shù)用于截取部分?jǐn)?shù)字。其工作機(jī)制非常類似函數(shù)用于截取部分?jǐn)?shù)字。其工作機(jī)制非常類似于于round()函數(shù)。與函數(shù)。與round()函數(shù)不同的是，該函數(shù)不對數(shù)值函數(shù)不同的是，該函數(shù)不對數(shù)值做四舍五入處理，而是直接截取。做四舍五入處理，而是直接截取。select trunc(2745.173, 2) result from dual; 保留位數(shù)的值可以為保留位數(shù)的值可以為0，當(dāng)該參數(shù)的值為，當(dāng)該參數(shù)的值為0時(shí)，將保留時(shí)，將保留到整數(shù)。到整數(shù)。 select trunc(2745.173) result from dual; 當(dāng)保留位數(shù)小于當(dāng)保留位數(shù)小于0時(shí)

27、，表示保留到小數(shù)點(diǎn)之前的位數(shù)。時(shí)，表示保留到小數(shù)點(diǎn)之前的位數(shù)。 select trunc(2745.173, -1) result from dual; 14.2.10 vsize14.2.10 vsize() ()函數(shù)函數(shù)返回?cái)?shù)據(jù)的存儲(chǔ)空間返回?cái)?shù)據(jù)的存儲(chǔ)空間vsize()函數(shù)根據(jù)數(shù)據(jù)庫的存儲(chǔ)格式，來返回其所占用函數(shù)根據(jù)數(shù)據(jù)庫的存儲(chǔ)格式，來返回其所占用的存儲(chǔ)空間的字節(jié)數(shù)。的存儲(chǔ)空間的字節(jié)數(shù)。select vsize(abc123) from dual; 注意與說明：注意與說明：vsize()函數(shù)在返回的是函數(shù)在返回的是Oracle實(shí)際存儲(chǔ)實(shí)際存儲(chǔ)數(shù)據(jù)的字節(jié)數(shù)，在實(shí)際開發(fā)中使用的幾率也較小。讀者

28、可以數(shù)據(jù)的字節(jié)數(shù)，在實(shí)際開發(fā)中使用的幾率也較小。讀者可以不必了解不必了解Oracle本身的存儲(chǔ)機(jī)制。本身的存儲(chǔ)機(jī)制。14.2.11 to_number14.2.11 to_number() ()函數(shù)函數(shù)將字符串轉(zhuǎn)換為將字符串轉(zhuǎn)換為數(shù)值類型數(shù)值類型to_number()函數(shù)可以將字符串轉(zhuǎn)換為數(shù)值型。函數(shù)可以將字符串轉(zhuǎn)換為數(shù)值型。select to_number(257.90) result from dual; 需要注意的是，被轉(zhuǎn)換的字符串必須符合數(shù)值類型格需要注意的是，被轉(zhuǎn)換的字符串必須符合數(shù)值類型格式。如果被轉(zhuǎn)換的字符串不符合數(shù)值型格式，式。如果被轉(zhuǎn)換的字符串不符合數(shù)值型格式，Oracle將

29、拋出將拋出錯(cuò)誤提示。錯(cuò)誤提示。select to_number(a) result from dual;14.3 Oracle14.3 Oracle中的日期函數(shù)中的日期函數(shù)Oracle提供了豐富的日期函數(shù)。利用日期函數(shù)可以靈提供了豐富的日期函數(shù)。利用日期函數(shù)可以靈活的對日期進(jìn)行運(yùn)算。活的對日期進(jìn)行運(yùn)算。14.3.1 to_date14.3.1 to_date() ()函數(shù)函數(shù)將字符串轉(zhuǎn)換為日期將字符串轉(zhuǎn)換為日期型型to_date()函數(shù)用于將字符串轉(zhuǎn)換為日期。被轉(zhuǎn)換的字函數(shù)用于將字符串轉(zhuǎn)換為日期。被轉(zhuǎn)換的字符串必須符合特定的日期格式。符串必須符合特定的日期格式。select to_date(1

30、2/02/09, mm/dd/yy) result from dual; 14.3.2 add_months14.3.2 add_months() ()函數(shù)函數(shù)為日期加上特定為日期加上特定月份月份add_months()函數(shù)將為日期添加特定月份，并獲得新函數(shù)將為日期添加特定月份，并獲得新的日期。的日期。select to_char(add_months(sysdate, 2), yyyy-mm-dd) result from dual;14.3.3 last_day14.3.3 last_day() ()函數(shù)函數(shù)返回特定日期所在返回特定日期所在月的最后一天月的最后一天last_day()函數(shù)將

31、接受一個(gè)日期參數(shù)。該函數(shù)首先獲得函數(shù)將接受一個(gè)日期參數(shù)。該函數(shù)首先獲得日期參數(shù)所在月的信息，然后獲得該月最后一天的日期。日期參數(shù)所在月的信息，然后獲得該月最后一天的日期。select to_char(last_day(sysdate), yyyy-mm-dd) result from dual;可以綜合利用可以綜合利用add_months()函數(shù)來獲得若干月之后的函數(shù)來獲得若干月之后的月份的最后一天。月份的最后一天。 select to_char(last_day(add_months(sysdate, 3), yyyy-mm-dd) result from dual;14.3.4 month

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

33、02-08, yyyy-mm-dd), to_date(2009-03-08, yyyy-mm-dd) result from dual; 14.3.5 current_date14.3.5 current_date() ()函數(shù)函數(shù)返回當(dāng)前會(huì)話返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前日期時(shí)區(qū)的當(dāng)前日期current_date()函數(shù)用于返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前日期函數(shù)用于返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前日期。select sessiontimezone, to_char(current_date, yyyy-mm-dd hh:mi:ss) result from dual; 注意與說明：注意與說明：current_dat

34、e等無參數(shù)函數(shù)作為等無參數(shù)函數(shù)作為Oracle的關(guān)鍵字存在。在使用時(shí)，不能為其添加小括號。即的關(guān)鍵字存在。在使用時(shí)，不能為其添加小括號。即select current_date() from dual是錯(cuò)誤的是錯(cuò)誤的SQL語句。語句。14.3.6 current_timestamp14.3.6 current_timestamp() ()函數(shù)函數(shù)返回當(dāng)前返回當(dāng)前會(huì)話時(shí)區(qū)的當(dāng)前時(shí)間戳?xí)挄r(shí)區(qū)的當(dāng)前時(shí)間戳current_timestamp()函數(shù)用于返回當(dāng)前會(huì)話時(shí)的區(qū)時(shí)函數(shù)用于返回當(dāng)前會(huì)話時(shí)的區(qū)時(shí)間戳。可以結(jié)合間戳。可以結(jié)合sessiontimezone來查看其用法。來查看其用法。select s

35、essiontimezone, current_timestamp from dual;14.3.7 extract()14.3.7 extract()函數(shù)函數(shù)返回日期的某個(gè)域返回日期的某個(gè)域日期由若干域組成，例如年、月、日、小時(shí)等等。日期由若干域組成，例如年、月、日、小時(shí)等等。extract()函數(shù)可以返回這些域的具體值。為了使用該函數(shù)，函數(shù)可以返回這些域的具體值。為了使用該函數(shù)，除了要指定原日期外，還應(yīng)該指定要返回的域名。除了要指定原日期外，還應(yīng)該指定要返回的域名。select extract(year from sysdate) result from dual;需要注意的是，需要注意的

36、是，year、month、day域只能從日期（如域只能從日期（如sysdate）中獲得，而）中獲得，而hour、minute、second只能從時(shí)間只能從時(shí)間型（如型（如systimestamp）中獲得。）中獲得。 14.4 Oracle14.4 Oracle中的聚合函數(shù)中的聚合函數(shù)所謂聚合函數(shù)是指針對多條記錄的函數(shù)。所謂聚合函數(shù)是指針對多條記錄的函數(shù)。Oracle最常最常用的聚合函數(shù)包括，用的聚合函數(shù)包括，max()、min()、avg()、sum()和和count()函數(shù)。本節(jié)將講述這些函數(shù)的用法。函數(shù)。本節(jié)將講述這些函數(shù)的用法。14.4.1 max()14.4.1 max()函數(shù)函數(shù)求最

37、大值求最大值max()函數(shù)用于獲得記錄集在某列的最大值。例如，為函數(shù)用于獲得記錄集在某列的最大值。例如，為了返回員工最高工資，可以利用了返回員工最高工資，可以利用max()函數(shù)。函數(shù)。select max(salary) max_salary from t_salary; 需要注意的是，聚合函數(shù)往往是返回記錄集的統(tǒng)計(jì)值需要注意的是，聚合函數(shù)往往是返回記錄集的統(tǒng)計(jì)值，因此，不能與其中的單條記錄同時(shí)出現(xiàn)。例如，不能將，因此，不能與其中的單條記錄同時(shí)出現(xiàn)。例如，不能將max(salary)與具體列一起查詢。與具體列一起查詢。select employee_id, max(salary) max_sa

38、lary from t_salary; select distinct e.employee_name, s.salary from t_employees e, t_salary swhere e.employee_id = s.employee_id and s.salary = (select max(salary) from t_salary)14.4.2 min()14.4.2 min()函數(shù)函數(shù)求最小值求最小值min()函數(shù)可以用來獲得記錄集在某列上的最小值，其函數(shù)可以用來獲得記錄集在某列上的最小值，其功能與功能與max()函數(shù)相反。函數(shù)相反。select distinct e.e

39、mployee_name, s.salary from t_employees e, t_salary swhere e.employee_id = s.employee_id and s.salary = (select min(salary) from t_salary)14.4.3 avg14.4.3 avg() ()函數(shù)函數(shù)求平均值求平均值avg()函數(shù)用于獲得記錄集在某列上的平均值。函數(shù)用于獲得記錄集在某列上的平均值。select e.employee_name, avg(salary)from t_employees e, t_salary swhere e.employee_id

40、 = s.employee_idgroup by e.employee_id, e.employee_name14.4.4 sum()14.4.4 sum()函數(shù)函數(shù)求和求和sum()函數(shù)用于獲得結(jié)果集上某列值的和。函數(shù)用于獲得結(jié)果集上某列值的和。select e.employee_name, sum(salary)from t_employees e, t_salary swhere e.employee_id = s.employee_idgroup by e.employee_id, e.employee_name14.4.5 count()14.4.5 count()函數(shù)函數(shù)獲得記錄數(shù)

41、獲得記錄數(shù)count()函數(shù)的作用對象同樣為記錄集。與其他聚合函函數(shù)的作用對象同樣為記錄集。與其他聚合函數(shù)不同的是，數(shù)不同的是，count()函數(shù)可以有三種方式來進(jìn)行計(jì)數(shù)：函數(shù)可以有三種方式來進(jìn)行計(jì)數(shù)：count(*)計(jì)算行數(shù)、計(jì)算行數(shù)、count(column)計(jì)算某列和計(jì)算某列和count(1)累加累加1。insert into t_employees values (16, null, null,null);select count(*) from t_employees; select count(employee_id) from t_employees;select count(e

42、mployee_name) from t_employees;select count(1) from t_employees; 一般來說，利用一般來說，利用count(1)進(jìn)行計(jì)數(shù)的速度最快，但是特進(jìn)行計(jì)數(shù)的速度最快，但是特別注意的是，預(yù)期的結(jié)果是針對整行數(shù)據(jù)，還是某列的數(shù)據(jù)別注意的是，預(yù)期的結(jié)果是針對整行數(shù)據(jù)，還是某列的數(shù)據(jù)。14.5 Oracle14.5 Oracle中的其他函數(shù)中的其他函數(shù)除了數(shù)值函數(shù)、字符串函數(shù)、日期函數(shù)和聚合函數(shù)外除了數(shù)值函數(shù)、字符串函數(shù)、日期函數(shù)和聚合函數(shù)外，Oracle還提供了其他功能性更強(qiáng)的函數(shù)。本節(jié)將介紹還提供了其他功能性更強(qiáng)的函數(shù)。本節(jié)將介紹decode(

43、)、nvl()和和cast()函數(shù)。函數(shù)。14.5.1 decode()14.5.1 decode()函數(shù)函數(shù)多值判斷多值判斷decode()函數(shù)用于多值判斷。其執(zhí)行過程類似于解碼函數(shù)用于多值判斷。其執(zhí)行過程類似于解碼操作。該函數(shù)最常見的應(yīng)用為，實(shí)現(xiàn)類似操作。該函數(shù)最常見的應(yīng)用為，實(shí)現(xiàn)類似if else的功能。例的功能。例如，可以利用如，可以利用decode()函數(shù)為員工工資添加標(biāo)識，工資大于函數(shù)為員工工資添加標(biāo)識，工資大于6000者為高收入，其余的為一般收入。者為高收入，其余的為一般收入。select e.employee_id, e.employee_name, decode(sign(a

44、vg(s.salary) - 6000),1, 高收入高收入, 一般收入一般收入) incommingfrom t_employees e, t_salary swhere e.employee_id = s.employee_idgroup by e.employee_id, e.employee_name 14.5.2 nvl14.5.2 nvl() ()函數(shù)函數(shù)為空值重新賦值為空值重新賦值nvl()函數(shù)用于處理某列的值。該函數(shù)有兩個(gè)參數(shù)，第函數(shù)用于處理某列的值。該函數(shù)有兩個(gè)參數(shù)，第一個(gè)參數(shù)為要處理的列。如果其值為空，則返回第二個(gè)參數(shù)一個(gè)參數(shù)為要處理的列。如果其值為空，則返回第二個(gè)參數(shù)的值

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

46、迎的結(jié)果，那么可以利用歡迎的結(jié)果，那么可以利用nvl()函數(shù)進(jìn)行處理。函數(shù)進(jìn)行處理。select e.employee_id, nvl(e.employee_name, 未知未知) employee_name, nvl(sum(s.salary), 0) salaryfrom t_employees e, t_salary swhere e.employee_id = s.employee_id(+)group by e.employee_id, e.employee_name14.5.3 cast()14.5.3 cast()函數(shù)函數(shù)強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型cast()函數(shù)用于強(qiáng)制轉(zhuǎn)

47、換數(shù)據(jù)類型。函數(shù)用于強(qiáng)制轉(zhuǎn)換數(shù)據(jù)類型。Oracle會(huì)根據(jù)操作符來會(huì)根據(jù)操作符來自動(dòng)進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)換，例如：自動(dòng)進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)換，例如：select 123 + 200 result from dual;Oracle會(huì)根據(jù)運(yùn)算符會(huì)根據(jù)運(yùn)算符“+”將將123轉(zhuǎn)換為數(shù)值型轉(zhuǎn)換為數(shù)值型123。 select 123 | 200 result from dual;Oracle會(huì)根據(jù)運(yùn)算符會(huì)根據(jù)運(yùn)算符“|”將數(shù)字將數(shù)字200轉(zhuǎn)換為字符串轉(zhuǎn)換為字符串200。 cast()函數(shù)最常用的場景是轉(zhuǎn)換列的數(shù)據(jù)類型，以創(chuàng)建新表函數(shù)最常用的場景是轉(zhuǎn)換列的數(shù)據(jù)類型，以創(chuàng)建新表create table tmp_sala

48、ry asselect cast(salary_id as varchar2(20) salary_id, cast(employee_id as varchar2(20) employee_id, cast(month as varchar2(20) month, cast(salary as varchar2(20) salaryfrom t_salarydesc tmp_salary; 14.6 Oracle14.6 Oracle中的運(yùn)算表達(dá)式中的運(yùn)算表達(dá)式Oracle中的常用運(yùn)算包括：數(shù)學(xué)運(yùn)算、邏輯運(yùn)算和按中的常用運(yùn)算包括：數(shù)學(xué)運(yùn)算、邏輯運(yùn)算和按位運(yùn)算。本節(jié)將通過范例著重講述這三種運(yùn)算

49、的常用運(yùn)算符位運(yùn)算。本節(jié)將通過范例著重講述這三種運(yùn)算的常用運(yùn)算符和運(yùn)算規(guī)則。和運(yùn)算規(guī)則。14.6.1 14.6.1 數(shù)學(xué)運(yùn)算數(shù)學(xué)運(yùn)算數(shù)學(xué)運(yùn)算是最常用的運(yùn)算方式，數(shù)學(xué)運(yùn)算是最常用的運(yùn)算方式，Oracle中的數(shù)學(xué)運(yùn)算符包括：中的數(shù)學(xué)運(yùn)算符包括：+、-、*、/，分別代表了加、減、乘除運(yùn)算。在使用數(shù)學(xué)運(yùn)算時(shí)，分別代表了加、減、乘除運(yùn)算。在使用數(shù)學(xué)運(yùn)算時(shí)，Oracle會(huì)自會(huì)自動(dòng)將其他數(shù)據(jù)類型轉(zhuǎn)換為數(shù)值型，然后再參與運(yùn)算。動(dòng)將其他數(shù)據(jù)類型轉(zhuǎn)換為數(shù)值型，然后再參與運(yùn)算。select 5+3 result from dual;select 5-3 result from dual;select 5*2 res

50、ult from dual;select 5/2 result from dual;需要注意的是，任何一種運(yùn)算符與需要注意的是，任何一種運(yùn)算符與null的運(yùn)算結(jié)果均為的運(yùn)算結(jié)果均為null。 select 5+null result from dual;select 5-null result from dual;select 5*null result from dual;select 5/null result from dual;14.6.2 14.6.2 邏輯運(yùn)算邏輯運(yùn)算Oracle中的邏輯運(yùn)算包括：中的邏輯運(yùn)算包括：大于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；：大于運(yùn)算，可用于數(shù)值型

51、、日期型和字符串類型；=：大于等于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；：大于等于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；：小于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；：小于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；=：大于等于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；：大于等于運(yùn)算，可用于數(shù)值型、日期型和字符串類型；=：等于，可用于數(shù)值型、日期型和字符串類型；：等于，可用于數(shù)值型、日期型和字符串類型；：不等于，可用于數(shù)值型、日期型和字符串類型；：不等于，可用于數(shù)值型、日期型和字符串類型；!=：與：與用法相同；用法相同；NOT：取反操作；：取反操作；AND：布爾值的與操作；：布爾值的與操作；OR

52、：布爾值的或操作。：布爾值的或操作。14.6.2 14.6.2 邏輯運(yùn)算邏輯運(yùn)算需要注意的是，需要注意的是，Oracle中的邏輯運(yùn)算符只能作為條件中的邏輯運(yùn)算符只能作為條件判斷，并不返回值。為了查詢工資在判斷，并不返回值。為了查詢工資在5000-7000之間的記錄之間的記錄，可以利用邏輯運(yùn)算符來組合查詢條件。，可以利用邏輯運(yùn)算符來組合查詢條件。select * from t_salary where salary=5000 and salary=7000; 對于對于null值，需要特別注意的是，無論使用哪種運(yùn)算符值，需要特別注意的是，無論使用哪種運(yùn)算符，結(jié)果都會(huì)返回，結(jié)果都會(huì)返回null。當(dāng)比

53、較的結(jié)果為。當(dāng)比較的結(jié)果為null，并作為條件出，并作為條件出現(xiàn)時(shí)，現(xiàn)時(shí)，Oracle都會(huì)將其解釋為都會(huì)將其解釋為false。select 1 result from dual where 1=null;select 1 result from dual where 1null; select 1 result from dual where null=null; select 1 result from dual where nullnull;14.6.3 14.6.3 位運(yùn)算位運(yùn)算 從從Oracle8i開始，系統(tǒng)已經(jīng)提供了位運(yùn)算符。最常開始，系統(tǒng)已經(jīng)提供了位運(yùn)算符。最常用的莫過于用的莫過于

54、bitand運(yùn)算符。運(yùn)算符。select bitand(192, 100) result from dual; 14.7 Oracle14.7 Oracle中的特殊判式中的特殊判式除了邏輯運(yùn)算之外，除了邏輯運(yùn)算之外，Oracle提供了一些特殊判式。這提供了一些特殊判式。這些判式可以用來生成更加復(fù)雜和靈活的查詢條件。本節(jié)將著些判式可以用來生成更加復(fù)雜和靈活的查詢條件。本節(jié)將著重介紹以下幾種判式。重介紹以下幾種判式。Between：取值范圍。：取值范圍。In：集合成員測試。：集合成員測試。Like：模式匹配。：模式匹配。is null：空值判斷。：空值判斷。all，some，any：數(shù)量判斷。：數(shù)

55、量判斷。exists：存在性判斷。：存在性判斷。14.7.1 between14.7.1 between范圍測試范圍測試between判式，用于判斷某個(gè)值是否在另外兩個(gè)值之判式，用于判斷某個(gè)值是否在另外兩個(gè)值之間。這些值可以為數(shù)值型、字符串和日期型。使用間。這些值可以為數(shù)值型、字符串和日期型。使用betwwen判式來獲得判式來獲得ID號在號在1-5之間的員工信息。之間的員工信息。select * from t_employees where employee_id between 1 and 5; betwwen判式同樣可以應(yīng)用于字符串和日期型。字符判式同樣可以應(yīng)用于字符串和日期型。字符串是按

56、照字母表的順序進(jìn)行比較，而日期型是按照日期的先串是按照字母表的順序進(jìn)行比較，而日期型是按照日期的先后順序進(jìn)行比較。后順序進(jìn)行比較。select * from t_employees where b between b and c;select * from t_employees where b between bc and c; 注意與說明：注意與說明：between判式與判式與=、=的組合是等價(jià)關(guān)的組合是等價(jià)關(guān)系。但是，效率上要比后者差。系。但是，效率上要比后者差。14.7.2 in14.7.2 in集合成員測試集合成員測試in用于判斷某個(gè)值是否一個(gè)集合的成員。用于判斷某個(gè)值是否一個(gè)集合的

57、成員。select * from t_employees where status in(NEW, ACT); 值得注意的是，值得注意的是，in判式中的集合的成員的數(shù)據(jù)類型可以判式中的集合的成員的數(shù)據(jù)類型可以不一致，例如，不一致，例如，select * from t_employees where status in(NEW, ACT, sysdate, 1)中的數(shù)據(jù)類型包含了字符串、中的數(shù)據(jù)類型包含了字符串、日期型和數(shù)值型。日期型和數(shù)值型。14.7.3 like14.7.3 like模式匹配模式匹配like判式的最大特點(diǎn)在于，可以使用通配符。其通常的判式的最大特點(diǎn)在于，可以使用通配符。其通常

58、的應(yīng)用場景為處理模糊查詢。應(yīng)用場景為處理模糊查詢。select * from t_employees where employee_name like 鐘鐘%; 如果要求字符串中含有原義字符如果要求字符串中含有原義字符“%”，例如，含有百，例如，含有百分比的字符串。那么，分比的字符串。那么，like判式應(yīng)寫作：判式應(yīng)寫作：like 鐘鐘% escape 。Oracle會(huì)首先解釋會(huì)首先解釋escape關(guān)鍵字，并將其后的字符關(guān)鍵字，并將其后的字符“”解釋為轉(zhuǎn)義字符。那么在解釋為轉(zhuǎn)義字符。那么在“鐘鐘%”中的中的“%”不再表示通配符不再表示通配符，而是表示原義字符，而是表示原義字符“%”。“_”（下

59、劃線）是可用于（下劃線）是可用于like判式的另一個(gè)通配符，該判式的另一個(gè)通配符，該通配符表示一個(gè)任意的字符。通配符表示一個(gè)任意的字符。14.7.4 is null14.7.4 is null空值判斷空值判斷在邏輯判斷中，對于列值為空的判斷，不能使用在邏輯判斷中，對于列值為空的判斷，不能使用=或者或者。oracle對與空值的判斷提供了專門的判式對與空值的判斷提供了專門的判式is null。例如，為了獲取表例如，為了獲取表t_employees中員工信息不全的記錄，可中員工信息不全的記錄，可以利用如下所示的查詢語句。以利用如下所示的查詢語句。select * from t_employees w

60、here employee_id is null or employee_name is null or work_years is null or status is null; 14.7.5 exists14.7.5 exists存在性判斷存在性判斷in判式用于判斷表的列值是否存在于列表（集合）中。判式用于判斷表的列值是否存在于列表（集合）中。而而exists判式則可用于判斷查詢結(jié)果集合是否為空。例如，判式則可用于判斷查詢結(jié)果集合是否為空。例如，為了查詢出表為了查詢出表t_employees所存儲(chǔ)的員工信息中，哪些員工所存儲(chǔ)的員工信息中，哪些員工存在于工資表中，即可利用存在于工資表中，即可