oracle强大函数,oracle函数

函数的分类:

单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数

单行函数:

大小写控制函数

select lower(‘Hello World’) 转小写, upper(‘Hello World’) 转大写 from dual;

–initcap: 首字母大写

select initcap(‘hello world’) 首字符大写 from dual;

字符控制函数

concat: 字符连接函数, 等同于 ||

select concat(‘Hello’,’ World’) from dual;

substr:求母串中的某个子串

select substr(‘Hello World’,3) from dual;

select substr(‘Hello World’,3,4) from dual;

length和lengthb: 字符数和字节数

select length(‘China’) 字符数, lengthb(‘China’) 字节数 from dual;

instr:在母串中，查找子串的位置

select instr(‘Hello World’,’ll’) from dual;

lpad,rpad: 左右填充,将abcd用*填充到10位

select lpad(‘abcd’,10,’‘) 左填充, rpad(‘abcd’,10,’‘) 右填充 from dual;

trim: 去掉字符串前后指定的字符

select trim(‘H’ from ‘Hello WorldH’) from dual;

replace:字符串替换函数

select replace(‘Hello Wordl’,’l’,’*’) from dual;

数字函数

select round(45.926,2) 四舍五入, trunc(45.926,2) 截断 ,mod(1600,300) 求于 from dual;

ROUND函数

select round(45.923,0) 整数位, round(45.923,-1) 十位,round(45.923,-2) 百位 from dual;

日期函数

显示当前日期

select sysdate from dual;

显示时间部分

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;

显示昨天，今天和明天，加减数字仍未日期

select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;

两个日期相减，结果为相差的天数，查询员工信息，显示员工工龄。两个日期不能相加

select empno,ename, sysdate-hiredate 天 from emp;

查询员工信息，显示员工工龄,分别按照天，星期，月显示

select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from emp;

months_between:两个日期相差的月数

select (sysdate-hiredate)/30 方式一, months_between(sysdate,hiredate) 方式二 from emp;

add_months:在指定日期上加上若干个月

select add_months(sysdate,1) 下个月, add_months(sysdate,123) “123个月后” from dual

last_day: 某个日期当月的最后一天

select last_day(sysdate) from dual;

next_day:下周六

select next_day(sysdate,’星期五’) from dual;

对日期进行四舍五入

select round(sysdate,’MONTH’) 月,round(sysdate,’YEAR’) from dual;

对日期进行截断

select trunc(sysdate,’MONTH’) 月,trunc(sysdate,’YEAR’) from dual;

日期格式

select * from emp where hiredate=to_date(‘1982-01-23’,’yyyy-mm-dd’);

查询当前日期：显示: 2011-09-17 15:12:15今天是星期六

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss”今天是”day’) from dual;

查询员工信息，显示员工的编号，姓名，月薪,要求有货币代码(L)，千位符(,)，小数点(.),

select empno,ename,to_char(sal,’L9,999.99’) from emp;

大小写控制函数

select lower(‘Hello World’) 转小写, upper(‘Hello World’) 转大写 from dual;

initcap: 首字母大写

select initcap(‘hello world’) 首字符大写 from dual;

通用函数

nvl(exp1,exp2):当exp1为空时，返回exp2

nvl2(exp1,exp2,exp3):当exp1为空时，返回exp3；否则返回exp2

select ename,sal*12+nvl2(comm,comm,0) 年收入 from emp;

NULLIF (expr1, expr2),如果expr1=expr2,返回null;否则,返回expr1

select nullif(‘abc’,’abc’) from dual;

select nullif(‘abc’,’abcaa’) from dual;

COALESCE :找到参数列表中，第一个不为空的值

select ename,comm,sal,COALESCE(comm,sal) from emp;

例子:给员工涨工资,根据职位涨，总裁涨1000，经理涨600 其他人员涨400

select ename,job,sal 涨前工资,

case job when ‘PRESIDENT’ then sal+1000 when ‘MANAGER’ then sal+600 else sal+400

end 涨后工资

from emp;

select ename,job,sal 涨前工资, decode(job,’PRESIDENT’,sal+1000,’MANAGER’,sal+600,sal+400) 涨后工资

转换函数(Conversion Functions)

转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。

TO_CHAR() 本函数又可以分三小类，分别是

例如：SELECT TO_CHAR(‘AABBCC’) FROM DUAL;转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；

例如：SELECT TO_CHAR(sysdate,’yyyy-mm-dd hh24:mi:ss’) FROM DUAL;转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；

例如：SELECT TO_CHAR(-100, ‘L99G999D99MI’) FROM DUAL;

TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为’J’则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。

例如：

SELECT TO_DATE(2454336, ‘J’) FROM DUAL;

SELECT TO_DATE(‘2007-8-23 23:25:00’, ‘yyyy-mm-dd hh24:mi:ss’) FROM DUAL;

为什么公元制的话，c的值必须不大于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句：

SELECT TO_CHAR(TO_DATE(‘9999-12-31’,’yyyy-mm-dd’),’j’) FROM DUAL;

TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字符串按照fmt中指定格式转换为数值类型并返回。

例如：SELECT TO_NUMBER(‘-100.00’, ‘9G999D99’) FROM DUAL;

其它辅助函数(Miscellaneous Single-Row Functions)

DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else的功能。

exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。

毫无疑问，decode是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。

例如：select decode(‘a2’,’a1’,’true1’,’a2’,’true2’,’default’) from dual;

GREATEST(n1,n2,…n) 返回序列中的最大值

例如：SELECT GREATEST(15,5,75,8) “Greatest” FROM DUAL;

LEAST(n1,n2….n) 返回序列中的最小值

例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

NULLIF(c1,c2)

Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END

例如：SELECT NULLIF(‘a’,’b’),NULLIF(‘a’,’a’) FROM DUAL;

NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。

例如：SELECT NVL(null, ‘12’) FROM DUAL;

NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1为空则返回c3

例如：select nvl2(‘a’, ‘b’, ‘c’) isNull,nvl2(null, ‘b’, ‘c’) isNotNull from dual;

SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。

多行函数

和单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数能在select或select的having子句中使用，当用于select子串时常常都和GROUP BY一起使用。多行函数分为接收多个输入，返回一个输出

求员工的工资总和

select sum(sal) from emp;

求个数

select count(*) from emp;

求平均工资

select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;

关于空值：组函数会自动滤空

select count(*), count(comm) from emp;

max和min：求最高工资和最低工资

select max(sal) 最高工资,min(sal) 最低工资 from emp;

分组数据：求各个部门的平均工资

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

group by作用于多列： 按部门，不同的工种，统计平均工资

group by作用于多列：先按照第一列分组；如果相同，再按照第二列分组

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

：求部门的平均工资大于2000的部门

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

group by的增强

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

不同的deptno空两行/取消设置

break on deptno skip 2/break on null