Oracle数据库常用的函数详解

A_aspectJ

已于 2023-09-24 12:09:41 修改

阅读量929

点赞数

分类专栏： sql语句文章标签： oracle 数据库 sql

于 2023-08-12 11:31:41 首次发布

本文链接：https://blog.csdn.net/A_aspectJ/article/details/132246109

版权

sql语句专栏收录该内容

11 篇文章 0 订阅

订阅专栏

字符串函数：

ASCII(X) 返回字符X的ASCII码
CONCAT(X,Y) 连接字符串X和Y
INSTR(X,STR[,START][,N) 从X中查找str，可以指定从start开始，也可以指定从n开始
LENGTH(X) 返回X的长度
LOWER(X) X转换成小写
UPPER(X) X转换成大写
TRIM([TRIM_STR FROM]X) 把X的两边截去trim_str字符串，缺省截去空格
REPLACE(X,old,new) 在X中查找old，并替换成new
SUBSTR(X,start[,length]) 返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
SUBSTR函数：如果最后一个截取长度参数为空，则表示从截取开始位置起截到最末
如果截取开始位置为大于0的数字，则表示从字符串左数几位开始
如果截取开始位置为小于0的数字，则表示从字符串右数几位开始

示例：
SELECT ASCII(‘a’) FROM dual;
SELECT CONCAT(‘Hello’,‘world’) FROM dual;
SELECT INSTR(‘Hello world’,‘or’) FROM dual;
SELECT LENGTH(‘Hello’) FROM dual;
SELECT LOWER(‘Hello’) FROM dual;
SELECT UPPER(‘hello’) FROM dual;
SELECT TRIM(‘=‘FROM’=Hello=’) FROM dual;
SELECT REPLACE(‘ABCDE’,‘CD’,‘AAA’)FROM dual;
SELECT SUBSTR(‘ABCDE’,2,3) FROM dual;

数字函数：

ABS(X) X的绝对值 ABS(-3)=3
CEIL(X) 大于或等于X的最小值 CEIL(5.4)=6
FLOOR(X) 小于或等于X的最大值 FLOOR(5.8)=5
MOD(X,Y) X除以Y的余数 MOD(8，3)=2
POWER(X,Y) X的Y次幂 POWER(2，3)=8
ROUND(X[,Y]) X在第Y位四舍五入 ROUND(3.456，2)=3.46
TRUNC(X[,Y]) X在第Y位截断 TRUNC(3.456，2)=3.45

说明：

ROUND(X[,Y])，四舍五入。

在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

TRUNC(x[,y])，直接截取，不四舍五入。

在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

日期函数

1、ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。

d 表示日期，n 表示要加的月数。

例如： select sysdate,add_months(sysdate,5) from dual;

2、TRUNC，只是不对日期进行舍入，直接截取到对应格式的第一天。

例如：select trunc(sysdate) from dual;

3、EXTRACT(fmt FROM d)，提取日期中的特定部分。

fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

例如： select sysdate ‘date’,

extract(year from sysdate) ‘year’,

extract(month from sysdate) ‘month’,

extract(hour from sysdate) ‘hour’,

extract(minute from sysdate) 'minute ',

extract(second from sysdate) 'second ’

from dual;

4、格式转换：
select sysdate,to_char(sysdate,‘yyyy-mm-dd’)from dual;
select sysdate,to_char(sysdate,‘yyyymmdd hh24:mi:ss’)from dual;
select to_date (’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual;
select to_number(‘008812’) from dual;

其他函数

1、NVL(X,VALUE)

如果X为空，返回value，否则返回X

例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元

代码演示：NVL函数

select ename,job,sal,nvl(comm,100) from emp where sal <2000;

2、NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元

代码演示：NVL2函数

select ename,job,sal,nvl2(comm,comm+100,200) ‘comm’ from emp where sal <2000;

3、decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3

当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

使用方法：

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

实际上，decode函数在实际开发中非常的有用，比如：
1、我要查询某班男生和女生的数量分别是多少?
第一种，我们写两个sql 使用union关联一下，但是太麻烦了;
第二种，用decode呢，只需要一句话
select decode(性别，男，1，0），decode(性别，女，1，0） from 表。
2、我们还可以在order by中使用decode对字符列进行特定的排序。
例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。这时，就可以非常轻松的使用decode完成要求了。
select * from table_subject order by decode(subject_name,‘语文’,1,‘数学’,2,‘外语’,3);

聚合函数

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值

等。
名称作用语法
AVG 平均值 AVG（表达式）
SUM 求和 SUM(表达式)
MIN、MAX 最小值、最大值 MIN(表达式)、MAX(表达式)
COUNT 数据统计 COUNT（表达式）

例：求本月所有员工的基本工资总和

代码演示：sum函数

select sum(sal) from emp;

例：求不同部门的平均工资

代码演示：AVG函数下的分组查询

select deptno,avg(sal) from emp group by deptno;

Oracle中的正则替换REGEXP_REPLACE和正则REGEXP_LIKE

1、函数TRANSLATE(string,from_str,to_str)
返回（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。
to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。

2、函数regexp_like（1，2，3）
例如： regexp_like(className,‘java’,‘i’)
语法说明：1：字段 2：查询的关键字 3：不区分大小写

3、函数regexp_replace(1,2,3,4,5,6)
例如： regexp_replace(className,‘java’,‘数据库’,1,0,‘i’) from
语法说明：1：字段 2：要替换的字段 3：替换为什么 4：起始位置（默认从1开始） 5：替换的次数（0是无限次） 6：不区分大小写

用regexp_replace来判断oracle中的某列为数字的值
SELECT(REGEXP_REPLACE(‘class0123’, ‘[^0-9]’)) FROM DUAL;–取出字段值里面的全部数字

SELECT LENGTH(‘class0123’),LENGTH(REGEXP_REPLACE(‘class0123’, ‘[^0-9]’)) FROM DUAL; --查询出字段的长度和字段内数字的长度

SELECT* FROM DUAL WHERE LENGTH(‘class0123’) = LENGTH(REGEXP_REPLACE(‘class0123’, ‘[^0-9]’)) ; --查询这个字段值都是纯数字的列。
我们判断入库的时候，插入的手机号码是11位
select * from t_phoneNo t where length(regexp_replace(t.t_name,‘[^0-9]’)) =11 and t.t_isdeleted = 0