原创

Oracle常用函数

整理常用的Oracle函数


先安利一个方便的在线SQL测试环境:Oracle Live SQL

教程可参考 :最方便的在线SQL学习环境——Oracle Live SQL

首先我们把常用的函数分为两个大类:单行函数和多行函数(聚合函数),区别在于单行函数仅对单个行进行运算,并且每行返回一个结果;而多行函数能够操纵成组的行,每个行组给出一个结果,也被称为组函数:

一、单行函数

注:[]内参数为可忽略参数。

1.字符函数:对字符串进行操作

(1) concat(str1,str2) : 字符串拼接函数

select concat('Hello ','World') from dual;
--等价于
select 'Hello '||'World' from dual; --返回结果为 ‘HelloWorld’

(2) initcap(str) : 将每个单词首字母大写,其他字母小写

select initcap('hello world!') from dual; --返回结果为'Hello World!'
select initcap('HELLO WORLD!') from dual; --返回结果为'Hello World!'

(3) instr(x,find_string[,start][,occurrence]) : 字符查找函数,用于模糊查找需要查找的字符串在源字符串中出现的位置,例如

--查找时下标从1开始计算
select instr('Hello World!','o') from dual;--从1位置开始搜索,返回第一次出现的o的位置,结果为5
select instr('Hello World!','o',6) from dual;--从6位置开始搜索,返回第一次出现的o的位置,结果为8
select instr('Hello World!','o',1,2) from dual;--从1位置开始搜索,返回第二次出现o的位置,结果为8

(4) length(str) : 返回表达式中的字符数

select length('Hello World!') from dual;--返回结果为12
select length('张三') from dual;--返回结果为2

(5) lengthb(str) : 返回表达式中的字节数

select lengthb('Hello World!') from dual;--返回结果为12
select lengthb('张三') from dual;--返回结果为6

(6) lower(str) : 将字符串转换为小写

select lower('Hello World!') from dual;

(7) upper(str) : 将字符串转换为大写

select upper('Hello World!') from dual;

(8) lpad(str,width[,pad_string]) : 当字符串长度不够时,左填充补齐,可以指定补齐时用什么字符补齐,若不指定,则以空格补齐

select lpad('Hello World!',20) from dual;--返回结果为'        Hello World!'
select lpad('Hello World!',20,'*') from dual;--返回结果为'********Hello World!'

(9) rpad(str,width[,pad_string]) : 当字符串长度不够时,右填充补齐,原理同左填充

select rpad('Hello World!',20) from dual;--返回结果为'Hello World!        '
select rpad('Hello World!',20,'*+') from dual;--返回结果为'Hello World!*+*+*+*+'

(10) ltrim(x[,trim_string]) : 从字符串左侧去除指定的所有字符串,若没有指定去除的字符串,则默认去除左侧空白符

select ltrim('   Hello World!    ') from dual;--返回结果为'Hello World!    '
select ltrim('***+*Hello World!***+*','*+') from dual;--返回结果为'Hello World!***+*'

(11) rtrim(x[,trim_string]) : 从字符串右侧去除指定的所有字符串,原理同ltrim()

select rtrim('   Hello World!    ') from dual;--返回结果为'    Hello World!'
select rtrim('***+*Hello World!***+*','*+') from dual;--返回结果为'***+*Hello World!'

(12) trim(trim_string from x) : 从字符串两侧去除指定的所有字符串

select trim('*+' from '***+*Hello World!***+*') from dual;
--注意,ltrim()和rtrim()的截取集可以使多个字符,但trim的截取集只能有一个字符:
select trim('*+' from '***+*Hello World!***+*') from dual;--错误,截取集只能有一个字符

(13) replace(x,search_string,replace_string) : 从字符串x中搜索search_string字符串,并使用replace_string字符串替换。并不会修改数据库中原始值

select replace('Lee','e','o') from dual; --返回‘Loo’

(14) substr(x,start[,length]) : 返回字符串中的指定的字符,这些字符从字符串的第start个位置开始,长度为length个字符;如果start是负数,则从x字符串的末尾开始算起;如果 length省略,则将返回一直到字符串末尾的所有字符

select substr('Hello World',3) from dual; --返回结果为'llo World'
select substr('Hello World',-3) from dual;--返回结果为'rld'
select substr('Hello World',3,2) from dual;--返回结果为'll'
select substr('Hello World',-7,4) from dual;--返回结果为'o Wo'

2.数值函数:计算数字并返回

(1) abs(value) : 返回value的绝对值:

select abs(-10) from dual;--返回结果为10

(2) ceil(value) : 向上取整

select ceil(2.3) from dual; --返回结果为3

(3) floor(value) : 向下取整

select floor(2.3) from dual; --返回结果为2

(4) trunc(value,n) : 取整函数,y缺省值为0,x保留整数;y>0,x保留小数点右侧y位;y<0,x保留小数点左边y位;可以对时间使用,效果是只保留年/月/日,例如

--取整时直接舍去不保留部分
select trunc(555.666) from dual; --返回结果为555,不加n时默认去掉小数部分
select trunc(555.666,2) from dual;--返回结果为555.66
select trunc(555.666,-2) from dual;--返回结果为500
--对日期进行使用
select sysdate from dual; --30-JUL-20 (2020/07/30)
select trunc(sysdate,'year') from dual; --01-JAN-20 (2020/01/01)

(5) round(value,n) : 四舍五入函数,y缺省值为0,x保留整数;y>0,x保留小数点右侧y位;y<0,x保留小数点左边y位;可以对时间进行round,效果是只保留年/月/日,例如

--取整时四舍五入
select round(555.666) from dual;--返回结果为556,不加n时默认去掉小数部分
select round(555.666,2) from dual;--返回结果为555.67
select round(555.666,-2) from dual;--返回结果为600
--对日期进行使用
select sysdate from dual; --30-JUL-20 (2020/07/30)
select round(sysdate,'year') from dual; --01-JAN-21 (2021/01/01)

3.转换函数:数据类型转换

将值从一种类型转换成另外一种类型,或者从一种格式转换为另外一种格式

(1) to_char(x[,format]) : 将x转化为字符串,format为转换的格式,可以为数字格式或日期格式

select to_char('12345.67') from dual; --返回结果为12345.67
select to_char('12345.67','99,999.99') from dual; --返回结果为12,345.67
select to_char(sysdate,'yyyy-MM-dd') from dual; -- 2020-07-30

(2) to_number (x [, format]) : 将x转换为数字。可以指定format格式

select to_number('970.13') + 25.5 from dual; --995.63
select to_number('-$12,345.67', '$99,999.99') from dual;-- -12345.67

(3) cast(x as type) : 将x转换为指定的兼容的数据库类型

select cast(12345.67 as varchar2(10))str,cast('05-7月-07' as date)date1, cast(12345.678 as num1(10,2))num from dual;
--str        date1        num1
--12345.67    05-07-07    12345.68

(4) to_date(x [,format]) : 将x字符串转换为日期

select to_date('2012-3-15','YYYY-MM-DD') from dual

4.日期函数:对日期进行运算

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

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;
-- sysydate                add_months
-- 2020/7/30 20:26:33     2020/12/30 20:26:33

(2) last_day(d) : 返回指定日期当月的最后一天

SELECT SYSDATE,last_day(SYSDATE) FROM dual;
-- sysydate                add_months
-- 2020/7/30 20:26:33     2020/7/31 20:26:33

(3) round(d[,fmt]) : 返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默认 fmt 为 DDD,即月中的某一天

-- 如果 fmt 为 ‘year’ 则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下一年。
-- 如果 fmt 为 ‘month’ 则舍入到某月的 1 日,即前月舍去,后半月作为下一月。
-- 默认为 ‘ddd’ ,即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天。
-- 如果 fmt 为 ‘day’ 则舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日。
SELECT sysdate,ROUND(SYSDATE)ddd,ROUND(SYSDATE,'day')day,
ROUND(SYSDATE,'month')month,ROUND(SYSDATE,'year')years FROM dual;
-- sysdate                ddd            day            month            years
-- 2020/7/30 20:30:30       2020/7/31      2020/8/2     2020/8/1         2021/1/1

(4) trunc(d[,fmt]) : 和round用法相同,只是不对日期进行舍入,直接截取到对应格式的第一天。

(5) 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(DAY FROM SYSDATE)"day",
       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
FROM dual;
-- date                    year    month    day        hour    minute        second
-- 2020/7/30 20:54:33     2020      7          30      12      54          33.18321

5.其他单行函数

(1) nvl(expr1,expr2) : 用于将null转变为实际值,如果expr1是null,则返回expr2;否则返回expr1,expr1和expr2必须为同类型,或expr2可以隐式转换为expr1,否则函数会报错,例如

select nvl(null,'value') from dual; --expr1是null,则返回expr2 value
select nvl(1,'value') from dual; --类型不同,报错
select nvl(1,2) from dual; --1

(2) nvl2(x,value1,value2) : 如果x不为NULL,返回value1,否则,返回value2

select nvl2('1','value1','value2') from dual; --返回‘value1’
select nvl2(null,'value1','value2') from dual; --返回‘value2’

(3) decode (base,comparel,value1,compare2,value2.....,default) : 参数base跟后面的每个compare进行比较,如果匹配的话就返回对应的value(每个value数据类型必须一致);如果没有匹配则返回默认值default,如果没有传default则返回null,例如

select  decode(1, 1, '男生', 2, '女生', '人妖') as sex --返回‘男生’
-- 如果参数sex为‘1’则返回‘男生’,‘2’则女生,都不是则返回默认值‘人妖’。
  from dual;

二、聚合函数

1.常用函数

(1) avg(x) : 返回x的平均值

select avg(grade) from sc;

(2) count(x) : 返回统计的行数

select count(name) from sc;

(3) max(x) : 返回x的最大值

select max(grade) from sc;

(4) min(x) : 返回x的最小值

select min(grade) from sc;

(5) sum(x) : 返回x的总计值

select sum(grade) from sc;

2.对分组行使用聚集函数

对分组后的行使用聚集函数,聚集函数会统计每组中的值,对于每组分别统计后返回一个值:

--按照职位分组,求出每个职位的最高和最低工资
select job ,max(sal),min(sal) from emp 
group by job 
order by job;
--注意:
--1.分组时select子句后边的列名必须与group by子句后的列名一致,除非是聚合函数
select deptno,avg(sal) from EMP;--错误,因为deptno不是聚集函数,也不是group by后面跟的列名
--2.不能使用聚集函数作为WHERE子句的筛选条件
select deptno from emp where avg(sal)>1000;--错误
--3.分组后,需要使用条件进行筛选,则使用having过滤分组后的行,不能使用where,where只能放在group by前面。
select deptno, avg(sal) from emp where deptno<>10 
group by deptno  
having avg(sal) > 900;

  

三、补充

(1) pivot(聚合函数 for 列名 in 类型) :行转列函数,其用法如下:

select * from table_name pivot(max(column_name) --行转列后的列的值value,聚合函数是必须要有的
for column_name in(value_1,value_2,value_3))     --需要行转列的列及其对应列的属性1/2/3

(2) unpivot() :列转行函数,例如:

select column_name from table_name unpivot (column_name forcolumn_name in (value_1,value_2,value_3));
正文到此结束