ORACLE 2

日期函数

在 Oracle 中日期分为 时间和日期两个部分

-- 查看当前系统时间

select sysdate from dual;

日期上进行加减

在日期上加上或者减去一个数字获得一个新的日期

两个日期相间返回的是日期之间相差的天数

可以用数字 除以24 向日期加或者减对应的小时

-- 一天后的时间

select sysdate+1 from dual;

-- 一天前的时间

select sysdate-1 from dual;

-- 两个日期相减

select sysdate-date'2024-01-01' from dual;

-- 进行日期的便宜，日期直接加对应的天数

select sysdate-4000 from dual;

-- 获取1个小时以后的时间

select sysdate+1/24 from dual;

-- 获取1分钟以后的时间

select sysdate+1/24/60 from dual;

select sysdate+1/(24*60) from dual;

-- 获取1秒钟以后的时间

select sysdate+1/24/60/60 from dual;

select sysdate+1/(24*60*60) from dual;

-- 获取 emp 表员工入职了多少天

select sysdate-hiredate from emp;

-- 员工入职多少周

select (sysdate-hiredate)/7 from emp;

日期函数

months_between 两个日期之间相差的月数

参数1 - 参数2 获得对应的数据

select months_between(date'2024-1-10',date'2024-3-10') from dual;-- -2

select months_between(date'2024-3-10',date'2024-1-10') from dual;-- 2

select months_between(date'2024-3-20',date'2024-3-10') from dual;-- 0.32258064516129

select months_between(sysdate,date'1924-3-20') from dual;-- 1200

add_months 加指定的月份

select add_months(sysdate,1) from dual;

select add_months(sysdate,-1) from dual;

select add_months(date'2024-01-31',1) from dual; -- 2024-02-29

select add_months(date'2024-01-30',1) from dual; -- 2024-02-29

last_day 指定日期，返回当月的最后一天

select last_day(sysdate) from dual; -- 2024-03-31

select last_day(date'2024-02-10') from dual; -- 2024-02-29

next_day 下一个星期中的第几天是几号(采用的是美国的计时方式，中国每周的第一天是周一，美国每周的第一天是周天)

-- 如果这个时间从本周过了，就去下周的时间，如果这个时间没过就取本周的时间

-- 当前日期 2024-03-20

select next_day(sysdate,3) from dual;-- 返回2024-03-26

select next_day(sysdate,5) from dual;-- 返回2024-03-21

select next_day(sysdate,4) from dual;-- 返回2024-03-27

round 四舍五入

默认保存到整数位，可以通过第二个参数进行设置

month 月

year 年

-- 超过一半就入(15号)，不超过就舍

select round(sysdate,'month') from dual;-- 2024-4-1

select round(sysdate,'year') from dual;-- 2024-1-1

select round(date'2024-04-01','month') from dual;-- 2024-4-1

select round(date'2024-04-16','month') from dual;-- 2024-5-1

select round(date'2024-04-15','month') from dual;-- 2024-4-1

select round(date'2024-04-01','year') from dual;-- 2024-1-1

select round(date'2024-07-01','year') from dual;-- 2025-1-1

select round(date'2024-06-01','year') from dual;-- 2024-1-1

round(day)

将一周分为 周一到周三，周四到周日，返回最近的一个周日

本周四到下周三 返回本周日

上周四到本周三 返回上周日

select round(sysdate,'day') from dual;-- 上周日

select round(date'2024-03-21','day') from dual; -- 本周日

select round(date'2024-03-18','day') from dual;-- 上周日

周一 到 周三 上周日 周四 到周日 本周日

trunc 截取

-- 截取当前系统时间的月份

select trunc(sysdate,'month') from dual;

select trunc(sysdate,'year') from dual;

-- 返回当前年的第一天

select trunc(sysdate,'year') from dual;

-- 返回当前月的第一天

select trunc(sysdate,'month') from dual;

-- 返回当前周的第一天

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

-- 返回当前的日期

select trunc(sysdate,'dd') from dual;

-- 返回当前的小时

select trunc(sysdate,'hh') from dual;

-- 返回当前的分钟

select trunc(sysdate,'mi') from dual;

转换函数和通用函数

转换函数

转换函数：数据类型之间可以进行相互转换

转换函数分为：显式转换和隐式转换

隐式转换

自己被动触发的

不需要人工干预，自己触发的

Oracle 自动完成的

如果符合转换要求，会自动进行转换

select 1+'1' from dual;-- 返回结果为 2 ，把 '1' 转换成数字类型

select 1+'帅' from dual; -- 报错，SQL 运行可以进行隐式转换，但是数据的格式符合原本的格式

显式转换

看得见，需要自己写，自己触发

包含：to_char、to_number、to_date

to_char

可以把时间类型转换成对应的字符

可以把数值类型转换成对应的字符

将时间类型转换成字符，to_char(时间,时间的精度)

可以把时间的精度获取出来

格式

1. 必须包含在单引号当中，而且对大小写敏感

1. 可以包含任意有效的日期格式

1. 日期之间可以用 逗号 隔开

to_char(时间，按照什么格式进行转换)

格式	显示
yyyy	2024(年)
year	twenty twenty-four
mm	03(月)
month	3月 (英文是全称英文月)
mon	3月 (英文是简称的英文月)
day	星期三(英文全称英文周)
dy	星期三(英文简称英文周)
dd	20 (日)
hh	11(时)
mi	27(分)
ss	03(秒)
am/pm	上午/下午

-- 将当前日期转换成 字符串

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

向字符中添加双引号

select to_char(sysdate,'mm "--" dd') from dual;

练习：

-- 查看当前系统时间

select sysdate from dual;

-- 查看1个月以后的时间

select add_months(sysdate,1) from dual;

-- 查看这个月的最后一天

select last_day(sysdate) from dual;

-- 查看今天最近的周日是哪天

select round(sysdate,'day') from dual;

-- 查看当前时间 +1H 转换成 字符串

select to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss am') from dual;

-- 查看在 1981 年入职的员工(不能用模糊查询)

select * from emp where to_char(hiredate,'yyyy')=1981;

to_char 对数值进行转换

to_char(数字,数字格式)

9——>数字

0——>0

$——>美元符

L——>本地符号

.——>小数点

,——>千位符

select sal,to_char(sal,'999,999,999,999.999') from emp;

select sal,to_char(sal,'L999,999,999,999.999') from emp;

select sal,to_char(sal,'$999,999,999,999.999') from emp;

select sal,to_char(sal,'99.999') from emp; -- 转换过程中，数据格式不够转换，就会出现乱码

to_date

to_date 函数对于字符进行转换，转换成时间

to_date(字符,格式)

select to_date('2024-03-01','yyyy-mm-dd') from dual;

select to_date('2024-03-01 10:10:10','yyyy-mm-dd hh24:mi:ss') from dual;

select to_date('08:08:08 2024-03-02','hh24:mi:ss yyyy-mm-dd') from dual;

-- 查询一个永远是早上 8点的时间

-- 1. 转换成字符串

select to_char(sysdate+1,'yyyy-mm-dd')||' 08:00:00' from dual;

-- 2. 转换成时间

select to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 08:00:00','yyyy-mm-dd hh24:mi:ss') from dual;

to_number

to_number 可以对字符进行转换，转换成数值

to_number(字符,格式)

seelct to_number('666') from dual;

select to_number('$1,234,567.789','$999,999,999.999') from dual;

select to_number('￥1,234,567.789','￥999,999,999.999') from dual;

转换成数字，不带符号的(人民币符号、美元符号)

通用函数

不管什么数据类型都可以使用，一般就是对空值进行处理

空值处理函数

因为 null 跟任何数据对比，返回的结果还是 null，在计算的时候就需要提前处理

nvl

nvl 对空值进行处理，使用默认值进行替换

nvl(列名,默认值)

判断列名是否为空，如果为空使用默认值，如果不为空用原始值

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

nvl2

nvl2(列名，不为空的值，为空的值)

nvl2 是通过判断列名是否为空，如果为空就用参数3，如果不为空，就用参数2

nvl2 不管是不是空，返回的都跟他本身没有关系

-- 判断 员工是否有奖金，如果有就返回 奖金+工资，如果没有就返回工资本身

select comm,sal,nvl2(comm,sal+comm,sal) from emp;

nullif

nullif(值1，值2)

nullif 本身就不是处理空值

判断两个值是否相等，相等返回空，不等返回 值1

select job,nullif(job,'SALESMAN') from emp;

coalesce

coalesce 与 nvl 相比，可以同时交替处理多个值

coalesce(值1，值2，值3，值4...)

判断值1是否为空，如果不为空返回值1，如果为空就判断值2是否为空，如果不为空就返回值2，如果为空......

select coalesce(comm,null,sal) from emp;

条件表达式

表达式分为 case 和 decode 函数

有点类似于编程中的分支语言，满足什么条件执行什么代码，不满足条件就执行另外的代码

case 表达式

case 表达式语法：(读取数据进行逻辑判断)

case

when 条件判断1 then 执行结果1

when 条件判断2 then 执行结果2

...

else 执行结果

end

/*

如果部门编号是 10、20、30 分别打印

10 返回工资的 1.5 倍

20 返回工资的 2 倍

30 返回工资的 3 倍

*/

select e.*,case deptno 

  when 10 then sal*1.5

  when 20 then sal*2

  when 30 then sal*3

  else 0 end 工资倍数

from emp e;

/*

判断工资    大于 1000 小于等于 1500 输出 一般收入

           大于 1500 小于等于 3000 输出中等收入

           大于 3000 输出 土豪

           其他 五保户

*/

select e.*,case 

       when sal > 1000 and sal <= 1500 then '一般收入'

       when sal > 1500 and sal <= 3000 then '中等收入'

       when sal > 3000 then '土豪'

       else '五保户' end  收入情况

 from emp e;

-- 判断 是否有奖金，如果奖金为空或者0 返回 没有奖金，如果有就返回 有奖金

select comm,case

       when comm is null or comm = 0 then '没有奖金'

       else '有奖金' end 奖金

from emp;

/*

查询 emp 中的工资等级，如果是 1600 以下就是 C

如果是 3000 以上就是 A

1600-3000 就是B

*/

select sal,

case when sal < 1600 then 'C'

when sal > 3000 then 'A'

else 'B' end "等级"

from emp;

decode 函数

decode(参数1，参数2，参数3...)

参数1 对那个列进行判断

参数2 列是什么值

参数3 怎么处理

decode(列名，值1，返回值1，值2，返回值2，...返回值N)

/*

如果部门编号是 10、20、30 分别打印

10 返回工资的 1.5 倍

20 返回工资的 2 倍

30 返回工资的 3 倍

*/

select e.*,decode(deptno,

10,sal*1.5,

20,sal*2,

sal*3)

from emp e

where deptno in(10,20,30);

-- 判断 是否有奖金，如果奖金为空或者0 返回 没有奖金，如果有就返回 有奖金

select comm,decode(comm,null,'没有奖金',

0,'没有奖金',

'有奖金') 奖金情况

from emp;

sign

sign(N) 对某个值进行判断，判断是正数还是负数，对应的值是 0，1，-1

如果N是0 返回0，如果N是负数，返回 -1 ，如果N 是正数返回 1

select sign(123.1),sign(-321.2),sign(0) from dual;-- 1 -1 0

-- 给 emp 表工资打等级，如果是1600 以下的C，3000以上的A，1600-3000 B

select ename,sal,decode((sign(sal-1600)+sign(sal-3000)),-2,'C',

2,'A',

'B') 工资等级

from emp;

-- 判断员工的入职年限，在 1981-5-1 之前入职的是老员工， 1981-5-1 ~1981-11-1 普通员工， 在 1981-11-1 入职的是新员工

select ename,hiredate,decode((sign(hiredate-date'1981-5-1')+sign(hiredate-date'1981-11-1')),-2,'老员工',2,'新员工','普通员工') 员工情况

from emp;

练习：

在 emp 表中，给 10 号部门员工加 1000 工资，20部门加2000，30部门500，

加完之后判断奖金和工资超过 4000 打工皇帝，超过3400 摸鱼大王 ，超过 3000 打工仔

其他 人嫌

嵌套函数

嵌套本质就是套娃，函数中有另外一个函数

select concat(concat('315','淀粉肠不能吃'),'有的正规的可以吃') from dual;

-- 输出员工的奖金，没有奖金就打印 没有奖金(先不管 0)

select nvl(to_char(comm),'没有奖金')

from emp;