Oracle函数

怕几眼o

已于 2022-03-01 21:10:10 修改

阅读量959

点赞数

文章标签： oracle 数据库 database

于 2022-02-28 22:56:30 首次发布

本文链接：https://blog.youkuaiyun.com/niubaosama/article/details/123189576

版权

函数介绍

什么是函数

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

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

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

函数类型

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

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

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

*****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;