第4章单行函数ppt课件

1、1Oracle SQL开发根底开发根底2课程构造课程构造内容内容课时（课时（H H）第一章 Oracle数据库基础1.8第二章 编写简单的查询语句1.2第三章 限制数据和对数据排序1.5第四章 单行函数2.5第五章 多表查询2第六章 分组函数2第七章 子查询2第八章 数据操作与事务控制3.5第九章 表和约束4第十章 其他数据库对象2.53第四章 单行函数目的目的:本章旨在向学员引见：本章旨在向学员引见：1)字符函数、数字函数和日期函数字符函数、数字函数和日期函数2)转换函数和通用函数转换函数和通用函数时间：时间：2.5学时学时教学方法：教学方法：4本章要点本章要点字符函数数值函数日期函数转换函

2、数通用函数函数嵌套5第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6通用函数64.1.1单行函数引见单行函数引见 1/574.1.1单行函数引见单行函数引见 2/5函数类型函数类型84.1.1单行函数引见单行函数引见 3/5语法:函数名(参数1，参数2,)其中的参数可以是以下之一：变量列名表达式94.1.1单行函数引见单行函数引见 4/5单行函数还有以下的一些特征：单行函数对单行操作每行前往一个结果有能够前往值与原参数数据类型不一致转换函数单行函数可以写在SELECT、WHERE、ORDER BY子句中有些函数没有参数，有些函数包括

3、一个或多个参数函数可以嵌套104.1.1单行函数引见单行函数引见 5/5单行函数的分类单行函数的分类11第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6通用函数124.2字符函数字符函数字符函数：主要指参数类型是字符型，不同函数前往值能够是字符型或数字类型。134.2.1字符大小写操作函数字符大小写操作函数LOWER(列名|表达式)：将大写或大小写混合的字符转换成小写UPPER(列名|表达式) ：将小写或大小写混合的字符转换成大写INITCAP(列名|表达式) ：将每个单词的第一个字母转换成大写，其他的字母都转换成小写函数函数结果结

4、果LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course144.2.2字符处置函数字符处置函数CONCAT:衔接两个值,等同于|格式：CONCAT(column1|expression1,column2|expression2)SUBSTR:前往第一个参数中从n1字符开场长度为n2的子串，假设n1是负值，表示从后向前数的abs(n1)位，假设n2省略,取n1之后的一切字符格式：SUBSTR(column | expression,n1,n2)LENGTH:取字符长度格式:LENGTH

5、(column | expression)INSTR:前往s1中，子串s2从n1开场，第n2次出现的位置。n1,n2默许值为1格式:INSTR(s1,s2,n1,n2)LPAD:前往s1被s2从左面填充到n1长度。格式：LPAD(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)154.2.2字符

6、处置函数字符处置函数CONCAT(Good, String)SUBSTR(String,1,3)LENGTH(String)INSTR(String, r)GoodStringStr63函数函数结果结果164.2.2字符处置函数字符处置函数LPAD(sal,10,*)RPAD(sal,10,*)TRIM(S FROM SSMITH)REPLACEabc,b,d)*50005000*MITHadc函数函数结果结果174.2.2字符处置函数字符处置函数查找公司员工编号，用户名查找公司员工编号，用户名first_name与与last_name衔接成衔接成一个字符串，职位编号及一个字符串，职位编号及l

7、ast_name的长度，要求职位从第的长度，要求职位从第四位起匹配四位起匹配ACCOUNT，同时，同时last_name中至少包含一个中至少包含一个e字母。字母。SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name) length FROM employeesWHERE SUBSTR(job_id, 4) = ACCOUNTAND INSTR(last_name, e)0;18第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6

8、通用函数194.3数字函数数字函数ROUND(列名|表达式, n)：将列或表达式所表示的数值四舍五入到小数点后的第n位。TRUNC(列名|表达式,n)：将列或表达式所表示的数值截取到小数点后的第n位。MOD(m,n)：取m除以n后得到的余数。204.3数字函数数字函数SELECT ROUND(65.654,2),ROUND(65.654,0),ROUND(65.654,-1)FROM DUAL;SELECT TRUNC(65.654,2),TRUNC(65.654,0),TRUNC(65.654,-1)FROM DUAL;SELECT employee_id, last_name, salar

9、y, MOD(salary,900)FROM employeesWHERE department_id=90;21第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6通用函数224.4.1日期类型数学运算日期类型数学运算常用的日期运算如下：日期类型列或表达式可以加减数字，功能是在该日期上加减对应的天数。如：10-AUG-06+15结果是25-AUG-06。日期类型列或表达式之间可以进展减操作，功能是计算两个日期之间间隔了多少天。如：10-AUG-06-4-AUG-06结果四舍五入后是6天。假设需求加减相应小时或分钟，可以运用n/24来实

10、现。234.4.2常用日期函数常用日期函数 1/6SYSDATE：前往系统日期MONTHS_BETWEEN：前往两个日期间隔的月数ADD_MONTHS：在指定日期根底上加上相应的月数NEXT_DAY：前往某一日期的下一个指定日期 LAST_DAY：前往指定日期当月最后一天的日期ROUND(date,fmt)将date按照fmt指定的格式进展四舍五入，fmt为可选项，假设没有指定fmt，那么默以为DD，将date四舍五入为最近的天。格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SSTRUNC(date,fmt)将date按照fmt指定的格式进展截取，fmt为可选项，假设没有指

11、定fmt，那么默以为DD，将date截取为最近的天。 EXTRACT：前往从日期类型中取出指定年、月、日 244.4.2常用日期函数常用日期函数 2/6MONTHS_BETWEEN 函数演示函数演示公司员工效力的月数。公司员工效力的月数。 ADD_MONTHS 函数演示函数演示99年公司员工转正日期。年公司员工转正日期。SELECT last_name, salary, MONTHS_BETWEEN(SYSDATE,hire_date) monthsFROM employeesORDER BY months;SELECT last_name, salary, hire_date, ADD_MO

12、NTHS(hire_date,3) new_dateFROM employeesWHERE hire_date01-1月-2019;254.4.2常用日期函数常用日期函数 3/6NEXT_DAY 函数演示函数演示下周一的日期。下周一的日期。LAST_DAY 函数演示函数演示06年年2月月2日所在月份最后一天。日所在月份最后一天。 SELECT NEXT_DAY(02-2月-06,星期一) NEXT_DAYFROM DUAL;SELECT LAST_DAY(02-2月-2019) LAST DAYFROM DUAL;264.4.2常用日期函数常用日期函数 4/6ROUND函数演示函数演示98年入

13、职员工入职日期按月四舍五年入职员工入职日期按月四舍五入。入。 SELECT employee_id, hire_date, ROUND(hire_date, MONTH)FROM employeesWHERE SUBSTR(hire_date,-2,2)=98;274.4.2常用日期函数常用日期函数 5/6TRUNC 函数演示函数演示98年入职员工入职日期按月截断。年入职员工入职日期按月截断。 SELECTemployee_id, hire_date, TRUNC(hire_date, MONTH)FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;284.

14、4.2常用日期函数常用日期函数 6/6EXTRACT 函数语法部门编号是90的部门中一切员工入职月份。EXTRACT (YEAR MONTHDAY FROM 日期类型表达式)SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTHFROM employeesWHERE department_id = 90;29第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6通用函数304.5转换函数转换函数314.5.1数据类型隐性转换数据类型隐性转换假设不同的数据类型之间关

15、联，假设不显式转换数据，那么它会根据以下规假设不同的数据类型之间关联，假设不显式转换数据，那么它会根据以下规那么对数据进展隐式转换：那么对数据进展隐式转换： 对于对于INSERT和和UPDATE操作，操作，oracle会把插入值或者更新值隐式转换为字会把插入值或者更新值隐式转换为字段的数据类型段的数据类型对于对于SELECT语句，语句，oracle会把字段的数据类型隐式转换为变量的数据类型会把字段的数据类型隐式转换为变量的数据类型当比较一个字符型和数值型的值时，当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值会把字符型的值隐式转换为数值型型当比较字符型和日期型的数据时，

16、当比较字符型和日期型的数据时，oracle会把字符型转换为日期型会把字符型转换为日期型用衔接操作符用衔接操作符(|)时，时，oracle会把非字符类型的数据转换为字符类型会把非字符类型的数据转换为字符类型假设字符类型的数据和非字符类型的数据假设字符类型的数据和非字符类型的数据(如如number、date、rowid等等)作算作算术运算，那么术运算，那么oracle会将字符类型的数据转换为适宜的数据类型，这些数据会将字符类型的数据转换为适宜的数据类型，这些数据类型能够是类型能够是number、date、rowid等等324.5.2数据类型显性转换数据类型显性转换 1/2通常是在字符类型、日期类型

17、、数字类型之间进展显性转换。主要有3个显性函数： TO_CHARTO_NUMBERTO_DATE334.5.2数据类型显性转换数据类型显性转换 2/2344.5.3TO_CHAR函数函数 1/6TO_CHAR(date|number ,fmt)把日期类型/数字类型的表达式或列转换为字符类型。fmt指的是需求显示的格式：需求写在单引号中，并且是大小写敏感可包含任何有效的日期格式354.5.3TO_CHAR函数函数 2/6常用日期格式YYYY：4位数字表示年份；YY：2位数字表示年份，但是无世纪转换(与RR区别在后面章节引见)；RR：2位数字表示年份，有世纪转换与YY区别在后面章节引见；YEAR：

18、年份的英文拼写；MM：两位数字表示月份；MONTH：月份英文拼写；364.5.3TO_CHAR函数函数 3/6常用日期格式续DY：星期的英文前三位字母；DAY：星期的英文拼写；D：数字表示一星期的第几天，星期天是一周的第一天。DD：数字表示一个月中的第几天；DDD：数字表示一年中的第几天。374.5.3TO_CHAR函数函数 4/6常用时间格式AM 或PM：上下午表示；HH 或HH12或HH24：数字表示小时。HH12代表12小时计时，HH24代表24小时计时；MI：数字表示分钟；SS：数字表示秒；384.5.3TO_CHAR函数函数 5/6一些特殊格式TH：显示数字表示的英文序数词，如：DD

19、TH显示天数的序数词。SP：显示数字表示的拼写。SPTH：显示数字表示的序数词的拼写。 “字符串:如在格式中显示字符串，需求两端加双引号。TO_CHAR(SYSDATE,DDSPTH)TO_CHAR(SYSDATE,DD “of MONTH )394.5.3TO_CHAR函数函数 6/6日期到字符型转换TO_CHAR 函数进展日期到字符型复杂格式转换演示。ALTER SESSION SET NLS_LANGUAGE = AMERICAN;SELECT employee_id, last_name, TO_CHAR(hire_date,Day ,the Ddspth of YYYY HH24:M

20、I:SS) hire_dateFROM employeesWHERE department_id=90;404.5.4TO_NUMBER函数和函数和TO_DATE函数函数 1/3TO_NUMBER(char,fmt) 把字符类型列或表达式转换为数字类型。运用格式和TO_CHAR中转换成字符类型中的格式一样TO_DATE(char,fmt) 把字符类型列或表达式转换为日期类型。格式和TO_CHAR中转换成字符类型中的格式一样。414.5.4TO_NUMBER函数和函数和TO_DATE函数函数 2/3详细格式如下：9：一位数字；0：一位数字或前导零；$：显示为美圆符号；L：显示按照区域设置的本地货

21、币符号；.：小数点;,：千位分割符；424.5.4TO_NUMBER函数和函数和TO_DATE函数函数 3/3TO_CHAR 函数进展数字到字符型格式转换函数进展数字到字符型格式转换留意：进展数字类型到字符型转换，格式中的宽度一定要超越留意：进展数字类型到字符型转换，格式中的宽度一定要超越实践列宽度，否那么会显示为实践列宽度，否那么会显示为#。 SELECT last_name, TO_CHAR(salary, $99,999.00) salaryFROM employeesWHERE last_name = King;SELECT last_name, TO_CHAR(salary, $9,

22、999.00) salaryFROM employeesWHERE last_name = King;434.5.5RR功能功能当前年份当前年份2019201920192019指定的日期指定的日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式格式2019201920192019YY 格式格式2019191720192095假设当前年份假设当前年份的最后两位数的最后两位数是：是：04904950995099前往的日期是当前世纪前往的日期是当前世纪的日期的日期前往的日期是下一个前往的日期是下一个世纪的日期世纪的日期前往的日期是上一个世前往的日期是上一个世纪的日期

23、纪的日期前往的日期是当前世纪前往的日期是当前世纪的日期的日期假设指定两位数年份是：假设指定两位数年份是：44第四章 单行函数单行函数:4.1函数的概念及分类4.2字符函数4.3数字函数4.4日期函数4.5转换函数4.6通用函数454.6通用函数通用函数与空值NULL相关的一些函数，完成对空值NULL的一些操作。主要包括以下函数：NVLNVL2NULLIFCOALESCE条件处置函数：CASE表达式DECODE464.6.1NVL函数函数NVL (表达式1, 表达式2)函数功能是空值转换，把空值转换为其他值，处理空值问题。表达式1是需求转换的列或表达式，表达式2是假设第一个参数为空时，需求转换的

24、值。NVL(comm,0)NVL(hire_date,01-JAN-06)NVL(job_id,No Job Yet)留意：数据格式可以是日期、字符、数字，但数据类型必需匹配。 474.6.2NVL2函数函数NVL2(表达式1, 表达式2, 表达式3)函数是对第一个参数进展检查。假设第一个参数不为空，那么输出第二个参数；假设第一个参数为空，那么输出第三个参数。表达式1可以为任何数据类型。SELECT last_name, salary,NVL2(commission_pct, salary + commission_pct,salary) incomeFROM employeesWHERE l

25、ast_name LIKE _a%;484.6.3NULLIF函数函数NULLIF (表达式1, 表达式2)函数主要是完成两个参数的比较。当两个参数不相等时，前往值是第一个参数值；当两个参数相等时，前往值是空值。SELECT last_name, LENGTH(last_name) LEN_last_NAME, , LENGTH() LEN_EMAIL, NULLIF(LENGTH(last_name), LENGTH() resultFROM employeesWHERE last_name LIKE D%;494.6.4COALESCE函数函数COALESCE (表达式1, 表达式2, .

26、 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是前往第一个不为空的参数，参数个数不受限制。SELECT last_name, COALESCE(commission_pct, salary*1.1, 100) comm , department_idFROM employeesWHERE department_id in (50,80)ORDER BY COMM;504.6.5CASE和和DECODE函数函数 1/3CASE语法CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THE

27、N return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND514.6.5CASE和和DECODE函数函数 2/3SELECT last_name, commission_pct, (CASE commission_pct WHEN 0.1 THEN 低 WHEN 0.2 THEN 中 WHEN 0.3 THEN 高 ELSE 无 END) Commission FROM employeesWHERE commission_pct IS NOT NULLORDER BY last_name;CASE例如例如524.6.5CASE和和DECODE函数函数 3/3DECODE(字段|表达式, 条件1