Oracle数据库学习（七）：oracle函数的使用

tfghmx

已于 2022-11-02 10:10:29 修改

阅读量515

点赞数 1

文章标签： oracle 数据库 sql

于 2022-09-20 16:21:00 首次发布

本文链接：https://blog.csdn.net/tfghmx/article/details/126954197

版权

Oracle函数

概念：是oracle提供用来进一步修饰或者处理数据的方法
- 用法：在书写sql语句直接使用
- 常见的函数：单行函数，多行函数，转换函数，其他函数

单行函数

特点：不修改库中数据，临时表中进行修改；

字符函数

函数	功能	示例	结果
INITCAP（str）	首字母大写	initcap (‘hello’)	Hello
LOWER（str）	转换为小写	lower(‘FCN’)	fcn
UPPER （str）	转换为大写	upper(‘fcn’)	FCN
LTRIM（str，crop）	左裁剪	ltrim(‘xyz’, ‘x’)	yz
RTRIM（str，crop）	右裁剪	rtrim(‘xyz’, ‘z’)	xy
TRANSLATE（str，src, dest）	按字符翻译	translate(‘jack’, ‘abcd’, ‘1234’)	j13k
REPLACE（str, 搜索值,替换值）	字符串替换	replace(‘fcn is’, ‘fcn’, ‘unet’)	unet is
INSTR(str, sub_str)	查找子串位置	instr(‘worldhello’, ‘r’)	3
substr(str, start, len)	取子字符串	substr(‘abcdef’, 3, 2)	cd
CONCAT(str1, str2)	连接字符串	concat(‘hello’, ‘world’)	helloworld

 -- 单行函数: 
    语法：select 字段名, 函数(字段名),…… from tab_name
	-- 查询员工编号，姓名，姓名小写，首字母大写，临时数据
    select empno,ename,lower(ename) 小写,initcap(ename) 首字母大写,'HH' 临时数据 from emp;

请添加图片描述

数值函数

概念：对数值类型数据进行运算

函数	功能	示例	结果
abs(n)	绝对值	abs(-15)	15
ceil(n)	向上取整	ceil(23.23)	24
sin(n)	正弦	sin(1)	0.84
cos(n)	余弦	cos(0)	1
sign(n)	取符号	sin(-10)，sin(10)	-1，1
floor(n)	向下取整	floor(100.9)	100
power(m,n)	m的n次幂	power(4,2)	16
mod(m,n)	取余数	mod(10,3)	1
round(m,n)	四舍五入	round(10.123,2)	10.12
trunc(m,n)	截断	trunk(10.3543, 2)	10.35
sqrt(n)	平方根	sqrt(4)	2

伪表：真实存在的表但不存储数据，是为了方便进行数据的验证而临时存在的表 – dual

select abs(-3),ceil(3.2),sin(1),cos(0),sign(-10),floor(2.9),power(2,3), mod(8,3), round(10.34,1),trunc(1.123,2),trunc(10.23,-1), sqrt(4)  from dual

请添加图片描述

日期函数

请添加图片描述

select months_between('05-9月-22','05-6月-22'),add_months('2-8月-2201',3),next_day('15-9月-2022','星期六'),last_day('11-9月-2022') from dual

请添加图片描述

-- 四舍五入:年，月，日：年对半（6月，7月），月份对半（15日，16日），星期对半（周三，周四）
select round(to_date('16-9月-22'),'year'),round(to_date('15-2月-22'),'month'),
round(to_date('14-9月-22'),'day') from dual

请添加图片描述

多行函数（max、min、sum、avg、count）
- 作用：对查询数据进行统计
- 使用：select 多行行数名（字段名）多行函数名（字段名）…… from 表名
- 注意：多行函数不能和单行函数以及普通字段混用，除非分组
- max(字段名)：返回该字段的最大值
- min(字段名)：返回该字段的最小值
- sum(字段名)：返回该字段的和
- avg(字段名)：返回该字段的平均值
- count
  - count(*)：返回表的记录数
  - count(字段名)：返回非空值的数量
  - count(distinct 字段名)：去除重复后的数量
```
-- 查询最高工资,最低工资,平均工资,所有员工工资之和
	select max(sal),min(sal),avg(sal),sum(sal) from emp
-- 查询有多少员工
	select count(*) from emp 
-- 查询有津贴的员工人数
	select count(comm) from emp  
-- 查询公司有多少工作种类
	select count(distinct job) from emp
```
转换函数
- 常用函数
  - to_number(数值类型的字符)：将字符转换为数值
  - to_char(数值或日期)：将数值或日期转换为字符
  - to_date(日期格式的字符)：将字符转换为日期
- 字符和数值互转（数值和字符之间存在隐式转换）
```
-- 字符转换为数字char-->number
  select to_number('123')+2 from dual
-- 数字转换为字符number-->char
  select to_char(123) from dual
-- 指定显示格式
  -- 9占位符，L表示人民币，$表示美元
  -- 0占位符，如果真实数据位数不够，0将会进行补位
	  select to_char(123456789,'999,999,999'), to_char(1234,'L999,999'), to_char(1234,'$9,999'), to_char(1234,'000,000') from dual 
```
- 字符转换为日期char–>date
  - 使用to_date(‘ 要转换的字符 ’, ‘ 日期格式’) 函数将字符转换日期
  - 注意：
    - 1.字符必须符合日期格式
    - 2.oracle默认转换格式为日月年，eg：‘01-1月-2018’
    - 3.日期常用格式：yyyy-mm-dd, yyyy/mm/dd
```
-- 查询1982以后入职员工全部信息
	select * from emp where hiredate>to_date('1982-01-01','yyyy-mm-dd')
```
- 日期转换为字符date–>char
  - 使用to_char(‘要转换的日期’, ‘ 转换格式’)
  - 注意：
    - 1.没有指明格式，则使用默认格式，日月年‘01-1月-1982’
    - 2.常用格式：yyyy-mm-dd, yyyy/mm/dd, yyyy“年”mm“月“dd”日“
```
    select to_char(hiredate),to_char(hiredate,'yyyy-mm-dd'), to_char(hiredate,'yyyy/mm/dd'),to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp
```
其他函数
- nvl(字段名，赋值)：如果字段值不为null，则返回该字段的值，如果为null，则返回赋值
  - 类型于：if语句
```
    select ename,job,sal+nvl(comm,0),sal+comm,sal,comm from emp 
```
- nvl2(字段名, 处理1, 处理2)：如果字段值不为null，则执行处理1，为null则执行处理2
  - 类似于if……else
```
	 select ename,job,nvl2(comm,sal+comm,sal),sal+comm,sal,comm from emp;
```
- decode(字段名, 值1, 处理1, 值2, 处理2, ……, 值n, 处理n, 公共处理)：
  - 若字段值和条件值相等，则执行对应的处理，若无，则执行公共处理
  - 类似于：case()条件判断
```
   select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售员','员工') from emp
```
注意：
- 双引号的作用：关键字，对象名、字段名、别名加双引号，避免系统面临模棱两可的局面
- 单引号的作用：引用一个字符串常量；作为字符串常量或一部分；转义字符