oracle-函数

数据库开发技术福建工程学院第8章函数本章要点了解SQL函数的分类；掌握常用单行函数的使用方法；了解数据类型隐式转换的原则，掌握显式转换函数的使用方法；SQL函数函数是SQL的一个强大的特征，使用情形：对数据执行计算改变特定的数据项生成数据行分组输出格式化日期和数值的显示形式对列数据类型进行转换SQL函数可以接收多个参数但通常只返回一个值DUAL表DUAL表属于SYS用户并且可以被所有的用户使用，它包含一个列DUMMY，并且只有一行值是未知的数据。当您只想一次得到一个值，DUAL表是非常有用的，例如，不是来自用户数据的常量值、虚拟列或表达式。DUAL表通常用在一个语法完整的SELECT子句中，因为SELECT和FROM是必需的，并且个别的计算没必要从实际的表中选取。SQL函数函数输入参数1参数2参数n函数执行作用输出结果值两种类型的SQL函数函数单行函数多行函数两种类型的SQL函数单行函数这类函数只对单数据行进行操作，并且每行都返回一个结果，单行函数的不同类型：字符型数值型日期型转换函数多行函数这类函数对多数据行的群组进行操作，并且每组返回一个结果。单行函数操作数据项接收参数并返回一个结果对每个返回的行起作用每行返回一个结果可以改变数据类型能被嵌套function_name(column|expression,[arg1,arg2,...])单行函数转换字符数值日期通用单行函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPADTRIM大小写转换字符处理函数结果大小写转换函数转化字符串的大小写LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse使用大小写转换函数显示Blake的雇员编号、姓名和部门编号。SQLSELECTempno,ename,deptno2FROMemp3WHEREename='blake';norowsselectedEMPNOENAMEDEPTNO----------------------------7698BLAKE30SQLSELECTempno,ename,deptno2FROMemp3WHEREename=UPPER('blake');表字符函数函数名称功能实例结果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')ADCCONCAT('Good','String')SUBSTR('String',1,3)LENGTH('String')INSTR('String','r')INSTR('CORPORATEFLOORDOOR','OR',1,2)LPAD(sal,10,'*')TRIM('S'FROM'SSMITH')REPLACE('ABC','B','D')GoodStringStr635******5000MITHADC函数结果字符处理函数字符处理函数使用字符处理函数SQLSELECTename,CONCAT(ename,job),LENGTH(ename),2INSTR(ename,'A')3FROMemp4WHERESUBSTR(job,1,5)='SALES';ENAMECONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,'A')----------------------------------------------------------MARTINMARTINSALESMAN62ALLENALLENSALESMAN51TURNERTURNERSALESMAN60WARDWARDSALESMAN42如何利用CHR函数在字符串里加回车？Sqlselect'Welcometovisit'||chr(10)||'www.fjut.edu.cn'aswelcomefromdual;WELCOME--------------------------------Welcometovisitwww.fjut.edu.cn使用ceil函数：SELECTceil(2.35)FROMdual;执行结果：CEIL(2.35)-------------3说明：该函数求得大于等于2.35的最小整数，结果为3。数值型函数数值型函数使用floor函数：SELECTfloor(2.35)FROMdual;执行结果：FLOOR(2.35)----------------2说明：该函数求得小于等于2.35的最大整数，结果为2。数值型函数使用cos函数：SELECTcos(3.14159)FROMdual;执行结果：COS(3.14159)------------------1说明：cos函数的输入参数应为弧度，3.14159的cos值为-1。数值型函数求|sin(230o)|的值，保留一位小数:SELECTround(abs(sin(230*3.14159/180)),1)FROMdual;结果为：ROUND(ABS(SIN(230*3.14159/180)),1)---------------------------------------------------.8说明：先将230o转换成为弧度，然后进行计算求值。再求绝对值。再进行四舍五入，保留小数点后1位。数值函数ROUND:四舍五入到指定的小数位ROUND(45.926,2)45.93TRUNC:截取到指定的小数位TRUNC(45.926,2)45.92MOD:取余数MOD(1600,300)100使用ROUND函数SQLSELECTROUND(45.923,2),ROUND(45.923,0),2ROUND(45.923,-1)3FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650SQLSELECTTRUNC(45.923,2),TRUNC(45.923),2TRUNC(45.923,-1)3FROMDUAL;TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)-------------------------------------------45.924540使用TRUNC函数使用MOD函数计算工作为salesman的雇员的薪水和佣金相除后的剩余。SQLSELECTename,sal,comm,MOD(sal,comm)2FROMemp3WHEREjob='SALESMAN';ENAMESALCOMMMOD(SAL,COMM)-----------------------------------------MARTIN125014001250ALLEN1600300100TURNER150001500WARD1250500250使用SIGN函数SIGN函数的参数若是负数，则返回值是-1，若参数是正数，则返回值是1；若参数是0，则返回值是0。SIGN(-50)=-1SIGN(+43)=1SIGN(0)=0日期的处理Oracle是以一种内部的数值形式存储日期的，即：世纪、年、月、日、小时、分、秒。默认的日期形式是：DD-MON-RR.有效的日期范围是公元前4712年1月1日到公元9999年12月31日SYSDATE是一个可以返回日期和时间的函数DUAL是一个虚拟表，可用于查看SYSDATE等表达式.例：selectsysdatefromdual;日期的运算对一个日期型数据加上或减去一个数可以得到一个新的日期型数据。两个日期型数据相减得到这两个日期的间隔数。如要以小时相加则必须用小时数除以24得到的数据进行相加。对日期使用算术运算符SQLSELECTename,(SYSDATE-hiredate)/7WEEKS2FROMemp3WHEREdeptno=10;ENAMEWEEKS-------------------KING830.93709CLARK853.93709MILLER821.36566受雇了多少周日期函数函数功能实例结果months_between返回两个日期间的月份months_between('04-11月-05','11-1月-01')57.7741935add_months返回把月份数加到日期上的新日期add_months('06-2月-03',1)add_months('06-2月-03',-1)06-3月-0306-1月-03next_day返回指定日期后的星期对应的新日期next_day('06-2月-03','星期一')10-2月-03last_day返回指定日期所在的月的最后一天last_day('06-2月-03')28-2月-03round按指定格式对日期进行四舍五入round(to_date('13-2月-03'),'YEAR')round(to_date('13-2月-03'),'MONTH')round(to_date('13-2月-03'),'DAY')01-1月-0301-2月-0316-2月-03(按周四舍五入)trunc对日期按指定方式进行截断trunc(to_date('06-2月-03'),'YEAR')trunc(to_date('06-2月-03'),'MONTH')trunc(to_date('06-2月-03'),'DAY')01-1月-0301-2月-0302-2月-03(按周截断)日期函数两个日期之间间隔多少个月MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC向一个日期数据加一定的月份返回指定日期的下一个事件日返回某月的最后一天对日期进行四舍五入对日期进行截取函数描述EXTRACT(YEAR|MONTH|DAYFROMdate)从日期值中得出年、月或日NEW_TIME(date,existingtimezone,newtimezone)返回不同时区(例如EST或PST)之间的日期和时间•MONTHS_BETWEEN('01-9月-95','11-1月-94')使用日期函数•ADD_MONTHS('11-1月-08',6)•NEXT_DAY('01-4月-08','星期一')•LAST_DAY('01-9月-95')19.677419411-7月-0807-4月-08'30-9月-95'ROUND(to_date('25-6月-95'),'MONTH')01-7月-95ROUND(to_date('25-7月-95