ORACLE SQL单行函数细节

ORACLE SQL单行函数细节

单行函数有很多，不过有些经常用到又比较复杂（易忘）的概念或函数还是值得提出来做个笔记

首先是有关日期的运算都分先后的，比如：日期四则运算，months_between

select months_between(to_date('1997-7-7','yyyy-mm-dd'),sysdate) from dual

避免复合运算时遗漏的错误

取整函数round在数字上表现为0-9四舍五入,而时间上则是0-59与0-23对半分,星期则是7-6三舍四入,日期则是固定15舍16入,二月28日或29日,大小月30日或31日都是如此,在月份上则是六舍七入,

select round(to_date('2016-2-15','yyyy-mm-dd'),'month') a,

       round(to_date('2016-2-16','yyyy-mm-dd'),'month') b,

       round(to_date('2019-2-15','yyyy-mm-dd'),'month') a1,

       round(to_date('2019-2-16','yyyy-mm-dd'),'month') b1,

       round(to_date('2019-6-16','yyyy-mm-dd'),'year') c,

       round(to_date('2019-7-15','yyyy-mm-dd'),'month') d,

       round(to_date('2019-7-16','yyyy-mm-dd'),'month') e,        

       round(to_date('2019-7-18','yyyy-mm-dd'),'day') f,--星期四

       round(to_date('2019-7-17','yyyy-mm-dd'),'day') f1,--星期三

       round(to_date('2019-7-16 23:59:30','yyyy-mm-dd
hh24:mi:ss'),'dd') g,

       round(to_date('2019-7-16 11:30:00','yyyy-mm-dd
hh24:mi:ss'),'hh') h,

       round(to_date('2019-7-16 23:59:30','yyyy-mm-dd
hh24:mi:ss'),'mi') i

from dual

字符控制函数中的索引instr返回值为自然数,且起点为1,即当字符串中没有要查询的字符时返回0而非空null

select instr('abcdefg','h'),instr('abcdefg','a') from dual

空值转换函数中,nvl(exp,exp1)中exp类型要与exp1相同 ,coalesce(exp,exp1…)中exp类型也要相同,而nvl2(exp,exp1,exp2)中exp类型可不同exp1与2,返回值的类型则为转换值类型

select nvl(commission_pct,1),
	nvl2(commission_pct,'加倍',to_date('1997-7-7','yyyy-mm-dd'))
from employees

If_when_then格式的条件表达式有case_when_then外还有decode,而decode使用起来没有case那么灵活,其含义为decode(exp,exp1,val1,…valn),仅当exp1=exp返回val1,…,其余返回valn

select commission_pct,decode(commission_pct,null,'空值','有值') from employees

Sign函数根据返回值负数,零,正数返回-1,0,1,而对于空值则返回空值

select sign(commission_pct),sign(commission_pct-1),
	commission_pct-1,commission_pct
from employees

日期截取函数extract可用于取代to_char(date,‘format’)获取部分日期,不过只能年月日:P

select extract(day from to_date('1997-7-7','yyyy-mm-dd')) a,
       extract(month from to_date('1997-7-7','yyyy-mm-dd')) b,
       extract(year from to_date('1997-7-7','yyyy-mm-dd')) c
       --extract(hour from to_date('1997-7-7','yyyy-mm-dd hh24:mi:ss')) d  错误示范
       --extract(date from to_date('1997-7-7','yyyy-mm-dd hh24:mi:ss')) 
from dual