函数（字符，日期，数值，及其他函数）"/>
oracle数据库，函数（字符，日期，数值，及其他函数）
- 字符型函数
针对字符、且返回值仍是字符的函数
- 连接 CONCAT（STR1,STR2）
CONCAT(STR1,STR2)：括号内包括两个参数，字符串STR1和字符串STR2，函数效果为将括号内的两个字符串合并到一起，相比于||的连接，CONCAT函数若是想要同时连接多个数据，需要反复嵌套该函数
SELECT CONCAT(CONCAT('HE','LL'),'O') FROM DUAL; HELLO
- 大小写转换函数 LOWER(STR) UPPER(STR) INITCAP(STR)
- 1）LOWER() 将字符串转化为小写
SELECT LOWER('ASDFGF') FROM DUAL;
2）UPPER() 将字符串转化为大写
SELECT UPPER('jjkhkjj') FROM DUAL;
3）INITCAP() 将首字母转化为大写
SELECT ENAME,INITCAP(ENAME) FROM EMP;
SELECT INITCAP('AFSF HSG AHSG') FROM DUAL; //如果对多个字符做首字母大写操作，用空格隔开
3.替换函数 (整体) REPLACE(STR,S1,S2)
REPLACE(STR,S1,S2)：该函数包括三个参数，STR-待处理的字符串，S1-将被替换掉的部分，S2-替换后的部分。换句话说：将STR中的S1替换成S2。这其中S1和S2都是视作一个整体，不能单独看其中的某个字符。
SELECT REPLACE('ASDFGH','A','K') FROM DUAL;
SELECT REPLACE('SDFGHTG','SDK','很好') FROM DUAL; //返回原字符串
4.去除函数 （个体） TRIM(LEADING/TRAILING/BOTH 'S' FROM STR) //LTRIM(STR,S1)//RTRIM(STR,S1)
- TRIM()：去除指定字符串或字段两侧的空格LTRIM()
SELECT TRIM(' KKK ') FROM DUAL;
SELECT TRIM('A' FROM 'AGGGA') FROM DUAL;
--trim()
SELECT TRIM(' AAA ') FROM DUAL; --两侧有空格
SELECT TRIM(' AAA BBB ') FROM DUAL; --两侧及中间都有空格
SELECT TRIM('@ AAABBB @') FROM DUAL; --虽然两侧有空格但不是在最边上
- LTRIM()：去除指定字符串或字段左侧的空格
SELECT LTRIM('ASD',',A') FROM DUAL; --去除字符串ASD中左侧的A，如果A参数省略，则默认去除空格。
SELECT LTRIM('ABCDE','ABC') FROM DUAL;
- RTRIM()：去除指定字符串或字段右侧的空格
SELECT RTRIM('ABCDE','ED') FROM DUAL; //’ED’是两个个体，
SELECT RTRIM('ASD',',A') FROM DUAL; --去除字符串STR中右侧的S，如果S参数省略，则默认去除空格。
/*TRIM([LEADING/TRAILING/BOTH] [S FROM] STR) 去除字符串STR中的S，具体去除左侧/右侧/两侧，
根据参数[LEADING/TRAILING/BOTH] 决定，若省略该参数，默认去除两侧；
参数[S FROM] 决定了被去除的内容，若省略则默认去除空格。
*/
//--SELECT TRIM(LEADING/TRAILING/BOTH 'S' FROM 'SADSDS') FROM DUAL;
LEADING:去除首部 TRAILING:去除尾部 BOTH:去除两端
注意！虽然TRIM()/LTRIM()/RTRIM()都是用来去除字符串中的指定部分，但在完整用法上有很大区别，一定要注意区分！！！
1)LTRIM()与RTRIM()中的S可以是单个字符，也可以是多个字符，但TRIM()中的S只能是一个字符；
2)TRIM()函数没有TRIM(STR,S)这种用法，反过来LTRIM()与RTRIM()也不会写成LTRIM(S FROM STR)这种形式;
3)三个函数在去除时，都是从某侧开始去除直到第一个非S出现，特别需要注意LTRIM()与RTRIM()中的S若有多个字符，在去除时是挨个检索S中的每一个字符，而非将S视作一个整体（区分REPLACE函数）。
5.截取 SUBSTR(STR,IND,LEN)
SUBSTR(STR,IND,[LEN]):从字符串STR的IND位置，截取LEN长度的字符串并返回该截取内容
SELECT SUBSTR('ASDFG',0,3) FROM DUAL;
SELECT SUBSTR('ASDFG',1,3) FROM DUAL; //0和1都表示从第一位开始
SUBSTR函数可以说是除聚合函数外使用最频繁的函数，要牢记。
(1)不写截取长度时就截到末尾
SELECT SUBSTR('ASBGFDY',2) FROM DUAL; --从第二位开始截，截到末尾
（2）IND<0时 定位时从右到左定位，截取时还是从左到右截取
SELECT SUBSTR('ABCDEFG',-3,3) FROM DUAL; EFG --如果IND参数为负，表示从右向左定位，截取时是从左向右截取
SELECT ENAME,SUBSTR(ENAME,-4,1) FROM EMP;--不论IND参数是正还是负，截取时都是从左向右截取
（3）LEN<=0时，截取结果返回空值
SELECT ENAME,SUBSTR(ENAME,-2,-2) FROM EMP; --若LEN参数为0或负数，截取结果返回空值
SELECT ENAME,SUBSTR(ENAME,2,100) FROM EMP;--若LEN参数已超出STR实际长度，则截取到末尾；
--当IND为负数时，表示从右边算起第几位，SUBSTR('adgdshd',-2,4)表示从h开始，从左往右截取长度为4的字符串，
--如果长度不够，就把剩余的全部截取。
SELECT SUBSTRB('那还好多久啊',0,8) FROM DUAL; --SUBSTRB() 表示的是字节，在utf-8编码里一个汉字占三个字节，一个英文占一个字节
SELECT SUBSTRB('dsyfkhgkuhkgh',0,8) FROM DUAL;
SELECT SUBSTRB('5752523752',0,8) FROM DUAL; --一个数字占一个字节
总结：
1）截取函数中的参数IND和LEN分别表示起始位置和截取长度，而非起始位置和终止位置（因工作中常常从首位开始截取因而产生误解）；
2）从0位开始截取，相当于从1位开始截取（需注意在某些数据库或程序语言中，0位与1位表示不同的位置）；
3）如果IND参数为正，表示从左向右定位；如果IND参数为负，表示从右向左定位；
4)如果IND参数长度大于str实际长度，会返回空
5）无论IND参数是正还是负，截取时都是从左向右截取；
6）若LEN参数为0或负数，截取结果返回空值；
7）若LEN参数已超出STR实际长度，则截取到末尾；
8）LEN参数可以省略，表示截取到末尾，而IND参数必不可少。
9）当IND和LEN为小数时，按整数部分截取
6.填充 (针对字节，这里的长度是显示长度，汉字字符字节数固定为两个)（填充的字符为）
LPAD(STR,N,S)：在字符串STR左侧，填充S字符，使长度达到N
SELECT LPAD('ABCDEF',14,'S') FROM DUAL;//
RPAD(STR,N,S)：在字符串STR右侧，填充S字符，使长度达到N
--返回数值的函数
SELECT RPAD('ACSDF',10,'A') FROM DUAL;
- 如果填充长度小于字符长度，会根据填充长度从左至右截取字符
SELECT LPAD('AGHGDHSG',4,'1') FROM DUAL; AGHG
- 填充长度为0或负数时，返回空值（跟substr中len<=0时一样）
SELECT RPAD('KHDSHSHKD',-5,'1') FROM DUAL;
- 所填充的字符是一个个个体，而非一个整体
SELECT LPAD('ASSD',9,'**') FROM DUAL;
- 如果填充的长度大于指定的长度，那会根据指定长度将字符按顺序填充
SELECT RPAD('HJJJ',5,'%$^') FROM DUAL;
总结：
1）填充函数属于使用频率比较小但是十分有效的函数，该函数不求能完全记住用法，但一定要记住有这么一个函数提供填充功能；
2）S可以是某一个字符，也可以是一段字符串，若S是字符串，填充时会循环使用S中的每一个字符；
3）填充的步骤是先计算为达到长度N还欠缺多少位字符，然后使用S字符串拼接成相应长度的空缺，直接附在目标字符串左侧或右侧；
4）注意尽量避免N比STR总长还要小的情况
5)不指定填充物时会以空填充，填充物为空，返回结果为空
--返回数值的函数
1.获取字符长度 (针对字符) LENGTH(STR)
LENGTH(STR):返回字符串STR的字符长度
如何直观地判断字节长度？
LENGTHB(STR)：返回字符串STR的字节长度
--所有函数中，若见到以字母B结尾的函数，多为针对字节的函数
2.获取字符位置（整体） INSTR(STR,S,IND,N)
INSTR(STR,S,IND,N):从第IND位开始，查找S在字符串STR中第N次出现的位置。
SELECT INSTR('ADSFGHAS','S',2,2) FROM DUAL; --8
一般用法：
INSTR(STR,S) ：查找S在字符串STR中第一次出现的位置
SELECT INSTR('HGGHH','G') FROM DUAL; --2
深入研究：
1）IND=0 返回0（这里可以发现与SUBSTR（STR,IND,LEN）的区别，在SUBSTR函数中，IND=0与IND=1相同，而INSTR(STR,’S’,IND,N)中，IND=0返回0）
SELECT INSTR('ABCDE','B',0) FROM DUAL; --0
2) IND<0 （定位时从右往左，查找时也是从右往左，返回时从左往右）
SELECT INSTR('ABCDE','B',-2) FROM DUAL; --2
SELECT INSTR('ABCDEABCDE','B',-4,2) FROM DUAL; --2
SELECT INSTR('ABCDEABCDEABCDE','B',-4,3) FROM DUAL; --2
SELECT INSTR('CABDEABCDE','B',-5) FROM DUAL; --3
3) IND 超过字符长度返回0
SELECT INSTR('ABCDE','B',6) FROM DUAL; 0
4) N<=0 （ERROR）
SELECT INSTR('ABCDE','B',1,0) FROM DUAL;
SELECT INSTR('ABCDE','B',1,-2) FROM DUAL;
5) N 超过S在字符串中出现的次数 返回0
SELECT INSTR('AABBCCDD','B',2,3) FROM DUAL;
再次总结：
1）IND=0，返回0
2）IND<0，代表从右侧开始数起，第N次出现S的位置，返回位置时仍从左侧开始算起。
3）IND超出字符总长时,返回0
4）N<=0，会报错
5）N超过S在字符串中出现的次数，返回0
6）IND和N是小数，按整数部分运行
(截取函数)SUBSTR(STR,IND,LEN) 与 (获取位置)INSTR(STR,’S’,IND,N)两个函数的区别：
答：1.IND<0时，SUBSTR(STR,IND,LEN)定位时从右至左定位，截取时从左到右截取；而INSTR(STR,’S’,IND,N)定位时也是从右至左定位，查找时还是从右至左查找，返回时则是从左至右。
2.IND=0时，SUBSTR(STR,IND,LEN)定位从1开始，而INSTR(STR,’S’,IND,N)则返回0。
3.IND超过字符串长度时，SUBSTR()返回空，而INSTR()返回0。
4.SUBSTR（STR,IND,LEN）返回字符串 INSTR（STR,S,IND,N） 返回数值；
5.SUBSTR(STR,IND,LEN)中的LEN超过字符串长度时，定位后面的全截；
INSTR(STR,’S’,IND,N)中N超过字符在字符串出现的次数时，返回0。
6.在SUBSTR(STR,IND,LEN) 中LEN<=0时，返回空值。
而在INSTR(STR,’S’,IND,N) 中N<=0,ERROR
- 在SUBSTR(STR,IND,LEN)和INSTR(STR,’S’,IND,N)中，遇到小数一律按整数部分算。
- SUBSTR不满足要求时一般返回空，而INSTR不满足要求一般返回0或报错。
- 数值型函数
- 取绝对值 ABS(NUM)
ABS(NUM):获取数值NUM对应的绝对值。
SELECT ABS(-52.2) FROM DUAL; 52.2
该函数通常不直接对某数值进行取绝对值，而是对于一系列的计算结果获取绝对值。
SELECT ABS(-1.2*2.8) FROM DUAL;
2.向上取整与向下取整 CEIL(NUM) FLOOR(NUM)
2.1 CEIL(NUM) ：向上取整，获取离NUM最近的两个整数中较大的那个数
SELECT CEIL(2.5) FROM DUAL; -- 3
SELECT CEIL(-3.9) FROM DUAL; -- -3
2.2 FLOOR(NUM)：向下取整，获取离NUM最近的两个整数中较小的那个数
SELECT FLOOR(2.5) FROM DUAL; -- 2
SELECT FLOOR(-3.2) FROM DUAL; -- -4
注意：
- 若NUM本身即为整数，则无论向上取整还是向下取整，返回结果都为NUM本身
SELECT FLOOR(-5) FROM DUAL; -5
SELECT FLOOR(5) FROM DUAL; 5
2）无论NUM是正数还是负数，所谓上，即数轴指向的方向，所谓下，即数轴背向的方向
3.取余 MOD(NUM1,NUM2)
MOD(X,Y)：X是被除数，Y是除数，计算Y整除X，获取计算结果中的余数。（不是商！不是商！不是商！）
SELECT MOD(5,2) FROM DUAL; 1
//MOD函数对于纯数学范畴的使用很少，但智慧的程序员们却因此提出了“分库分表”的概念：将数据按照一定规则均分到不同的数据库（表）中，变串行为并行，以此来提高代码和程序的运行效率。
4.四舍五入 ROUND(NUM,P)
ROUND(NUM[,P])：对数值NUM进行四舍五入，其中参数P决定了四舍五入的精度
- 基础用法
SELECT ROUND(2.254,2) FROM DUAL; 2.25// 2是精确到小数点第二位
- 精度为0或者精度省略 精确到个位
SELECT ROUND(215.2154,0) FROM DUAL; 215
SELECT ROUND(2.56521) FROM DUAL; 3//精度为0或精度不要取整。
- 负数的四舍五入
SELECT ROUND(-2.56,1) FROM DUAL; -2.6
SELECT ROUND(-1.2365,1) FROM DUAL; -1.2
SELECT ROUND(-5.564,0) FROM DUAL; -6
SELECT ROUND(-5.564) FROM DUAL; -6 //负数做四舍五入时，把负号去掉，四舍五入完后再把负数加上去
- 精度为负数
SELECT ROUND(2.253,-1) FROM DUAL; 0-- -1,-2,-3分别为保留到十位百位千位，返回值分别是10,100,1000
SELECT ROUND(55.253,-1) FROM DUAL; 60
SELECT ROUND(152.253,-2) FROM DUAL; 200
SELECT ROUND(1552.253,-3) FROM DUAL; 2000
注：p为-1时，保留到十位，对个位做四舍五入；P为-2时，保留到百位，对十位做四舍五入；
p为-3时，保留到千位，对百位做四舍五入。。。
- 精度不为整数
SELECT ROUND(2.325,1.21) FROM DUAL; 2.3//精度不为整数时，忽略小数部分。
5.截断 TRUNC(NUM,P)
TRUNC(NUM[,P]) ：对数值NUM进行截断，其中参数P决定了截断的精度
- 基础用法
SELECT TRUNC(2245,-1) FROM DUAL; 2240//直接按照精度截断，不做四舍五入
- 精度为0或精度省略
SELECT TRUNC(33.25，0) FROM DUAL; 33
SELECT TRUNC(23.255) FROM DUAL; 23//直接截断
- 负数的截断
SELECT TRUNC(-11.254,1) FROM DUAL; -11.2
- 精度为负数
SELECT TRUNC(12.36,-1) FROM DUAL; 10
SELECT TRUNC(-12.2,-1) FROM DUAL; -10//直接截断
- 精度不为整数
SELECT TRUNC(12.255,-1.7) FROM DUAL; -10//将精度中的小数部分去掉，按整数精度截断
- 日期型函数
关于日期的计算：
日期直接加减一个整数，该数值以天为单位进行日期计算；
SELECT TO_DATE('1987/02/05','YYYY/MM/DD')+5 FROM DUAL;//1987/02/10
两日期相减，返回两日期相差的天数。
SELECT TO_DATE('1998/02/15','YYYY/MM/DD')-TO_DATE('1997/01/02','YYYY/MM/DD') FROM DUAL; 409
1.关于日期的四舍五入与截断
ROUND()函数与TRUNC()函数除了可以作用在数值上，也可以作用在日期上，对于日期也同样有四舍五入或截断的需求
对于四舍五入，在数值方面依靠数值5作为分界线，达不到5即被舍去，达到5即向前进1。在日期方面，四舍五入同样需要一个分界线，该分界线在不同的时间单位中并不相同，原则上是取居中的位置作为分界线。
对于截断，日期的截断与数值的截断类似，定位到截取精度，直接舍弃精度之外的部分，留下精度之内的部分。
- 省略精度，即默认的精确到天，天的界限
ROUND(): (12小时)
SELECT SYSDATE,ROUND(SYSDATE) FROM DUAL;
SELECT ROUND(TO_DATE('2022/01/02 12:00:00','YYYY/MM/DD HH24:MI:SS'))FROM DUAL; 2022/01/03 --天的界限是小时，大于或等于12小时进一天
TRUNC():
SELECT TRUNC(SYSDATE) FROM DUAL;
SELECT TRUNC(TO_DATE('2022/01/02 15:36:25','YYYY/MM/DD HH24:MI:SS'))FROM DUAL; 2022/01/02
- 年的界限 //7月份
ROUND():
SELECT ROUND(SYSDATE,'YYYY') FROM DUAL; //月份大于等于7时，年份进1，月日变成01/01
SELECT ROUND(TO_DATE('1998/07/02','YYYY/MM/DD'),'YYYY') FROM DUAL;
//1999/01/01
TRUNC():
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
SELECT TRUNC(TO_DATE('1998/05/09','YYYY/MM/DD'),'YYYY') FROM DUAL;
1998/01/01 截断是直接返回所在年份的一月一日。
- 月的界限 //16号
ROUND（）：
SELECT ROUND(SYSDATE,'MM') FROM DUAL; //对当天所处月份进行四舍五入
SELECT ROUND(TO_DATE('2022/05/16','YYYY/MM/DD'),'MM') FROM DUAL;
2022/06/01 当天数大于等于16时，月份进1
TRUNC():
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/05/07','YYYY/MM/DD'),'MM') FROM DUAL; 2022/05/01 返回所在月份的一号
- 季的界限 //每季度中间月份的16号
ROUND():
SELECT ROUND(SYSDATE,'Q') FROM DUAL;
SELECT ROUND(TO_DATE('2022/05/16','YYYY/MM/DD'),'Q') FROM DUAL;
2022/07/01
一季度 01/01（02/16） 二季度 04/01 （05/16） 三季度 07/01 （08/16） 四季度 10/01 （11/16）
TRUNC():
SELECT TRUNC(SYSDATE,'Q') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/9/16','YYYY/MM/DD'),'Q') FROM DUAL;
2022/07/01 按所在月份所在季度的第一个月份的第一天来截
- 小时的界限 （30分钟）
ROUND()：
SELECT ROUND(SYSDATE,'HH') FROM DUAL;
SELECT ROUND(TO_DATE('2022/02/12 15:30:00','YYYY/MM/DD HH24:MI:SS'),'HH') FROM DUAL; 2022/02/12/ 16:00:00
TRUNC()：
SELECT TRUNC(SYSDATE,'HH') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/02/02 15:30:14','YYYY/MM/DD HH24:MI:SS'),'HH') FROM DUAL; 2022/02/02 15:00:00 按小时截
- 星期的界限
星期：
WW：一年中的第几个星期，从第一天开始算起，一个七天算作一周，和星期几无关
IW：一年中的第几个星期，星期一为本周第一天，每年末最后一个周不足四天算至下年第一周，
足四天将下年本周的剩余时间一同算作本年最后一周
W：一个月中的第几个星期，从第一天开始算起，一个七天算作一周，和星期几无关
DAY：星期几全称
1.星期的界限WW（一个七天算作一周）：--每7天内的第四天的12点
SELECT ROUND(TO_DATE('20220104 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/01
SELECT ROUND(TO_DATE('20220104 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/08
SELECT ROUND(TO_DATE('20220111 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/08
SELECT ROUND(TO_DATE('20220111 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/15
2.星期的界限IW（星期一为本周第一天）：--周四的12点
SELECT ROUND(TO_DATE('20220728 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT ROUND(TO_DATE('20220728 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT TRUNC(TO_DATE('20220728 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT TRUNC(TO_DATE('20220728 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
3.月周界限W（7天一周）：--每7天内的第四天的12点
SELECT ROUND(TO_DATE('20220704 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220704 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220711 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220711 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
4.星期几-DAY界限：--周三12:00
SELECT ROUND(TO_DATE('20220726 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220726 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220727 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220727 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--下个星期天
日期函数
1.获取月份差值 （d1-d2）
MONTHS_BETWEEN(d1,d2) :求D1和D2之间相差几个月（d1>d2）
常规用法：给定两个日期求两日期之间的月份差
1.计算从元旦到今天经过了几个月（整数月）
SELECT CEIL(MONTHS_BETWEEN(SYSDATE,TO_DATE('2022/01/01','YYYY/MM/DD')))
FROM DUAL; --CEIL:向上取整
2.计算从元旦到今天经过了几个月（非整数月）
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2022/01/01','YYYY/MM/DD')) FROM DUAL;
3.计算从今天到明年元旦还要等待几个月（整数月）
SELECT CEIL(MONTHS_BETWEEN(TO_DATE('2023/01/01','YYYY/MM/DD'),SYSDATE)) FROM DUAL;
4.两个日期都是月末日期的时候，只计算整数部分
SELECT ABS(MONTHS_BETWEEN(TO_DATE('2022/01/31','YYYY/MM/DD'),TO_DATE('2022/06/30','YYYY/MM/DD'))) FROM DUAL;
特殊情况
1）一般工作中使用到的情况是，给定两个月初或月末日期、或者两个DD相同的日期。如果给定的两个日期DD不相同，月份差会得到非整数，小数部分为剩余天数/31，这个规则无论针对哪一个月都是如此；
2）如果给定的两个日期DD不同，但是想要获得整数月，可以配合TRUNC函数或后续提到的获取月末日期函数使用；
3）如果两个日期的DD不同，但是都是月末日期，则获取的月份差将是整数；
4）需要特别注意，函数中录入的两个日期是有大小之分的（区别BETWEEN AND），一般晚的日期在前，早的日期在后，如此得到的结果便是正数，反之获得负数。如果不确定两个日期的大小，可搭配ABS()函数使用。
2.获取月末日期
LAST_DAY(D)：获取指定日期对应的当月最后一天，即月末日期
举例：
- 获取当月最后一天。
SELECT LAST_DAY(SYSDATE) FROM DUAL; //会显示时分秒
SELECT TRUNC(LAST_DAY(SYSDATE)) FROM DUAL; //加上TRUNC函数，会把时分秒隐去
- 查询某月份的最大天数
SELECT LAST_DAY(TO_DATE('07','MM')) FROM DUAL; //显示年月日
SELECT TO_CHAR(LAST_DAY(TO_DATE('02','MM')),'DD') FROM DUAL;
SELECT SUBSTR(TO_CHAR(LAST_DAY(TO_DATE('07','MM'))),1,2) FROM DUAL;31
//与电脑显示有关，先看一下天对应的位置，再按位置截断
SELECT TO_CHAR(LAST_DAY(SYSDATE),'DD') FROM DUAL; --31(精确到天)
- 查询某日期对应的年份是平年还是闰年
SELECT CASE
WHEN TO_NUMBER(TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'YYYY') + 31), 'DD')) = 28 THEN
'平年'
ELSE
'闰年'
END 年份
FROM DUAL;
- 获取给定两日期的月份差（整数月） --将两个日期都做LAST_DAY处理
SELECT ABS(MONTHS_BETWEEN(LAST_DAY(TO_DATE('2022/02/04','YYYY/MM/DD')),LAST_DAY(TO_DATE('2022/07/15','YYYY/MM/DD')))) FROM DUAL;
3.月份加减 ADD_MONTHS(D,N)
ADD_MONTHS(D,N) :在D日期的基础上加N个月。N可为正可为负
--基础用法：
SELECT ADD_MONTHS(TO_DATE('2022/01/15','YYYY/MM/DD'),2) FROM DUAL;
SELECT ADD_MONTHS(TO_DATE('2022/01/15','YYYY/MM/DD'),-2) FROM DUAL;
--求平年闰年
SELECT CASE
WHEN TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)),'DDD')) = 365 THEN
'平年'
ELSE
'闰年'
END 年份
FROM DUAL;
--N为负数表示在D日期的基础上减去几个月
特殊情况：
1）如果N是小数
SELECT ADD_MONTHS(TO_DATE('2022/02/14','YYYY/MM/DD'),1.5) FROM DUAL; //小数部分没有意义
2）如果DD是月末（返回所在月份的最后一天）
SELECT TO_DATE('2012/02/29','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/02/29','YYYY/MM/DD'),2) FROM DUAL; --2022/04/30
SELECT TO_DATE('2012/03/31','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/03/31','YYYY/MM/DD'),3) FROM DUAL; --2012/06/30
- 如果DD不在计算后的月份中
（如果DD不在计算后的月份中，则返回计算后的最后一天）
SELECT TO_DATE('2012/01/31','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/01/31','YYYY/MM/DD'),3) FROM DUAL; --2012/04/30
SELECT TO_DATE('2013/01/29','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2013/01/29','YYYY/MM/DD'),1) FROM DUAL; --2013/02/28
思考：
返回日期对应的当月第一天
1.）SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FROM DUAL;
--将当前月份减1，再做LAST_DAY,可以得到当前所在月份上个月的最后一天，在加上一天，即为这个月的一号
2.）SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM DUAL;
--将当前日期经过LAST_DAY得到这个月的最后一天，再经过ADD_MONTHS减去一个月，得到上个月的最后一天，再加一天，即为当月的第一天
3.）SELECT ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1) FROM DUAL;
--将当前月份经过LAST_DAY返回这个月的最后一天，再用ADD_DATE减去1，即为这个月的第一天
4.SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')||'01','YYYY/MM/DD') FROM DUAL
4.获取下个周几
NEXT_DAY (D,W)：给定日期D，和星期W，获取D日期之后的下一个星期W（不是下个周的周几）（下一次出现星期几是几号）
例如：
某业务只能在周五办理，错过只能等下一个周五。请问今天对应的下一个周五是几号。
SELECT NEXT_DAY(SYSDATE,'星期五') FROM DUAL;
--思考题：
给定任意日期，如何获取下个周的星期五
思考：
给定任意日期，如何获取下个周的星期五
SELECT TRUNC(SYSDATE,'IW')+6,NEXT_DAY(TRUNC(SYSDATE,'IW')+6,'星期五') FROM DUAL;
思考：
给定任意日期，如何获取下个周的星期五（或者下个周的星期N）
SELECT NEXT_DAY(TRUNC(TO_DATE('20220727','YYYYMMDD'),'IW')+6,'星期五') FROM DUAL;
例如：在本周日获取下个周的星期日
SELECT NEXT_DAY(TRUNC(TO_DATE('20220731','YYYYMMDD'),'IW')+6,'星期日') FROM DUAL;
--基于周日获取的NEXT_DAY，结果总是在下个周
- 其他函数
- 条件取值
DECODE(EXPR,VALUE1,RESULT1,VALUE2,RESULT2,…,DEF_RESULT)
举例：
使用decode函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
SELECT ENAME,SAL,JOB,
DECODE(JOB,'ANALYST',SAL+1000,'MANAGER',SAL+800,SAL+400) FROM EMP;
--IN() LIKE BETWEEN AND ANY() > < =...在DECODE函数中用不了
其实效果即等同于
CASE EXPR
WHEN VALUE1 THEN RESULT1
WHEN VALUE2 THEN RESULT2
…
ELSE DEF_RESULT
END
SELECT ENAME, --只能做相等判断
JOB,
SAL,
CASE JOB
WHEN 'ANALYST' THEN
SAL + 1000
WHEN 'MANAGER' THEN
SAL + 800
ELSE
SAL + 400
END
FROM EMP;
--CASE WHEN THEN WHEN THEN END (都可以用)
SELECT ENAME,JOB,SAL,CASE WHEN JOB='ANALYST' THEN SAL+1000 WHEN JOB='MANAGER' THEN SAL+800 ELSE SAL+400 END FROM EMP;
2.返回集合中第一个不为空的内容
--COALESCE(c1,c2,c3,c4,.....):返回括号中第一个非空表达式，如果都为空，则返回空
CREATE TABLE BOY(姓名 CHAR(50),
小名 CHAR(20),
绰号 CHAR(50)); --建表
插入数据
SELECT * FROM BOY FOR UPDATE; --在插入的时候留几个空不写数据
SELECT 姓名,小名,绰号,COALESCE(姓名,小名,绰号) FROM BOY;
3.空值的赋值
--NVL(EXPR,VALUE):如果EXPR是空，返回一个VALUE，如果EXPR不为空，返回EXPR本身
SELECT NVL(小名,'没有') FROM BOY;
SELECT NVL(COMM,10000) FROM EMP;
--NVL2(EXPR1,EXPR2,EXPR3):expr1不为NULL，返回expr2；expr1为NULL，返回expr3
--公司今年业务赚了很多钱，老板开心，决定给员工发奖金，有奖金的加5000，没奖金的给4000
SELECT COMM,NVL2(COMM,COMM+5000,4000) FROM EMP;
--坑爹老板//公司不景气，老板决定工资加奖金不为空的，只发工资部分，空的，返回工资加奖金（空）
SELECT NVL2(SAL+COMM,SAL,SAL+COMM)FROM EMP;
4.去重
--DISTINCT COL_LIST:
--SELECT DISTINCT COL_LIST FROM TB_NAME …对COL_LIST范围内的字段进行去重
--举例（以下查询结果全部要求去重）：
--获取公司的所有岗位
SELECT DISTINCT JOB FROM EMP;
--获取公司的所有部门
SELECT DISTINCT DEPTNO FROM EMP;
--获取各部门的各岗位
SELECT DISTINCT DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB;
--平年闰年
SELECT CASE
WHEN MOD(TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('1900/01/01',
'YYYY/MM/DD')),
'YYYY')),400) = 0 OR
MOD(TO_NUMBER(TO_CHAR(TO_DATE('1900/01/01', 'YYYY/MM/DD'),
'YYYY')),4) = 0 AND
MOD(TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('1900/01/01',
'YYYY/MM/DD')),
'YYYY')),100) <> 0 THEN
'闰年'
ELSE
'平年'
END 平年闰年
FROM EMP;
更多推荐
oracle数据库,函数（字符,日期,数值,及其他函数）
发布评论