oracle 函数用法（ORACLE数据库中常见的单行函数的使用和操作）

在oracle数据库中，函数有聚合函数、单行函数、开窗函数等等，其中在sql语句编辑计算过程中，单行函数的使用频率又尤为突出。

所以今天我们一起来看看单行函数在oracle中有哪些是比较常见常用的。

一、数字类型

进行数字绝对值的求值：abs()

例如，查看emp表里面，有设置奖金的员工里面，奖金和工资之间的绝对值差距是多少？

对数字进行四舍五入：round(n, 保留的小数精度)

对数字进行截取：trunc(n, 保留的小数精度)

进行数字的向上取整：ceil() 找比自己大的最近的这个整数

进行数字的向下取整：floor() 找比自己小的最近的这个整数

进行数字的幂运算：power(n, 次方) 数字的多少次方

进行数字的取余数运算：mod(被除数, 除数) 除法运算的余数，经常用来判断倍数关系

二、字符串类型

进行字符串的截取：substr()

1. substr(s, 开始序号, 连续长度)

查询首字母是A开头的员工名字

查询倒数第二个字母是E的员工名字

2. substr(s, 开始序号) 没有连续的长度，默认就是从开始一直取到最后一个

查询名字的最后三个字母是 NES 的员工名字

字符串的拼接：concat(s1, s2) concat()函数只能拼接两个内容

如果要在两个内容中间，需要加上符号，用三个内容拼接起来一个字符串，那么就需要使用concat()函数的嵌套了。

在oracle里面，拼接一般使用 || 进行。

查询字符串的长度：length()

查询名字的长度刚好是5个字母的员工信息

将字符串转换成小写：lower()

将字符串转换成大写：upper()

将字符串转换成首字母大写其他字母小写：initcap()

使用上面学过的函数，嵌套一个新的功能出来，查询员工的名字，最后一个字母大写，其他字母小写。

在一个大的字符串里面，查找是否有包含另一个字符或者字符串，返回找到的字符在大的字符串中的位置：instr()

1. instr(s, '查找的内容')

只会返回遇到的第一个匹配值，如果没有匹配，就返回0。

2. instr(s, '查找的内容', 从第几个序号开始查找, 找第几个匹配的内容)

3. 我们放到一个笔试题里面看看 instr() 函数的使用：

下面的表格，每一行都由省-市-区组成，现在我们需要取出每行中间城市的名称。

create table addrs(

addr varchar2(50)

);

insert into addrs values('广东省-深圳市-罗湖区');

insert into addrs values('新疆维吾尔自治区-伊犁市-乌鲁木齐');

insert into addrs values('湖南省-长沙市-天心区');

commit;

我们使用instr()查找第一个和第二个分隔符的位置，再使用substr()进行截取就好了。

字符串的替换方法：replace(s, '旧字符', '新字符')

取出名字里面所有的A

删除某个指定范围的数据：translate(s, '要被替换的很多个旧字符' , ' ')

例如下面这个题目，我们有很多个用户名username，我们需要找出包含了数字的有哪些。

create table user_inf2(

userid number,

username varchar2(50)

);

insert into user_inf2 values(1,'ieiuefjdkk');

insert into user_inf2 values(2,'ifjsk13234');

insert into user_inf2 values(3,'5634kjdfkl12');

insert into user_inf2 values(4,'9883475');

insert into user_inf2 values(5,'872176lkgkj');

我们将数字都去掉，然后查看和原字符串不相同的数据即可。

正则表达式的数据替换方法：regexp_replace(s, '正则表达式', '新的内容')

这个用正则的替换方法同样可以实现。

举一个正则表达式的例子：

我们要删除下面这个表格中，所有城市名字前面的数字序号。

create table user_inf3(

userid number,

addr varchar2(100)

);

insert into user_inf3 values(1,'1:深圳,2:广州');

insert into user_inf3 values(2,'1:北京,2:上海,3:杭州');

insert into user_inf3 values(3,'1:武汉,2:武汉2');

数字范围[0-9]也可以使用 \d 进行替换。

三、时间日期类型

获取当前的时间日期：sysdate current_date

获取查看日期所在的月份的最后一天：last_day()

查看日期之间的月份间隔：months_between()

月份的偏移查询：add_months() 在这个日期的基础上，往前或者往后移动N个月

日期天数的间隔计算，直接用 减法 ：

进行日期天数的偏移查询：

日期 数字截取的函数 trunc() ，实现日期的截断：

日期截取的维度：

yyyy 年

mm 月

dd 天

hh24 小时

mi 分钟

day 周

一个实际的练习：

显示一个永远是明天早上十点的时间：

四、数据类型转换

将一个纯数字组成的字符串转换成数字类型：to_number()

将一个数字类型或者日期类型转换成字符串类型：to_char()

将时间日期格式的字符串 转换成日期类型：to_date()

时间提取的维度有：

yyyy 年

mm 月

dd 日

hh24 时

mi 分

ss 秒

day 周

练习：

查询emp表中，每一年分别有多少员工入职？

查询emp表中，每个员工入职了多少年零多少月？

,