Oracle学习——单行函数

　　函数：能够处理查询结果的方法。能够用于下面的目的：1、执行数据计算。2、修改单个数据项。3、格式化显示日期和数字。4、转换列数据类型。5、函数是有输入参数以及总会有一个返回值。

　　主要的函数类型包括有：

• 单行函数：这些函数仅对单个行进行运算，对每一行都执行，且每一行都有一个返回结果。
• 多行函数（聚合函数）：这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也成为组函数。

　　聚合函数和单行函数最大的区别就是，作用的对象不同，聚合函数是对多行结果运算返回一个结果，单行函数是对每一行结果都返回一个结果。本文主要介绍Orcale中常见单行函数。

1、单行函数特性

• 作用于每一个的返回行，每行返回一个结果
• 需要一个或多个参数
• 可以修改结果集的数据类型（比如日期与字符串中互相转换）
• 可以嵌套
• 能够用在SELECT、WHERE和ORDER BY子句中

2、字符函数

　　单行字符串函数接手字符数据作为输入，既可以返回字符值也可以返回数字值。常见字符串函数包括大小写处理函数、字符处理函数。

2.1、大小写处理函数

函数	结果
LOWER('SQL Course')	sql course
UPPER('SQL Course')	SQL COURSE
INITCAP('SQL Course')	Sql Course

LOWER：将字符串全部转换为小写

UPPER：将字符串全部转化为大写

INITCAP：将每个单词的首字母大写，其他字母小写

　　大小写处理函数只需要一个参数，参数类型为字符串类型，返回一个字符串。

SELECT id, name, salary, departmentid FROM employee
WHERE name = INITCAP('joye')

　　这里检索了姓名为joye的这名员工的信息。(这里的where子句中还可以把name全部转化为小写再与joye进行匹配)

2.2、字符处理函数

　　dual表：dual表是一张只有一个字段，一行记录的表，他不储存主题数据。我们前面强调了SELECT...FROM...是最基本的查询语句，我们必须要定义在从那个表中提取数据，但是有些时候，我们仅仅只是想满足结构化查询语言的格式，并不需要对某个表进行提取，所以我们可以FROM dual，让Orcale知道我们并不想对那些表进行操作。

函数	结果
CONCAT('Hello', 'World')	HelloWorld
SUBSTR('HelloWorld', 1, 5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld', 'w')	6
LPAD(salary, 10, '*')	*****24000
RPAD(salary, 10, '*')	24000*****
TRIM('H' FROM 'HelloWorld')	elloWorld

CONCAT(arg1，arg2)：将arg1和arg2拼接起来，返回一个字符串。

arg1：字符串类型

arg2：字符串类型

 

SUBSTR(arg1,  num1[,  num2])：将arg1中切取子串，注意字符串编码从1开始

arg1：字符类型，原串

num1：整数类型，表示开始位置可以是一个负数，-1表示原串倒数第一位，-2表示原串中倒数第2位。

num2：整数类型，表示截取多少位。（3,5）表示从3位置开始截取5个字符，如果该参数被省略则表示截取到末尾。

 

LENGTH(arg1)：计算arg1的字符串长度，返回一个整数类型

arg1：字符串类型

 

INSTR(arg1，arg2[，arg3，arg4])：返回arg1中arg2所在位置，返回一个整数类型。如果找不到则返回0

arg1：字符串类型。原字符串。

arg2：字符串类型。查找内容。

arg3：整数类型。表示开始查找的位置。

arg4：整数类型。表示第几次出现的位置。

 

LPAD(arg1，arg2，arg3)：在头部补全字符串长度

arg1：字符串类型。原字符串。

arg2：整数类型。总长度。

arg3：字符串类型。填充的字串是什么。

 

RPAD(arg1，arg2，arg3)：在末尾补全字符串长度

arg1：字符串类型。原字符串。

arg2：整数类型。总长度。

arg3：字符串类型。填充的字串是什么。

 

TRIM（【lead|trailing|both】 arg1 FROM arg2）：去掉【头部|尾部|两侧】子字符串，返回去除之后的字符串。（默认是both）

arg1：字符串类型，需要去除的字串

arg2：字符串类型，原字符串（待去除的原字符串）

 

REPLACE（arg1，arg2，arg3）：用arg3取替换arg1中的arg2部分

arg1：字符串类型。原字符串。

arg2：字符串类型。需要被替换的字符串

arg3：字符串类型。替换的内容。

3、数字函数

函数	结果
ROUND(45.926, 2)	45.93
TRUNC(45.926, 2)	45.92
MOD(1600, 300)	100

ROUND（arg1 [, arg2]）：四舍五入到指定小数位，默认保留整数。

arg1：数字类型。原数字

arg2：整数类型。小数点保留位数，可以是一个负数。负数则表示四舍五入到整数的哪一位。

例如：ROUND（arg1，0）表示保留整数，ROUND（arg1，2）表示保留2位小数，ROUND（arg1，-1）表示保留到十位。

 

TRUNC（arg1[，arg2]）：截断到指定小数位

arg1：数字类型。原数字

arg2：整数类型。小数点保留位数，可以是一个负数。负数则表示截断到整数的哪一位。

该函数与ROUND的差别就在于不用四舍五入。

 

MOD（arg1，arg2）：返回除法的余数

arg1：数字类型。被除数

arg2：数字类型。除数

4、日期函数

　　在讲解日期函数之前，我们需要先补充两个知识点，一个是SYSDATE函数，一个是关于日期的计算

SYSDATE函数：是一个日期函数，它会返回数据库当前服务器的日期和时间（精确到秒）。需要注意的是该函数的使用不用加()，也就是说该函数没有参数传入。

日期计算：

• 从日期加或者减一个数，结果是一个日期值
• 两个日期相减，得到两个日期之间的天数
• 用小时数除以24可以得到天，这样操作可以实现将小时加到日期上

运算	结果	说明
date + number(带小数)	日期	加一个天数到一个日期上
date - number	日期	从一个日期上减一个天数
date - date	天数	用一个日期减去另一个日期
date + number/24	日期	加一个小时数到一个日期上

只需要注意日期的加减都是天为单位，比如0.5天表示的就是12小时。下面这个例子将会查询员工已经到现在工作了多少周。

SELECT name, ROUND((sysdate-hire_data)/7) FROM employee

 

运算	结果	说明
MONTHS_BETWEEN（date1，date2）	数字	计算两个日期间相隔了多少个月
ADD_MONTHS（date，n）	日期	将date加上n个月之后的日期是多少
NEXT_DAY（date，char）	日期	距离date最近的一个星期几是什么日期
LAST_DAY（date）	日期	date所在的月份最后一天的日期
ROUND（date [，format]）	日期	将date进行四舍五入，可以按照年、月、日
TRUNC（date[, format]）	日期	将date进行截取，可以按照年、月、日

MONTHS_BETWEEN（date1，date2）：计算date1和date2之间的月数，返回数字(浮点数)。其结果可以是正的也是负的。如果date1 > date2，结果是正的。返回数字。

date1：日期类型

date2：日期类型

 

ADD_MONTHS（date，n）：添加n个月到date中，返回一个日期。n必须为整数，但可以为负数。返回日期。

date：日期类型

n：整数类型

SELECT ADD_MONTHS(SYSDATE, 2) FROM dual

 

NEXT_DAY（date，char）：计算在date之后的最近的星期几的具体日期。char的值可能是一个表示一天的数或者是一个字符串。如果使用数字表示星期则1是从星期日开始，范围为1-7.

date：日期类型

char：数字(1-7)或者字符串('星期一')

SELECT NEXT_DAY(SYSDATE, '星期一') FROM dual

 

LAST_DAY（date）：计算包含date的月的最后一天的日期，date这个月的最后一天，返回日期。

date：日期类型

SELECT LAST_DAY(SYSDATE) FROM dual

 

ROUND（date [，format]）：将日期进行四舍五入处理，第二个参数表明四舍五入到哪一位(比如四舍五入到年，则月和日都是1)。如果不给第二个参数则表示四舍五入到天。返回日期。

date：日期类型

format：字符串

SELECT ROUND(SYSDATE) FROM dual

SELECT ROUND(SYSDATE,'YYYY') FROM dual

 

TRUNC（date[, format]）：与ROUND使用方式一样，区别在于直接截断，而不是四舍五入。

date：日期类型

format：字符串

5、转换函数

5.1、隐式类型转换

　　当源数据的类型和目标数据的类型不同的时候，如果没有转换函数，就会发生隐式类型转换，也称为自动类型转换。

直接赋值转换：可以从左边的类型直接转换到右边的类型。

从	到
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

　　举个例子，如果该列的数据类型是NUMBER但是我使用‘206’依然可以进行筛选出NUMBER等于206的数据行。

　　缺点：隐式类型转换可能会导致索引无效，从而导致进行全表查询。当表的数据量很大时导致很大的性能问题

5.2、显式类型转换

 　　通过数据库中的转换函数来执行对数据转换类型的操作。

TO_CHAR（arg1，‘format’）：将一个日期或者数字转化为字符类型。带格式化样式format。

arg1：数字类型或者日期类型，需要转化的数据

format：转化格式

日期格式模板：

格式模式	含义
YYYY	数字全写年
YEAR	年的拼写
MM	月的两数字值
MONTH	月的全名
MON	月的三字母缩写(英文版)
DY	星期几的三字母缩写(英文版)
DAY	星期几的全名
DD	数字天，两位

SELECT TO_CHAR(SYSDATE,'YYYY-MM') FROM dual

时间格式模板：

格式模式	含义
AM或者PM	正午指示，会根据当前时间返回上午或者下午
HH 或 HH12 或 HH24	天的小时，或12时制的小时，或24小时制的小时
MI	分钟（0-59）
SS	秒（0-59）

SELECT TO_CHAR(SYSDATE,'hh:mi:ss AM') FROM dual

　　Orcale会自动匹配这些关键字，其余的字符都会作为字符被返回到最后结果中，如果想加入一下常量字符串可以在单引号中加入双引号括起那部分字符。

SELECT TO_CHAR(SYSDATE,'yyyy "年" mm "月" dd "日"') FROM dual

数字格式模板：

格式模式	含义
9	表示一个数
0	如果不存在则强制显示为0（占位）
$	放置一个浮动的美元符号
L	使用浮动本地货币符号
.	打印一个小数点
，	千位符号
fm	保留字去掉空格

　　一般当我们需要在数字之间加入某种字符的时候，会用到数字转化为字符的操作。

SELECT TO_CHAR(36472.312, '999,999.99') FROM dual

　　上述命令的意思是把数字变成最大6位整数且2位小数，具有千位符的格式。（注意如果数字的长度不满足格式的长度，那么会导致返回#####。因此模板长度可以大于实际数字长度，反之不行）。

SELECT TO_CHAR(36472.312, 'fm999,999.99L') FROM dual

　　上述命令会显示：36,472.31￥，并且会去掉前面的空格。

 

TO_NUMBER（arg1，format）：将字符串转化为数值格式。带格式样式format。

arg1：字符串类型，需要转换的数据。

format：转换格式。

转化格式模板：

格式模式	含义
9	表示一个数
0	如果不存在则强制显示为0（占位）
$	放置一个浮动的美元符号
L	使用浮动本地货币符号
.	打印一个小数点
，	千位符号

　　需要注意转化的模板需要大于等于实际字符串(包括小数部分)，否则会报无效错误。并且字符串中不能包含除了数字和规定字符以外的字符。

 

TO_DATE（arg1，format）：将一个字符串转换为日期类型。

arg1：字符串，需要转换的数据。

format：转换格式。

转换格式模板：与TO_CHAR中对时间转化的格式是一样的，参看前文。

SELECT TO_DATE('2019-03-09 11:57:01', 'yyyy-mm-dd hh:mi:ss') FROM dual

SELECT TO_DATE('2019年03月09日 11:57:01', 'yyyy"年"mm"月"dd"日" hh:mi:ss') FROM dual

　　Orcale相当于会根据你的模板去获取字符串中的指定位置，并转化为日期。

6、通用函数

　　通用函数：可以用于任意数据类型，并且适用于空值。

• NVL（expr1，expr2）
• NVL2（expr1，expr2，expr3）
• NULLIF（expr1，expr2）
• COALESCE（expr1，expr2，....，exprn）

函数	说明
NVL（expr1，expr2）	转换空值为一个实际值
NVL2（expr1，expr2，expr3）	如果expr1非空，则返回expr2； 如果expr1为空，则返回expr3
NULLIF（expr1，expr2）	比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式
COALESCE（expr1，expr2，....，exprn）	返回表达式列表中的第一个非空表达式

 expr：表达式或者列名

 

NVL（expr1，expr2）：如果expr1为空值，则使用expr2，否则使用expr1。

expr1：可以是日期，字符，数字。可能包含空值的源值或者表达式。

expr2：转化之后的值。必须和expr1的数据类型相同

 

NVL2（expr1，expr2，expr3）：如果expr1为非空值则使用expr2；若expr1为空值则使用expr3。

expr1：可以是日期，字符，数字。可能包含空值的源值或者表达式。

expr2：expr1非空时的返回值。必须和expr1的数据类型相同

expr3：expr1空值时的返回值。必须和expr1的数据类型相同

 

NULLIF（expr1，expr2）：比较expr1和expr2，如果相等返回空；如果不相等，返回第一个表达式。注意第一个表达式不能为NULL。

expr1：可以是日期，字符，数字。如果不相同，需要返回的内容。

expr2：需要和expr1相同的数据类型，但数据可以不相同。

 

COALESCE（expr1，expr2，....，exprn）：返回列表中第一个非空表达式。

7、条件表达式

　　在SQL中可以使用IF-ELSE的逻辑使用，具体使用有两种方式：

• CASE 表达式
• DECODE 函数

7.1 CASE 表达式

语法结构：

CASE [expr] WHEN comparsion_expr1 THEN return_1
          [WHEN comparsion_expr2 THEN return_2...
           WHEN comparsion_exprn THEN return_n
           ELSE else_expr
          ]
END

　　CASE表达式：如果comparsion_expr1返回TRUE则返回return_1，如果comparsion_expr2返回TRUE则返回return_2.....如果所有WHEN都没有满足，那么会返回ELSE的内容。如果连ELSE都没有指定，那么该表达式将会返回NULL。注意所有的表达式（expr，comparsion_expr1，return_1）都必须是相同的数据类型。

7.2 DECODE 函数

　　使得CASE表达式更容易实现。

语法结构：

DECODE(col|expression, search1, result1
                       [, search2, result2,....,]
                       [, default]
)

参数解释：

• col|expression：就是CASE之后跟的那个expr
• search1：WHEN后面的表达式，若成立则返回result1
• default：被当成ELSE看待，如果前面所有情况都没返回，则返回这个。

　　注意到这里DECODE只能做等于判断，进入条件，不能做大于小于判断。但是CASE可以做相等或者大小于的判断。

 

SELECT name, salary, 
DECODE(departmentid=10, salary*1.1,
              　　　 11, salary*1.2,
              　　   20, salary*1.5)
FROM employee

 

　　上述代码意思是，将员工表中部门id是10的员工薪水乘以1.1，部门id是11的员工薪水乘以1.2，部门id是20的员工薪水乘以1.5，其他部门的员工返回NULL。