函数的分类:
单行函数分为五种类型:字符函数、数值函数、日期函数、转换函数、通用函数
单行函数:
大小写控制函数
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