《单行函数和转换函数省公共课一等奖全国赛课获奖课件.pptx》由会员分享，可在线阅读，更多相关《单行函数和转换函数省公共课一等奖全国赛课获奖课件.pptx（42页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle数据库查询与管理第第3 3章：函数章：函数第1页本章内容字符函数字符函数数字函数数字函数日期函数日期函数转换函数转换函数常规函数常规函数第2页本章目标描述描述SQLSQL提供各类函数提供各类函数掌握各个函数使用方法掌握各个函数使用方法在在SELECTSELECT语句中使用字符、数字和日期函数语句中使用字符、数字和日期函数第3页1 SQL函数概要SQLSQL函数功效函数功效进行数据计算进行数据计算修改单个数据项修改单个数据项处理成组行输出处理成组行输出指定日期和数字显示格式指定日期和数字显示格式转换列数据类型转换列数据类型SQLSQL函数类型函数类型单行函数单行函数多行函数多行函数第
2、4页单行函数单行函数特征单行函数特征处理数据项处理数据项接收参数并返回一个值接收参数并返回一个值作用于每个返回行作用于每个返回行为每行返回一个结果为每行返回一个结果能够修改数据类型能够修改数据类型能够嵌套能够嵌套接收参数，这些参数能够是列，也能够是表示式接收参数，这些参数能够是列，也能够是表示式语法说明：语法说明：语句语句说明说明function_name是指函数名称是指函数名称arg1,arg2是函数将使用任意参数，能够是函数将使用任意参数，能够用列名或表示式来代表用列名或表示式来代表第5页单行函数单行函数单行函数第6页2字符函数 知识点概述知识点概述#知识点知识点重点重点难点难点应用应用说
3、明说明1SQL函数概要介绍介绍SQLSQL功效及类型功效及类型2单行函数概要介绍单行函数特征及功效介绍单行函数特征及功效3大小写处理函数学习惯用大小写处理函数学习惯用大小写处理函数4字符处理函数学习惯用字符处理函数学习惯用字符处理函数56789第7页大小写处理函数字符函数分类字符函数分类u大小写处理函数大小写处理函数u字符处理函数字符处理函数大小写处理函数大小写处理函数函数函数说明说明LOWER(x)将将x x中字母转换成小写中字母转换成小写UPPER(x)将将x x中字母转换成大写中字母转换成大写INITCAP(x)将将x x中每个单词首字母都转换成大写，并返回得到字中每个单词首字母都转换成
4、大写，并返回得到字符串符串第8页示例：从示例：从employeesemployees表中选择表中选择first_namefirst_name和和last_namelast_name列列将将first_namefirst_name列转换成大写列转换成大写将将last_namelast_name列转换成小写列转换成小写SELECT UPPER(FIRST_NAME),LOWER(LAST_NAME)FROM EMPLOYEES；第9页示例：显示雇员示例：显示雇员HigginsHiggins雇员编号雇员编号在where条件中使用大小写转换函数在无法定字段内容大小写情况下使用转换函数在无法定字段内容大
5、小写情况下使用转换函数第10页字符处理函数惯用字符处理函数与示例惯用字符处理函数与示例函数函数说明说明/结果结果CONCAT（x,y）将将y y添加到添加到x x上，并将得到字符串作为结上，并将得到字符串作为结果返回果返回CONCAT(Hello,World)HelloWorldSUBSTR(x,start,length)返回返回x x中一个子字符串，这个字符串从中一个子字符串，这个字符串从startstart处开始，还能够为这个子字符串处开始，还能够为这个子字符串指定一个可选指定一个可选lengthlength参数参数SUBSTR(HelloWorld,1,5)HelloLENGTH(x)返
6、回返回x x中字符个数中字符个数LENGTH(HelloWorld)10第11页惯用字符处理函数与示例惯用字符处理函数与示例函数函数说明说明/结果结果INSTR(x,find_string,start,occurrence)在在x x中查找字符串中查找字符串find_stringfind_string，然后返回，然后返回find_stringfind_string所在位置。能够提供可选所在位置。能够提供可选startstart位置来指定该函数从这个位置开始查找。一位置来指定该函数从这个位置开始查找。一样，也指定一个可选样，也指定一个可选occurrenceoccurrence参数，来说参数，来
7、说明该返回明该返回find_stringfind_string第几次出现位置第几次出现位置INSTR(HelloWorld,W)6LPAD(x,width,pad_string)在在x x左边补齐空格，得到总长为左边补齐空格，得到总长为widthwidth个字符个字符字符串。该函数能够提供一个可选字符串。该函数能够提供一个可选pad_string,pad_string,这个参数用于指定重复使用哪个这个参数用于指定重复使用哪个字符串来补齐字符串来补齐x x左边空位。返回左边空位。返回x x被补齐之后被补齐之后结果字符串结果字符串LPAD(salary,10,*)*24000RPAD(x,widt
8、h,pad_string)与与LPADLPAD（）类似，不过要对（）类似，不过要对x x右边补齐右边补齐RPAD(salary,10,*)24000*第12页第13页练习：显示姓氏以字母练习：显示姓氏以字母“n n”为结尾雇员数据为结尾雇员数据用其它方法怎样实现？用其它方法怎样实现？第14页3 数字函数 知识点概述知识点概述#知识点知识点重点重点难点难点应用应用说明说明1数字函数概要介绍常见数字函数介绍常见数字函数2MOD函数学习怎样使用学习怎样使用MODMOD函数函数3ROUND函数学习怎样使用学习怎样使用ROUNDROUND函数函数4TRUNC函数学习怎样使用学习怎样使用TRUNCTRUN
9、C函数函数56789第15页数字函数概要数字函数能够用于计算数字函数能够用于计算常见数字函数：常见数字函数：函数函数说明说明MOD（x,y）返回返回x x除以除以y y余数余数ROUND(x,y)返回对返回对x x取整结果；取整结果；TRUNC(x,y)返回对返回对x x截断结果；截断结果；第16页MOD函数MOD(x,y)MOD(x,y)用于计算用于计算x x除以除以y y所得余数所得余数示例：示例：使用使用MODMOD函数分别显示函数分别显示8 8除以除以3 3和和4 4余数余数SELECT MOD(8,3),MOD(8,4)FROM dual;第17页ROUND函数ROUND(x,y)R
10、OUND(x,y)用于计算对用于计算对x x取整结果取整结果其中其中y y为可选参数，说明对第几位小数取整为可选参数，说明对第几位小数取整假如没有指定假如没有指定y y，则，则x x在在0 0位小数处取整数位小数处取整数假如假如y y是负数，则对是负数，则对x x在小数点左边第在小数点左边第|y|y|位处取整数位处取整数示例：示例：使用使用ROUNDROUND函数分别对函数分别对5.755.75在在0 0位、位、1 1位和位和-1-1位取整位取整SELECT round(5.75),round(5.75,1),round(5.75,-1)FROM dual;第18页TRUNC函数TRUNC(x
11、,y)TRUNC(x,y)函数用于计算对函数用于计算对x x截断结果截断结果y y为可选参数，说明对第几位小数截断为可选参数，说明对第几位小数截断假如没有假如没有y y，则对在，则对在0 0位小数截断位小数截断假如假如y y为负数，则对为负数，则对x x在小数点左边第在小数点左边第|y|y|位处截断位处截断示例：示例：对对5.755.75在在0 0位、位、1 1位、位、-1-1位小数处截断位小数处截断SELECT trunc(5.75),trunc(5.75,1),trunc(5.75,-1)from dual;第19页4 日期与日期处理函数 知识点概述知识点概述#知识点知识点重点重点难点难点
12、应用应用说明说明1OracleOracle中日中日期存放期存放了解了解OracleOracle中对日期存放和中对日期存放和RRRR日期日期2日期运算日期运算学习怎样对日期进行加减运算学习怎样对日期进行加减运算3SYSDATE函数4日期处理函数5第20页Oracle中日期OracleOracle以内部数字格式存放日期：世纪、年、月、日、以内部数字格式存放日期：世纪、年、月、日、小时、分、秒小时、分、秒OracleOracle默认日期格式是：默认日期格式是：DD-MON-RRDD-MON-RR有效有效OracleOracle日期介于公元前日期介于公元前4747初到公元初到公元99999999年底年
13、底第21页RR日期第22页SYSDATE函数返回当前返回当前数据库服务器数据库服务器日期和时间日期和时间日期运算：日期运算：对日期加上或减去一个数字，能够取得一个新日期值对日期加上或减去一个数字，能够取得一个新日期值将两个日期相减能够得到他们之间天数将两个日期相减能够得到他们之间天数将小时数除以将小时数除以2424，能够将小时添加到日期中，能够将小时添加到日期中第23页上述例子中，怎么返回整数部分？上述例子中，怎么返回整数部分？第24页日期处理函数除了MONTHS_BETWEEN返回是数字，其余日期处理函数都返回一个DATE。第25页MONTHS_BETWEEN(date1,date2)ADD
14、_MONTHS(date,n)NEXT_DAY(date,char)LAST_DAY(date)ROUND(date,fmt)TRUNC(date,fmt)第26页第27页练习：显示聘用时间不足练习：显示聘用时间不足150150个月全部雇员雇员编号、个月全部雇员雇员编号、聘用日期、聘用月数、六个月复核日期、聘用日期之聘用日期、聘用月数、六个月复核日期、聘用日期之后第一个星期五和聘用月份最终一天后第一个星期五和聘用月份最终一天第28页5 转换函数 知识点概述知识点概述#知识点知识点重点重点难点难点应用应用说明说明1转换函数概要介绍转换函数类型以及隐式函介绍转换函数类型以及隐式函数数2TO_CHA
15、R函数学习使用TO_CHAR函数处理数字、日期3TO_NUMBER函数学习使用TO_NUMBER函数处理字符串4TO_DATE函数56789第29页转换函数概要从一个数据类型转换为另一个数据类型从一个数据类型转换为另一个数据类型数据类型转换数据类型转换隐式数据类型转换隐式数据类型转换显式数据类型转换显式数据类型转换隐式数据类型转换隐式数据类型转换在表示式中，自动执行以下转换在表示式中，自动执行以下转换VARCHAR2或CHAR至NUMBERVARCHAR2或CHAR至DATE对于表示式计算，执行自动执行以下转换对于表示式计算，执行自动执行以下转换NUMBER至VARCHAR2或CHARDATE
16、至VARCHAR2或CHAR第30页显式数据类型转换显式数据类型转换函数函数说明说明TO_CHAR()将将x转换为一个转换为一个VARCHAR2字符串。字符串。TO_NUMBER（x,format)将将x转换为一个转换为一个NUMBER类型类型TO_DATE(x,format)将将x转换为一个转换为一个DATE类型类型CAST(x AS type)将将x x转换为转换为typetype所指定兼容数据库类型所指定兼容数据库类型TO_BLOB(x)将x转换为一个二进制大对象类型第31页TO_CHAR函数使用使用TO_CHARTO_CHAR函数处理数字函数处理数字常见常见formatformat参数
17、参数参数参数格式例子格式例子说明说明9999返回指定位置处数字返回指定位置处数字0099999900999：返回一个数字，前面补09990：返回一个数字，后面补0.999.99在指定位置处返回一个小数点,9,999在指定位置处返回一个逗号$999在数字开头返回一个美元符号TO_CHAR(number,format)第32页示例示例:使用使用TO_CHAR(TO_CHAR(函数将函数将12345.67)12345.67)转换为一个字符串转换为一个字符串指定格式指定格式 9999，999.99999.99SELECT to_char(12345.67,99,999.99)from dual;第33
18、页TO_CHAR处理日期使用使用TO_CHARTO_CHAR函数处理日期函数处理日期格式样式含有以下特点格式样式含有以下特点必须放在单引号内必须放在单引号内区分大小写区分大小写能够包含任何有效日期格式元素能够包含任何有效日期格式元素输出日和月名称会自动用空白进行填充输出日和月名称会自动用空白进行填充与日期之间用逗号分隔与日期之间用逗号分隔TO_CHAR(date,format)第34页日期格式样式元素日期格式样式元素元素元素结果结果YYYY用数字表示完整年份YEAR拼写出年份（用英文标识）拼写出年份（用英文标识）MM月份两位数值月份两位数值MONTH月份完整名称月份完整名称MON月份三个字母缩
19、写月份三个字母缩写DY一周中某日三个字母缩写一周中某日三个字母缩写DAY一周中某日完整名称一周中某日完整名称DD用数字表示月份中某日用数字表示月份中某日第35页示例示例:日期格式样式元素日期格式样式元素日期中时间部分时间元素格式日期中时间部分时间元素格式经过将字符串放在双引号内来添加字符串：经过将字符串放在双引号内来添加字符串：HH24:MI:SS AM10:47:30 AMDD“of”MONTH12 of OCTOBER第36页使用使用TO_CHARTO_CHAR函数处理日期函数处理日期示例：使用示例：使用TO_CHARTO_CHAR函数将函数将employeesemployees表中表中h
20、ire_datehire_date列列转换为转换为指定指定字符串字符串select employee_id,last_name,to_char(hire_date,MON DD,YYYY)from employees第37页TO_NUMBER函数TO_NUMBERTO_NUMBER函数用于将字符串转换为一个数字函数用于将字符串转换为一个数字指定一个可选参数指定一个可选参数formatformat字符来说明字符来说明charchar格式格式示例：示例：使用使用TO_NUMBERTO_NUMBER函数将字符串函数将字符串970.13970.13转换为一个数字，然后加转换为一个数字，然后加25.52
21、5.5SELECT to_number(970.13)+25.5from dual;TO_NUMBER(char,format)第38页TO_DATE函数将字符串转换为日期格式将字符串转换为日期格式TO_DATE(char,format)注意注意RRRR格式日期和格式日期和yyyy日期不一样日期不一样select last_name,to_char(hire_date,DD-MON-YYYY)from employeeswhere hire_date to_date(01-1月-90,DD-MON-RR)select last_name,to_char(hire_date,DD-MON-YYYY)from employeeswhere hire_date to_date(01-1月-90,DD-MON-YY)第39页函数嵌套单行函数能够嵌套到任意层单行函数能够嵌套到任意层嵌套函数计算次序是从最内嵌套函数计算次序是从最内层到最外层层到最外层第40页从里到外进行三层运算。从里到外进行三层运算。SELECT last_name,UPPER(CONCAT(SUBSTR(last_name,1,8),US)FROM employees第41页第42页