单行函数省公共课一等奖全国赛课获奖课件.pptx

《单行函数省公共课一等奖全国赛课获奖课件.pptx》由会员分享，可在线阅读，更多相关《单行函数省公共课一等奖全国赛课获奖课件.pptx（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第五章第五章 单行函数单行函数p本章关键点：本章关键点：SQL中不一样类型函数。中不一样类型函数。在在 SELECT 语句中使用字符，数字和语句中使用字符，数字和日期函数。日期函数。描述转换型函数用途。描述转换型函数用途。第1页SQL 函数函数函数函数函数函数输入输入参数参数参数参数1 1参数参数参数参数2 2参数参数参数参数n n函数执行函数执行输出输出结果结果结果结果第2页两种两种 SQL 函数函数函数函数函数函数单行函数单行函数单行函数单行函数多行函数多行函数多行函数多行函数第3页单行函数单行函数单行函数单行函数:p操作数据对象操作数据对象p接收函数返回一个结果接收函数返回一个结果p只对

2、一行进行变换只对一行进行变换p每行返回一个结果每行返回一个结果p能够转换数据类型能够转换数据类型p能够嵌套能够嵌套p参数能够是一列或一个值参数能够是一列或一个值function_name(arg1,arg2,.)第4页单行函数单行函数转换转换转换转换字符字符字符字符数值数值数值数值日期日期日期日期通用通用通用通用单行函数单行函数单行函数单行函数第5页字符函数字符函数字符函数字符函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写控制函数大小写控制函数大小写控制函数大小写控制函数字符控制函数字符控制函数

3、字符控制函数字符控制函数第6页字符函数字符函数p字符函数：主要指参数类型是字符型，字符函数：主要指参数类型是字符型，不一样函数返回值可能是字符型或数不一样函数返回值可能是字符型或数字类型。字类型。p1.大小写转换函数：大小写转换函数：p LOWER(列名列名|表示式表示式)：将大写或大：将大写或大小写混合字符转换成小写小写混合字符转换成小写UPPER(列名列名|表示式表示式)：将小写或大小：将小写或大小写混合字符转换成大写写混合字符转换成大写INITCAP(列名列名|表示式表示式)：将每个单词：将每个单词第一个字母转换成大写，其余字母都第一个字母转换成大写，其余字母都转换成小写转换成小写 第7

4、页函数函数结果结果大小写控制函数大小写控制函数这类函数改变字符大小写。这类函数改变字符大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course第8页大小写控制函数大小写控制函数显示员工显示员工 scott信息信息:第9页2.字符处理函数字符处理函数CONCAT:连接两个值连接两个值,等同于等同于|格式：格式：CONCAT(column1|expression1,column2|expression2)SUBSTR:返回第一个参数中从返回第一个参数中从n1字符开始长度为字符开始长

5、度为n2子串，假如子串，假如n1是负值，表是负值，表示从后向前数示从后向前数abs(n1)位，假如位，假如n2省略省略,取取n1之后全部字符之后全部字符 格式：格式：SUBSTR(column|expression,n1,n2)第10页LENGTH:取字符长度取字符长度 格式格式:LENGTH(column|expression)INSTR:返回返回s1中，子串中，子串s2从从n1开始，第开始，第n2次出现次出现位置。位置。n1,n2默认值为默认值为1 格式格式:INSTR(s1,s2,n1,n2)第11页LPAD:返回返回s1被被s2从左面填充到从左面填充到n1长度。长度。格式：格式：LPA

6、D(s1,n1,s2)RPAD:返回返回s1被被s2从右面填充到从右面填充到n1长度。长度。格式：格式：RPAD(s1,n1,s2)TRIM:去除字符串头部或尾部（头尾）字符去除字符串头部或尾部（头尾）字符 格式：格式：TRIM(leading|trailing|both trim_character From trim_source)REPLACE:把把s1中中s2用用s3替换。替换。格式：格式：REPLACE(s1,s2,s3)第12页去掉前面去掉前面*去掉前面空格去掉前面空格去掉前后去掉前后*第13页CONCAT(Hello,World)SUBSTR(HelloWorld,1,5)LEN

7、GTH(HelloWorld)INSTR(HelloWorld,W)LPAD(salary,10,*)RPAD(salary,10,*)TRIM(H FROM HelloWorld)HelloWorldHello106*2400024000*elloWorld函数函数结果结果字符控制函数字符控制函数这类函数控制字符这类函数控制字符:第14页SELECT ename,concat(ename,sal)name,mgr,length(ename)length,instr(ename,S)instr FROM emp WHERE SUBSTR(mgr,3)like%9%;字符控制函数字符控制函数31

8、2213第15页数字函数数字函数p1、ROUND(列名列名|表达式表达式,n)：将列或表达式：将列或表达式所表示数值四舍五入到小数点后第所表示数值四舍五入到小数点后第n位。位。p2、TRUNC(列名列名|表达式表达式,n)：将列或表达式所：将列或表达式所表示数值截取到小数点后第表示数值截取到小数点后第n位。位。p注意：第二个参数可认为负数，表示为小数点注意：第二个参数可认为负数，表示为小数点左边指定位数后面部分截去，即均以左边指定位数后面部分截去，即均以0记。记。p3、MOD(m,n)：取：取m除以除以n后得到余数。后得到余数。pROUND:四舍五入四舍五入pROUND(45.926,2)45

9、.93pTRUNC:截断截断pTRUNC(45.926,2)45.92pMOD:求余求余pMOD(1600,300)100第16页SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;ROUND 函数函数DUAL 是一个是一个伪表伪表，能够用来测试函数和表示式。，能够用来测试函数和表示式。123312第17页SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;TRUNC 函数函数312123第18页SELECT empno,ename,sal,M

10、OD(sal,1000)FROM empWHERE job=MANAGER;MOD 函数函数11第19页日期日期pOracle 内部使用数字存放日期内部使用数字存放日期:世纪世纪,年年,月月,日日,小时小时,分钟分钟,秒。秒。p默认日期格式是默认日期格式是 DD-MON-RR.SELECT sysdate FROM dual;11第20页日期数学运算日期数学运算p在日期上加上或减去一个数字结果仍为日在日期上加上或减去一个数字结果仍为日期。期。p两个日期相减返回日期之间相差天数。两个日期相减返回日期之间相差天数。p能够用数字除能够用数字除24来向日期中加上或减去小来向日期中加上或减去小时。时。第

11、21页日期数学运算日期数学运算SELECT ename,(SYSDATE-hiredate)/7 AS WEEKSFROM empWHERE deptno=30;第22页日期函数日期函数两个日期相差月数两个日期相差月数MONTHS_BETWEEN(d1,d2)ADD_MONTHS(d,n)NEXT_DAY（d,s）LAST_DAY（d）ROUND（date，fmt）TRUNC（date，fmt）向指定日期向指定日期d中加上中加上n个月个月指定日期指定日期d下一个星期几所在日期下一个星期几所在日期d所在月份最终一天所在月份最终一天将日期将日期date按按fmt指定格式取整指定格式取整将日期将日期

12、date按按fmt指定格式截断指定格式截断函数函数描述描述第23页日期函数日期函数MONTHS_BETWEEN(14-11月月-12,11-10月月-11)ADD_MONTHS(14-11月月-12,6)NEXT_DAY(14-11月月-12,星期五星期五)LAST_DAY(14-11月月-12）13.096774214-5月月-1316-11月月-1230-11月月-12第24页ROUND(SYSDATE,MONTH)01-11月月-12ROUND(SYSDATE,YEAR)01-1月月-13TRUNC(SYSDATE,MONTH)01-11月月-12 TRUNC(SYSDATE,YEAR)

13、01-1月月-12日期函数日期函数Assume SYSDATE=14-11月月-12:按月份小于等于按月份小于等于6舍，大于舍，大于6入入按天小于等于按天小于等于15舍，大于舍，大于15入入第25页转换函数转换函数隐性隐性隐性隐性显性显性显性显性数据类型转换数据类型转换数据类型转换数据类型转换自动类型转自动类型转换换明确强制类型转换明确强制类型转换转换函数转换函数用于将数值从一个数据类型强制转换为另一个数据类型。用于将数值从一个数据类型强制转换为另一个数据类型。第26页隐式数据类型转换隐式数据类型转换Oracle 自动完成以下转换：自动完成以下转换：VARCHAR2 or CHAR源数据类型源

14、数据类型目标数据类型目标数据类型VARCHAR2 or CHARNUMBERDATEVARCHAR2NUMBERDATEVARCHAR2SELECT 12.5+11 FROM dual;SELECT 12.5|11 FROM dual;第27页TO_CHAR 函数对日期转换函数对日期转换format_model说明：说明：式式:p必须包含在单引号中。必须包含在单引号中。p能够包含任意有效日期格式。能够包含任意有效日期格式。MM YYYY DDp能够使用能够使用 fm 去掉多出空格或者前导零。去掉多出空格或者前导零。p与日期逗号隔开。与日期逗号隔开。TO_CHAR(date,format_mod

15、el)第28页TO_CHAR 函数对日期转换函数对日期转换SELECT ename,hiredate,TO_CHAR(hiredate,DD MM YYYY)AS HIRE_DATEFROM emp第29页TO_CHAR 函数对日期转换函数对日期转换SELECT ename,TO_CHAR(hiredate,fmDD Month YYYY)AS HIREDATEFROM emp;第30页TO_CHAR 函数对数字转换函数对数字转换下面是在下面是在TO_CHAR 函数中函数中format_model经常使用经常使用几个格式几个格式:TO_CHAR(number,format_model)90$L

16、.,数字数字零零美元符美元符当地货币符号当地货币符号小数点小数点千位符千位符第31页SELECT sal,TO_CHAR(sal,$99,999.00)SALARYFROM empWHERE ename=SCOTT;TO_CHAR函数对数字转换函数对数字转换第32页TO_NUMBER 和和 TO_DATE 函数函数 p使用使用 TO_NUMBER 函数将字符转换成数字函数将字符转换成数字:p使用使用 TO_DATE 函数将字符转换成日期函数将字符转换成日期:TO_NUMBER(char,format_model)TO_DATE(char,format_model)第33页SELECT TO_D

17、ATE(-05,YYYY-MM)FROM DUALTO_NUMBER 和和 TO_DATE 函数函数忽略了忽略了DD参数，参数，Oracle会采取会采取1作为日默认值，也就作为日默认值，也就是说会取整到月。是说会取整到月。忽略忽略MM参数，参数，Oracle并不会取整到年，取整到当前月。并不会取整到年，取整到当前月。第34页pSELECT TO_DATE(-05,YYYY-MM)FROM DUAL第35页嵌套函数嵌套函数p单行函数能够嵌套。单行函数能够嵌套。p嵌套函数执行次序是由内到外。嵌套函数执行次序是由内到外。F3(F2(F1(col,arg1),arg2),arg3)步骤步骤1=结果结果

18、1步骤步骤2=结果结果2步骤步骤3=结果结果3第36页SELECT ename,NVL(TO_CHAR(mgr),No Manager)FROM empWHERE deptno=10;嵌套函数嵌套函数第37页通用函数通用函数这些函数适合用于任何数据类型，同时也适合用于这些函数适合用于任何数据类型，同时也适合用于空值：空值：pNVL(NVL(表示式表示式1,1,表示式表示式2)2)函数功效是空值转换，函数功效是空值转换，把空值转换为其它值，处理空值问题。表示式把空值转换为其它值，处理空值问题。表示式1 1是是需要转换列或表示式，表示式需要转换列或表示式，表示式2 2是假如第一个参数是假如第一个参

19、数为空时，需要转换值。为空时，需要转换值。pNVL(expr1,expr2)pNVL2(expr1,expr2,expr3)NVL2(expr1,expr2,expr3)函数功效是用于处理函数功效是用于处理nullnull，假如假如expr1值为值为NULL，则该函数显示，则该函数显示expr3值；不为值；不为NULL，显示，显示expr2值值。pNVL2(expr1,expr2,expr3)第38页SELECT ename,NVL2(mgr,*,No Manager)FROM empWHERE deptno=10;嵌套函数嵌套函数第39页NVL 函数函数将空值转换成一个已知值：将空值转换成一个已知值：p能够使用数据类型有日期、字符、数能够使用数据类型有日期、字符、数字。字。p函数普通形式函数普通形式:NVL(ename,no name)NVL(hiredate,01-JAN-97)NVL(job,No Job Yet)第40页总结总结经过本章学习，应该学会经过本章学习，应该学会:p使用函数对数据进行计算使用函数对数据进行计算p使用函数修改数据使用函数修改数据p使用函数控制一组数据输出格式使用函数控制一组数据输出格式p使用函数改变日期显示格式使用函数改变日期显示格式p使用函数改变数据类型使用函数改变数据类型p使用使用 NVL 函数函数第41页