오라클 예제 - 날짜관련 함수 및 to_char 사용 예제

오라클 예제 - 날짜관련 함수 및 to char 사용 예제

날짜와 관련한 함수 : 

month_between(날짜1, 날짜2);

두 날짜사이에 개월수를 알려주는 함수

날짜1 - 날짜2 개월수를 반환

양수가 나오게 하려면 날짜1이 최근(큰값)이여야 한다

자신이 몇개월 살았는지 계산해보기

select months_between(sysdate,'1991-11-28') from dual;

소수점 없애기

select round(months_between(sysdate,'1991-11-28')) from dual;

연습)

근무개월수가 60개월 이상이고 남자 근무자들 중에

서울에 거주하는 사원들에게 상여금을 지급하고자 한다.

상여금은 연봉의 5%이다.

대상자의 사원번호, 사원명, 부서명, 상여금, 근무개월수, 입사일을 출력합니다.

단, 입사일순으로 출력하고 동일할 경우 이름순으로 출력합니다.

select no, name, dname, nvl(sal,0)*0.05 bonus, 

round(months_between(sysdate, hiredate)) 근무개월수, 

hiredate

from emp

where round(months_between(sysdate,hiredate)) >= 60

and substr(jumin, 8,1) in (1,3,5,7)

and addr like '%서울%'

order by hiredate asc, name asc;

만약에 급여가 null 이라면 평균급여를 설정하고자 한다.

// 0 자리에 평균급여를 구하는 질의문을 사용한다.

select avg(sal) from emp;

select no, name, dname, (nvl(sal,(select avg(sal) from emp))*12)*0.05 bonus, 

round(months_between(sysdate, hiredate)) 근무개월수, 

hiredate

from emp

where round(months_between(sysdate,hiredate)) >= 60

and substr(jumin, 8,1) in (1,3,5,7)

and addr like '%서울%'

order by hiredate asc, name asc;

add_months(날짜, 개월수);

예)

add_months(sysdate, 6);

select add_months(sysdate, 6) from dual;  // 결과값 : 16/09/10

밑에처럼 날짜에는 연산도 가능하다.

select sysdate +1 from dual; 내일

select sysdate - 1 from dual; 어제

select sysdate -1 , sysdate, sysdate +1 from dual;

select sysdate -1 어제, sysdate 오늘, sysdate +1 내일 from dual;

to_char

날짜를 문자열로 변환하는 함수

형식)

to_char(날짜, '형식문자')

예)

select to_char(sysdate, 'yyyy') from dual;

연습)

개발부의 사원들은  입사후 6개월 이내에 개발능력평가를 통과해야 한다.

각 사원별 개발능력평가 만료일을 출력합니다.

사원번호, 사원명, 부서명, 입사일, 평가만료일을 출력하시오.

평가만료일 순으로 출력하되, 동일할 경우 사원명순으로 출력합니다.

select no, name, dname, hiredate, add_months(hiredate, 6) 평가만료일

from emp

where dname like '%개발%'

order by add_months(hiredate, 6) < sysdate , name;

** 실습을 위하여 emp 테이블에 평가여부를 저장할 항목(필드)를 추가하고자 한다.

평가를 했으면 1

평가를 안했으면 0 을 넣으려고 한다.

칼럼명 자료형

pass number 

이미 있는 테이블에 칼럼을 추가하기 위한 명령

alter table 테이블명 add 칼럼이름 자료형

alter table emp add pass number

여기까지하면 추가된 칼럼에 데이터가 없는 상황

** 개발부의 모든 근무자들의 pass를 1로 변경합니다.

데이터를 변경하는 명령어의 형식

update 테이블명 set 칼럼명 = 값, 칼럼명 = 값,..... where 조건식;

update emp set pass = 1 where dname like '%개발%';

** 사원번호가 개발부에 근무하고, 사원번호가 120 이상인 사원들의 pass를 0 으로 설정한다.

update emp set pass = 0 where dname like '%개발%' and no >= 120 ;

** 개발부에 근무하고, 사원번호가 112 이상인 사원들의 pass를 0으로 설정한다.

update emp set pass = 0 where dname like '%개발%' and no >= 112;

연습)

개발부의 사원들은 입사후 6개월 이내에 개발능력평가를 통과해야 합니다.

평가일이 지났으나 평가를 치르지 않은 근로자의 정보를 출력합니다.

사원번호, 사원명, 평가만료일, 입사일, 부서명을 출력합니다.

select no, name, round(add_months(hiredate, 6)) 평가만료일, hiredate, dname

from emp

where dname like '%개발%'

and pass = 0;

** to_char 연습문제

근무개월수가 3개월이상이고 개발부에 근무하는 모든근로자의

이름, 이메일, 근무개월수, 입사일을 출력하시오

단 입사일은 다음과 같이 출력합니다. 2015/02/15 월

select name, email, months_between(sysdate,hiredate) month, 

to_char(hiredate, 'yyyy/mm/dd dy') hiredate

from emp 

where months_between(sysdate,hiredate) >= 3

and dname like '%개발%';

** to_char 연습문제2

2010년 이전에 입사한 개발부에 근무하는 근로자들에게 재교육을 실시하고자 한다.

대상자의 정보를 출력하시오.

사원번호, 사원이름, 입사일, 이메일을 출력합니다.

select no, name, hiredate, email

from emp

where to_char(hiredate,'yyyy') <= 2010

and dname like '%개발%';