Oracle函数

函数介绍

什么是函数

函数：是数据库产品中提供的能够处理查询结果的方法

函数能够用于下面的目的：

• 执行数据计算
• 修改单个数据项
• 格式化显示的日期和数字
• 转换列数据类型
• 函数有输入参数，并且总有一个返回值

函数类型

单行函数：这些函数仅对单个行进行运算，并且每行返回一个结果

多行函数（聚合函数）：这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被成为组函数

SQL函数的两种类型

函数语法

function_name(arg1,arg2,......)

function_namer：是函数的名称

arg1,arg2：是由函数使用的任意参数。参数可以是一个列名、用户提供的常数、变量值、或者一个表达式

---

单行函数

单行函数的特性包括：

• 作用于每一个返回行，每行返回一个结果
• 可能需要一个或者多个参数
• 可以修改结果集的数据类型
• 可以嵌套
• 可能返回一个与参数不同类型的数据值
• 能够用在select 、where 和order by 子句中

单行函数分类

字符函数：接受字符输入，可以返回字符或者数字值

数字函数：接收数字输入，返回数字值

日期函数：对date数据类型的值进行运算（除了months_between函数返回一个数字，所有日期函数都返回一个date数据类型的值）

转换函数：从一个数据类型到另一个数据类型转换一个值

通用函数：

• nvl
• nvl2
• nullif
• coalsece
• case
• decode

字符函数

字符函数：单行字符函数接受字符数据作为输入，既可以返回字符值也可以返回数字值

字符函数分类

• 大小写处理函数
• 字符处理函数

大小写处理函数

函数	结果
lower（‘SQL course’） upper（‘SQL course’） initcap（‘SQL course’）	sql course SQL COURSE Sql Course

lower

：转换大小写混合的字符串为小写字符串

upper

：转换大小写混合的字符串为大写字符串

initcap

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

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

--查询员工表，使用‘The Job id for’链接转换大写格式后的员工姓名，并使用‘is’字符串链接他们的工作ID转换为小写格式
--修改列名为‘EMPLOYEE DETAILS’
select 'The Job id for'||upper(last_name)||'is'||lower(job_id) as "EMPLOYEE DETAILS" 
from employees

--显示雇员higgins的雇员号，姓名，部门号
select employee_id,last_name,department_id from employees 
where last_name='Higgins'or first_name='Higgins'

--或者用lower
select employee_id,last_name,department_id from employees 
where lower(last_name)='higgins'or lower(first_name)='higgins'

字符处理函数

dual表：dual是一张只有一个字段，一行记录的表。dual表也称为为‘伪表’，因为它不存储主题数据。如果我们不需要从具体的表来取得表中数据，而是单纯地为了得到一些我们想得到的信息，并且通过select完成时，就需要借助dual表来满足结构化查询语言的格式

函数	结果
concat（‘Hello’，‘World’） substr（‘HelloWorld’，1,5） length（‘HelloWorld’） instr（‘HelloWorld’，‘W’） lpad（salary，10，‘*’） rpad（salary，10，‘*’） trim（‘H’ from ‘HelloWorld’）	HelloWorld Hello 10 6 *****24000 24000***** elloWorld

concat（arg1,arg2）

:连接值在一起，字符串拼接（concat函数有两个输入参数）

arg1：字符串类型，字符拼接的值

arg2：字符串类型，字符拼接的值

--dual伪表
--concat字符串拼接
select concat('Hello','World') from dual
select concat(employee_id,last_name) from employees 

 

substr（arg1，arg2，arg3）

：截取子串

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

arg2：整数类型，开始位置（开始位置可以是一个负数，-1表示原串的最后一位，-2则表示倒数第二位以此类推）

arg3：整数类型，截取个数

--substr字符串截取
--注意-1虽然代表是最后一位，但截取顺序依然是从左往右
select upper(substr(last_name,1,3)) from employees
select substr('HelloWorld',1,5) from dual --Hello
select substr('HelloWorld',-1,5) from dual --d
select substr('HelloWorld',-5,5) from dual --World

substr（arg1，arg2）

：截取子串

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

arg2：整数类型，开始位置（开始位置可以是一个负数，-1表示原串的最后一位，-2则表示倒数第二位以此类推）截取到末尾

--两个参数，从当前位置开始截取到末尾
select substr('HelloWorld',-5) from dual --World
select substr('HelloWorld',6) from dual --World

length（arg1）

：以数字值显示员工字符串的长度

arg1：字符串类型，计算长度的字符串

--length 计算字符串长度
select last_name,length(last_name) as "员工名字长度" from employees;
select length('HelloWorld') from dual --10

instr（arg1，arg2）

：找到一个给定字符的数字位置

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

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

instr（arg1，arg2，arg3，arg4）

：指定查找位置以及出现的次数

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

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

arg3：整数类型，开始位置（从哪个位置进行查找）

arg4：整数类型，第几次出现（要查找的字符在当前字符串中是第几次出现）

--instr(x,x)找到给定字符的数字位置
select last_name,instr(last_name,'a') from employees
select instr('HelloWorld','o') from dual --5
--instr(x,x,x,x) 指定位置搜索以及出现次数----下标
select instr('HelloWorld','l',1,2) from dual --4
select instr('HelloWorld','l',1,3) from dual --9
select instr('HelloWorld','l',6,1) from dual --9

lpad（arg1，arg2，arg3）

：用给定的字符左填充字符串到给定的长度

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

arg2：整数类型，总长度

arg3：字符串类型，填充的子字符串

--lpad(x,x,x) 给定的字符左填充到指定长度--l--left
--原字符串，总长度，填充的字符
select lpad('hello',5,'*') from dual --*****hello
select lpad(last_name,2,'q') from employees --******Abel

--rpad(x,x,x) 给定的字符右填充到指定长度--r--right
略

rpad（arg1，arg2，arg3）

：用给定的字符右填充字符串到给定的长度

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

arg2：整数类型，总长度

arg3：字符串类型，填充的子字符串

trim（arg1）

：从一个字符串中除去头（leading）或尾两侧（both）的字符（默认为头尾两侧）如果trim_character或trim_sources是一个文字字符，必须放在单引号中

arg1：需要操作的字符串，from为关键字

格式1：需要去掉的内容，‘要去除的字符’ from 原字符串

格式2：leading|trailing|both 需要去掉的内容from原字符串

--trim 去除头尾字符
select trim('A' from last_name) from employees;
select last_name from employees;
--默认是有both关键字的
select trim(both 'l' from 'lHelloWorldl') from dual  --HelloWorld
--只去掉头
select trim(leading 'l' from 'lHelloWorldl') from dual  --HelloWorldl
--只去掉尾
select trim(trailing 'l' from 'lHelloWorldl') from dual  --lHelloWorld

replace（arg1，arg2，arg3）

：replace函数是用另外一个值来替代串中的某个值

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

arg2：字符串类型，需要替换的子串

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

--replace 用一个值替换串中某个值
select replace(salary,2600,100000) from employees --2600替换成了100000
select replace('Helloworld','l','*') from dual --He**owor*d

字符函数案例练习

--将工作岗位名称从第4个字符位置开始显示，将雇
--员的姓和名连接显示在一起，还显示雇员名的长度，以及名字中字母a的位置。
select substr(job_id,4),length(first_name||last_name) 
as "雇员姓名长度",instr(first_name||last_name,'a') 
as "雇员姓名‘a’的位置" from employees
order by 1 asc,2 desc,3 desc

--显示名字是以n结束的雇员的数据，将雇员的姓和名连接显示在一起，还显示雇员名
--的的长度，以及名字中字母a的位置。
select first_name||last_name as "雇员名字以‘n’结尾",length(first_name||last_name) 
as "雇员姓名长度",instr(first_name||last_name,'a') 
as "雇员姓名‘a’的位置" from employees
where substr(last_name,-1) in 'n'
order by 2 desc

 

--将手机号中间四位用星号替代
select replace('18238657913','38657','*****') from dual

--先找到要替换的字符串，再进行替换
--replace 替换，substr 截取（源字符串，从哪个截取，截取长度）
select replace('18238657913',substr('18238657913',4,5),'*****' ) 
from dual

 

---

数字函数 

round（arg1，arg2）

：四舍五入指定小数的值

arg1：数字类型，原数字

arg2：整数类型，小数点保留的位数，可以用一个负数，负数表示指定整数的位置

round（arg1）

：四舍五入保留整数

arg1：数字类型，原数字

arg2：整数类型，小数点保留的位数

trunc（arg1，arg2）

：截断指定小数的值，不做四舍五入处理

arg1：数字类型，原数字

arg2：整数类型，小数点保留的位数，可以用一个负数，负数表示指定整数的位置

trunc（arg1）

：不做四舍五入保留整数

arg1：数字类型，原数字

arg2：整数类型，小数点保留的位数

mod（arg1，arg2）

：取余

arg1：数字类型，被除数

arg2：数字类型，除数

日期处理

日期的说明

sysdate函数： 是一个日期函数，返回当前数据库服务器的日期和时间

用日期计算

• 从日期加或减得到一个数，结果是一个日期值
• 两个日期相减，得到两个日期之间的天数
• 用小数除以24，可以加小数到日期上

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

用日期做算术运算

略

日期函数

函数	说明
months_between	两个日期之间的月数
add_months	加日历月到日期
next_day	下个星期几是几号
last_day	指定月的最后一天
round	四舍五入日期
trunc	截断日期

数据类型转换

 隐式数据类型转换

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

对于直接赋值转换

从	到
varchar2 or char	number
varchar2 or char	date
number	varchar2
date	varchar2

对于表达式赋值

从	到
varchar2 or char	number
varchar2 or char	date

隐式转换的问题

性能影响：最大的问题就是转换时会导致索引的无效，进而可能导致全表扫描。当表的数据量很大的时候，会产生很大的性能问题；比如varchar2和nvarchar2隐式数据类型转换导致的性能问题

不便于阅读：难以了解到究竟发了怎样的类型转换，而且如果代码很长查出问题就需要时间

显示数据类型转换

通过数据库中的转换函数完成对数据类型的转换

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

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

fmt：转换格式

to_char日期转换

 

 to_char数字转换

 to_number字符串到数字转换

to_number（‘arg1’，‘fmt’）：将字符串转换为数值型的格式，带格式化样式fmt

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

fmt：转换格式

to_date字符到日期转换

to_date（‘arg1’，‘fmt’）：将字符串转换为数值型的格式，带格式化样式fmt

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

fmt：转换格式

select to_date('2019-03-09','yyyy-MM-dd') from dual;

select to_date('2019-03-09 11:57','yyyy-MM-dd HH:MI') from dual;

select to_date('2019年03月9日 11点30分','yyyy"年"MM"月"DD"日"HH"点"MI"分"') from dual;

函数嵌套

• 单行函数能够被嵌套任意层次
• 嵌套函数的计算是从最里层到最外层，参数格式也要正确

 通用函数

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

nvl（expr1,expr2）

nvl2（expr1,expr2,expr3）

nullif（expr1,expr2）

coalesce（expr1,expr2...,exprn）

 nvl（expr1,expr2）函数：

转换一个空值到一个实际的值（参数类型必须匹配）

expr1，expr2：可用的数据类型可以是日期、字符和数字。两个参数的数据类型必须匹配

expr1：是包含空值的源值或者表达式

expr2：是用于转换空值的目的值

--计算所有员工的年薪，如果有佣金则包含佣金
--如果佣金是空值，则因为空值运算，都变为空值了
select last_name,salary*12*commission_pct from employees
-- nvl（expr1,expr2）使用函数
select last_name,salary*12*nvl(commission_pct,1) from employees order by 2 desc

--求年薪，有佣金则年薪乘以佣金百分比
select last_name,salary*12+salary*12*nvl(commission_pct,0),salary,commission_pct 
from employees order by 2 desc

nvl2（expr1,expr2,expr3）函数：

nvl2函数检查第一个表达式，如果第一个表达式不为空，那么nvl2函数返回第二个表达式；如果第一个表达式为空，那么第三个表达式被返回

expr1：是包含空值的源值或者表达式

expr2：expr1非空时的返回值

expr3：expr1非空时的返回值

--查询雇员信息，有佣金显示sal+comm，没有显示sal
select last_name,nvl2(commission_pct,'sal+comm','sal') 
from employees order by 2 asc

--这里包含了一个隐式转换
--查询雇员信息，有佣金显示sal+comm，没有显示sal
select last_name,nvl2(to_char(commission_pct),'sal+comm','sal') 
from employees order by 2 asc

nullif（expr1,expr2）函数：

比较两个表达式，如果相等函数返回空，如果不相等函数返回第一个表达式。第一个表达式不能为null

expr1是对于expr2的被比较原值

expr2是对于expr1的被比较原值（如果它不等于expr1，expr1被返回）

--nullif（expr1,expr2）
--查询雇员，显示他们的first_name与长度，长度列命名为exprl。last_name与长度，长
--度命名为expr2。判断他们的first_name与last_name的长度,如果长度相同返回空，否则返
--回first_ name的长度。判断结果列命名为result.

select first_name expr1,last_name expr2,
nullif(length(first_name),length(last_name)) result 
from employees

 coalesce（expr1,expr2...,exprn）函数：

expr1：如果它非空，返回该表达式

expr2：如果第一个表达式为空并且该表达式非空，返回该表达式

expr3：如果前面的表达式都为空，返回该表达式

--查询雇员表，如果COMMISSION PCT值是非空，显示它。如果COMMISSION PCT
--值是空，则显示SALARY。如果COMMISSION PCT和SALARY值都是空，那么显示
--10。
select last_name,coalesce(commission_pct,salary,10) from employees order by 2 asc

条件表达式

在SQL语句中提供 if-then-else 逻辑的使用

两种用法：

• case表达式
• decode函数

case表达式

 case表达式：可以让你再SQL语句中使用if-then-else的逻辑。如果没有when...then满足条件，并且else子句存在。Oracle返回else_expr。否则，Oracle返回null。所有的表达式（expr、comparison_expr和return_expr）必须是相同的数据类型

--查询雇员，显示last_ name,job_ id,salary 如果JOB_ ID是IT_ PROG，薪水增加10%;
--如果JOB_ ID是ST_ CLERK,薪水增加15%; 如果JOB_ ID是SA REP,薪水增加20%。
--对于所有其他的工作角色，不增加薪水。
select last_name,job_id,salary,
case job_id 
when 'IT_PROG' then salary*1.1
when 'ST_CLERK' then salary*1.15
when 'SA_REP' then salary*1.2
end 
from employees;

decode函数

 decode函数：以一种类似在多种语言中使用的if-then-else逻辑的方法判断一个表达式。decode函数在比较表达式（expression）和每个查找值（search）值后，如果表达式与查找相同，返回结果。如果省略默认值，当没有查找值与表达式相匹配时返回一个空值

--decode函数实现
select last_name,job_id,salary,
decode(
job_id,'IT_PROG',salary*1.1,
'ST_CLERK',salary*1.15,
'SA_REP',salary*1.2
) from employees;