第五章 oracle常用函數

第五章常用函數計算機科學與工程學院

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

Oracle內置函數（1）Oracle內置函數（2）字符函數1

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

selectlower(ENAME),salfromscott.emp;2UPPER(char)返回字符串,并將所有的字符大寫。例：selectupper('AaBbCcDd')fromdual;例：將員工名字按大寫方式顯示selectUPPER(ENAME)FROMscott.EMP;Oracle內置函數（3）字符函數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內置函數（4）字符函數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內置函數（5）字符函數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內置函數（5）例：顯示正好有5個字符的員工的姓名selectenamefromscott.empwherelength(ename)=5;例：顯示所有員工姓名前3個字母selectsubstr(ename,1,3)fromscott.emp;Oracle內置函數（6）字符函數5

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

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

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

Oracle內置函數（8）數學函數5ceil（n)：返回大于或等于n的最小整數。

例：selectceil(12.56)fromdual;

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

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

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

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

selectadd_months(sysdate,5)fromdual;

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

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

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

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

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

insertintot2values(1);系統(tǒng)自動將數值1轉換為‘1’Oracle內置函數（11）轉換函數1to_char(d[,fmt[,’nlsparams’]])：用于將日期值和數字值轉變?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:兩位數字的年份，如2003年——03；

yyyy：4位數字的年份，如2003年——2003；year：英文全拼的年份，如2003年——twothousandthreeOracle內置函數（12）轉換函數1to_char(d[,fmt[,’nlsparams’]])(續(xù)）mm：兩位數字的月份，如3月——03；mon:簡拼的月份，如3月——MAR(英文），3月——3月（中文）；month:全拼的月份，例如3月——MARCH(英文），3月（中文）；DD：2位數字的天，如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內置函數（12）轉換函數2to_char(n,[fmt]):用于將數字值轉換成fmt格式的varchar2類型的值。如果省略fmt，則顯示實際的數字值。格式模型的元素如下：9：顯示數字，并且忽略前導0；

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