1、函数介绍
1.1 什么是函数
函数:是数据库产品中提供的能够处理查询结果的方法。
函数的作用:
- 执行数据计算
- 修改单个数据项
- 格式化显示的日期和数字
- 转换列数据类型
- 函数有输入参数,并且总有一个返回值
1.2 函数类型
单行函数:函数仅对单个行进行运算,并且每行返回一个结果
多行函数:又称聚合函数、组函数,能够操控成组的行,并且每个行组给出一个结果
1.3 函数语法
例:function_name(arg1,arg2,…)
function_name 函数名
arg1,arg2,… 由函数使用的任意参数
参数可以是一个列名、用户提供的常数、变量值或者一个表达式
2 单行函数
2.1 单行函数的特性
单行函数的特性如下:
- 作用于每一个返回行,每行返回一个结果
- 可能需要一个或多个参数
- 可以修改结果集的数据类型
- 可以嵌套
- 可能返回一个与参数不同类型的数据值
- 能够用在 SELECT 、 WHERE 和 ORDER BY 子句中
2.2 单行函数分类
字符函数:接受字符输入,可以返回字符或者数字值
数字函数:接受数字输入,返回数字值
日期函数:对DATE数据类型的值进行运算(除了 MONTHS_BETWEEN 函数返回一个数字,所有日期函数都返回一个 DATE 数据类型的值)
转换函数:从一个数据类型到另一个数据类型转换
通用函数:例如NVL()、NVL2()、NULLIF()、COALSECE()、CASE()、DECODE()
2.3 字符函数
单行字符函数接受字符数据作为输入,既可以返回字符值也可以返回数字值。
2.3.1 字符函数分类
- 大小写处理函数:LOWER()、UPPER()、INITCAP()
- 字符串处理函数:CONCAT()、SUBSTR()、LENGTH()、INSTR()、LPAD() | RPAD()、TRIM()、REPLACE()
2.3.1.1 大小写处理函数
函数 | 结果 |
---|---|
LOWER(‘SQL Course’) | sql course |
UPPER(‘SQL Course’) | SQL COURSE |
INITCAP(‘SQL Course’) | Sql Course |
LOWER:转换大小写混合的字符串为小写字符串
SQL> select lower('HelloWorld') from dual;
LOWER('HELLOWORLD')
-------------------
helloworld
UPPER:转换大小写混合的字符串为大写字符串
SQL> select upper('HelloWorld') from dual;
UPPER('HELLOWORLD')
-------------------
HELLOWORLD
INITCAP:将每个单词的首字母转换为大写,其他字符为小写
SQL> select initcap('system') from dual;
INITCAP('SYSTEM')
-----------------
System
大小写处理函数需要一个参数,参数类型为字符串类型,返回一个字符串。
2.3.1.2 字符串处理函数
dual表:dual 是一张只有一个字段,一行记录的表。dual 表也称为伪表,因为它不存储具体数据。如果我们不需要从具体的表来取得表中数据,而是单纯的为了得到一些我们想得到的信息,并要通过select 完成时,就要借助dual 表来满足结构化查询语言的格式。
函数 | 结果 |
---|---|
CONCAT(‘Hello’,‘World’) | HelloWorld |
SUBSTR(‘HelloWorld’,1,5) | Hello |
LENGTH(‘HelloWorld’) | 10 |
INSTR(‘Hello’World’,‘w’) | 6 |
LPAD(‘salary’,10,‘*’) | ****salary |
RPAD(‘salary’,10,‘*’) | salary**** |
TRIM(‘H’ FROM ‘HelloWorld’) | elloWorld |
CONCAT(arg1,arg2):连接值在一起(CONCAT函数有两个输入参数)
arg1:字符串类型,字符拼接的值
arg2:字符串类型,字符拼接的值
SQL> select concat('apple','iphone') from dual;
CONCAT('APPLE','IPHONE')
------------------------
appleiphone
SUBSTR(arg1,arg2,arg3):截取子串
arg1:字符串类型,原字符串
arg2:整数类型,开始位置(开始位置可以是一个负数,‘-1’ 表示原串的最后一位,‘-2’ 则表示倒数第二位以此类推)
arg3:整数类型,截取个数,开始位置后的个数
SQL> select substr('qwertyuiop',5,6) from dual;
SUBSTR('QWERTYUIOP',5,6)
------------------------
tyuiop
SUBSTR(arg1,arg2):截取子串
arg1:字符串类型,原字符串
arg2:开始位置(开始位置可以是一个附属,‘-1’ 表示原串的最后一位,‘-2’ 则表示倒数第二位,以此类推)截取到末尾
SQL> select substr('qwertyuiop',5) from dual;
SUBSTR('QWERTYUIOP',5)
----------------------
tyuiop
LENGTH(arg1):以数字值显示一个字符串的长度
arg1:字符串类型,计算长度的字符串
SQL> select length('qwertyuiop') from dual;
LENGTH('QWERTYUIOP')
--------------------
10
INSTR(arg1,arg2):找到一个给定字符的数字位置
arg1:字符串类型,原字符串
arg2:字符串类型,查找内容
SQL> select instr('qwertyuiop','u') from dual;
INSTR('QWERTYUIOP','U')
-----------------------
7
INSTR(arg1,arg2,arg3,arg4):指定查找位置以及出现的次数
arg1:字符串类型,原字符串
arg2:字符串类型,查找内容
arg3:整数类型,开始位置
arg4:整数类型,第几次出现
SQL> select instr('HelloWorld','l',2,2) from dual;
INSTR('HELLOWORLD','L',2,2)
---------------------------
4
LPAD(arg1,arg2,arg3):用给定的字符左填充字符串到给定的长度
arg1:字符串类型,原字符串
arg2:整数类型,总长度
arg3:字符串类型,填充的子字符串
SQL> select lpad('World',10,'Hello') from dual;
LPAD('WORLD',10,'HELLO')
------------------------
HelloWorld
RPAD(arg1,arg2,arg3):用给定的字符右填充字符串到给定的长度
arg1:字符串类型,原字符串
arg2:整数类型,总长度
arg3:字符串类型,填充的子字符串
SQL> select rpad('Hello',10,'World') from dual;
RPAD('HELLO',10,'WORLD')
------------------------
HelloWorld
TRIM(arg1):从一个字符串中去除头(leading)或尾(trailing)或头尾两侧(both)的字符(默认为头尾两侧),如果trim_character 或trim_source 是一个文字字符,必须放在单引号中
arg1 需要操作的字符串,FROM 为关键字
格式1:需要去掉的内容,FROM 原字符串
SQL> select trim('H' FROM 'HelloWorldH') from dual;
TRIM('H'FROM'HELLOWORLDH')
--------------------------
elloWorld
格式2:leading|trailing|both 需要去掉的内容 FROM 原字符串
SQL> select trim(trailing 'H' FROM 'HelloWorldH') from dual;
TRIM(TRAILING'H'FROM'HELLOWORL
------------------------------
HelloWorld
REPLACE(arg1,arg2,arg3):REPLACE 函数是用另外一个值来替代串中的某个值
arg1:字符串类型,原字符串
arg2:字符串类型,需要替换的子串
arg3:字符串类型,替换的内容
SQL> select replace('HelloWorld','ll','LL') from dual;
REPLACE('HELLOWORLD','LL','LL'
------------------------------
HeLLoWorld
2.4 数字函数
- ROUND(arg1,arg2):四舍五入指定小数的值
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数,可以是一个负数,负则表示指定整数的位置
SQL> select round(126.456,2),round(126.456,-1) from dual;
ROUND(126.456,2) ROUND(126.456,-1)
---------------- -----------------
126.46 130
- ROUND(arg1):四舍五入保留整数
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数
SQL> select round(126.456) from dual;
ROUND(126.456)
--------------
126
- TRUNC(arg1,arg2):截断指定小数的值,不做四舍五入处理
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数,可以是一个负数,负则表示指定整数的位置
SQL> select trunc(126.456,2),trunc(126.456,-1) from dual;
TRUNC(126.456,2) TRUNC(126.456,-1)
---------------- -----------------
126.45 120
- TRUNC(arg1):四舍五入保留整数
arg1:数字类型,原数字
arg2:整数类型,小数点保留的位数
SQL> select trunc(126.456) from dual;
TRUNC(126.456)
--------------
126
- MOD:返回除法的余数
arg1:数字类型,被除数
arg2:数字类型,除数
SQL> select mod(1560,100) from dual;
MOD(1560,100)
-------------
60
2.5 数字函数
2.5.1 日期的使用
2.5.1.1 SYSDATE函数
SYSDATE 是一个日期函数,它返回当前数据库服务器的日期和时间
2.5.1.2 日期计算
- 从日期加或减一个数,结果是一个日期值
SQL> select sysdate + 1 from dual;
SYSDATE+1
-----------
2022-05-08
SQL> select sysdate - 1 from dual;
SYSDATE-1
-----------
2022-05-06
-
两个日期相减,得到两个日期之间的天数
-
用小时数除以24,可以加小时到日期上
SQL> select sysdate + 48/24 from dual;
SYSDATE+48/24
-------------
2022-05-09
运算 | 结果 | 说明 |
---|---|---|
date + number | 日期 | 加一个天数到一个日期上 |
date - number | 日期 | 从一个日期上减一个天数 |
date - date | 天数 | 用一个日期减另一个日期 |
date + number/24 | 日期 | 加一个小时数到一个日期上 |
2.5.2 日期函数
函数 | 说明 |
---|---|
MONTHS_BETWEEN | 两个日期之间的月数 |
ADD_MONTHS | 加月份到日期 |
NEXT_DAY | 下个星期几是几号 |
LAST_DAY | 指定月的最后一天 |
ROUND | 四舍五入日期 |
TRUNC | 截断日期 |
MONTHS_BETWEEN(date1,date2):计算 date1 和 date2 之间的月数。其结果可以是正的也可以是负的。如果 date1 大于 date2 ,结果是正的。反之,结果是负的。
date1:日期类型
date2:日期类型
ADD_MONTHS(date,n):添加 n 个月份到 date 。n 的值必须是整数,但可以是负的。
date:日期类型
n:整数
SQL> select add_months(sysdate,5) from dual;
ADD_MONTHS(SYSDATE,5)
---------------------
2022-10-07
NEXT_DAY(date,‘char’):计算在 date 之后的下一个周(‘char’)的指定天的日期。char 的值可能是一个表示一天的数或者是一个字符串。如果使用数字表示星期,1是从星期日开始。数字范围为:1-7。
date:日期类型
char:数字或字符串
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDATE,'星期一')
--------------------------
2022-05-09
LAST_DAY(date):计算包含 date 的月的最后一天的日期。
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2022-05-31
ROUND(date,‘fmt’):返回用格式化模式 fmt 四舍五入到指定单位的 date,如果格式模式 fmt 被忽略,date 被四舍五入到最近的天。
date:日期类型
fmt:字符串类型
SQL> select round(sysdate,'yy') from dual;
ROUND(SYSDATE,'YY')
-------------------
2022-01-01
TRUNC(date,‘fmt’):返回用格式化模式 fmt 截断到指定单位的带天的。如果格式模式 fmt 被忽略,date 被截断到最近的天。
date:日期类型
fmt:字符串类型
2.6 数据类型转换
2.6.1 隐式数据类型转换
隐式转换:当源数据的类型和目标数据的类型不同的时候,如果没有转换函数,就会发生隐式转换,也称自动转换。
2.6.1.1 对于直接赋值转换
从 | 到 |
---|---|
VARCHAR2 or CHAR | NUMBER |
VARCHAR2 or CHAR | DATE |
NUMBER | VARCHAR2 |
DATE | VARCHAR2 |
2.6.1.2 对于表达式赋值
从 | 到 |
---|---|
VARCHAR2 or CHAR | NUMBER |
VARCHAR2 or CHAR | DATE |
2.6.1.3 隐式转换的问题
-
性能影响
隐式转换的最大问题就是转换时会导致索引的无效,进而可能导致全表扫描。当表的数据量很大的时候,会产生很大的性能问题。比如,VARCHAR2 和 NVARCHAR2 隐式数据类型转换导致的性能问题。 -
不便于阅读
由于隐式转换使得数据库编程人员和DBA难以了解到究竟发生了怎样的类型转换,而且如果代码很多很长的话要查出错误就需要费很大的劲。
2.6.2 显示数据类型转换
通过数据库中的转换函数完成数据类型的转换。
2.6.3 转换函数
TO_CHAR(arg1,‘fmt’):将一个日期或者数字转换为字符类型。带格式化样式 fmt。
arg1:数字或者日期类型,需要转换的数据
fmt:转换格式
日期格式模板元素
元素 | 说明 |
---|---|
YYYY | 数字全写年,例2022 |
year | 英文年的全拼 |
MM | 月的两数字值,例05 |
MONTH | 月的全拼,仅在英文版数据库有效 |
MON | 月的三字母缩写,仅在英文版数据库有效 |
DY | 周中天的三字母缩写,仅在英文版数据库有效 |
DAY | 周中天的全名 |
DD | 月的数字天 |
日期格式模板元素补充
元素 | 说明 |
---|---|
SCC 或 CC | 世纪,带 - 服务器前缀 B.C. 日期 |
日期中的年 YYYY 或 SYYYY | 年,带 - 服务器前缀 B.C. 日期 |
YYY 或 YY 或 Y | 年的最后 3、2或1个数字 |
Y,YYY | 年,在这个位置带逗号 |
IYYY,IYY,IY,I | 基于ISO标准的4、3、2或1位数字年 |
SYSEAR 或 YEAR | 拼写年;带 - 服务器前缀 B.C. 日期 |
BC 或 AD | B.C.A.D.指示器 |
B.C. 或 A.D. | 带周期的 B.C./A.D.指示器 |
Q | 四分之一年 |
MM | 月:两位数字值 |
MONTH | 9位字符长度的带空格填充的月的名字 |
MON | 3字母缩写的月的名字 |
RM | 罗马数字月 |
WW 或 W | 年或月的周 |
DDD 或 DD 或 D | 年、月或周的天 |
DAY | 9位字符长度的带空格填充的天的名字 |
DY | 3字母缩写的天的名字 |
J | 儒略日,从公元前4713年12月31日开始的天数 |
时间格式模板元素
元素 | 说明 |
---|---|
AM 或 PM | 正午指示 |
A.M. 或 P.M. | 带句点的正午指示 |
HH 或 HH12 或 HH24 | 天的小时,或小时(1-12),或小时(0-23) |
MI | 分钟(0-59) |
SS | 秒(0-59) |
SSSS | 午夜之后的秒(0-86399) |
日期的时间部分,时间元素格式
SQL> select to_char(sysdate,'HH24:MI:SS AM') from dual;
TO_CHAR(SYSDATE,'HH24:MI:SSAM'
------------------------------
18:53:28 下午
加字符串,将它们括在双引号中
SQL> select to_char(sysdate,'DD "of" MONTH') from dual;
TO_CHAR(SYSDATE,'DD"OF"MONTH')
------------------------------
07 of 5月
数字前缀拼出数字
SQL> select to_char(sysdate,'ddspth') from dual;
TO_CHAR(SYSDATE,'DDSPTH')
-------------------------
seventh
其他格式
元素 | 说明 |
---|---|
/., | 在结果中使用标点符号 |
“of the” | 在结果中使用英文串 |
指定后缀来影响数字显示
元素 | 说明 |
---|---|
TH | 序数(例如,DDTH 显示为 4TH) |
SP | 拼写出数字(例如,DDSP 显示为 FOUR) |
SPTH or THSP | 拼写出序数(例如,DDSPTH 显示为 FOURTH) |
- to_char 数字转换
数字格式模板
元素 | 说明 |
---|---|
9 | 表示一个数 |
0 | 强制显示为零 |
$ | 放置一个浮动美元符号 |
L | 使用浮动本地货币符号 |
. | 打印一个小数点 |
, | 打印一个千位指示 |
FM | 代表去掉返回结果中的前后空格 |
SQL> select to_char(346.555,'999,999.99'),to_char(346.555,'009,999.99') from dual;
TO_CHAR(346.555,'999,999.99') TO_CHAR(346.555,'009,999.99')
----------------------------- -----------------------------
346.56 000,346.56
模板数据的长度可以大于实际数据的长度,但实际数据的长度不能大于模板数据的长度
SQL> select to_char(9999346.555,'999,999.99') from dual;
TO_CHAR(9999346.555,'999,999.9
------------------------------
###########
- to_number 字符串到数字转换
TO_NUMBER(‘argl’,‘fmt’):将字符串转换为数值型的格式。带格式化样式 fmt。
argl:字符串类型,需要转换的数据。
fmt:转换格式。
元素 | 说明 |
---|---|
9 | 表示一个数 |
0 | 强制显示为零 |
$ | 放置一个浮动美元符号 |
L | 使用浮动本地货币符号 |
. | 打印一个小数点 |
, | 打印一个千位指示 |
SQL> select to_number('$456.84','$999.99') from dual;
TO_NUMBER('$456.84','$999.99')
------------------------------
456.84
SQL> select to_number('$456.84','$99.99') from dual;
select to_number('$456.84','$99.99') from dual
ORA-01722: invalid number
- to_date 字符到日期转换
TO_DATE(‘argl’,‘fmt’):将字符串转换为日期格式。带格式化样式 fmt。
argl:字符串类型,需要转换的数据。
fmt:转换格式。
SQL> select to_date('2022-05-20 09:00:00','yyyy-mm-dd hh-mi-ss') from dual;
TO_DATE('2022-05-2009:00:00','
------------------------------
2022-05-20 09:00:00
SQL> select to_date('2022年6月1日 9点48分','yyyy"年"mm"月"dd"日"hh"点"mi"分"') from dual;
TO_DATE('2022年6月1日9点48分',
------------------------------
2022-06-01 09:48:00
2.6.4 函数嵌套
- 单行函数能够被嵌套任意层次
- 嵌套函数的计算是从最里层到最外层
SQL> select to_char(next_day(add_months(sysdate,6),'星期五'),'day,dd"日",mm"月",yyyy"年"') from dual;
TO_CHAR(NEXT_DAY(ADD_MONTHS(SY
------------------------------
星期五,24日,06月,2022年
2.7 通用函数
通用函数:可用于任意数据类型,并且适用于空值。
函数 | 说明 |
---|---|
NVL | 转换空值为一个实际值 |
NVL2 | 如果expr1非空,NVL2返回expr2;如果expr1为空,NVL2返回expr3。参数expr1可以是任意数据类型 |
NULLIF | 比较两个表达式,如果相等返回空;如果不相等,返回第一个表达式 |
COALESCE | 返回表达式列表中的第一个非空表达式 |
- NVL(expr1,expr2):转换一个空值到一个实际的值。
expr1,expr2:可用的数据类型可以是日期、字符和数字。两个参数的数据类型必须匹配。
expr1:是包含空值的源值或者表达式。
expr2:是用于转换空值的目的值。
SQL> select nvl(null,67) from dual;
NVL(NULL,67)
------------
67
SQL> select nvl(1,67) from dual;
NVL(1,67)
----------
1
- NVL2(expr1,expr2,expr3):NVL2函数检查第一个表达式,如果第一个表达式不为空,那么NVL2函数返回第二个表达式;如果第一个表达式为空,那么第三个表达式被返回。
expr1:是可能包含空的源值或表达式。
expr2:expr1非空时的返回值。
expr3:expr1为空时的返回值。
SQL> select nvl2(null,67,99) from dual;
NVL2(NULL,67,99)
----------------
99
SQL> select nvl2(1,67,99) from dual;
NVL2(1,67,99)
-------------
67
- NULLIF(expr1,expr2):比较连个表达式,如果相等,函数返回空;如果不相等,函数返回第一个表达式。第一个表达式不能为NULL。
expr1是对于expr2的被比较原值;
expr2是对于expr1的被比较源值。(如果它不等于expr1,expr1被返回)
SQL> select nullif('china','china') from dual;
NULLIF('CHINA','CHINA')
-----------------------
SQL> select nullif('china','China') from dual;
NULLIF('CHINA','CHINA')
-----------------------
china
- COALESCE(expr1,expr2,…,exprn):返回列表中的第一个非空表达式。
expr1如果它非空,返回该表达式;
expr2如果第一个表达式为空并且该表达式非空,返回该表达式;
exprn如果前面的表达式都为空,返回该表达式。
SQL> select coalesce(91,92,93) from dual;
COALESCE(91,92,93)
------------------
91
SQL> select coalesce(null,92,93) from dual;
COALESCE(NULL,92,93)
--------------------
92
SQL> select coalesce(null,null,93) from dual;
COALESCE(NULL,NULL,93)
----------------------
93
2.8 条件表达式
- 在SQL语句中提供IF-THEN-ELSE逻辑的使用
- 两种用法:
——CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
ELSE return_expr END
CASE表达式可以在SQL语句中使用IF-THEN-ELSE逻辑。如果没有WHEN…THEN满足条件,并且ELSE子句存在,Oracle返回else_expr。否则,Oracle返回null。所有的表达式(expr、comparison_expr和return_expr)必须是相同的数据类型。
——DECODE表达式
DECODE(col|expression,search1,result1,search2,result2,DEFAULT)
DECODE函数以一种类似于在多种语言中使用的IF-THEN-ELSE逻辑的方法判断一个表达式。DECODE函数在比较表达式(expression)和每个查找(search)值后,如果表达式与查找相同,返回结果。如果省略默认值,当没有查找值与表达式相匹配时返回一个空值。