Oracle函数

Oracle函数大集合。另外还有练习题。

--日期函数

-- 1、sysdate服务器时间====date类型

select sysdate from dual;

-- 2、systimestamp 服务器时间===timestamp类型

--2018-10-18 14:27:14:753000 +08:00

select systimestamp from dual;

-- 受时区影响的服务器时间

select current_date from dual;

-- 2018-10-18 14:29:07:504000 +08:00

-- 2018-10-18 15:33:23:491000 ASIA/TOKYO

select current_timestamp from dual;

-- 修改当前会话时区--东八区 Asia/Shanghai

alter session set time_zone='Asia/Tokyo';

-- 一起查询对比四个时间的区别

select sysdate,systimestamp,current_date,

current_timestamp from dual;

-- 3、to_char(日期,格式)将日期转换成字符串

/*

d 一周当中的第几天 1-7

dd 月份中的某一天 1-31

day 一周中的某一天，和语言设置有关

ddd 一年中第一天 1-365

dl 长时间格式

ds 短时间格式

yyyy 四位数的年

mm 月

mi 分

ss 秒

ff 毫秒

iw 一年中的第几周 1-53

hh24 24小时制

*/

select to_char

(current_timestamp,'yyyy-mm-dd hh24:mi:ss')

from dual;

-- day获取的星期受语言环境影响

select to_char

(current_timestamp,'yyyy-mm-dd day')

from dual;

--ddd 获取一年中的第几天

select to_char

(current_timestamp,'yyyy-mm-dd ddd')

from dual;

--iw 获取一年中第几周

select to_char

(current_timestamp,'yyyy-mm-dd iw')

from dual;

-- dl 年月日 星期---Thursday, October 18, 2018

select to_char(current_timestamp,'dl') FROM dual;

-- ds 年月日 10/18/2018

select to_char(current_timestamp,'ds') FROM dual;

-- to_date(字符，格式) -- day of month must be between 1 and last day of

month

select to_date('2018-09-30','yyyy-mm-dd')

from dual;

-- 6、增加月份

select add_months(sysdate,3) from dual;

select add_months

(to_date('2018-3-22','yyyy-mm-dd hh24:mi:ss'),2)

from dual;

-- 7、last_day 返回日期中指定月的最后一天

select last_day(sysdate) from dual;

-- 8、months_between计算两个日期之间的月份差

select months_between

(sysdate,

to_date('2018-3-22','yyyy-mm-dd hh24:mi:ss'))

from dual;

--

select * from emp;

-- 获取员工入职的月份

select ename,

months_between(sysdate,hiredate) as "dd"

from emp order by "dd" desc;

-- 星期一是一周的第二天

select to_char(sysdate,'d') from dual;

-- next_day(date,x)

-- 周日=1 星期一=2 以此类推

-- 从指定日开始，返回下一个星期X是哪一天

select next_day(sysdate,2) from dual;

-- extract 返回日期/时间单独部分

-- year、month\day\hour\minute\second

select extract(year from sysdate) from dual;

-- 二、数学函数 Math

-- 1、abs 绝对值

select abs(-17) from dual;

-- 2、ceil(x) 返回大于等于x的最小整数值

select ceil(5.7) from dual;

-- floor(x) 返回小于等于x的最大整数

select floor(5.7) from dual;

-- power(x,y) 求x的y次幂

select power(10,3) from dual;

-- mod(x,y) 求x除以y的余数

select mod(10,3) from dual;

-- round(x,[y]) 返回四舍五入的值

select round(4.5) from dual;

-- sqrt(x) 返回x的平方根

select sqrt(9) from dual;

-- trunc(x,[y]) 返回x按照精度y截取后的值，

--y为正，表示保留的小数位数，

--y为负，表示左边y位变成0

select trunc(12.987,2) from dual;-- 12.98

select trunc(12.987,-2) from dual;-- 0

select trunc(112.987,-2) from dual;-- 100

select trunc(4312.987,-2) from dual;-- 4300

-- y为0，或者不写，取整

select trunc(4312.987,0) from dual;-- 4312

-- 字符函数

-- 1、chr(x) 将数字以AscII码的方式转成字符

select chr(97) from dual;

--2、ascii 将字符转成数字

select ascii('喻') from dual;

-- contact 连接字符串 Oracle中，字符串相连用'||'

select concat('fuck','you') from dual;

-- 4、ltrim |我爱 Oracle | 去掉左边的空格

select concat('|',ltrim(' 我爱 Oracle '))

from dual;

-- 5、trim 删除两边的空格

-- 6、upper 转换成大写

-- 7、lower 转换成小写

-- 8、replace(string,origstr,[replacestr])

-- 你不是很喜欢程序么

select replace('你爱程序么','爱','不是很喜欢')

from dual;

select replace('你爱程序么','程序')

from dual;

-- aabbccdd 全部替换

select replace('aa2bbc2cd2d','2') from dual;

-- translate 替换，功能比replace更强大

-- @#c##@@ef b被#代替 a被@代替，d对应的是空值，移除

select translate('abcbbaadef','bad','#@')

from dual;

select translate('我爱你','我你','你我') FROM

dual;

-- rpad(string,x,[str2])

-- 截取/填充x个长度，不足的话右边用指定的字符来填充

select rpad('abcd',5,'m') FROM dual;-- abcdm

select rpad('abcdefg',5,'m') FROM dual;

-- 汉字占两个字节

select rpad('单行单列',10,'猪') from dual;

-- ‘11猪 ’

select rpad('11',9,'猪') from dual;

-- lpad(str,x,[str2])

-- 截取/填充x个长度，不足的话【左】边用指定的字符来填充

-- substr(str,start,[length])

-- 截取子串，从start开始，截取中包括start的位置

-- 字符索引从1开始

SELECT substr('ABCDEFG',2,4) from dual;--BCDE

SELECT substr('ABCDEFG',2) from dual;--BCDEFG

-- 索引超出，不会异常，直接给null

SELECT substr('ABCDEFG',8,1) from dual;

-- length 求字符串的长度

SELECT length('YF1705') from dual;

-- 中文以字符数量返回长度=4

select length('中科韬睿') from dual;

-- 转换函数

-- 1、decode(条件,值1,翻译1,值2,翻译2……)

select ename,job from emp;

select ename,job,

decode(job,

'SALESMAN','销售员',

'MANAGER','经理',

'ANALYST','分析师') from emp;

-- case

/*

语法一,和decode函数一样，做等值替换

case 表达式

when 值1 then 返回值1

when 值2 then 返回值2

else 返回值3

end

*/

select ename,job,

case job

when 'SALESMAN' then '销售员'

when 'MANAGER' then '经理'

when 'ANALYST' then '分析师'

else '其他'

end from emp;

-- 不写else，用null值替代

select ename,job,

case job

when 'SALESMAN' then '销售员'

when 'MANAGER' then '经理'

when 'ANALYST' then '分析师'

end from emp;

/*

语法二（更灵活）

case

when 条件1 then 返回值1

when 条件2 then 返回值2

else 返回值3

end

*/

select ename,sal from emp;

select ename,sal,

case

when sal<1000 then '屌丝'

when sal between 1000 and 5000 then '温饱'

when sal between 5001 and 10000 then '小康'

else '壕'

end from emp;

-- 空值函数

-- 1、nvl(e1,e2) e1为null，返回e2。e1不为空，返回自己

-- 两个参数类型要一致

select ename,comm from emp;

select ename,comm,nvl(comm,0) from emp;

-- 2、nvl2(e1,e2,e3)

-- e1不为空，则返回e2，为空则返回e3。

--如果e2和e3的类型不同，会将e3转成e2的类型

select ename,comm,nvl2(comm,'牛',0)

from emp;

-- 3、nullif(e1,e2)

-- e1和e2相等则返回null，不等则返回e1

select nullif(2,1) from dual; --2

select nullif(2,2) from dual; -- NULL

练习题（以下是Oracle函数的练习题）

-- 在Oracle中，请以scott登陆，完成下列的查询：

--A、请统计出每一个月入职的人数

SELECT ename,hiredate from emp;

-- 第一步

select ename,hiredate,

extract(month from hiredate) as "hm" from emp;

--

select extract(month from hiredate) as hm,

count(*) from emp

group by extract(month from hiredate)

order by hm;

-- B、请统计出每一个职务的平均工资（sal + comm）

select job,avg(sal+nvl(comm,0))

from emp group by job;

-- C、请查询出所有的员工，其中员工编号

-- 必须显示10位，不足部分使用0填充

select empno,ename from emp;

select rpad(empno,10,0),empno,ename

from emp;

-- D、请统计出每一个部门的人数

-- E、请找出最高级别的领导的所有下属

-- 中工资最高的员工信息

select empno,ename,job,mgr from emp;

-- 错误写法

select empno,ename,job,max(sal) from emp;

-- 第一步 找到员工工资最高的用户

select * from emp where sal=(select max(sal) from emp)

-- 第二步 查找权限最高的mrg=null的用户的子级信息

/* 这里利用了connect BY prior递归查询，因为emp中的等级很多。

start with 子句：遍历起始条件

connect by 子句：连接条件。

关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；

prior跟子结点列subid放在一起，则往叶子结点方向遍历，

order by 子句：排序

*/

select level as "等级", empno, ename, mgr,(sal+nvl(comm,0)) newsal

FROM emp

where level>1 --

start with mgr is null

connect BY prior empno = mgr

order by newsal desc;

-- 根据如上结果，可以获取工资最高的用户信息

select max(sal+nvl(comm,0))

FROM emp

where level>1

start with mgr is null

connect BY prior empno = mgr

order by level;

-- 第三步 找到最高工资属于权限最高的mgr=NULL的用户的子级的信息

-- 最高级别的子级中工资最高的记录和emp，联合查询，条件是工资相等，然后得出员

工信息

select e2.* from emp e2 join (select max(sal+nvl(comm,0)) sal

FROM emp

where level>1

start with mgr is null

connect BY prior empno = mgr

order by level) e1 on e2.sal=e1.sal;