第五章 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)（续）例：以首字母大写方式显示员工的姓名①完成首字母大写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)（续）例：以首字母小写方式显示员工的姓名①完成首字母小写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]):用于执行四舍五入运算。如果省略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:该函数返回系统时间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)（续）例：显示满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:返回会话时区的日期。例：selectcurrent_datefromdual;7current_timestamp:用于返回会话时区的日期时间

例：selectcurrent_timestampfromdual;Oracle内置函数（10）转换函数将一种数据类型转换成另一种数据类型，可以是隐式转换，即由系统自动完成，显示转换则需要调用转换函数。隐式转换：例：createtablet1(idint);

insertintot1values('10')系统自动将’10’转换为数值10例：createtablet2(idvarchar2(10));

insertintot2values(1);系统自动将数值1转换为‘1’Oracle内置函数（11）转换函数1to_char(d[,fmt[,’nlsparams’]])：用于将日期值和数字值转变为字符串，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’]])(续）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补齐；.：在指定位置显示小数点；，：在指定位置显示逗号；$