oracle 函数

-- 日期函数：
select sysdate from dual;
insert into emp(empno, hiredate) values(77, to_date('1999-9-9', 'yyyy-mm-dd'));
-- 入职时间大于400个月的员工
select * from emp where sysdate > add_months(hiredate, 400);
-- 入职时间大于10年的员工姓名和入职时间
select * from emp where add_months(hiredate, 12*10) < sysdate;
-- 每个员工入职天数
select ename, trunc(sysdate - hiredate) "入职天数" from emp;
-- 找出每月倒数第3天入职的员工
select ename, hiredate, last_day(hiredate), last_day(hiredate)-2 from emp where hiredate=last_day(hiredate)-2;

-- 类型转换函数：
-- 字符串和数字oracle会自动转换
insert into emp(empno, ename) values('88', 88);
select ename, to_char(hiredate, 'yyyy-mm-dd HH:mi:ss'), to_char(sal, 'L99,999.99') from emp;
select ename, to_char(hiredate, 'yyyy-mm-dd HH:mi:ss'), to_char(sal, 'C99,999.99') from emp;
select ename, to_char(hiredate, 'yyyy-mm-dd HH:mi:ss'), to_char(sal, '$99,999.99') from emp;
-- 1980年入职的员工
select * from emp where to_char(hiredate, 'yyyy') = 1980;
-- 12月入职的员工
select * from emp where to_char(hiredate, 'mm') = 12;

-- 系统函数：
select sys_context('userenv', 'db_name') from dual;
select sys_context('userenv', 'language') from dual;
select sys_context('userenv', 'terminal') from dual;
select sys_context('userenv', 'nls_date_format') from dual;
select sys_context('userenv', 'session_user') from dual;
select sys_context('userenv', 'current_schema') from dual; -- 方案

转载于:https://www.cnblogs.com/Mike_Chang/p/9311727.html