oracle组函数处理过,Oracle 单行函数和多行函数（组函数、聚合函数）

Oracle中函数主要分为单行函数和多行函数

一、特点：

单行函数可以多层嵌套，多行函数(组函数)只能嵌套两层(多层嵌套没有意义)。

多行函数通常用于整表或分组统计查询中

每次处理完一条记录返回一个结果

二、常见的单行函数(数量比较多)：

lower    转小写

upper    转大写

initcap   首字母大写select lower('hello Mack'),upper('hello Mack'),initcap('hello Mack') from dual;

LOWER('HEL UPPER('HEL INITCAP('H

---------- ---------- ----------

hello mack HELLO MACK Hello Mack

substr(a,b)  从a中第b位开始取字符

substr(a,b,c) 从a中第b位开始取c个字符select substr('hello world',3) 子串 from dual;

子串

---------

llo world

select substr('hello world',3,4) 子串 from dual;

子串

----

llo

instr(a,b)  在a中查找b，返回b所在的位置select instr('hello','l')位置 from dual;

位置

----------

3

select instr('hello','a')位置 from dual;

位置

----------

0

length  返回字符数

lengthb    返回字节数select length('hello') 字符数,lengthb('hello') 字节数 from dual;

字符数 字节数

---------- ----------

5 5

select length('中国，你好!') 字符数,lengthb('中国，你好！') 字节数 from dual;

字符数 字节数

---------- ----------

6 12

lpad 左填充

rpad 右填充select lpad('abc',10,'*')左,rpad('abc',10,'*') 右 from dual;

左 右

---------- ----------

*******abc abc*******

trim 去掉前后指定的字符select trim('*' from '*hello world!*') trim from dual;

TRIM

------------

hello world!

replace  替换字符select replace('hello world','l','*')替换 from dual;

替换

-----------

he**o wor*d

round   四舍五入select round(11.981,2) 一,round(11.981,1) 二,round(11.981,0) 三,round(11.981,-1) 四,round(11.981,-2) 五 from dual;

一 二 三 四 五

---------- ---------- ---------- ---------- ----------

11.98 12 12 10 0

trunc 截断select trunc(11.981,2) 一,trunc(11.981,1) 二,trunc(11.981,0) 三,trunc(11.981,-1) 四,trunc(11.981,-2) 五 from dual;

一 二 三 四 五

---------- ---------- ---------- ---------- ----------

11.98 11.9 11 10 0

sysdate     查询当前时间select sysdate from dual;

SYSDATE

--------------

26-12月-18

to_char(date,格式)   格式转换select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')时间 from dual;

时间

-------------------

2018-12-26 11:56:02

to_date(date,格式)    格式转换select to_date('2015-12-13 12:23:55','yyyy-mm-dd hh24:mi:ss')时间 from dual;

时间

--------------

13-12月-15

to_number(date,格式)   格式转换select to_number('￥12345','L999999') from dual;

TO_NUMBER('￥12345','L999999')

------------------------------

12345

sysdate-1：昨天，sysdate：今天，sysdate+1：明天select (sysdate-1)昨天,sysdate 今天,(sysdate+1) 明天 from dual;

昨天 今天 明天

-------------- -------------- --------------

25-12月-18 26-12月-18 27-12月-18

months_between(a,b)     计算两个日期相差的月数select ename,hiredate,months_between(sysdate,hiredate) 月 from emp;

ENAME HIREDATE 月

---------- -------------- ----------

SMITH 17-12月-80 456.309836

ALLEN 20-2月 -81 454.213062

WARD 22-2月 -81 454.148546

JONES 02-4月 -81 452.793707

add_months(a,b)  ：给日期a加上b个月select sysdate 现在, add_months(sysdate,12) 一年后 from dual;

现在 一年后

-------------- --------------

26-12月-18 26-12月-19

last_day ： 某个日期当前月份的最后一天select sysdate 现在, last_day(sysdate)月末 from dual;

现在 月末

-------------- --------------

26-12月-18 31-12月-18

next_day(a,b)：a日期后的第一星期bselect sysdate 现在, next_day(sysdate,'星期一')下个星期一 from dual;

现在 下个星期一

-------------- --------------

26-12月-18 31-12月-18

round(a,b)：四舍五入a日期，精确度为bselect sysdate 现在,round(sysdate,'year') 精确度为年,round(sysdate,'month') 精确度为月 from dual;

现在 精确度为年 精确度为月

-------------- -------------- --------------

26-12月-18 01-1月 -19 01-1月 -19

nullif(a,b)：    当a=b的时候，返回null；否则返回aselect nullif('abcd','abc') 值,nullif('ad','ad') 值 from dual;

值 值

---- --

abcd

coalesce：   从左到右找到第一个不为null的值select comm,sal,coalesce(comm,sal) "第一个非null值" from emp;

COMM SAL 第一个非null值

---------- ---------- --------------

800 800

300 1600 300

500 1250 500

nvl(a,b) ：   当a=null时返回bselect nvl(comm,0)comm from emp;

COMM

----------

0

300

500

0

1400

case表达式select ename,job,sal 涨前,case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+800 else sal+400 end 涨后 from emp;

ENAME JOB 涨前 涨后

---------- --------- ---------- ----------

JONES MANAGER 2975 3775

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

CLARK MANAGER 2450 3250

KING PRESIDENT 5000 6000

decode 函数(功能和case end相同，但更简洁)select ename,job,sal 涨前,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400)涨后 from emp;

ENAME JOB 涨前 涨后

---------- --------- ---------- ----------

JONES MANAGER 2975 3775

MARTIN SALESMAN 1250 1650

BLAKE MANAGER 2850 3650

CLARK MANAGER 2450 3250

KING PRESIDENT 5000 6000

三、常见的多行函数(还有几个不常用)：

max()     返回最大值，忽略空值

min()      返回最小值，忽略空值

count()    返回记录数量，若为空用*表示，否则忽略

avg()       返回平均值，忽略空值

sum()      返回总数，忽略空值

用法比较简单，如：

select max(sal)，min(sal),avg(sal),sum(sal),count(*) from emp;

注意：在查询语句中，当多行函数和其它列名同时是查询结果时要使用group by进行分组，否则会出现错误：ORA-00937: 非单组分组函数 。