Big Data展
Oracle을 활용한 SQL 입문 # 3. 날짜함수, 형변환함수, 조건문
빅브로오
2020. 4. 2. 18:53
날짜함수 이어서
4. next_day함수
바로 다음의 요일 날짜를 알려주는 함수
1 : 일요일, 2: 월요일 ... 7 : 토요일
select next_day(sysdate, 4)
from dual;
-- 다음주 수요일인 2020-04-08 출력5. last_day(날짜) : 해당 날짜가 속한 '월'의 마지막 날짜
select last_day(sysdate)
from dual;
--2020-04-30 출력6. round(날짜, 단위) : 시간 단위 반올림
7. trunc(날짜, 단위) : 시간 단위 버림
select sysdate,
round(sysdate), --일단위
round(sysdate, 'month'), --월단위
trunc(sysdate, 'month'), --월단위 버림
round(sysdate, 'year'),
trunc(sysdate, 'year')
from dual;형변환 함수 : 데이터 타입을 변경(중요)
1. to_char : 숫자와 날짜를 문자로 변경
1-1) 숫자를 문자로 변경
select to_char(123456789,'$999,999,999.99'),
to_char(1234,'99,999'), -- 9 : 빈자리수 비움
to_char(1234, '09,999') -- 0 : 빈자리수 0으로 출력
from dual;1-2) 날짜를 문자로 변경
alter session set nls_date_language = 'american';
select sysdate,
substr(sysdate,1,4),
--시스템에 따라 다르므로 년도나 월을 추출하기에 좋은 방법은 아님
--YY/MM/DD로 저장된 데이터의 경우 YY/M 까지만 나오기 때문
to_char(sysdate, 'YYYY'), -- 리턴된 모든 결과 타입은 문자임
to_char(sysdate, 'Month'), -- 시스템 언어로 된 국/영문 월 출력, 대소문자는 출력 양식을 구분
to_char(sysdate, 'Day'), -- 요일 출력
to_char(sysdate, 'ddth'), --일을 서수 형식으로 출력
to_char(sysdate, 'ddspth'), -- 영문 서수 형식으로 출력
to_char(sysdate, 'year'), -- 영문년도
to_char(sysdate, 'Q') -- 분기
from dual;2. to_number : 문자를 숫자로 변경
select '1'+1 -- sql db는 앞의 1의 경우 묵시적 형변환을 해줌 : db만의 특징
from dual; -- 그러나 성능상 좋지 않다.
-- 문자면 문자 숫자면 숫자 착오 없이 작성해주는 것이 좋다.
-- to_number를 통해 형변환 해주는 것이 좋다.3. to_date : 문자와 숫자를 날짜로 변경
select '2020-02-05' + 100
from dual; -- 연산 안됨. 문자열로 인식, 날짜로는 인식하지 않음
select to_date('2020-02-05') + 100
from dual; -- 날짜로 인식 ok
select to_date('02-05-2020', 'MM-DD-YYYY')
from dual; -- 형식을 알려줘야 하는 경우
-- 그러나 변경의 목적이 아닌 해석의 목적이다!
select to_date('89-12-05'), --Y2K 이전으로 표기
to_date('89-12-05', 'YY-MM-DD'), -- 지정시 Y2K 이후
to_date('89-12-05', 'RR-MM-DD') -- 지정시 Y2K 이전
from dual;연습문제) student 테이블에서 jumin 칼럼을 사용하여 학생의 생년월일의 그 다음주 일요일의 날짜 출력
select name,
birthday,
next_day(to_date(substr(jumin,1,6),'RRMMDD'), 1) 다음주일요일
from student;문제1) EMP 테이블에서 현재까지 근무일수가 몇 주, 몇 일인가를 출력하여라. (단, 근무일수가 많은 사람 순으로 출력하여라)
select ename , trunc((sysdate - hiredate) / 7) || '주' as 주, trunc(mod(sysdate- hiredate, 7)) || '일'
from EMP
order by sysdate - hiredate desc; 문제2) emp 테이블에서 입사한 달의 남은 근무 일수를 계산하여 출력하여라.
select ename, last_day(hiredate) - hiredate 남은근무일수
from emp;실습문제 1)
EMP 테이블에서 10번 부서원의 입사 일자를
'1 MAY 1981'와 '1998년 01월 01일' 의 형태로
각각 출력하여라.
select ename, deptno, ltrim(to_char(hiredate, 'DD MON RRRR'), '0'),
to_char(hiredate, 'RRRR') || '년 ' || to_char(hiredate, 'MM') || '월 '
|| to_char(hiredate, 'DD') || '일 '
from EMP
where deptno = 10;
select to_char(hiredate, 'YYYY"년 MM"월" DD"일"')
from EMP
where deptno = 10;실습문제 2)
emp 테이블에서 이름, 부서번호, 연봉을 출력
단 연봉은 '$3,000'의 형태로 출력
select ename, deptno, to_char(sal, '$9,999')
from EMP
where deptno = 20;실습문제 3)
february 22, 1981 에 입사한 직원의 이름, 직무, 입사일을 출력
입사일은 위와 동일 형식으로 출력
tip 날짜를 문자로 변경해서 해결하는 것 보다
문자를 날짜로 변경해서 해결하는 게 성능상 훨씬 좋다.\
select ename, job, hiredate, to_char(hiredate, 'month dd, RRRR')
from emp
where hiredate = to_date('february 22, 1981', 'month dd, rrrr'); -- 문자를 날짜로 변경해서 해결하는 법
-- 칼럼을 가공하는 것 보다 칼럼과의 비교대상을 가공하는게 성능상 훨씬 좋다예제 10% 인상된 sal이 3000이상인 직원 출력\
성능보다 문법 그대로 짠 쿼리
select *
from EMP
where sal*1.1 >= 1000;성능을 우선한 쿼리
select *
from EMP
where sal >= 1000/1.1; -- 컬럼에는 변형을 일으키지 않는다!일반함수
1. nvl(데이터, 널치환값) : null 치환함수
select ename, sal, nvl(comm, 0), sal + comm, sal + nvl(comm, 0)
from emp;select comm, nvl(comm, 'comm이 없다') -- 잘못 된 치환. comm은 num type의 컬럼이기 때문에 컬럼타입과 일치하는 type의 데이터로 치환
from emp;select hpage, nvl(hpage,0)
from professor; -- 이건 오케이. 문자컬럼에 숫자는 가능
-- 숫자 컬럼에 문자는 위 comm에서 언급했듯 불가능2. nvl2(데이터, '널이 아닐 때 치환', '널일 때 치환')
예제) emp 테이블의 직원의 보너스를 부여하려 한다.
보너스가 null인 경우는 300, null이 아닌 경우는 10% 인상.
select comm, nvl(comm, 300), nvl2(comm, comm*1.1, 300)
from emp;연습문제) student 테이블에서 학생의 학과 번호를 출력
학과번호는 제 2 전공번호로 출력하되, 없으면 제 1전공번호로 출력
select studno, name, deptno1, deptno2, nvl2(deptno2, deptno2, deptno1)
from student;SELECT EMPNO, ENAME, CASE WHEN comm IS NULL THEN comm+300
ELSE 1.1*comm END As comm
FROM EMP; -- case문을 활용해도 같은 기능을 만들 수 있다.select empno, ename, nvl2(comm, comm*1.1, 300),
nvl2(comm, 'comm이 있음.', 'comm이 없음'),
-- nvl2는 nvl과 달리 첫번째 항의 데이터 타입과 일치 시켜줄 필요가 없다.
-- 다만, 두번째 항과 세번째 항의 데이터 타입은 같아야 한다.
nvl2(comm, 'comm이 있음', 0),
-- 가능, 두번째 항이 기준이 됨. 문자 --> 숫자는 가능하므로 뒤 0을 문자로 자동 변환
nvl2(comm, 0, 'comm이 없음') -- 불가능 숫자에 문자는 못들어가므로
from emp;조건문
1. decode 오라클에서 조건문을 대신 할 수 있는 함수 (if는 없음)
decode( 검사대상, 조건1, 치환1, 치환2(불일치하는 경우))
예제) job이 clerk인 경우는 직원으로 치환
아닌 경우에는 사원으로 치환
select decode (job, 'CLERK', '직원','사원')
from emp;예제) job이 clerk인 경우는 직원으로 치환
아닌 경우에는 변경없음
select decode(job, 'CLERK', '직원', job)
from emp;
-- 치환2는 생략할 수 있고, 생략되었을 경우에는 null이 자동으로 들어간다.select empno, ename, decode(comm, null, nvl(comm,300), comm *1.1)
from emp;
-- decode 문 내부에 다른 함수 적용 가능decode함수는 조건과 치환값이 무한대로 들어갈 수 있다.
마지막에 모든 조건에 해당되지 않을 때 들어갈 치환값만 정해주면 된다
select empno, ename, comm, decode(comm, null, 300,
0, 300, comm * 1.1) 익년comm
from emp;예제) CLERK일 때는 A, SALESMAN일 때는 B, Manager일 때는 C, 그 외에는 D 출력
select ename, job, decode(job, 'CLERK', 'A',
'SALESMAN', 'B',
'MANAGER', 'C', 'D')
from emp; 실습문제1) EMP 테이블에서 부서번호가 10이면 ‘A’로, 20이면 ‘B’로, 30이면‘C’로 바꾼 뒤 \
이름, 업무, 부서번호와 함께 출력하여라.
select decode(deptno, 10, 'A'
, 20, 'B',
30, 'C')as 부서코드,
ename,
job, deptno
from EMP;문제2) EMP 테이블에서 JOB이 ANALYST이면 급여 증가는 10%이고,
CLERK이면 15%, MANAGER이면 20%이고, 다른 업무에 대해서는
급여 증가가 없다. 사원번호, 이름, 업무, 급여, 증가된 급여를 출력하여라
select empno, ename, job, sal, decode(job, 'ANALYST', sal * 1.1
, 'CLERK', sal * 1.15
, 'MANAGER', sal * 1.2, sal) 급여변동
from emp;문제3) EMP 테이블의 사원이름, 매니저번호(MGR)를 출력하고, 매니저번호가
--null이면 ‘상위관리자’로 표시하고, 매니저번호가 있으면 ‘7869담당‘으로
--표시하여라.
select ename, mgr,
decode(mgr, null, '상위관리자', mgr ||'담당 ')
from emp;
select ename, mgr, nvl2(mgr, mgr||'담당 ', '상위관리자 ')
from emp;
select ename, case when mgr is null then '상위관리자'
else mgr||'담당 ' end
from emp;3일차 실습
- professor 테이블에서 각 교수의 이메일 아이디를
출력하되, 특수기호를 제거한 형태로 출력하여라.select name, translate(substr(email,1, instr(email, '@')-1),'!@#$%^&*()_-+=',' ') from professor; - STUDENT 테이블을 사용하여 주민번호, 생년월일
각각을 사용하여 각 학생의 태어난 날의 요일을 출력select name, to_char(to_date(substr(jumin,1,6), 'RRMMDD'), 'DAY') 요일by주민, to_char(birthday, 'DAY') 요일by생년월일 from student; - EMP 테이블을 이용하여 사원이름, 입사일 및
급여검토일을 표시합니다.
급여검토일은 여섯달 근무후 해당되는 첫번째 월요일
날짜는 "Sunday the Seventh of September, 1981"
형식으로 표시. 열 이름은 check로 한다.alter session set nls_date_language = 'american'; select ename, hiredate, to_char(next_day(add_months(hiredate, 6), 2), 'Day"the" ddspth" of" Month, YYYY') as "check" from emp; - student 테이블에서 jumin 컬럼을 사용하여
생년월일이 다음 사이인 학생의 정보를 출력하세요.
(19760205 ~ 19760924)select * from STUDENT where jumin between to_char(to_date('19760205','YYYYMMDD'), 'YYMMDD"0000000"') and to_char(to_date('19760924', 'YYYYMMDD'), 'YYMMDD"9999999"');select * from STUDENT where jumin between rpad(19760205-19000000, 13, 0) and rpad(19760924-19000000, 13, 9); - emp 테이블을 이용하여 현재까지 근무일수를
XX년 XX개월 XX일 형태로 출력하세요.select ename, trunc((sysdate - hiredate) / 365) || '년 ' ||trunc(mod(months_between(sysdate, hiredate),12)) || '개월 ' ||trunc(sysdate - add_months(hiredate, months_between(sysdate, hiredate))) || '일' from emp;