-날짜함수
--select sysdate -1 : 어제, sysdate : 오늘 sysdate+1 : 내일
-- from dual;
select trunc((sysdate - hiredate)/365,1) as 근무일수 --차가 일수로 반환
from emp;
select sysdate,
round(sysdate, 'CC') as cc,
round(sysdate, 'YYYY') as YYYY,
round(sysdate, 'Q') as Q,
round(sysdate, 'D') as D,
round(sysdate, 'DDD') as DDD,
round(sysdate, 'HH') as HH
from emp;
select sysdate,
trunc(sysdate, 'CC') as cc,
trunc(sysdate, 'YYYY') as YYYY,
trunc(sysdate, 'Q') as Q,
trunc(sysdate, 'D') as D,
trunc(sysdate, 'DDD') as DDD,
trunc(sysdate, 'HH') as HH
from emp;
select hiredate,
to_char(hiredate, 'YYYY-MM-DD DAY HH24:MI:SS') as 입사날짜
from emp;
select to_char(123456,'L999,999')
from dual;
select sal, to_char(sal,'$99,000') as sal
from emp;
select '20000' -1000
from dual; --자동형변환
select '20,000' - '5,000' -- to_number()형변환필요
from dual;
select to_number('20,000','999,999') - to_number('5,000','999,999') as to_number
from dual;
select to_date('20221019','YYYY/MM/DD')
from dual;
select *
from emp
where hiredate < to_date('19820101','YYYY/MM/DD');
-- nvl(comm,0) : comm이 null인 값을 0으로 대체
select ename 사원명, sal, sal*12 + nvl(comm,0) as 연봉, comm -- 그냥 연봉에 comm 을 더하면 null값 때매 null이 됨
from emp;
select *
from emp
where mgr is null;
select ename, job, mgr
from emp
where mgr is null;
select ename, job, nvl(to_char(mgr,'9999'),'CEO') as mgr
from emp
where mgr is null; --이렇게 바꿨지만, 실제 데이터값이바뀌는게 아니라 이렇게 조회한거다.
select comm, nvl2(comm,'O','X') as OX
from emp;
select ename, job, deptno, decode(deptno, 10,'AAA',20,'BBB',30,'CCC','기타') as부서명
from emp;
--========================
'oracle' 카테고리의 다른 글
1024_제약조건(foreign key, check, default) (0) | 2022.10.26 |
---|---|
뷰 (View) (0) | 2022.10.26 |
1020_서브쿼리 (0) | 2022.10.26 |
Foreign Key(외래키) (0) | 2022.10.25 |
순위함수(ROW_NUMBER, DENSE_RANK,RANK) (0) | 2022.10.25 |