第五章++++常用函數(shù)

1、第五章第五章 常用函數(shù)常用函數(shù)計算機科學與工程學院計算機科學與工程學院 2013年年3月月Dual Dual 表表：是一個虛擬表，用于查詢那些不屬于實際表里的內(nèi)容。：是一個虛擬表，用于查詢那些不屬于實際表里的內(nèi)容。例例： select 3+3 from dual ;select 3+3 from dual ;例例：查看當前用戶：查看當前用戶 select user from dual;select user from dual;例例：查看當前日期、時間：查看當前日期、時間 select sysdate from dual;select sysdate from dual; OracleOrac

2、le內(nèi)置函數(shù)（內(nèi)置函數(shù)（1 1）OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（2 2） 字符函數(shù)字符函數(shù)1 LOWER（char） 返回字符串返回字符串,并將所有的字符小寫。并將所有的字符小寫。 例例：select lower(AaBbCcDd)AaBbCcDd from dual; 例例：將：將emp表的員工姓名全部轉(zhuǎn)換為小寫表的員工姓名全部轉(zhuǎn)換為小寫 select lower(eNAME) FROM scott.EMP; 例例：查詢員工姓名和工資，將：查詢員工姓名和工資，將emp表的員工姓名全部小寫顯示表的員工姓名全部小寫顯示 select lower(ENAME) , sal from s

3、cott.emp;2 UPPER( char) 返回字符串返回字符串,并將所有的字符大寫。并將所有的字符大寫。 例例： select upper(AaBbCcDd) from dual; 例：例：將員工名字按大寫方式顯示將員工名字按大寫方式顯示 select UPPER(ENAME) FROM scott.EMP;OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（3 3）字符函數(shù)字符函數(shù)3 LENGTH ： 返回字符串的長度返回字符串的長度; 例例：顯示正好有：顯示正好有5個字符的員工姓名個字符的員工姓名 select * from scott.emp where length(ename) =5;

4、注意注意：ename字段為字段為varchar（10）類型，）類型，length(ename) 是實際長度而不是定是實際長度而不是定義時的長度，所以可以查詢到實際長度為義時的長度，所以可以查詢到實際長度為5的名字。的名字。 例例：顯示正好有：顯示正好有10個字符的員工姓名個字符的員工姓名 select * from scott.emp where length(ename) =10;4 SUBSTR(string,start,count) ：取子字符串：取子字符串,從從start開始開始,取取count個個 例例： select substr3,8) from dua

5、l; 例例：顯示所有員工姓名的前：顯示所有員工姓名的前3個字母個字母 select substr(ename ,1,3) from scott.emp; OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（4 4）字符函數(shù)字符函數(shù)4 4 SUBSTR(string,start,count) SUBSTR(string,start,count) （續(xù)）（續(xù)）例例：以首字母大寫方式顯示員工的姓名以首字母大寫方式顯示員工的姓名 完成首字母大寫完成首字母大寫 select upper(substr(ename,1,1) from scott.emp ;select upper(substr(ename,1,1

6、) from scott.emp ; 完成后面字母小寫完成后面字母小寫 select lower(substr(ename,2,length(ename)-1) from select lower(substr(ename,2,length(ename)-1) from scott.emp ;scott.emp ; 合并合并select upper(substr(ename,1,1) select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from scott.emp ;lower(substr(ename,

7、2,length(ename)-1) from scott.emp ; OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（5 5）字符函數(shù)字符函數(shù)4 SUBSTR(string,start,count) （續(xù)）（續(xù)）例：例：以首字母小寫方式顯示員工的姓名以首字母小寫方式顯示員工的姓名 完成首字母小寫完成首字母小寫 select lower (substr(ename,1,1) from scott.emp; 完成后面字母大寫完成后面字母大寫 select upper (substr(ename,2,length(ename)-1) from scott.emp; 合并合并select lower(s

8、ubstr(ename,1,1) | upper(substr(ename,2,length(ename)-1) from scott.emp;OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（5 5）例：例：顯示正好有顯示正好有5 5個字符的員工的姓名個字符的員工的姓名 select ename from scott.emp where length(ename)=5;select ename from scott.emp where length(ename)=5;例：例：顯示所有員工姓名前顯示所有員工姓名前3 3個字母個字母 select substr(ename,1,3) from scot

9、t.emp;select substr(ename,1,3) from scott.emp;OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（6 6）字符函數(shù)字符函數(shù)5 replace(char, search_string , replace_string) search_string:要查找的字符串；要查找的字符串； replace_string：用于替換用于替換search_string的字符串。的字符串。例：例： 顯示所有員工姓名，用顯示所有員工姓名，用“我是老鼠我是老鼠”替換所有替換所有A select replace( ename , A , 我是老鼠我是老鼠 ) from scott.

10、emp; OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（7 7） 數(shù)學函數(shù)數(shù)學函數(shù) 數(shù)學函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型。數(shù)學函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型。1 round（n,m):用于執(zhí)行四舍五入運算。如果省略用于執(zhí)行四舍五入運算。如果省略m,則四舍五入則四舍五入至整數(shù)位，如果至整數(shù)位，如果m是負數(shù)，則四舍五入到小數(shù)點前是負數(shù)，則四舍五入到小數(shù)點前m位，如果位，如果m是是正數(shù)，則四舍五入到小數(shù)點后正數(shù)，則四舍五入到小數(shù)點后m位。位。例例：查詢：查詢ALLEN的年薪的年薪 select (round(sal)+round(comm)*13 FROM EMP WHERE

11、 NAME= ALLEN; 注意：注意：此時此時COMM為空的列，年薪也為空，需用為空的列，年薪也為空，需用NVL函數(shù)處理函數(shù)處理例例：select round(15.567,2) from dual;select scott.emp.*,(round(sal)+round(nvl(comm,0)*13 FROM scott.EMP ; OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（8 8）數(shù)學函數(shù)數(shù)學函數(shù)2 trunc(n,m):用于截取數(shù)字，如果省略用于截取數(shù)字，如果省略m,則省去小數(shù)部分，如果則省去小數(shù)部分，如果m是正數(shù)，則截取到小數(shù)點后是正數(shù)，則截取到小數(shù)點后m位位;如果如果m是負數(shù)，則

12、截取到小數(shù)點是負數(shù)，則截取到小數(shù)點前前m位。位。 例例： select trunc(45.936) from dual; select trunc(45.936，2) from dual; select trunc(45.936,-1) from dual;3 MOD(n1,n2) 返回一個返回一個n1除以除以n2的余數(shù)的余數(shù) 例例：select mod(10,3), mod(3,3), mod(2,3) from dual;4 floor(n)：返回小于或等于返回小于或等于n的最大整數(shù)的最大整數(shù) 例例：select floor(12.56) from dual; OracleOracle內(nèi)置

13、函數(shù)（內(nèi)置函數(shù)（8 8）數(shù)學函數(shù)數(shù)學函數(shù)5 ceil（n) ：返回大于或等于返回大于或等于n的最小整數(shù)。的最小整數(shù)。 例例：select ceil(12.56) from dual; 例例: select ceil(comm) FROM EMP WHERE ename=ALLEN;OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（8 8）日期函數(shù)日期函數(shù) 默認情況下，日期格式為默認情況下，日期格式為dd_mod_yy1 sysdate:該函數(shù)返回系統(tǒng)時間該函數(shù)返回系統(tǒng)時間 select sysdate from dual;2 add_months(d,n):用于在指定日期上增加月數(shù)，其中用于在指定日

14、期上增加月數(shù)，其中d用于指定日期用于指定日期數(shù)據(jù)，數(shù)據(jù)，n可以是任意整數(shù)可以是任意整數(shù) 例例：返回當前日期后：返回當前日期后5個月的日期個月的日期 select add_months(sysdate ,5) from dual; 例例：顯示入職：顯示入職300個月以上的員工個月以上的員工 select * from emp where sysdateadd_months(HIREDATE,300);OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（9 9）2 add_months(d,n)（續(xù)）（續(xù)） 例：例：顯示滿顯示滿10年服務年限的員工的姓名和受雇傭日期年服務年限的員工的姓名和受雇傭日期 se

15、lect * from emp where sysdate=add_months(HIREDATE,10*12);例例：對于每個員工，顯示其加入公司的天數(shù)：對于每個員工，顯示其加入公司的天數(shù) select sysdate-hiredate from emp;3 last_day（d):該函數(shù)用于返回指定日期所處月份的最后一天該函數(shù)用于返回指定日期所處月份的最后一天 例例：select last_day(sysdate) from dual; 例例：select last_day(4-9月月-11) from dual; 例例：找出各月倒數(shù)第三天受雇傭的員工：找出各月倒數(shù)第三天受雇傭的員工 se

16、lect hiredate , name from emp where last_day(hiredate)- 2=hiredate;OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（9 9）4 next_day(d,char) :用于返回指定日期后第一個工作日（由用于返回指定日期后第一個工作日（由char指定指定)所所對應的星期。對應的星期。 例例： Select next_day(sysdate,星期一星期一) from dual;5 months_between (d1,d2)：返回：返回d1和和d2之間相差的月數(shù)，如果之間相差的月數(shù)，如果d1小于小于d2，則返回負數(shù)；如果，則返回負數(shù)；如果d

17、1和和d2的天數(shù)相同或都是月底，則返回為整數(shù)；的天數(shù)相同或都是月底，則返回為整數(shù)；否則否則oracle 以每月以每月31天為準，計算結(jié)果有小數(shù)部分天為準，計算結(jié)果有小數(shù)部分 例例：select months_between(sysdate,10_4月月_10) from dual;6 current_date: 返回會話時區(qū)的日期。返回會話時區(qū)的日期。 例例：select current_date from dual;7 current_timestamp :用于返回會話時區(qū)的日期時間用于返回會話時區(qū)的日期時間 例例： select current_timestamp from dual;Or

18、acleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（1010）轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) 將一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，可以是隱式轉(zhuǎn)換，即將一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，可以是隱式轉(zhuǎn)換，即由系統(tǒng)自動完成，顯示轉(zhuǎn)換則需要調(diào)用轉(zhuǎn)換函數(shù)。由系統(tǒng)自動完成，顯示轉(zhuǎn)換則需要調(diào)用轉(zhuǎn)換函數(shù)。隱式轉(zhuǎn)換：隱式轉(zhuǎn)換：例：例： create table t1 (id int); insert into t1 values(10) 系統(tǒng)自動將系統(tǒng)自動將10轉(zhuǎn)換為數(shù)值轉(zhuǎn)換為數(shù)值10例：例：create table t2 (id varchar2(10); insert into t2 values(1); 系統(tǒng)自動將數(shù)值系統(tǒng)自動將數(shù)

19、值1轉(zhuǎn)換為轉(zhuǎn)換為1OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（1111）轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)1 to_char(d,fmt,nlsparams)：用于將日期值和數(shù)字值轉(zhuǎn)變?yōu)樽址糜趯⑷掌谥岛蛿?shù)字值轉(zhuǎn)變?yōu)樽址?串，串，d用于指定日期值；用于指定日期值；fmt用于指定日期格式，如果省略，則使用默用于指定日期格式，如果省略，則使用默 認日期顯示格式；認日期顯示格式；nlsparams用于指定日期顯示語言，指定方式為用于指定日期顯示語言，指定方式為nls_date_language=language,如果省略，則使用默認的日期語如果省略，則使用默認的日期語言。言。 例：例：select to_char(s

20、ysdate,dd_mon_yy) from dual; 例例：select ename ,to_char(hiredate,yyyy_mm_dd:hh24:mi:ss) from scott.emp ; 例例：select ename ,to_char(hiredate,yyyy_mm_dd hh24:mi:ss), to_char(sal,l99999.99) from scott.emp ; 格式模型格式模型fmt有以下一些元素：有以下一些元素： yy :兩位數(shù)字的年份，如兩位數(shù)字的年份，如2003年年03 ； yyyy：4位數(shù)字的年份，如位數(shù)字的年份，如2003年年2003； year

21、：英文全拼的年份，如：英文全拼的年份，如2003年年two thousand threeOracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（1212）轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)1 to_char(d,fmt,nlsparams)(續(xù)）續(xù)） mm：兩位數(shù)字的月份，如兩位數(shù)字的月份，如3月月03； mon: 簡拼的月份，如簡拼的月份，如3月月MAR(英文），英文），3月月3月（中文）；月（中文）； month:全拼的月份，例如全拼的月份，例如3月月MARCH(英文），英文），3月（中文）；月（中文）； DD： 2位數(shù)字的天，如位數(shù)字的天，如30號號30； DDSPTH：英文全拼的天，例如：英文全拼的天，例如30號號

22、THIRTIETH DY：工作日的縮寫，例如：工作日的縮寫，例如2003年年3月月30日日SUNDAY(英文），星期日英文），星期日(中文）；中文）； DAY：工作日的全拼，例如：工作日的全拼，例如2003年年3月月30日日SUNDAY(英文），星期英文），星期日日(中文）；中文）； Hh24: 按照按照24小時格式顯示小時，如晚上小時格式顯示小時，如晚上8點點20； Hh12:按照按照12小時格式顯示小時，如晚上小時格式顯示小時，如晚上8點點08； Mi:顯示分鐘顯示分鐘 Ss :顯示秒；顯示秒；OracleOracle內(nèi)置函數(shù)（內(nèi)置函數(shù)（1212）轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)2 to_char(n,fmt) : 用于將數(shù)字值轉(zhuǎn)換成用于將數(shù)字值轉(zhuǎn)換成fmt格式的格式的varchar2類型的值。類型的值。如果省略如果省略fmt，則顯示實際的數(shù)字值。格式模型的元素如下：，則顯示實際的數(shù)字值。格式模型的元素如下： 9：顯示數(shù)字，并且忽略前導：顯示數(shù)字，并且忽略前導0； 0：顯示數(shù)字，如果位數(shù)不足，則用：顯示數(shù)字，如果位數(shù)不足，則用0補齊；補齊； .：在指定位置顯示小數(shù)點；：在指定位置顯示小數(shù)點； ，：在指定位置顯示逗號；：在指定位置顯示逗號； $ ：在數(shù)字前加美元符號；：在數(shù)字前加美元