03-单行函数省名师优质课赛课获奖课件市赛课一等奖课件.ppt

单行函数单行函数第1页目标目标经过本章学习，您将能够经过本章学习，您将能够:SQL中不一样类型函数。中不一样类型函数。在在 SELECT 语句中使用字符，数字和日期函数。语句中使用字符，数字和日期函数。描述转换型函数用途。描述转换型函数用途。第2页SQL 函数函数函数函数函数函数输入输入参数参数参数参数1 1参数参数参数参数2 2参数参数参数参数n n函数执行函数执行输出输出结果结果结果结果第3页两种两种 SQL 函数函数函数函数函数函数单行函数单行函数单行函数单行函数多行函数多行函数多行函数多行函数第4页单行函数单行函数单行函数单行函数:操作数句对象操作数句对象接收函数返回一个结果接收函数返回一个结果只对一行进行变换只对一行进行变换每行返回一个结果每行返回一个结果能够转换数据类型能够转换数据类型能够嵌套能够嵌套参数能够是一列或一个值参数能够是一列或一个值function_name(arg1,arg2,.)第5页单行函数单行函数转换转换转换转换字符字符字符字符数值数值数值数值日期日期日期日期通用通用通用通用单行函数单行函数单行函数单行函数第6页字符函数字符函数字符函数字符函数字符函数字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE大小写控制函数大小写控制函数大小写控制函数大小写控制函数字符控制函数字符控制函数字符控制函数字符控制函数第7页函数函数结果结果大小写控制函数大小写控制函数这类函数改变字符大小写。这类函数改变字符大小写。LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course第8页大小写控制函数大小写控制函数显示员工显示员工 Higgins信息信息:SELECT employee_id,last_name,department_idFROM employeesWHERE last_name=higgins;no rows selectedno rows selectedSELECT employee_id,last_name,department_idFROM employeesWHERE LOWER(last_name)=higgins;第9页CONCAT(Hello,World)SUBSTR(HelloWorld,1,5)LENGTH(HelloWorld)INSTR(HelloWorld,W)LPAD(salary,10,*)RPAD(salary,10,*)TRIM(H FROM HelloWorld)HelloWorldHello106*2400024000*elloWorld函数函数结果结果字符控制函数字符控制函数这类函数控制字符这类函数控制字符:第10页SELECT employee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,a)Contains a?FROM employeesWHERE SUBSTR(job_id,4)=REP;字符控制函数字符控制函数123123第11页数字函数数字函数ROUND:四舍五入四舍五入ROUND(45.926,2)45.93TRUNC:截断截断TRUNC(45.926,2)45.92MOD:求余求余MOD(1600,300)100第12页SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;ROUND 函数函数DUAL 是一个是一个伪表伪表，能够用来测试函数和表示式。，能够用来测试函数和表示式。123312第13页SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROM DUAL;TRUNC 函数函数312123第14页SELECT last_name,salary,MOD(salary,5000)FROM employeesWHERE job_id=SA_REP;MOD 函数函数第15页日期日期Oracle 内部使用数字存放日期内部使用数字存放日期:世纪世纪,年年,月月,日日,小时小时,分钟分钟,秒。秒。默认日期格式是默认日期格式是 DD-MON-RR.能够只指定年后两位在能够只指定年后两位在20世纪存放二十一世纪日期。世纪存放二十一世纪日期。一样能够在二十一世纪存放一样能够在二十一世纪存放20世纪日期。世纪日期。SELECT last_name,hire_dateFROM employeesWHERE last_name like G%;第16页日期日期函数函数SYSDATE 返回返回:日期日期时间时间第17页日期数学运算日期数学运算在日期上加上或减去一个数字结果仍为日期。在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差天数。两个日期相减返回日期之间相差天数。能够用数字除能够用数字除24来向日期中加上或减去小时。来向日期中加上或减去小时。第18页日期数学运算日期数学运算SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKSFROM employeesWHERE department_id=90;第19页日期函数日期函数两个日期相差月数两个日期相差月数MONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNC 向指定日期中加上若干月数向指定日期中加上若干月数指定日期下一个日期指定日期下一个日期本月最终一天本月最终一天日期四舍五入日期四舍五入 日期截断日期截断函数函数描述描述第20页MONTHS_BETWEEN(01-SEP-95,11-JAN-94)日期函数日期函数ADD_MONTHS(11-JAN-94,6)NEXT_DAY(01-SEP-95,FRIDAY)LAST_DAY(01-FEB-95)19.677419411-JUL-9408-SEP-9528-FEB-95第21页ROUND(SYSDATE,MONTH)01-AUG-95ROUND(SYSDATE,YEAR)01-JAN-96TRUNC(SYSDATE,MONTH)01-JUL-95 TRUNC(SYSDATE,YEAR)01-JAN-95日期函数日期函数Assume SYSDATE=25-JUL-95:第22页转换函数转换函数隐性隐性隐性隐性显性显性显性显性数据类型转换数据类型转换数据类型转换数据类型转换第23页隐式数据类型转换隐式数据类型转换Oracle 自动完成以下转换：自动完成以下转换：VARCHAR2 or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCHAR2 or CHARNUMBERDATENUMBERDATEVARCHAR2VARCHAR2第24页隐式数据类型转换隐式数据类型转换表示式计算中表示式计算中,Oracle 自动完成以下转换自动完成以下转换:VARCHAR2 or CHAR源数据类型源数据类型目标数据类型目标数据类型VARCHAR2 or CHARNUMBERDATE第25页TO_CHAR 函数对日期转换函数对日期转换格式格式:必须包含在单引号中而且大小写敏感。必须包含在单引号中而且大小写敏感。能够包含任意有效日期格式。能够包含任意有效日期格式。能够使用能够使用 fm 去掉多出空格或者前导零。去掉多出空格或者前导零。与日期指用逗号隔开。与日期指用逗号隔开。TO_CHAR(date,format_model)第27页YYYY日期格式元素日期格式元素YEARMMMONTHDYDAYTWO THOUSAND AND FOUR02MONMONDAYJULYMONJULDD02第28页日期格式元素日期格式元素时间格式时间格式使用双引号向日期中添加字符使用双引号向日期中添加字符日期在月份中位置日期在月份中位置HH24:MI:SS AM15:45:32 PMDD of MONTH12 of OCTOBERddspthfourteenth第29页TO_CHAR 函数对日期转换函数对日期转换SELECT last_name,TO_CHAR(hire_date,fmDD Month YYYY)AS HIREDATEFROM employees;第30页TO_CHAR 函数对数字转换函数对数字转换下面是在下面是在TO_CHAR 函数中经常使用几个格式函数中经常使用几个格式:TO_CHAR(number,format_model)90$L.,数字数字零零美元符美元符当地货币符号当地货币符号小数点小数点千位符千位符第31页SELECT TO_CHAR(salary,$99,999.00)SALARYFROM employeesWHERE last_name=Ernst;TO_CHAR函数对数字转换函数对数字转换第32页TO_NUMBER 和和 TO_DATE 函数函数 使用使用 TO_NUMBER 函数将字符转换成数字函数将字符转换成数字:使用使用 TO_DATE 函数将字符转换成日期函数将字符转换成日期:这些函数能够使用这些函数能够使用fx 修饰符。修饰符。nTO_NUMBER(char,format_model)TO_DATE(char,format_model)第33页TO_NUMBER 和和 TO_DATE 函数函数 使用使用 TO_NUMBER 函数将字符转换成数字函数将字符转换成数字:使用使用 TO_DATE 函数将字符转换成日期函数将字符转换成日期:这些函数能够使用这些函数能够使用 fx 修饰符。修饰符。TO_NUMBER(char,format_model)TO_DATE(char,format_model)第34页RR 日期格式日期格式当前年当前年19951995日期日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式格式19951995YY 格式格式199519172095当前年份当前年份:04904950995099The return date is in the current centuryThe return date is in the century after the current oneThe return date is in the century before the current oneThe return date is in the current century指定年份指定年份:第35页RR 日期格式日期格式SELECT last_name,TO_CHAR(hire_date,DD-Mon-YYYY)FROM employeesWHERE hire_date TO_DATE(01-Jan-90,DD-Mon-RR);使用使用使用使用RRRRRRRR日期格式查找雇佣日期在日期格式查找雇佣日期在日期格式查找雇佣日期在日期格式查找雇佣日期在1990199019901990年之前员工，年之前员工，年之前员工，年之前员工，在在在在1999199919991999或现在使用下面命令会产生相同结果或现在使用下面命令会产生相同结果或现在使用下面命令会产生相同结果或现在使用下面命令会产生相同结果:第36页嵌套函数嵌套函数单行函数能够嵌套。单行函数能够嵌套。嵌套函数执行次序是由内到外。嵌套函数执行次序是由内到外。F3(F2(F1(col,arg1),arg2),arg3)步骤步骤1=结果结果1步骤步骤2=结果结果2步骤步骤3=结果结果3第37页SELECT last_name,NVL(TO_CHAR(manager_id),No Manager)FROM employeesWHERE manager_id IS NULL;嵌套函数嵌套函数第38页通用函数通用函数这些函数适合用于任何数据类型，同时也适合用于空值：这些函数适合用于任何数据类型，同时也适合用于空值：NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,.,exprn)第39页NVL 函数函数将空值转换成一个已知值：将空值转换成一个已知值：能够使用数据类型有日期、字符、数字。能够使用数据类型有日期、字符、数字。函数普通形式函数普通形式:NVL(commission_pct,0)NVL(hire_date,01-JAN-97)NVL(job_id,No Job Yet)第40页SELECT last_name,salary,NVL(commission_pct,0),(salary*12)+(salary*12*NVL(commission_pct,0)AN_SALFROM employees;使用使用NVL函数函数1212第41页SELECT last_name,salary,commission_pct,NVL2(commission_pct,SAL+COMM,SAL)incomeFROM employees WHERE department_id IN(50,80);使用使用 NVL2 函数函数1212第42页SELECT first_name,LENGTH(first_name)expr1,last_name,LENGTH(last_name)expr2,NULLIF(LENGTH(first_name),LENGTH(last_name)resultFROM employees;使用使用 NULLIF 函数函数123123第43页使用使用 COALESCE 函数函数COALESCE 与与 NVL 相比优点在于相比优点在于 COALESCE 能够同时能够同时处理交替多个值。处理交替多个值。假如第一个表示式费空假如第一个表示式费空,则返回这个表示式，对其它参数则返回这个表示式，对其它参数进行进行COALESCE。第44页SELECT last_name,COALESCE(commission_pct,salary,10)commFROM employeesORDER BY commission_pct;使用使用 COALESCE 函数函数第45页条件表示式条件表示式在在 SQL 语句中使用语句中使用IF-THEN-ELSE 逻辑。逻辑。使用两种方法使用两种方法:CASE 表示式表示式DECODE 函数函数第46页CASE 表示式表示式在需要使用在需要使用 IF-THEN-ELSE 逻辑时逻辑时:CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND第47页SELECT last_name,job_id,salary,CASE job_id WHEN IT_PROG THEN 1.10*salary WHEN ST_CLERK THEN 1.15*salary WHEN SA_REP THEN 1.20*salary ELSE salary END REVISED_SALARYFROM employees;CASE 表示式表示式下面是使用下面是使用case表示式一个例子：表示式一个例子：第48页DECODE 函数函数在需要使用在需要使用 IF-THEN-ELSE 逻辑时逻辑时:DECODE(col|expression,search1,result1 ,search2,result2,.,default)第49页DECODE 函数函数SELECT last_name,job_id,salary,DECODE(job_id,IT_PROG,1.10*salary,ST_CLERK,1.15*salary,SA_REP,1.20*salary,salary)REVISED_SALARYFROM employees;第50页DECODE 函数函数SELECT last_name,salary,DECODE(TRUNC(salary/,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATEFROM employeesWHERE department_id=80;使用使用decode函数一个例子：函数一个例子：第51页总结总结经过本章学习，您应该学会经过本章学习，您应该学会:使用函数对数据进行计算使用函数对数据进行计算使用函数修改数据使用函数修改数据使用函数控制一组数据输出格式使用函数控制一组数据输出格式使用函数改变日期显示格式使用函数改变日期显示格式使用函数改变数据类型使用函数改变数据类型使用使用 NVL 函数函数使用使用IF-THEN-ELSE 逻辑逻辑第52页