单行函数

2
1

programming
1
Oracle SQL开发基础

2
2

programming
课程结构
内容

课时（H）

第一章 Oracle数据库基础

2.5

第二章 编写简单的查询语句

1.5

第三章 限制数据和对数据排序

2

第四章 单行函数

4

第五章 多表查询

4

第六章 分组函数

3

第七章 子查询

4

第八章 数据操作与事务控制

4

第九章 表和约束

4

第十章 其他数据库对象

4

 

2
3

programming
第四章 单行函数

目标:

本章旨在向学员介绍：

1)字符函数、数字函数和日期函数

2)转换函数和通用函数

时间：学时
教学方法：
2
4

programming
本章要点

.字符函数
.数值函数
.日期函数
.转换函数
.通用函数
.函数嵌套

2
5

programming
第四章 单行函数

单行函数:
4.1函数的概念及分类
4.2字符函数
4.3数字函数
4.4日期函数
4.5转换函数
4.6通用函数
2
6

programming
4.1.1单行函数介绍 1/5

函数
函数

输入
输入
参数
1
参数 1

参数
2
参数 2
参数
n
参数n

函数执行作用
函数执行作用
输出
输出

结果值
结果值

2
7

programming
4.1.1单行函数介绍 2/5
Functions
Functions

单行函数
单行函数 多行函数
多行函数

函数类型
2
8

programming
4.1.1单行函数介绍 3/5

.语法:

函数名[(参数1，参数2,…)]

.其中的参数可以是以下之一：
–变量
–列名
–表达式

 

2
9

programming
4.1.1单行函数介绍 4/5
.单行函数还有以下的一些特征：
–单行函数对单行操作
–每行返回一个结果
–有可能返回值与原参数数据类型不一致（转换函数）
–单行函数可以写在SELECT、WHERE、ORDER BY子句中
–有些函数没有参数，有些函数包括一个或多个参数
–函数可以嵌套

 

 

2
10

programming
4.1.1单行函数介绍 5/5
单行函数
单行函数

通用函数
通用函数
字符函数
字符函数

数字函数
数字函数
日期类型
日期类型
转换函数
转换函数
单行函数的分类

2
11

programming
第四章 单行函数

单行函数:
4.1函数的概念及分类
4.2字符函数
4.3数字函数
4.4日期函数
4.5转换函数
4.6通用函数
2
12

programming
4.2字符函数

字符函数
字符函数
LOWER
UPPER
INITCAP
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
LPAD
RPAD
REPLACE
TRIM
CONCAT
SUBSTR
LENGTH
INSTR
LPAD
RPAD
REPLACE
TRIM
大小写转换
大小写转换

字符处理
字符处理

.字符函数：主要指参数类型是字符型，不同函数返回值可能是
字符型或数字类型。

2
13

programming
4.2.1字符大小写操作函数

.LOWER(列名|表达式)：将大写或大小写混合的字符转换成
小写
.UPPER(列名|表达式) ：将小写或大小写混合的字符转换成
大写
.INITCAP(列名|表达式) ：将每个单词的第一个字母转换成
大写，其余的字母都转换成小写

函数 结果
LOWER('SQL Course')

UPPER('SQL Course')

INITCAP('SQL Course')

sql course

SQL COURSE

Sql Course

2
14

programming
4.2.2字符处理函数
.CONCAT:连接两个值,等同于||
格式：CONCAT(column1|expression1,column2|expression2)
.SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串，如果n1是负值，表从后向前数的abs(n1)位，如果n2省略,取n1之后的所有字符
格式：SUBSTR(column | expression,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替换。
格式:REPLACE(s1,s2,s3)
.

2
15

programming
4.2.2字符处理函数
CONCAT('Good', 'String')
SUBSTR('String',1,3)
LENGTH('String')
INSTR('String', 'r')
GoodString

Str

6

3

函数

结果
2
16

programming
4.2.2字符处理函数

LPAD(sal,10,'*')
RPAD(sal,10,'*')
TRIM('S' FROM 'SSMITH')
REPLACE（'abc','b','d')
******5000
5000******
MITH
adc
函数

结果

2
17

programming
4.2.2字符处理函数
.查找公司员工编号，用户名（first_name与last_name连接成
一个字符串），职位编号及last_name的长度，要求职位从第
四位起匹配'ACCOUNT'，同时last_name中至少包含一个’e’
字母。

SELECT employee_id, CONCAT(first_name, last_name)

 NAME, job_id, LENGTH (last_name) length

FROM employees

WHERE SUBSTR(job_id, 4) = 'ACCOUNT'

AND INSTR(last_name, 'e')>0;

2
18

programming
第四章 单行函数
单行函数:
4.1函数的概念及分类
4.2字符函数
4.3数字函数
4.4日期函数
4.5转换函数
4.6通用函数
2
19

programming
4.3数字函数

.ROUND(列名|表达式, n)：将列或表达式所表示的数值四舍五
入到小数点后的第n位。
.TRUNC(列名|表达式,n)：将列或表达式所表示的数值截取到小
数点后的第n位。
.MOD(m,n)：取m除以n后得到的余数。

2
20

programming
4.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, salary, MOD(salary,900)
FROM employees
WHERE department_id=90;
2
21

programming
第四章 单行函数
单行函数:

4.1函数的概念及分类

4.2字符函数

4.3数字函数

4.4日期函数

4.5转换函数

4.6通用函数

2
22

programming
4.4.1日期类型数学运算
.常用的日期运算如下：
–日期类型列或表达式可以加减数字，功能是在该日期上加
减对应的天数。如：’10-AUG-06’+15结果是’25-AUG-
06’。
–日期类型列或表达式之间可以进行减操作，功能是计算两
个日期之间间隔了多少天。如：’10-AUG-06’-‘4-AUG-06’
结果四舍五入后是6天。
–如果需要加减相应小时或分钟，可以使用n/24来实现。

 

 

2
23

programming
4.4.2常用日期函数 1/6
.SYSDATE：返回系统日期
.MONTHS_BETWEEN：返回两个日期间隔的月数
.ADD_MONTHS：在指定日期基础上加上相应的月数
.NEXT_DAY：返回某一日期的下一个指定日期
.LAST_DAY：返回指定日期当月最后一天的日期
.ROUND(date[,'fmt'])将date按照fmt指定的格式进行四舍五入，fmt为可选项，
如果没有指定fmt，则默认为DD’，将date四舍五入为最近的天。

 格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS

.TRUNC(date[,'fmt'])将date按照fmt指定的格式进行截取，fmt为可选项，如
果没有指定fmt，则默认为‘DD’，将date截取为最近的天。
.EXTRACT：返回从日期类型中取出指定年、月、日

2
24

programming
4.4.2常用日期函数 2/6
.MONTHS_BETWEEN 函数演示——公司员工服务的月数。
.ADD_MONTHS 函数演示——99年公司员工转正日期。

SELECT last_name, salary,
MONTHS_BETWEEN(SYSDATE,hire_date) months

FROM employees

ORDER BY months;

SELECT last_name, salary, hire_date,
ADD_MONTHS(hire_date,3) new_date
FROM employees
WHERE hire_date>'01-1月-1999';
2
25

programming
4.4.2常用日期函数 3/6
.NEXT_DAY 函数演示——下周一的日期。
.LAST_DAY 函数演示——06年2月2日所在月份最后一天。

SELECT NEXT_DAY('02-2月-06','星期一') NEXT_DAY
FROM DUAL;
SELECT LAST_DAY('02-2月-2006') "LAST DAY"
FROM DUAL;
2
26

programming
4.4.2常用日期函数 4/6
.ROUND函数演示——98年入职员工入职日期按月四舍五
入。

SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH')
FROM employees
WHERE SUBSTR(hire_date,-2,2)='98';
2
27

programming
4.4.2常用日期函数 5/6
.TRUNC 函数演示——98年入职员工入职日期按月截断。

SELECT employee_id, hire_date, TRUNC(hire_date,
'MONTH')
FROM employees
WHERE SUBSTR(hire_date,-2,2)='98';
2
28

programming
4.4.2常用日期函数 6/6
.EXTRACT 函数语法
.部门编号是90的部门中所有员工入职月份。

EXTRACT ([YEAR] [MONTH][DAY]
FROM [日期类型表达式])
SELECT last_name, hire_date,
EXTRACT (MONTH FROM HIRE_DATE) MONTH
FROM employees
WHERE department_id = 90;
2
29

programming
第四章 单行函数
单行函数:
4.1函数的概念及分类
4.2字符函数
4.3数字函数
4.4日期函数
4.5转换函数
4.6通用函数
2
30

programming
4.5转换函数
隐式数据类型转换
隐式数据类型转换 显式数据类型转换
显式数据类型转换
数据类型转换
数据类型转换
2
31

programming
4.5.1数据类型隐性转换
.如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则
对数据进行隐式转换：
–对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为
字段的数据类型
–对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据
类型
–当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为
数值型
–当比较字符型和日期型的数据时，oracle会把字符型转换为日期型
–用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型
–如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作
算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些
数据类型可能是number、date、rowid等

 

2
32

programming
4.5.2数据类型显性转换 1/2
.通常是在字符类型、日期类型、数字类型之间进行显性转换。
主要有3个显性函数：
–TO_CHAR
–TO_NUMBER
–TO_DATE

 

2
33

programming
4.5.2数据类型显性转换 2/2
CHARACTER
CHARACTER
DATE
DATE
NUMBER
NUMBER

TO_DATE
TO_DATE
TO_NUMBER
TO_NUMBER
TO_CHAR
TO_CHAR
TO_CHAR
TO_CHAR

2
34

programming
4.5.3TO_CHAR函数 1/6
.TO_CHAR(date|number [,‘fmt’])把日期类型/数字类型的表达
式或列转换为字符类型。
–‘fmt’指的是需要显示的格式：
–需要写在单引号中，并且是大小写敏感
–可包含任何有效的日期格式

 

2
35

programming
4.5.3TO_CHAR函数 2/6

.常用日期格式
–YYYY：4位数字表示年份；
–YY：2位数字表示年份，但是无世纪转换(与RR区别在后面
章节介绍)；
–RR：2位数字表示年份，有世纪转换（与YY区别在后面章
节介绍）；
–YEAR：年份的英文拼写；
–MM：两位数字表示月份；
–MONTH：月份英文拼写；

 

2
36

programming
4.5.3TO_CHAR函数 3/6
.常用日期格式（续）
–DY：星期的英文前三位字母；
–DAY：星期的英文拼写；
–D：数字表示一星期的第几天，星期天是一周的第一天。
–DD：数字表示一个月中的第几天；
–DDD：数字表示一年中的第几天。

 

2
37

programming
4.5.3TO_CHAR函数 4/6

.常用时间格式
–AM 或PM：上下午表示；
–HH 或HH12或HH24：数字表示小时。HH12代表12小时计
时，HH24代表24小时计时；
–MI：数字表示分钟；
–SS：数字表示秒；

 

2
38

programming
4.5.3TO_CHAR函数 5/6
.一些特殊格式
–TH：显示数字表示的英文序数词，如：DDTH显示天数的序
数词。
–SP：显示数字表示的拼写。
–SPTH：显示数字表示的序数词的拼写。
–“字符串”:如在格式中显示字符串，需要两端加双引号。

 

TO_CHAR(SYSDATE,’DDSPTH’)
TO_CHAR(SYSDATE,’DD “of” MONTH ‘)
2
39

programming
4.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:MI:SS')
hire_date
FROM employees
WHERE department_id=90;
2
40

programming
4.5.4TO_NUMBER函数和TO_DATE函数 1/3
.TO_NUMBER(char[,’fmt’]) 把字符类型列或表达式转换为数字
类型。
–使用格式和TO_CHAR中转换成字符类型中的格式相同

.TO_DATE(char[,‘fmt’]) 把字符类型列或表达式转换为日期类型。
–格式和TO_CHAR中转换成字符类型中的格式相同。

 

2
41

programming
4.5.4TO_NUMBER函数和TO_DATE函数 2/3
.具体格式如下：
–9：一位数字；
–0：一位数字或前导零；
–$：显示为美元符号；
–L：显示按照区域设置的本地货币符号；
–.：小数点;
–,：千位分割符；

 

2
42

programming
4.5.4TO_NUMBER函数和TO_DATE函数 3/3
.TO_CHAR 函数进行数字到字符型格式转换
.注意：进行数字类型到字符型转换，格式中的宽度一定要超过
实际列宽度，否则会显示为###。

SELECT last_name, TO_CHAR(salary, '$99,999.00') salary
FROM employees
WHERE last_name = 'King';
SELECT last_name, TO_CHAR(salary, '$9,999.00') salary
FROM employees
WHERE last_name = 'King';
2
43

programming
4.5.5RR功能
当前年份
1995
1995
2001
2001
指定的日期
27-OCT-95
27-OCT-17
27-OCT-17
27-OCT-95
RR 格式
1995
2017
2017
1995
YY 格式
1995
1917
2017
2095
如果当前年份
的最后两位数
是：
0–49
0–49

50–99
50–99
返回的日期是当前世纪
的日期
返回的日期是下一个
世纪的日期
返回的日期是上一个世
纪的日期
返回的日期是当前世纪
的日期
如果指定两位数年份是：
2
44

programming
第四章 单行函数
单行函数:
4.1函数的概念及分类
4.2字符函数
4.3数字函数
4.4日期函数
4.5转换函数
4.6通用函数
2
45

programming
4.6通用函数
.与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操作。
主要包括以下函数：
–NVL
–NVL2
–NULLIF
–COALESCE

.条件处理函数：
–CASE表达式
–DECODE

 

2
46

programming
4.6.1NVL函数
.NVL (表达式1, 表达式2)函数功能是空值转换，把空值转换为其他值，
解决空值问题。表达式1是需要转换的列或表达式，表达式2是如果
第一个参数为空时，需要转换的值。
–NVL(comm,0)
–NVL(hire_date,'01-JAN-06')
–NVL(job_id,'No Job Yet')

.注意：数据格式可以是日期、字符、数字，但数据类型必须匹配。

2
47

programming
4.6.2NVL2函数
.NVL2(表达式1, 表达式2, 表达式3)函数是对第一个参数进行检
查。如果第一个参数不为空，则输出第二个参数；如果第一个
参数为空，则输出第三个参数。表达式1可以为任何数据类型。

SELECT last_name, salary,NVL2(commission_pct,
salary + commission_pct,salary) income
FROM employees
WHERE last_name LIKE '_a%';
2
48

programming
4.6.3NULLIF函数
.NULLIF (表达式1, 表达式2)函数主要是完成两个参数的比较。
当两个参数不相等时，返回值是第一个参数值；当两个参数相
等时，返回值是空值。

SELECT last_name, LENGTH(last_name) LEN_last_NAME,
email, LENGTH(email) LEN_EMAIL,
NULLIF(LENGTH(last_name), LENGTH(email)) result
FROM employees
WHERE last_name LIKE 'D%';
2
49

programming
4.6.4COALESCE函数
.COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL函数
的扩展。COALESCE函数的功能是返回第一个不为空的参数，
参数个数不受限制。

SELECT last_name, COALESCE(commission_pct,
salary*1.1, 100) comm , department_id
FROM employees
WHERE department_id in (50,80)
ORDER BY COMM;
2
50

programming
4.6.5CASE和DECODE函数 1/3
.CASE语法

CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
2
51

programming
4.6.5CASE和DECODE函数 2/3
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN '低'
WHEN 0.2 THEN '中'
WHEN 0.3 THEN '高'
ELSE '无'
END) Commission
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY last_name;
CASE示例
2
52

programming
4.6.5CASE和DECODE函数 3/3
.DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…，][,缺省
值]
)

SELECT last_name, commission_pct,
decode( commission_pct,
0.1,'低',
0.2,'中',
0.3 , '高',
'无') Commission
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY last_name;
2
53

programming
4.6.7函数嵌套 1/2
.单行函数可以嵌套于任何层。
.嵌套的函数是从最里层向最外层的顺序计算的。

F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2

Step 3 = Result 3
2
54

programming
4.6.7函数嵌套 2/2
SELECT employee_id,manager_id ,
NVL2(TO_CHAR(manager_id),to_char(manager_id),'No Manager')
FROM employees

2
55

programming
本章小结
.单行函数介绍
.字符函数的使用
.数字函数的使用
.日期函数的使用
.转换函数的使用
.其他函数的使用
.函数嵌套

2
56

programming
练习
1.计算2000年1月1日到现在有多少月，多少周（四舍五入）。

2.查询员工last_name的第三个字母是a的员工的信息(使用2个函
数)。

3.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’
分别处理得到下列字符串ello、Hello、ll、hello。

4.将员工工资按如下格式显示：123,234.00 RMB

5.查询员工的last_name及其经理（manager_id），要求对于没有
经理的显示“No Manager”字符串。

2
57

programming
练习
6.将员工的参加工作日期按如下格式显示：月份/年份。

7.在employees表中查询出员工的工资，并计算应交税款：如果
工资小于1000,税率为0，如果工资大于等于1000并小于2000，
税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。

2
58

programming
谢谢