Oracle常用函数

目录1.数值型函数...........................................................................................................................22.字符型函数...........................................................................................................................23.日期函数...............................................................................................................................34.转换函数...............................................................................................................................45.数值转换符...........................................................................................................................76.其他函数...............................................................................................................................87.最大、最小值函数................................................................................................................98.创建临时表............................................................................................................................99.创建表值函数....................................................................................................................101.数值型函数abs:求绝对值函数,如:abs(−5)5sqrt:求平方根函数,如:sqrt(2)1.41421356power:求幂函数,如:power(2,3)8cos:求余弦三角函数,如:cos(3.14159)−1mod:求除法余数,如:mod(1600,300)100ceil:求大于等于某数的最小整数,如:ceil(2.35)3floor:求小于等于某数的最大整数,如:floor(2.35)2round:按指定精度对十进制数四舍五入,如:round(45.923,1),结果,45.9round(45.923,0),结果,46round(45.923,−1),结果,50trunc:按指定精度截断十进制数,如:trunc(45.923,1),结果,45.9trunc(45.923),结果,45trunc(45.923,−1),结果,40使用数值型函数练习。使用求绝对值函数abs。Sql代码1.SELECTabs(?5)FROMdual;执行结果：ABS(?5)---------------5说明：求?5的绝对值，结果为5。2.字符型函数ascii:获得字符的ASCII码,如:Ascii('A')65chr:返回与ASCII码相应的字符,如:Chr(65)Alower:将字符串转换成小写,如:lower('SQLCourse')sqlcourseupper:将字符串转换成大写,如:upper('SQLCourse')SQLCOURSEinitcap:将字符串转换成每个单词以大写开头,如:initcap('SQLcourse')SqlCourseconcat:连接两个字符串concat('SQL','Course')SQLCoursesubstr:给出起始位置和长度，返回子字符串,如:substr('String',1,3)Strlength:求字符串的长度length('Wellcom')7instr:给出起始位置和出现的次数，求子字符串在字符串中出现的位置,如:instr('String','r',1,1)3lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-')--------Hirpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-')Hi--------trim:在一个字符串中(字头或字尾)去除另一个字符串,如:trim('S'FROM'SSMITH')MITHreplace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC','B','D')ADC如果不知道表的字段内容是大写还是小写，可以转换后比较。输入并执行查询：Sql代码1.SELECTempno,ename,deptnoFROMemp2.WHERElower(ename)='blake';结果为：Sql代码1.EMPNOENAMEDEPTNO2.-------------------------------------------------3.7698BLAKE30说明：该查询将表中的雇员名转换成小写，与小写的blake进行比较。3.日期函数months_between:返回两个日期间的月份,如:months_between('04-11月-05','11-1月-01'),结果,57.7741935add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03add_months('06-2月-03',-1),结果,06-1月-03next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03nmlast_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03round(to_date('13-2月-03'),'DAY'),结果,16-2月-03(按周四舍五入)trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03(按周截断)返回2003年2月的最后一天。输入并执行查询：Sql代码1.SELECTlast_day('08-2月-03')FROMdual;返回结果为：Sql代码1.LAST_DAY('2.----------------3.28-2月-03说明：该函数给定参数为某月份的任意一天，返回时间为该月份的最后一天。本例中，参数为03年2月8号，返回日期为03年2月28日，是该月的最后一天。假定当前的系统日期是2003年2月6日，求再过1000天的日期。输入并执行查询：Sql代码1.SELECTsysdate+1000ASNEWDATEFROMdual;返回结果为：Sql代码1.NEWDATE2.----------------3.04-11月-05说明：该查询使用到了日期的加法运算，求经过一定天数后的新日期。假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。输入并执行查询：Sql代码1.SELECTename,round(sysdate-hiredate)DAYS2.FROMemp3.WHEREdeptno=10;返回结果为：ENAMEDAYS------------------------------------CLARK7913KING7752MILLER7685说明：该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。4.转换函数Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBERTo_char:转换成字符串类型,如:To_char(1234.5,'$9999.9'),结果:$1234.5To_date:转换成日期类型,如:To_date('1980-01-01','yyyy-mm-dd'),结果:01-1月-80To_number:转换成数值类型,如:To_number('1234.5'),结果:1234.5自动类型转换Oracle可以自动根据具体情况进行如下的转换：*字符串到数值。*字符串到日期。*数值到字符串。*日期到字符串。以下是自动转换的训练。自动转换字符型数据到数值型。输入并执行查询：Sql代码1.SELECT'12.5'+11FROMdual;执行结果为：Sql代码1.'12.5'+112.------------3.23.5说明：在本训练中，因为出现+运算符，说明进行的是算术运算，所以字符串'12.5'被自动转换成数值12.5，然后参加运算。自动转换数值型数据到字符型。执行以下查询：Sql代码1.SELECT'12.5'||11FROMdual;结果为：Sql代码1.'12.5'2.------3.12.511说明：在本训练中，因为出现||运算符，说明进行的是字符串连接运算，数值11被自动转换成字符串'11'，然后参加运算。日期类型转换将日期型转换成字符串时，可以按新的格式显示。如格式YYYY-MM-DDHH24:MI:SS表示“年-月-日小时:分钟:秒”。Oracle的日期类型是包含时间在内的。主要的日期格式字符的含义AM、PM:上午、下午,如:08AMD:数字表示的星期(1～7),如:1,2,3,4,5,6,7DD:数字表示月中的日期(1～31),如:1,2,3,…,31MM:两位数的月份,如:01,02,…,12Y、YY、YYY、YYYY年份的后几位,如:3,03,003,2003RR:解决Y2K问题的年度转换DY:简写的星期名,如:MON,TUE,FRI,…DAY:全拼的星期名,如:MONDAY,TUESDAY,…MON:简写的月份名,如:JAN,FEB,MAR,…MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…HH、HH12:12小时制的小时(1～12),如:1,2,3,…,12HH24:24小时制的小时(0～23),如:0,1,2,…,23MI:分(0～59),如:0,1,2,…,59SS:秒(0～59),如:0,1,2,…,59,./-;:原样显示的标点符号'TEXT':引号中的文本原样显示,如:TEXT将日期转换成带时间和星期的字符串并显示。执行以下查询：Sql代码1.SELECTTO_CHAR(sysdate,'YYYY-MM-DDHH24:MI:SSAMDY')FROMdual;结果为：Sql代码1.TO_CHAR(SYSDATE,'YYYY-MM-DDHH242.------------------------