[데이터베이스] 18일차 - 날짜 함수, 변환 포맷 함수, null 처리 함수, 조건 제어 함수

6일차

-- add_months(날짜, 정수(월))

select add_months(sysdate, 10) from dual;   -- 현재로부터 10달뒤 출력

 

-- 날짜를 더할때는 그냥 + - 이용

select sysdate+100 from dual;         

 

-- extract (데이터를 가져올때 숫자타입으로 가져온다)

select extract (year from sysdate) year,
extract (month from sysdate) month,
extract (day from sysdate) day,
extract (hour from systimestamp) hour,  -- 9시가 0으로 출력됨
extract (timezone_hour from systimestamp) timezone_hour,  -- 정상출력
extract (minute from systimestamp) minute,
extract (timezone_minute from systimestamp) timezone_minute,
extract (second from systimestamp) second
from dual;

 

-- oracle의 데이터 연산,연결
select 3+3, '3'+'3', '3'||'3', 3||3 from dual;

 

-- last_day(날짜)
select last_day(sysdate) from dual;

 

--next_day(날짜, 요일)
select next_day(sysdate, '월') "다음 월요일",
next_day(sysdate, '화') "다음 화요일"
from dual;

 

-- months_between(날짜1, 날짜2)
-- (날짜1 - 날짜2) 의 정확한 실수값을 출력
select months_between(sysdate, add_months(sysdate,3)) as "날짜2-날짜1",
months_between(sysdate+100, sysdate)
from dual;

 

 

-- trunc(날짜, 포맷) : dd(자정), month(월초), year(년초), day(주초(일))
select trunc(sysdate, 'dd') 자정, -- '일'을 초기화 0시 0분 0초로 초기화
trunc(sysdate, 'month') 월초,  -- '월' 초기화
trunc(sysdate, 'year') 년초,    -- '년도' 초기화
trunc(sysdate, 'day') 일요일
from dual;

-- to_date, to_char
select to_char(sysdate, 'yyyy/mm/dd day, "시각>>" am hh24:mi:ss')
from dual;

-- 이대로 출력해보시오.
-- ex1) 2012.08.21 11:42
select to_char(sysdate, 'yyyy.mm.dd hh:mi')
from dual;

-- ex2) 2012/08/21 234일째
select to_char(sysdate, 'yyyy/mm/dd ddd"일째"')
from dual;

-- ex3) 21세기 12년 08월 21일 오전 11시
select to_char(sysdate, 'cc"세기" yy"년" mm"월" dd"일" am hh"시"')
from dual;

-- fm : 앞에 붙는 0을 삭제
select to_char(sysdate,'mm/dd hh:mi:ss') "0까지 표시",
to_char(sysdate,'fmmm/ddfm hh:mi:ss') "0제거" -- fm 사이에 있는 부분만 0제거
from dual;

-- to_date : 문자열을 -> 날짜타입으로 변경
select to_date('2012/08/01', 'yyyy/mm/dd') + 30 -- 30일 더하기 완료
from dual;

-- '860326'
select to_date('860326', 'rrmmdd') from dual;
select to_char(to_date('860326', 'rrmmdd'),'yyyy/mm/dd') from dual;

-- to_char, to_number
select to_char(5000.123,'9,999.0000') "1",
to_char(50.55,'99.0') "2", -- 자동 반올림 수행
to_char(100000000, 'L999,999,999') "3",
from dual;

select to_number('100,000,000', '999,999,999'),
to_number('1000')
from dual;


create table sawon(
empno number primary key,
ename varchar2(1000),
ssn varchar2(13),
hiredate date
);

insert into sawon values(1000,'짜장민준','8603261234567', '2000/10/01');
insert into sawon values(2000,'심슨광필','0112123234567', '2011/10/01');
insert into sawon values(3000,'귀엽수미','8705122234567', '2000/08/01');
insert into sawon values(4000,'까칠인애','0907254234567', '2011/12/01');
insert into sawon values(5000,'몽골용단','8607235234567', '2012/08/20');
commit;
select * from sawon;

-- 1. 사원들의 사원번호, 사원명, 근무일수, 근무년수 출력
select empno 사원번호,
ename 사원명,
ceil(sysdate - hiredate) 근무일수, 
trunc(ceil(sysdate-hiredate)/365) 근무년수1,
trunc(months_between(sysdate, hiredate)/12) 근무년수2
from sawon;
-- 2. 각 사원의 나이를 출력하세요.
select ename 사원명,
ceil(months_between(sysdate,to_date(substr(ssn,1,2),'rr'))/12)||'살' 나이
from sawon;
-- 3. 80세에 정년퇴임이라고 하면 남은 년수
select ename, 나이, 80 - 나이 남은근무년수
from(
select ename,
ceil(months_between(sysdate,to_date(substr(ssn,1,2),'rr'))/12) 나이
from sawon
);

select ename 사원명,
80-ceil(months_between(sysdate,to_date(substr(ssn,1,2),'rr'))/12) "정년까지"
from sawon;
-- 4. 정년퇴임일 : 80세가 된 해의 입사일 기준
select ename 사원명,
79+to_char(to_date(substr(ssn,1,2),'rr'),'yyyy')
||'/'||
to_char(hiredate,'mm/dd') 정년퇴임일
from sawon;

-- null 처리 함수
-- nvl(expr1, expr2) : expr1이 널이면 expr2를 반환
select first_name, salary*nvl(commission_pct,0) 보너스 from employees;

-- nvl2(expr1, expr2, expr3) : expr1 이 null이면 expr3 반환, 아니면 expr2반환
select nvl2(null, '널아님', '널임'), nvl2('와우', '굿잡!', '널널널')
from dual;

-- nullif(expr1, expr2) : expr1 과 expr2 가 같으면 null, 다르면 expr1을 반환
-- CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END 이것과 같은 동작
select nullif(3,3), nullif(3,4) from dual;

-- coalesce(가변형인자) : null이 아닐때까지 진행
select coalesce(null,null,null,'널아님',null,'우우~')
from dual;

-- case when then
-- decode(값, 비교값1, 결과값1, 비교값2, 결과값2, ... else값)
select decode('1', '0', '0이네', '1', '1이네', '아무것도 아니네')
from dual;

-- ex) decode를 써서 사원들의 성별을 출력하세요(남성, 여성, 귀화)
select ename 이름,
decode(substr(ssn,7,1),'1','남성','2','여성','3','남성','4','여성','귀화') 분류
from sawon;

-- case when then else end
select case '1' when '1' then '1이네'
                when '2' then '2이네'
                else '아무것도 아니네'
      end as 결과값
from dual;  -- decode와 같은 동작, 보통 decode로 많이 씀

select case when '1' = '1' then '1이네'
            when '2' = '2' then '2이네'
            else '아무것도~'
      end 결과값2
from dual;

-- ex2) case를 써서 사원들의 성별을 출력하세요(남성, 여성, 귀화)
select ename 이름,
case when substr(ssn,7,1) in('1','3') then '남성'
      when substr(ssn,7,1) in('2','4') then '여성'
      else '귀화한국인'
end 분류
from sawon;

-- ex1) 근무년수, 40년 근속 달성 날짜를 출력하시오
select hire_date 근무년수,
add_months(hire_date,39*12) "40년 근속일"
from employees;

-- ex2) 월급 10000달러 이상은 40% 세금, 5000달러 이상은 30% 세금,
--      그 외는 5% 세금이라고 했을때 이름과 세금, 실수령액을 출력하시오(employees 대상)
select 이름, 세금, salary-세금 실수령액
from(
select first_name 이름, salary,
case when salary >= 10000 then salary*0.4 
     when salary < 10000 and salary >= 5000 then salary*0.3 
     else salary*0.05
end 세금
from employees
);