东软o课件第4章单行函数

1、Oracle SQL开发基础课程结构2内容课时（H）第一章 Oracle数据库基础2.5第二章 编写简单的语句1.5第三章 限制数据和对数据排序2第四章 单行函数4第五章 多表4第六章 分组函数3第七章 子4第八章 数据操作与事务4第九章 表和约束4第十章 其他数据库对象4第四章 单行函数目标:本章旨在向学员介绍：1) 字符函数、数字函数和日期函数2) 转换函数和通用函数时间：学时教学方法：3本章要点 字符函数 数值函数 日期函数 转换函数 通用函数 函数嵌套4第四章 单行函数单行函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数54

2、.1.1单行函数介绍 1/5输入输出参数 1函数执行作用参数 2参数n6结果值函数4.1.1单行函数介绍 2/5函数类型Functions单行函数多行函数74.1.1单行函数介绍 3/5 语法:函数名(参数1，参数2,) 其中的参数可以是以下之一： 变量 列名 表84.1.1单行函数介绍 4/5 单行函数还有以下的一些特征： 单行函数对单行操作 每行返回一个结果 有可能返回值与原参数数据类型不一致（转换函数） 单行函数可以写在SELECT、WHERE、ORDER BY子句中 有些函数没有参数，有些函数包括一个或多个参数 函数可以嵌套94.1.1单行函数介绍 5/5单行函数的分类数字函数日期类型

3、字符函数转换函数通用函数10单行函数第四章 单行函数单行函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数114.2字符函数 字符函数：主要指参数类型是字符型，不同函数返回值可能是字符型或数字类型。大小写转换字符处理CONCAT SUBSTR LENGTH INSTR LPAD RPAD REPLACETRIMLOWER UPPERINITCAP12字符函数4.2.1字符大小写操作函数 LOWER(列名|表小写 UPPER(列名|表大写 INITCAP(列名|表)：将大写或大小写混合的字符转换成) ：将小写或大小写混合的字符转换成)

4、：将每个单词的第一个字母转换成大写，其余的字母都转换成小写13函数结果LOWER(SQL Course) UPPER(SQL Course) INITCAP(SQL Course)sql course SQL COURSESql Course4.2.2字符处理函数CONCAT:连接两个值,等同于|格式：CONCAT(column1|expression1,column2|expression2)SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串，如果n1是负值，表从后向前数的abs(n1)位，如果n2省略,取n1之后的所有字符格式：SUBSTR(column | expression

5、,n1,n2)LENGTH:取字符长度格式:LENGTH(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替换

6、。格式:REPLACE(s1,s2,s3)144.2.2字符处理函数15函数结果CONCAT(Good, String) SUBSTR(String,1,3)LENGTH(String)INSTR(String, r)GoodString Str634.2.2字符处理函数16函数结果LPAD(sal,10,*)RPAD(sal,10,*) TRIM(S FROM SSMITH)REPLACE（abc,b,d)*5000 5000* MITHadc4.2.2字符处理函数 查找公司员工编号，用户名（first_name与last_name连接成一个字符串），职位编号及last_name的长度，要求

7、职位从第四位起匹配ACCOUNT，同时last_name中至少包含一个e 字母。17SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name) lengthFROMemployeesWHERE SUBSTR(job_id, 4) = ACCOUNTANDINSTR(last_name, e)0;第四章 单行函数单行函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数184.3数字函数 ROUND(列名|表, n)：将列或表所表示的数

8、值四入到小数点后的第n位。 TRUNC(列名|表数点后的第n位。,n)：将列或表所表示的数值截取到小 MOD(m,n)：取m除以n后得到的余数。194.3数字函数20SELECT employee_id, last_name, salary,MOD(salary,900) FROM employeesWHERE department_id=90;SELECT TRUNC(65.654,2),TRUNC(65.654,0),TRUNC(65.654,-1)FROM DUAL;SELECT ROUND(65.654,2),ROUND(65.654,0), ROUND(65.654,-1)FROM

9、DUAL;第四章 单行函数单行函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数214.4.1日期类型数 常用的日期运算如下：算 日期类型列或表可以加减数字，功能是在该日期上加减对应的天数。如：10-AUG-06+15结果是25-AUG-06。 日期类型列或表之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：10-AUG-06-4-AUG-06入后是6天。结果四 如果需要加减相应小时或分钟，可以使用n/24来实现。224.4.2常用日期函数 1/6SYSDATE：返回系统日期MONTHS_BETWEEN：返回两个日期间隔的月

10、数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为可选项，如果没有指定fmt，则默认为DD，将date截取为最近的天。EXTRACT：返回从日期类型中取出指定年、月、日234.4.2常用日期函数 2/6 MO

11、NTHS_BETWEEN 函数演示公司员工服务的月数。 ADD_MONTHS 函数演示99年公司员工转正日期。24SELECT last_name, salary, hire_date,ADD_MONTHS(hire_date,3) new_date FROMemployeesWHERE hire_date01-1月-1999;SELECT last_name, salary,MONTHS_BETWEEN(SYSDATE,hire_date) monthsFROM employees ORDER BY months;4.4.2常用日期函数 3/6 NEXT_DAY 函数演示下周一的日期。 LA

12、ST_DAY 函数演示06年2月2日所在月份最后一天。25SELECT LAST_DAY(02-2月-2006) LAST DAY FROM DUAL;SELECT NEXT_DAY(02-2月-06,一) NEXT_DAYFROM DUAL;4.4.2常用日期函数 4/6 ROUND函数演示98年入职员工入职日期按月四 入。26SELECT employee_id, hire_date,ROUND(hire_date, MONTH) FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;4.4.2常用日期函数 5/6 TRUNC 函数演示98年入职员工入职日

13、期按月截断。27SELECTemployee_id, hire_date, TRUNC(hire_date, MONTH)FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;4.4.2常用日期函数 6/6 EXTRACT 函数语法 部门编号是90的部门中所有员工入职月份。28SELECT last_name, hire_date,EXTRACT (MONTH FROM HIRE_DATE) MONTHFROMemployeesWHERE department_id = 90;EXTRACT (YEAR MONTHDAYFROM日期类型表)第四章 单行函数单行

14、函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数294.5转换函数数据类型转换显式数据类型转换隐式数据类型转换304.5.1数据类型隐性转换 如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则 对数据进行隐式转换：对于INSERT和UPDATE操作，oracle会把字段的数据类型对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型当比较字符型和日期型的数据时，oracle会把字符型转换为日期型用连接操作符(|)时，ora

15、cle会把非字符类型的数据转换为字符类型如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等值或者更新值隐式转换为314.5.2数据类型显性转换 1/2 通常是在字符类型、日期类型、数字类型之间进行显性转换。 主要有3个显性函数： TO_CHAR TO_NUMBER TO_DATE324.5.2数据类型显性转换 2/2DATETO_CHARTO_DATECHARACTERTO_NUMBERTO_CHARNUMBER334.5.3TO_CHAR函数 1

16、/6 TO_CHAR(date|number ,fmt)把日期类型/数字类型的表达式或列转换为字符类型。 fmt指的是需要显示的格式： 可包含任何有效的日期格式344.5.3TO_CHAR函数 2/6 常用日期格式 YYYY：4位数字表示年份； YY：2位数字表示年份，但是无世纪转换(与RR区别在后面 章节介绍)； RR：2位数字表示年份，有世纪转换（与YY区别在后面章 节介绍）； YEAR：年份的英文拼写； MM：两位数字表示月份； MONTH：月份英文拼写；354.5.3TO_CHAR函数 3/6 常用日期格式（续） DY： DAY：的英文前三位字母；的英文拼写； D：数字表示一 DD：数

17、字表示一的第几天，中的第几天；天是一周的第一天。 DDD：数字表示一年中的第几天。364.5.3TO_CHAR函数 4/6 常用时间格式 AM 或PM：上下午表示； HH 或HH12或HH24：数字表示小时。HH12代表12时，HH24代表24 MI：数字表示分钟； SS：数字表示秒；时；374.5.3TO_CHAR函数 5/6 一些特殊格式 TH：显示数字表示的英文序数词，如：DDTH显示天数的序 数词。 SP：显示数字表示的拼写。 SPTH：显示数字表示的序数词的拼写。 “字符串”:如在格式中显示字符串，需要两端加双引号。38TO_CHAR(SYSDATE,DD “of” MONTH )T

18、O_CHAR(SYSDATE,DDSPTH)4.5.3TO_CHAR函数 6/6 日期到字符型转换 TO_CHAR 函数进行日期到字符型复杂格式转换演示。39SELECT employee_id, last_name,TO_CHAR(hire_date,Day ,the Ddspth of YYYY HH24:MI:SS) hire_dateFROM employeesWHERE department_id=90;ALTER SESSION SET NLS_LANGUAGE = AMERICAN;4.5.4TO_NUMBER函数和TO_DATE函数 1/3 TO_NUMBER(char,fmt

19、) 把字符类型列或表类型。 使用格式和TO_CHAR中转换成字符类型中的格式相同转换为数字 TO_DATE(char,fmt) 把字符类型列或表转换为日期类型。 格式和TO_CHAR中转换成字符类型中的格式相同。404.5.4TO_NUMBER函数和TO_DATE函数 2/3 具体格式如下： 9：一位数字； 0：一位数字或前导零； $：显示为符号； L：显示按照区域设置的本地货币符号； .：小数点; ,：千位分割符；414.5.4TO_NUMBER函数和TO_DATE函数 3/3 TO_CHAR 函数进行数字到字符型格式转换 注意：进行数字类型到字符型转换，格式中的宽度一定要超过实际列宽度，否

20、则会显示为#。42SELECT last_name, TO_CHAR(salary, $9,999.00) salary FROM employeesWHERE last_name = King;SELECT last_name, TO_CHAR(salary, $99,999.00) salaryFROMemployeesWHERE last_name = King;4.5.5RR功能43如果指定两位数年份是：0495099如果当前年份的最后两位数是：049返回的日期是当前世纪的日期返回的日期是上一个世纪的日期5099返回的日期是下一个世纪的日期返回的日期是当前世纪的日期当前年份指定的日期R

21、R 格式YY 格式199519952001200127-OCT-9527-OCT-1727-OCT-1727-OCT-9519952017201719951995191720172095第四章 单行函数单行函数:4.1 函数的概念及分类4.2 字符函数4.3 数字函数4.4 日期函数4.5 转换函数4.6 通用函数444.6通用函数 与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操作。主要包括以下函数： NVL NVL2 NULLIF COALESCE 条件处理函数： CASE表 DECODE454.6.1NVL函数 NVL (表1, 表2)函数功能是空值转换，把空值转换为其他

22、值，1是需要转换的列或表2是如果解决空值问题。表，表第一个参数为空时，需要转换的值。 NVL(comm,0) NVL(hire_date,01-JAN-06) NVL(job_id,No Job Yet) 注意：数据格式可以是日期、字符、数字，但数据类型必须匹配。464.6.2NVL2函数 NVL2(表1, 表2, 表3)函数是对第一个参数进行检查。如果第一个参数不为空，则输出第二个参数；如果第一个1可以为任何数据类型。参数为空，则输出第三个参数。表47SELECT last_name, salary,NVL2(commission_pct, salary + commission_pct,s

23、alary) incomeFROM employeesWHERE last_name LIKE _a%;4.6.3NULLIF函数 NULLIF (表1, 表2)函数主要是完成两个参数的比较。当两个参数不相等时，返回值是第一个参数值；当两个参数相等时，返回值是空值。48SELECT last_name, LENGTH(last_name) LEN_last_NAME, LENGTH() LEN_,NULLIF(LENGTH(last_name), LENGTH() resultFROM employeesWHERE last_name LIKE D%;4.6.4COALESCE函数 COALE

24、SCE (表1, 表2, . 表n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数，参数个数不受限制。49SELECT last_name, COALESCE(commission_pct, salary*1.1, 100) comm , department_idFROMemployeesWHERE department_id in (50,80) ORDER BY COMM;4.6.5CASE和DECODE函数 1/3 CASE语法50CASE exprWHEN comparison_expr1 THEN return_expr1 WHEN compariso

25、n_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND4.6.5CASE和DECODE函数 2/3CASE示例51SELECT last_name, commission_pct, (CASE commission_pctWHEN 0.1 THEN 低WHEN 0.2 THEN 中 WHEN 0.3 THEN 高 ELSE 无END)Commission FROM employeesWHERE commission_pct IS NOT NULLORDER BY last_name;4.6.5CASE和DECODE函数 3/3 DECODE(字段|表值), 条件1,结果1,条件2