单行函数和转换函数市公开课获奖课件

1、Oracle数据库查询与管理第3章：函数第1页第1页本章内容字符函数数字函数日期函数转换函数常规函数第2页第2页本章目的描述SQL提供各类函数掌握各个函数使用办法在SELECT语句中使用字符、数字和日期函数第3页第3页1 SQL函数概要SQL函数功效进行数据计算修改单个数据项处理成组行输出指定日期和数字显示格式转换列数据类型SQL函数类型单行函数多行函数第4页第4页单行函数单行函数特性处理数据项接受参数并返回一个值作用于每个返回行为每行返回一个结果能够修改数据类型能够嵌套接受参数，这些参数能够是列，也能够是表示式语法阐明：语句阐明function_name是指函数名称arg1,arg2是函数将

2、使用任意参数，能够用列名或表示式来代表第5页第5页单行函数单行函数第6页第6页2字符函数 知识点概述#知识点重点难点应用阐明1SQL函数概要简介SQL功效及类型2单行函数概要简介单行函数特性及功效3大小写处理函数学习惯用大小写处理函数4字符处理函数学习惯用字符处理函数56789第7页第7页大小写处理函数字符函数分类大小写处理函数字符处理函数大小写处理函数函数阐明LOWER(x)将x中字母转换成小写UPPER(x)将x中字母转换成大写INITCAP(x)将x中每个单词首字母都转换成大写，并返回得到字符串第8页第8页示例：从employees表中选择first_name和last_name列将fi

3、rst_name列转换成大写将last_name列转换成小写SELECT UPPER(FIRST_NAME),LOWER(LAST_NAME)FROM EMPLOYEES；第9页第9页示例：显示雇员Higgins雇员编号在where条件中使用大小写转换函数在无法定字段内容大小写情况下使用转换函数第10页第10页字符处理函数惯用字符处理函数与示例函数阐明/结果CONCAT（x,y）将y添加到x上，并将得到字符串作为结果返回CONCAT(Hello, World)HelloWorldSUBSTR(x,start,length)返回x中一个子字符串，这个字符串从start处开始，还能够为这个子字符串

4、指定一个可选length参数SUBSTR(HelloWorld,1,5)HelloLENGTH(x)返回x中字符个数LENGTH(HelloWorld)10第11页第11页惯用字符处理函数与示例函数阐明/结果INSTR(x,find_string,start,occurrence)在x中查找字符串find_string，然后返回find_string所在位置。能够提供可选start位置来指定该函数从这个位置开始查找。同样，也指定一个可选occurrence参数，来阐明该返回find_string第几次出现位置INSTR(HelloWorld, W)6LPAD(x,width,pad_strin

5、g)在x左边补齐空格，得到总长为width个字符字符串。该函数能够提供一个可选pad_string,这个参数用于指定重复使用哪个字符串来补齐x左边空位。返回x被补齐之后结果字符串LPAD(salary,10,*)*24000RPAD(x,width,pad_string)与LPAD（）类似，但是要对x右边补齐RPAD(salary, 10, *)24000*第12页第12页第13页第13页练习：显示姓氏以字母“n”为结尾雇员数据用其它办法如何实现？第14页第14页3 数字函数 知识点概述#知识点重点难点应用阐明1数字函数概要简介常见数字函数2MOD函数学习如何使用MOD函数3ROUND函数学习

6、如何使用ROUND函数4TRUNC函数学习如何使用TRUNC函数56789第15页第15页数字函数概要数字函数能够用于计算常见数字函数：函数阐明MOD（x,y）返回x除以y余数ROUND(x,y)返回对x取整结果；TRUNC(x,y)返回对x截断结果；第16页第16页MOD函数MOD(x,y)用于计算x除以y所得余数示例：使用MOD函数分别显示8除以3和4余数SELECT MOD(8,3),MOD(8,4)FROM dual;第17页第17页ROUND函数ROUND(x,y)用于计算对x取整结果其中y为可选参数，阐明对第几位小数取整假如没有指定y，则x在0位小数处取整数假如y是负数，则对x在小

7、数点左边第|y|位处取整数示例：使用ROUND函数分别对5.75在0位、1位和-1位取整SELECT round(5.75),round(5.75,1),round(5.75,-1)FROM dual;第18页第18页TRUNC函数TRUNC(x,y)函数用于计算对x截断结果y为可选参数，阐明对第几位小数截断假如没有y，则对在0位小数截断假如y为负数，则对x在小数点左边第|y|位处截断示例：对5.75在0位、1位、-1位小数处截断SELECT trunc(5.75),trunc(5.75,1),trunc(5.75,-1)from dual;第19页第19页4 日期与日期处理函数 知识点概述#

8、知识点重点难点应用阐明1Oracle中日期存储理解Oracle中对日期存储和RR日期2日期运算学习如何对日期进行加减运算3SYSDATE函数4日期处理函数5第20页第20页Oracle中日期Oracle以内部数字格式存储日期：世纪、年、月、日、小时、分、秒Oracle默认日期格式是：DD-MON-RR有效Oracle日期介于公元前47初到公元9999年终第21页第21页RR日期第22页第22页SYSDATE函数返回当前数据库服务器日期和时间日期运算：对日期加上或减去一个数字，能够取得一个新日期值将两个日期相减能够得到他们之间天数将小时数除以24，能够将小时添加到日期中第23页第23页上述例子中

9、，怎么返回整数部分？第24页第24页日期处理函数除了MONTHS_BETWEEN返回是数字，其余日期处理函数都返回一个DATE。第25页第25页MONTHS_BETWEEN(date1,date2) ADD_MONTHS(date,n)NEXT_DAY(date,char) LAST_DAY(date)ROUND(date,fmt) TRUNC(date,fmt)第26页第26页第27页第27页练习：显示聘用时间不足150个月所有雇员雇员编号、聘用日期、聘用月数、六个月复核日期、聘用日期之后第一个星期五和聘用月份最后一天第28页第28页5 转换函数 知识点概述#知识点重点难点应用阐明1转换函数

10、概要简介转换函数类型以及隐式函数2TO_CHAR函数学习使用TO_CHAR函数处理数字、日期3TO_NUMBER函数学习使用TO_NUMBER函数处理字符串4TO_DATE函数56789第29页第29页转换函数概要从一个数据类型转换为另一个数据类型数据类型转换隐式数据类型转换显式数据类型转换隐式数据类型转换在表示式中，自动执行下列转换VARCHAR2或CHAR至NUMBERVARCHAR2或CHAR至DATE对于表示式计算，执行自动执行下列转换NUMBER至VARCHAR2或CHARDATE至VARCHAR2或CHAR第30页第30页显式数据类型转换函数阐明TO_CHAR()将x转换为一个VA

11、RCHAR2字符串。TO_NUMBER（x,format)将x转换为一个NUMBER类型TO_DATE(x,format)将x转换为一个DATE类型CAST(x AS type)将x转换为type所指定兼容数据库类型TO_BLOB(x)将x转换为一个二进制大对象类型第31页第31页TO_CHAR函数使用TO_CHAR函数处理数字常见format参数参数格式例子阐明9999返回指定位置处数字0099999900999：返回一个数字，前面补09990：返回一个数字，后面补0.999.99在指定位置处返回一个小数点,9,999在指定位置处返回一个逗号$999在数字开头返回一个美元符号TO_CHAR(

12、number,format)第32页第32页示例:使用TO_CHAR(函数将12345.67)转换为一个字符串指定格式 99，999.99SELECT to_char(12345.67,99,999.99)from dual;第33页第33页TO_CHAR处理日期使用TO_CHAR函数处理日期格式样式含有下列特点必须放在单引号内区别大小写能够包括任何有效日期格式元素输出日和月名称会自动用空白进行填充与日期之间用逗号分隔TO_CHAR(date,format)第34页第34页日期格式样式元素元素结果YYYY用数字表示完全年份YEAR拼写出年份（用英文标识）MM月份两位数值MONTH月份完整名称M

13、ON月份三个字母缩写DY一周中某日三个字母缩写DAY一周中某日完整名称DD用数字表示月份中某日第35页第35页示例:日期格式样式元素日期中时间部分时间元素格式通过将字符串放在双引号内来添加字符串：HH24:MI:SS AM10:47:30 AMDD “of” MONTH12 of OCTOBER第36页第36页使用TO_CHAR函数处理日期示例：使用TO_CHAR函数将employees表中hire_date列转换为指定字符串select employee_id, last_name,to_char(hire_date,MON DD,YYYY)from employees第37页第37页TO_

14、NUMBER函数TO_NUMBER函数用于将字符串转换为一个数字指定一个可选参数format字符来阐明char格式示例：使用TO_NUMBER函数将字符串970.13转换为一个数字，然后加25.5SELECT to_number(970.13)+25.5from dual;TO_NUMBER(char,format)第38页第38页TO_DATE函数将字符串转换为日期格式TO_DATE(char,format)注意RR格式日期和yy日期不同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(h