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