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

第五章常用函數(shù)計算機科學(xué)與工程學(xué)院

2013年3月Dual表：是一個虛擬表，用于查詢那些不屬于實際表里的內(nèi)容。例：select3+3fromdual;例：查看當前用戶selectuserfromdual;例：查看當前日期、時間selectsysdatefromdual;

Oracle內(nèi)置函數(shù)（1）Oracle內(nèi)置函數(shù)（2）字符函數(shù)1

LOWER（char）返回字符串,并將所有的字符小寫。例：selectlower('AaBbCcDd')AaBbCcDdfromdual;例：將emp表的員工姓名全部轉(zhuǎn)換為小寫selectlower(eNAME)FROMscott.EMP;例：查詢員工姓名和工資，將emp表的員工姓名全部小寫顯示

selectlower(ENAME),salfromscott.emp;2UPPER(char)返回字符串,并將所有的字符大寫。例：selectupper('AaBbCcDd')fromdual;例：將員工名字按大寫方式顯示selectUPPER(ENAME)FROMscott.EMP;Oracle內(nèi)置函數(shù)（3）字符函數(shù)3LENGTH：返回字符串的長度;例：顯示正好有5個字符的員工姓名select*fromscott.empwherelength(ename)=5;注意：ename字段為varchar（10）類型，length(ename)是實際長度而不是定義時的長度，所以可以查詢到實際長度為5的名字。例：顯示正好有10個字符的員工姓名select*fromscott.empwherelength(ename)=10;4

SUBSTR(string,start,count)：取子字符串,從start開始,取count個例：selectsubstr(,3,8)fromdual;例：顯示所有員工姓名的前3個字母selectsubstr(ename,1,3)fromscott.emp;

Oracle內(nèi)置函數(shù)（4）字符函數(shù)4

SUBSTR(string,start,count)（續(xù)）例：以首字母大寫方式顯示員工的姓名①完成首字母大寫selectupper(substr(ename,1,1))fromscott.emp;②完成后面字母小寫selectlower(substr(ename,2,length(ename)-1))fromscott.emp;③合并selectupper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))fromscott.emp;

Oracle內(nèi)置函數(shù)（5）字符函數(shù)4

SUBSTR(string,start,count)（續(xù)）例：以首字母小寫方式顯示員工的姓名①完成首字母小寫selectlower(substr(ename,1,1))fromscott.emp;②完成后面字母大寫selectupper(substr(ename,2,length(ename)-1))fromscott.emp;③合并selectlower(substr(ename,1,1))||

upper(substr(ename,2,length(ename)-1))fromscott.emp;Oracle內(nèi)置函數(shù)（5）例：顯示正好有5個字符的員工的姓名selectenamefromscott.empwherelength(ename)=5;例：顯示所有員工姓名前3個字母selectsubstr(ename,1,3)fromscott.emp;Oracle內(nèi)置函數(shù)（6）字符函數(shù)5

replace(char,search_string,replace_string)search_string:要查找的字符串；replace_string：用于替換search_string的字符串。例：顯示所有員工姓名，用“我是老鼠”替換所有’A’selectreplace(ename,'A‘,'我是老鼠‘)fromscott.emp;

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

注意：此時COMM為空的列，年薪也為空，需用NVL函數(shù)處理例：selectround(15.567,2)fromdual;selectscott.emp.*,(round(sal)+round(nvl(comm,0)))*13FROMscott.EMP;Oracle內(nèi)置函數(shù)（8）數(shù)學(xué)函數(shù)2trunc(n,[m]):用于截取數(shù)字，如果省略m,則省去小數(shù)部分，如果m是正數(shù)，則截取到小數(shù)點后m位;如果m是負數(shù)，則截取到小數(shù)點前m位。例：①selecttrunc(45.936)fromdual;②selecttrunc(45.936，2)fromdual;③selecttrunc(45.936,-1)fromdual;3MOD(n1,n2)返回一個n1除以n2的余數(shù)例：selectmod(10,3),mod(3,3),mod(2,3)fromdual;4floor(n)：返回小于或等于n的最大整數(shù)例：selectfloor(12.56)fromdual;

Oracle內(nèi)置函數(shù)（8）數(shù)學(xué)函數(shù)5ceil（n)：返回大于或等于n的最小整數(shù)。

例：selectceil(12.56)fromdual;

例:selectceil(comm)FROMEMPWHEREename=‘ALLEN’;Oracle內(nèi)置函數(shù)（8）日期函數(shù)

默認情況下，日期格式為dd_mod_yy1sysdate:該函數(shù)返回系統(tǒng)時間selectsysdatefromdual;2

add_months(d,n):用于在指定日期上增加月數(shù)，其中d用于指定日期數(shù)據(jù)，n可以是任意整數(shù)

例：返回當前日期后5個月的日期

selectadd_months(sysdate,5)fromdual;

例：顯示入職300個月以上的員工

select*fromempwheresysdate>add_months(HIREDATE,300);Oracle內(nèi)置函數(shù)（9）2add_months(d,n)（續(xù)）例：顯示滿10年服務(wù)年限的員工的姓名和受雇傭日期select*fromempwheresysdate>=add_months(HIREDATE,10*12);例：對于每個員工，顯示其加入公司的天數(shù)selectsysdate-hiredatefromemp;3last_day（d):該函數(shù)用于返回指定日期所處月份的最后一天

例：selectlast_day(sysdate)fromdual;例：selectlast_day('4-9月-11')fromdual;例：找出各月倒數(shù)第三天受雇傭的員工selecthiredate,namefromempwherelast_day(hiredate)-2=hiredate;Oracle內(nèi)置函數(shù)（9）4next_day(d,char):用于返回指定日期后第一個工作日（由char指定)所對應(yīng)的星期。例：Selectnext_day(sysdate,'星期一')fromdual;5months_between(d1,d2)：返回d1和d2之間相差的月數(shù)，如果d1小于d2，則返回負數(shù)；如果d1和d2的天數(shù)相同或都是月底，則返回為整數(shù)；否則oracle以每月31天為準，計算結(jié)果有小數(shù)部分例：selectmonths_between(sysdate,'10_4月_10')fromdual;6current_date:返回會話時區(qū)的日期。例：selectcurrent_datefromdual;7current_timestamp:用于返回會話時區(qū)的日期時間

例：selectcurrent_timestampfromdual;Oracle內(nèi)置函數(shù)（10）轉(zhuǎn)換函數(shù)將一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，可以是隱式轉(zhuǎn)換，即由系統(tǒng)自動完成，顯示轉(zhuǎn)換則需要調(diào)用轉(zhuǎn)換函數(shù)。隱式轉(zhuǎn)換：例：createtablet1(idint);

insertintot1values('10')系統(tǒng)自動將’10’轉(zhuǎn)換為數(shù)值10例：createtablet2(idvarchar2(10));

insertintot2values(1);系統(tǒng)自動將數(shù)值1轉(zhuǎn)換為‘1’Oracle內(nèi)置函數(shù)（11）轉(zhuǎn)換函數(shù)1to_char(d[,fmt[,’nlsparams’]])：用于將日期值和數(shù)字值轉(zhuǎn)變?yōu)樽址?，d用于指定日期值；fmt用于指定日期格式，如果省略，則使用默認日期顯示格式；‘nlsparams’用于指定日期顯示語言，指定方式為‘nls_date_language=language’,如果省略，則使用默認的日期語言。例：selectto_char(sysdate,'dd_mon_yy')fromdual;

例：selectename,to_char(hiredate,'yyyy_mm_dd:hh24:mi:ss')fromscott.emp;

例：selectename,to_char(hiredate,'yyyy_mm_ddhh24:mi:ss'),to_char(sal,'l99999.99')fromscott.emp;格式模型fmt有以下一些元素：yy:兩位數(shù)字的年份，如2003年——03；

yyyy：4位數(shù)字的年份，如2003年——2003；year：英文全拼的年份，如2003年——twothousandthreeOracle內(nèi)置函數(shù)（12）轉(zhuǎn)換函數(shù)1to_char(d[,fmt[,’nlsparams’]])(續(xù)）mm：兩位數(shù)字的月份，如3月——03；mon:簡拼的月份，如3月——MAR(英文），3月——3月（中文）；month:全拼的月份，例如3月——MARCH(英文），3月（中文）；DD：2位數(shù)字的天，如30號——30；DDSPTH：英文全拼的天，例如30號——THIRTIETHDY：工作日的縮寫，例如2003年3月30日——SUNDAY(英文），星期日(中文）；DAY：工作日的全拼，例如2003年3月30日——SUNDAY(英文），星期日(中文）；Hh24:按照24小時格式顯示小時，如晚上8點——20；Hh12:按照12小時格式顯示小時，如晚上8點——08；Mi:顯示分鐘Ss:顯示秒；Oracle內(nèi)置函數(shù)（12）轉(zhuǎn)換函數(shù)2to_char(n,[fmt]):用于將數(shù)字值轉(zhuǎn)換成fmt格式的varchar2類型的值。如果省略fmt，則顯示實際的數(shù)字值。格式模型的元素如下：9：顯示數(shù)字，并且忽略前導(dǎo)0；

0：顯示數(shù)字，如果位數(shù)不足，則用0補齊；.：在指定位置顯示小數(shù)點；，：在指定位置顯示逗號；$