Oracle 单行函数

--单行函数
--字符函数
select upper('ChenFeiPeng') from emp;


select upper('ChenFeiPegn'),lower('chenfeipeng') from dual;


select * from emp where ename=upper('smith');


select ename 原始姓名, initcap(ename) from emp;


select ename ,replace(ename,'A','_') from emp;


select * from emp where length(ename)=5;


select * from emp where substr(ename,0,3)='JAM';


select ename 原始名,substr(ename,3) 截取之后的姓名 from emp where deptno=10;


select ename,substr(ename,length(ename)-2) from emp;


select ename,substr(ename,-3) from emp;


select ASCII('L') from dual;


select chr(100) from dual;


select '       chenfeipeng          ' 原始字符串, ltrim('                     chenfeipeng          ') 去掉左空格 from dual;


select '       chenfeipeng          ' 原始字符串, rtrim('                     chenfeipeng          ') 去掉右空格 from dual;


select '       chenfeipeng          ' 原始字符串, trim('                     chenfeipeng          ') 去掉左右 from dual;


select lpad('mldn',10,'*') lpad函数使用,rpad('mldn',10,'*') rpad函数使用,lpad(rpad('mldn',10,'*'),16,'*') 组合使用
from dual;


select instr('mldn java','mldn') 查找得到,
instr('mldn java','java')查找得到,
instr('mldn java','JAVA')查找不到
from dual;




--数值函数


select round(789.652) 不保留小数,round(789.652,2) 保留两位小数,round(789.652,-1) 处理整数进位 from dual;


select empno,ename,job,hiredate,sal,round(sal/30,2) 日薪金 from emp;


select trunc(789.652) 截取小数, trunc(789.652, 2) 截取两位小数, trunc(789.652,-2) 取整 from dual;


select mod(10,3)from dual;


--日期函数


select sysdate from dual;


select systimestamp from dual;


alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;


select sysdate+3 三天之后的日期,sysdate-3 三天之前的日期
from dual;


select empno 雇员编号,
ename 雇员姓名, 
sysdate-hiredate 雇佣天数,
(sysdate-10)-hiredate 十天前的雇佣日期
from emp;


select empno 雇员编号,
ename 雇员姓名, 
trunc(sysdate-hiredate) 雇佣天数,
trunc((sysdate-10)-hiredate) 十天前的雇佣日期
from emp;


select sysdate,
add_months(sysdate,3) 三个月之后的日期,
add_months(sysdate,-3) 三个月之前的日期,
add_months(sysdate,60) 六十个月之后的日期
from dual;


select empno,ename,job,sal,hiredate,add_months(hiredate,3) from emp;


select sysdate,next_day(sysdate,'星期日') 下一个星期日,
next_day(sysdate,'星期一') 下一个星期一
from dual;


select sysdate,last_day(sysdate) from dual;


select empno,ename,job,sal,hiredate,last_day(hiredate) from emp where hiredate=last_day(hiredate)-2;


select empno 雇佣编号,ename 雇佣姓名,hiredate 雇佣日期,
trunc(months_between(sysdate,hiredate)) 雇佣总月数,
trunc(months_between(sysdate,hiredate)/12) 雇佣总年份
from emp;


select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
trunc(months_between(sysdate,hiredate)/12) 已雇佣年数
from emp;


select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
trunc(months_between(sysdate,hiredate)/12) 已雇佣年数，
trunc(mod(months_between(sysdate, hiredate),12)) 已雇佣月数
from emp;


select empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
trunc(months_between(sysdate,hiredate)/12) 已雇佣年数，
trunc(mod(months_between(sysdate, hiredate),12)) 已雇佣月数,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) 已雇佣的天数
from emp;


select extract(year from date '2001-09-19') years,
extract(month from date '2001-09-19') months,
extract(day from date '2001-09-19') days
from dual;


select extract(year from systimestamp) years,
extract(month from systimestamp) months,
extract(day from systimestamp) days,
extract(hour from systimestamp) hours,
extract(minute from systimestamp) minutes,
extract(second from systimestamp) seconds
from dual;


select extract(day from to_timestamp('1982-08-13 12:17:57',
'yyyy-mm-dd hh24:mi:ss')-to_timestamp('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days,
extract(hour from datetime_one-datetime_two) hours,
extract(minute from datetime_one-datetime_two) minutes,
extract(second from datetime_one-datetime_two) seconds
from(
    select to_timestamp('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one,
           to_timestamp('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
    from dual
);


select extract(day from datetime_one-datetime_two) days,
extract(hour from datetime_one-datetime_two) hours,
extract(minute from datetime_one-datetime_two) minutes,
extract(second from datetime_one-datetime_two) seconds
from(
    select to_timestamp('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one,
           to_timestamp('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
    from dual
);


--转换函数


select sysdate 当前系统时间,
to_char(sysdate,'yyyy-mm-dd') 格式化日期, 
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 格式化日期时间,
to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss') 去掉前导0的日期时间
from dual;


select sysdate 当前系统时间,to_char(sysdate,'year-month-dy') 格式化日期 from dual;


select * from emp where to_char(hiredate,'mm')='02';


select * from emp where to_char(hiredate,'mm')=2;


select empno,ename,job,hiredate,
to_char(hiredate,'yyyy-mm-dd') 格式化雇佣日期,
to_char(hiredate,'yyyy') 年,
to_char(hiredate,'mm') 月,
to_char(hiredate,'dd') 日
from emp;


select empno,ename,hiredate,to_char(hiredate,'year-month-dy') from emp;


select to_char(987654321.789,'999,999,999,999.99999') 格式化数字,
       to_char(987654321.789,'000,000,000,000.00000') 格式化数字
from dual;


select to_char(987654321.789,'L999,999,999,999.99999') 格式货币，
       to_char(987654321.789,'$999,999,999,999.99999') 格式美元
from dual;


select to_date('1979-09-19','yyyy-mm-dd') from dual;


select to_timestamp('1989-09-27 18:07:10','yyyy-mm-dd hh24:mi:ss') datetime from dual;


select to_number('09')+to_number('19') 加法计算,
to_number('09')*to_number('19') 乘法计算
from dual;


select '09'+'19' 加法计算,
'09'*'19'
from dual;


select empno,ename,job,hiredate,(sal+comm)*12 年薪 from emp;


select nvl(null,0),nvl(3,0) from dual;


select empno,ename,job,hiredate,(sal+nvl(comm,0))*12 年薪 from emp;


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


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


select empno,ename,job,length(ename),length(job),nullif(length(ename),length(job)) nullif from emp;


select decode(2,1,'内容为一',2,'内容为二'),decode(2,1,'内容为一','没有条件满足') from dual;


select ename,sal,
decode(job,
'CLERK','业务员',
'SALESMAN','销售人员',
'MANAGER','经理',
'ANALYST','分析员',
'PRESIDENT','总裁') job
from emp;


select ename,sal,
decode(job,
'CLERK','业务员',
'SALESMAN','销售人员',
'MANAGER','经理') job
from emp;


select ename,sal,case job 
when 'CLERK' then sal*1.1
when 'SALESMAN' then sal*1.2
when 'MANAGER' then sal*1.3
else sal*1.5
end 新工资
from emp;


select ename,sal,comm,coalesce(comm,100,2000),coalesce(comm,null,null) from emp;