Notice
Recent Posts
Recent Comments
Link
Everything has an expiration date
★ [Oracle] 20231020 [프로그램소스 - 수료일까지 남은 연, 월, 일 해설 (전체시간 초로 변경)] 본문
[Oracle]/SQL (Program source)
★ [Oracle] 20231020 [프로그램소스 - 수료일까지 남은 연, 월, 일 해설 (전체시간 초로 변경)]
Jelly-fish 2023. 10. 23. 17:48
전체 시간을 초로 변환하여, 남은 기간의 일, 시, 분, 초 구하기(MOD 함수 사용)
날짜 반올림·절삭(ROUND, TRUNC), 날짜(DATE)타입 변환 TO_CHAR() 형
[숫자 연산 함수]
Ⅰ. `MOD(대상 값, 대상값을 나눌 값)`
대상 값에서 두 번째 매개변수로 나눠진 나머지 값을 반환하는 함수
Ⅱ. `ROUND(대상 값, 반올림할 자릿수 값)`
대상 값을 두 번째 매개변수 값에 따라 반올림할 자릿수를 결정
ⅰ 『반올림할 자릿수 값』 이 3 일 경우 : 소수점 아래 3자리까지 표현.
- `ROUND(1.2345, 3)` → 【1.234】
ⅱ 『반올림할 자릿수 값』 이 -3 일 경우 : 십의 자리에서 반올림을 실행.
- `ROUND(90843, -2)` → 【91000】
ⓐ 대상 값이 실수형일 때
SELECT ROUND(3.1415926535897932384626, 2)
FROM DUAL;
--==>> 3.14- 대상 값 : 3.1415926535897932384626
- 반올림 할 값 : 2 (소수점 아래 두 자리에서 반올림)
ⓑ 대상 값이 정수형일 때
SELECT ROUND(108090, -4)
FROM DUAL;
--==>> 110000- 대상 값 : 108090
- 반올림 할 값 : -4 (천의 자리 수에서 반올림 → 만의 자리로 올라감)
--○ 날짜 반올림
`ROUND(DATE, '반올림기준')`
SELECT SYSDATE "COL1" -- 2023-10-23 → 기본 현재 날짜
, ROUND(SYSDATE, 'YEAR') "COL2" -- 2024-01-01 → 연도까지 유효한 데이터 (상반기 / 하반기 기준)
, ROUND(SYSDATE, 'MONTH') "COL3" -- 2023-11-01 → 월까지 유효한 데이터 (15일 기준)
, ROUND(SYSDATE, 'DD') "COL4" -- 2023-10-23 → 일까지 유효한 데이터 (정오 기준)
, ROUND(SYSDATE, 'DAY') "COL5" -- 2023-10-22 → 일까지 유효한 데이터 (수요일 정오 기준)
-- 월, 화, 수 = 지난 주 일요일
-- 목, 금, 토 = 이번 주 일요일
FROM DUAL;
--○ 날짜 절삭
`TRUNC(DATE, '절삭기준')`
SELECT SYSDATE "COL1" -- 2023-10-23 → 기본 현재 날짜
, TRUNC(SYSDATE, 'YEAR') "COL2" -- 2023-01-01 → 연도까지 유효한 데이터 (상반기 / 하반기 기준)
, TRUNC(SYSDATE, 'MONTH') "COL3" -- 2023-10-01 → 월까지 유효한 데이터
, TRUNC(SYSDATE, 'DD') "COL4" -- 2023-10-23 → 일까지 유효한 데이터
, TRUNC(SYSDATE, 'DAY') "COL5" -- 2023-10-22 → 그 전 주에 해당하는 일요일
FROM DUAL;
[변환 함수]
Ⅰ `TO_CHAR()`
숫자나 날짜 데이터를 문자 타입으로 변환시켜주는 함수
Ⅱ `TO_DATE()`
문자 데이터를 날짜 타입으로 변환시켜주는 함수
Ⅲ `TO_NUMBER()`
문자 데이터를 숫자 타입으로 변환시켜주는 함수
[TO_CHAR(SYSDATE, '표현형식') 정리]
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "COL1" -- 2023-10-23
, TO_CHAR(SYSDATE, 'YYYY') "COL2" -- 2023 (문자 타입. 숫자로 보이지만 문자타입이다.)
, TO_CHAR(SYSDATE, 'YEAR') "COL3" -- TWENTY TWENTY-THREE -- 언어가 'KOREAN'으로 되어 있어도 실행 결과가 이와 같다.
, TO_CHAR(SYSDATE, 'MM') "COL4" -- 10 (문자 타입. TO_CHAR를 거쳤으므로 모든 실행결과는 문자타입이다.)
, TO_CHAR(SYSDATE, 'MONTH') "COL5" -- 10월
, TO_CHAR(SYSDATE, 'MON') "COL6" -- 10월
, TO_CHAR(SYSDATE, 'DD') "COL7" -- 23
, TO_CHAR(SYSDATE, 'MM-DD') "COL8" -- 10-23
, TO_CHAR(SYSDATE, 'DAY') "COL9" -- 월요일
, TO_CHAR(SYSDATE, 'DY') "COL10" -- 월
, TO_CHAR(SYSDATE, 'HH24') "COL11" -- 10 (24시간제로 현재 시간을 표현하는 키워드이다.)
, TO_CHAR(SYSDATE, 'HH') "COL12" -- 10
, TO_CHAR(SYSDATE, 'HH AM') "COL13" -- 10 오전 (꼭 AM은 오전으로 조회되는 것이 아니라, 현재가 오전이면 오전, 오후면 오후로 조회된다.)
, TO_CHAR(SYSDATE, 'HH PM') "COL14" -- 10 오전 (꼭 PM은 오후로 조회되는 것이 아니라, ...)
, TO_CHAR(SYSDATE, 'MI') "COL15" -- 30
, TO_CHAR(SYSDATE, 'SS') "COL16" -- 58
, TO_CHAR(SYSDATE, 'SSSSS') "COL17" -- 37875 (00시부터 흘러온 전체 초)
, TO_CHAR(SYSDATE, 'Q') "COL18" -- 4 (쿼터, 1분기, 2분기, 3분기, 4분기)
FROM DUAL;
- 문자 타입은 좌측 정렬
- 숫자 타입은 우측 정렬이 된다.
SELECT 10 "COL1" -- [숫자 타입] 오른쪽으로 정렬되어 있는 것을 확인할 수 있다.
, '10' "COL2" -- [문자 타입] 왼쪽으로 정렬되어 있는 것을 확인할 수 있다.
FROM DUAL;
20231023_01_scott.sql
SELECT USER
FROM DUAL;
--==>> SCOTT
--※ 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
--○ 현재 날짜 및 시간으로부터...
-- 수료일(2024-03-19 18:00:00) 까지
-- 남은 기간을... 다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다.
/*
-------------------- ---------------------- -------- ------- -------- --------
현재시간 수료일 일 시간 분 초
-------------------- ---------------------- -------- ------- -------- --------
2023-10-20 17:09:10 2024-03-19 18:00:00 140 0 41 50
*/
-- 『1일 2시간 3분 4초』를... 『초』로 환산하면...
SELECT (24 * 60 * 60) + (2 * 60 * 60) + (3 * 60) + 4
FROM DUAL;
SELECT (1일) + (2시간) + (3분) + (4초)
FROM DUAL;
SELECT (1*24*60*60) + (2*60*60) + (3*60) + (4)
FROM DUAL;
-- [전체 초 계산]
--==>> 93784
-- 『93784초』를... 다시 『일, 시간, 분, 초』로 환산하면...
SELECT TRUNC(93784 / (24*60*60)) "일"
TRUNC (
FROM DUAL;
-- 93784 / 60 의 몫을 취한 후에 그것을 다시 60으로 나머지 연산 : 분
-- 93784 / 60 의 몫을 취한 후, 그것을 다시 60으로 나누어 몫을 취한 후에 24로 나머지 연산 : 시간
-- 93784 / 60 의 몫을 취한후, 그것을 다시 60으로 나누어 몫을 취한 후 다시 24로 나누어 몫을 취하면 : 일
SELECT TRUNC (TRUNC(TRUNC(93784 / 60) / 60) / 24) "일"
, MOD(TRUNC(TRUNC(93784 / 60) / 60), 24) "시간"
, MOD(TRUNC(93784 / 60), 60) "분"
, MOD(93784, 60) "초"
FROM DUAL;
--==>> 1 2 3 4
-- 전체 초를 60으로 나눈 몫(분) 을 다시 60으로 나누어 몫으로 만든다.(시간) 이것을 다시 24로 나누어 몫을 취하면 그것은 바로 '일(DAY)'이 된다.
-- 전체 초를 60으로 나눈 몫을 다시 60으로 나누면 시간이 된다. 이것을 다시 24로 나머지 연산하여 잔여 시간만 남김. 24 미만의 시간.)
-- 전체 초를 60으로 나눈 몫을 60으로 나머지 연산 (전체 초를 60으로 나눈 몫 = 분, 이것을 다시 60으로 나머지 연산 하여 잔여 분만 남김. 60 미만의 분)
-- 전체 초
-- 오로지 몫만을 취하겠다고 하면, 전체 초가 전부 분으로 바뀌어서 나오기 때문에 나머지 연산을 추가적으로 해 준 것이다.
-- 전체 분을 60으로 나눠서 나머지를 취하겠다고 하면 시간으로 편입되지 못하는 나머지 분들만 분으로 표현하기 위한 것이다.
-- 수료일까지 남은 기간 확인 (단위 : 초)
SELECT 남은일수 * (24*60*60)
FROM DUAL;
SELECT (수료일 - 현재일) * (24*60*60)
FROM DUAL;
SELECT TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE
FROM DUAL;
--==>> 148.35017
SELECT TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)) "남은 일수 총 초"
FROM DUAL;
SELECT SYSDATE "현재 시간"
, TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') "수료일"
, TRUNC (TRUNC(TRUNC(TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)) / 60) / 60) / 24) "일"
, MOD(TRUNC(TRUNC(TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)) / 60) / 60), 24) "시간"
, MOD(TRUNC(TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)) / 60), 60) "분"
, MOD(TRUNC((TO_DATE('2024-03-19 18:00:00', 'YYYY-MM-DD HH24:MI:SS') - SYSDATE) * (24*60*60)), 60) "초"
FROM DUAL;
/*
-------------------- -------------------- -------- -------- ---------- ---------
현재 시간 수료일 일 시간 분 초
-------------------- -------------------- -------- -------- ---------- ---------
2023-10-23 09:49:00 2024-03-19 18:00:00 148 8 8 6
-------------------- -------------------- -------- -------- ---------- ---------
*/
--○ 각자 태어난 날짜 및 시각으로부터... 현재까지
-- 얼마만큼의 시간을 살고 있는지...
-- 다음과 같은 형태로 조회할 수 있도록 쿼리문을 구성한다.
/*
-------------------- -------------------- -------- -------- ---------- ---------
현재 시간 생년월일 일 시간 분 초
-------------------- -------------------- -------- -------- ---------- ---------
2023-10-23 09:49:00 2000-10-16 00:00:00 8407 10 4 46
-------------------- -------------------- -------- -------- ---------- ---------
*/
-- 내가 현재까지 살아 온 총 초
SELECT TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60))
FROM DUAL;
SELECT SYSDATE "현재시간"
, TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') "생년월일"
, TRUNC (TRUNC(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60) / 60) / 24) "일"
, MOD(TRUNC(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60) / 60), 24) "시간"
, MOD(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60), 60) "분"
, MOD(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)), 60) "초"
FROM DUAL;
--내 풀이 기반으로 다시 풀이-----------------------------------------------------------------------------------------------------
SELECT SYSDATE "현재 시간"
, TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS') "생년월일"
, TRUNC(SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) "일"
, TRUNC(MOD((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24, 24)) "시간"
, TRUNC(MOD((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60, 60)) "분"
, TRUNC(MOD((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60, 60)) "초"
FROM DUAL;
--------------------------------------------------------------------------------------------------------------------------------
-- 처음부터 다시해 본 결과.
SELECT TRUNC(TRUNC(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60) / 60) / 24) "일"
, MOD(TRUNC(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60) / 60), 24) "시간"
, MOD(TRUNC(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)) / 60), 60) "분"
, MOD(TRUNC((SYSDATE - TO_DATE('2000-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * (24*60*60)), 60) "초"
FROM DUAL;
-- 1. 전체 시간을 초로 바꾸어 60으로 나머지 연산한 값이 남은 초이다.
-- 2. 전체 시간을 초로 바꾸어 60으로 나눈 몫이 곧 분이다. 이 분은 전체 시간에 대한 분이므로 다시 60으로 나머지 연산한 값이 남은 분이다.
-- 3. 전체 시간을 초로 바꾸어 60으로 나눈 몫을 다시 60으로 나눈 몫이 곧 시간이다. 이것은 전체 시간을 시간으로 표현한 것이므로, 24로 나머지 연산한 값이 남은 시간이다.
-- 4. 전체 시간을 초로 바꾸어 60으로 나눈 몫을 다시 60으로 나눈 몫이 곧 시간이므로 이것을 24로 나누어 구한 몫이 곧 일이 된다.
--○ 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.
--※ 날짜 데이터를 대상으로 반올림, 절삭 등의 연산을 수행할 수 있다.
--○ 날짜 반올림
SELECT SYSDATE "COL1" -- 2023-10-23 → 기본 현재 날짜
, ROUND(SYSDATE, 'YEAR') "COL2" -- 2024-01-01 → 연도까지 유효한 데이터 (상반기 / 하반기 기준)
, ROUND(SYSDATE, 'MONTH') "COL3" -- 2023-11-01 → 월까지 유효한 데이터 (15일 기준)
, ROUND(SYSDATE, 'DD') "COL4" -- 2023-10-23 → 일까지 유효한 데이터 (정오 기준)
, ROUND(SYSDATE, 'DAY') "COL5" -- 2023-10-22 → 일까지 유효한 데이터 (수요일 정오 기준)
-- 월, 화, 수 = 지난 주 일요일
-- 목, 금, 토 = 이번 주 일요일
FROM DUAL;
--○ 날짜 절삭
SELECT SYSDATE "COL1" -- 2023-10-23 → 기본 현재 날짜
, TRUNC(SYSDATE, 'YEAR') "COL2" -- 2023-01-01 → 연도까지 유효한 데이터 (상반기 / 하반기 기준)
, TRUNC(SYSDATE, 'MONTH') "COL3" -- 2023-10-01 → 월까지 유효한 데이터
, TRUNC(SYSDATE, 'DD') "COL4" -- 2023-10-23 → 일까지 유효한 데이터
, TRUNC(SYSDATE, 'DAY') "COL5" -- 2023-10-22 → 그 전 주에 해당하는 일요일
FROM DUAL;
-------------------------------------------------------------------------------
--■■■ 변환 함수 ■■■--
-- TO_CHAR() : 숫자나 날짜 데이터를 문자 타입으로 변환시켜주는 함수
-- TO_DATE() : 문자 데이터를 날짜 타입으로 변환시켜주는 함수
-- TO_NUMBER() : 문자 데이터를 숫자 타입으로 변환시켜주는 함수
--○ 날짜형 → 문자형
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "COL1" -- 2023-10-23
, TO_CHAR(SYSDATE, 'YYYY') "COL2" -- 2023 (문자 타입. 숫자로 보이지만 문자타입이다.)
, TO_CHAR(SYSDATE, 'YEAR') "COL3" -- TWENTY TWENTY-THREE -- 언어가 'KOREAN'으로 되어 있어도 실행 결과가 이와 같다.
, TO_CHAR(SYSDATE, 'MM') "COL4" -- 10 (문자 타입. TO_CHAR를 거쳤으므로 모든 실행결과는 문자타입이다.)
, TO_CHAR(SYSDATE, 'MONTH') "COL5" -- 10월
, TO_CHAR(SYSDATE, 'MON') "COL6" -- 10월
, TO_CHAR(SYSDATE, 'DD') "COL7" -- 23
, TO_CHAR(SYSDATE, 'MM-DD') "COL8" -- 10-23
, TO_CHAR(SYSDATE, 'DAY') "COL9" -- 월요일
, TO_CHAR(SYSDATE, 'DY') "COL10" -- 월
, TO_CHAR(SYSDATE, 'HH24') "COL11" -- 10 (24시간제로 현재 시간을 표현하는 키워드이다.)
, TO_CHAR(SYSDATE, 'HH') "COL12" -- 10
, TO_CHAR(SYSDATE, 'HH AM') "COL13" -- 10 오전 (꼭 AM은 오전으로 조회되는 것이 아니라, 현재가 오전이면 오전, 오후면 오후로 조회된다.)
, TO_CHAR(SYSDATE, 'HH PM') "COL14" -- 10 오전 (꼭 PM은 오후로 조회되는 것이 아니라, ...)
, TO_CHAR(SYSDATE, 'MI') "COL15" -- 30
, TO_CHAR(SYSDATE, 'SS') "COL16" -- 58
, TO_CHAR(SYSDATE, 'SSSSS') "COL17" -- 37875 (00시부터 흘러온 전체 초)
, TO_CHAR(SYSDATE, 'Q') "COL18" -- 4 (쿼터, 1분기, 2분기, 3분기, 4분기)
FROM DUAL;
-- 문자 타입은 좌측 정렬
-- 숫자 타입은 우측 정렬이 된다.
-- 표를 좌우로 길게 늘어뜨려 봤을 때, 아래의 실행 결과는 모두 문자 타입이므로
-- 좌측 정렬되는 것을 확인할 수 있다.
SELECT 10 "COL1" -- [숫자 타입] 오른쪽으로 정렬되어 있는 것을 확인할 수 있다.
, '10' "COL2" -- [문자 타입] 왼쪽으로 정렬되어 있는 것을 확인할 수 있다.
FROM DUAL;
--※ 날짜나 통화 형식이 맞지 않을 경우...
-- 설정 값을 통해 세션을 설정할 수 있다.
-- LANGUAGE에 대한 세션 변경
ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
-- 역 슬러시가 아니라, 원 표시이다.
-- 통화 기호를 원으로 표시하는 구문이다. ($, ￦, …)
ALTER SESSION SET NLS_CURRENCY = '\'; -- ￦
-- 연월일에 대한 세션 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--○ 숫자형 → 문자형
SELECT 7 "COL1"
, '7' "COL2"
, TO_CHAR(7) "COL3"
FROM DUAL;
--==>> 7 7 7
--> 조회 결과가 좌측 정렬인지 우측 정렬인지 확인~!!!
SELECT '4' "COL1"
, TO_NUMBER('4') "COL2"
, 4 "COL3"
, TO_CHAR(4) "COL4"
, TO_NUMBER('04') "COL5"
FROM DUAL;
--==>> 4 4 4 4 4
-- 문자(좌), 숫자(우), 숫자(우), 문자(좌), 숫자(우)
--> 조회 결과가 좌측 정렬인지 우측 정렬인지 확인~!!!
--○ 현재 날짜에서 현재 년도(2023)를 숫자 형태로 조회(반환)
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) "현재 년도"
FROM DUAL;
--○ EXTRACT()
SELECT TO_CHAR(SYSDATE, 'YYYY') "COL1" -- 2023 → 연도를 추출하여 문자 타입으로...
, TO_CHAR(SYSDATE, 'MM') "COL2" -- 10 → 월을 추출하여 문자 타입으로...
, TO_CHAR(SYSDATE, 'DD') "COL3" -- 23 → 일을 추출하여 문자 타입으로...
, EXTRACT(YEAR FROM SYSDATE) "COL4" -- 2023 → 연도를 추출하여 숫자 타입으로...
, EXTRACT(MONTH FROM SYSDATE) "COL5" -- 10 → 월을 추출하여 숫자 타입으로...
, EXTRACT(DAY FROM SYSDATE) "COL6" -- 23 → 일을 추출하여 문자 타입으로...
FROM DUAL;
--==>> 2023 10 23 2023 10 23
--> 년, 월, 일 이외의 다른 항목은 불가~!!!
--○ TO_CHAR() 활용 → 형식 맞춤 표기 결과값 반환
SELECT 60000 "COL1"
, TO_CHAR(60000, '99,999') "COL2"
, TO_CHAR(60000, '$99,999') "COL3"
, TO_CHAR(60000, 'L99,999') "COL4"
, LTRIM(TO_CHAR(60000, 'L99,999')) "COL5" -- 왼쪽 공백을 제거한 후에 출력
FROM DUAL;
--○ 날짜 관련 세션 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
--○ 현재 시간을 기준으로 1일 2시간 3분 4초 후를 조회한다.
SELECT SYSDATE "오늘 날짜"
, SYSDATE + 1 + (2 / 24) + (3 / (24*60)) + (4 / (24*60*60)) "1일 2시간 3분 4초 후"
FROM DUAL;
SELECT SYSDATE "현재 시간"
, SYSDATE + (1) + (2/24) + (3/(24*60)) + (4/(24*60*60)) "1일2시간3분4초후"
FROM DUAL;
--==>> 2023-10-23 11:18:31
-- 2023-10-24 13:21:35
--○ 현재 시간을 기준으로 1년 2개월 3일 4시간 5분 6초 후를 조회한다.
-- TO_YMINTERVAL() : 연 월의 인터벌
-- TO_DSINTERVAL() : 일 시간 분 초의 인터벌
SELECT SYSDATE "현재 시간"
-- 1년 2개월 이후
, SYSDATE + TO_YMINTERVAL('01-02') + TO_DSINTERVAL('003 04:05:06') "연산 결과"
FROM DUAL;
--==>> 2023-10-23 11:22:26
-- 2024-12-26 15:27:32
--------------------------------------------------------------------------------
--○ CASE 구문(조건문, 분기문)
/*
CASE
WHEN
THEN
ELSE
END
*/
-- CASE : 어떤 상황이 벌어졌을 때...
-- WHEN : 이 상황이 ~할 때
SELECT CASE 5+2 WHEN 4 THEN '5+2=4'
ELSE '5+2는 몰라요'
END AS "5+2는 뭐예요"
FROM DUAL;
--==>> 5+2는 몰라요
SELECT CASE 5+2 WHEN 7 THEN '5+2=7'
ELSE '5+2는 몰라요'
END AS "5+2는 뭐예요"
FROM DUAL;
--==>> 5+2=7
SELECT CASE 1+1 WHEN 2 THEN '1+1=2'
WHEN 3 THEN '1+1=3'
WHEN 4 THEN '1+1=4'
ELSE '몰라'
END AS "1+1은 뭐예요"
FROM DUAL;
--==>> 1+1=2
SELECT CASE 2+2 WHEN 2 THEN '2+2=2'
WHEN 3 THEN '2+2=3'
WHEN 4 THEN '2+2=4'
ELSE '몰라'
END AS "2+2는 뭐예요"
FROM DUAL;
--==>> 2+2=4
SELECT CASE 3+3 WHEN 2 THEN '3+3=2'
WHEN 3 THEN '3+3=3'
WHEN 4 THEN '3+3=4'
ELSE '몰라'
END AS "3+3은 뭐예요"
FROM DUAL;
--==>> 몰라
-- 지금까지 해본 것은 상황은 하나인데 결과가 다른 각도로 분기될 때 사용되는 구문
-- 상황 자체가 분기가 될 때가 있다.
SELECT CASE WHEN 5+2=4 THEN '5+2=4'
WHEN 6-1=3 THEN '6-1=3'
WHEN 7+0=0 THEN '7+0=0'
ELSE '몰라'
END AS "뭐니"
FROM DUAL;
--==>> 몰라
SELECT CASE WHEN 5+2=4 THEN '5+2=4'
WHEN 6-1=3 THEN '6-1=3'
WHEN 7+0=7 THEN '7+0=7'
ELSE '몰라'
END AS "뭐니"
FROM DUAL;
--==>> 7+0=7
--○ DECODE()
-- 5-2의 값이 1이라면 '5-2=1'로 처리한다. (FALSE)
-- 5-2의 값이 2라면 '5-2=2'로 처리한다. (FALSE)
-- 5-2의 값이 3이라면 '5-2=3'으로 처리한다. (TRUE)
SELECT DECODE(5-2, 1, '5-2=1', 2, '5-2=2', 3, '5-2=3', '5-2 몰라요') "확인"
FROM DUAL;
--==>> 5-2=3
--○ CASE WHEN THEN ELSE END (조건문, 분기문) 활용
SELECT CASE WHEN 5<2 THEN '5<2'
WHEN 5>2 THEN '5>2'
ELSE '5와2는 비교불가'
END "결과확인"
FROM DUAL;
--==>> 5>2
SELECT '5>2' "결과확인"
FROM DUAL;
--> 조건에 맞는 것만 남고 모두 사라진다.
SELECT CASE WHEN 5<2 OR 3>1 AND 2=2 THEN '윤수만세'
WHEN 5>2 OR 2=3 THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
--==>> 윤수만세
SELECT CASE WHEN F OR 3>1 AND 2=2 THEN '윤수만세'
WHEN 5>2 OR 2=3 THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
SELECT CASE WHEN F OR T AND 2=2 THEN '윤수만세'
WHEN 5>2 OR 2=3 THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
SELECT CASE WHEN T AND T THEN '윤수만세'
WHEN 5>2 OR 2=3 THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
SELECT CASE WHEN T THEN '윤수만세'
WHEN 5>2 OR 2=3 THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
SELECT CASE WHEN 5<2 OR 3>1 AND 2=2 THEN '윤수만세'
WHEN T THEN '하성만세'
ELSE '민지만세'
END "결과확인"
FROM DUAL;
-- 앞에서 확인했더 WHEN 이하의 구문이 TRUE로 처리되어 실행 됐으면 ('윤수만세')
-- 그 다음 WHEN 구문을 확인하지 않고 구문이 종료된다는 것을 확인해야 한다.
SELECT CASE WHEN 3<1 AND 5<2 OR 3>1 AND 2=2 THEN '현욱만세'
WHEN 5<2 AND 2=3 THEN '나영만세'
ELSE '혜성만세'
END "결과확인"
FROM DUAL;
--==>> 현욱만세
SELECT CASE WHEN T THEN '현욱만세'
WHEN 5<2 AND 2=3 THEN '나영만세'
ELSE '혜성만세'
END "결과확인"
FROM DUAL;
-- F AND (T) AND T의 형태
-- F AND T = F 이므로 FALSE
-- '혜성만세' 출력
SELECT CASE WHEN 3<1 AND (5<2 OR 3>1) AND 2=2 THEN '현욱만세'
WHEN 5<2 AND 2=3 THEN '나영만세'
ELSE '혜성만세'
END "결과확인"
FROM DUAL;
--==>> 혜성만세
SELECT CASE WHEN F THEN '현욱만세'
WHEN F THEN '나영만세'
ELSE '혜성만세'
END "결과확인"
FROM DUAL;
--○ 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.
SELECT *
FROM TBL_SAWON;
--==>>
/*
SANO SANAME JUBUN HIREDATE SAL
1001 강혜성 9710171234567 2005-01-03 3000
1002 박가영 9511182234567 1999-11-23 4000
1003 박나영 9902082234567 2006-08-10 4000
1004 최혜인 9708112234567 2010-05-06 5000
1005 아이유 0502034234567 2015-10-19 1000
1006 이하이 0609304234567 2012-06-17 1000
1007 인순이 6510102234567 1999-08-22 2000
1008 선동열 6909101234567 1998-01-10 2000
1009 이이경 0505053234567 2011-05-06 1500
1010 선우용녀 6611112234567 2000-01-16 1300
1011 이윤수 9501061234567 2009-09-19 4000
1012 선우선 0606064234567 2011-11-11 2000
1013 남진 6511111234567 1999-11-11 2000
1014 이주형 9904171234567 2009-11-11 2000
1015 남궁진 0202023234567 2010-10-10 2300
*/
--○ TBL_SAWON 테이블을 활용하여 다음과 같은 항목을 조회할 수 있도록 쿼리문을 구성한다.
-- 『사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일,
-- 정년퇴직일, 근무일수, 남은일수, 급여, 보너스』
-- 단, 현재나이는 만나이 적용 이전 한국나이 계산법에 따라 연산을 수행한다.
-- 또한, 정년 퇴직일은 해당 직원의 나이가 한국 나이로 60세가 되는 해의
-- 그 직원의 입사 월, 일로 연산을 수행한다.
-- 그리고, 보너스는 1000일 이상 2000일 미만 근무한 사원은
-- 그 사원의 원래 급여 기준 30% 지급, 2000일 이상 근무한 사원은
-- 그 사원의 원래 급여 기준 50% 지급을 할 수 있도록 처리한다.
SELECT SANO "사원번호"
, SANAME "사원명"
, JUBUN "주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1) = '1' OR SUBSTR(JUBUN, 7, 1) = '3' THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) = '2' OR SUBSTR(JUBUN, 7, 1) = '4' THEN '여성'
ELSE '알수없음'
END AS "성별"
, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) "현재년도"
, CASE WHEN SUBSTR(JUBUN, 7, 1) = '1' OR SUBSTR(JUBUN, 7, 1) = '2'
THEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1
WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4'
THEN TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 2000 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1
END AS "현재나이"
, HIREDATE "입사일"
, CASE WHEN SUBSTR(JUBUN, 7, 1) = '1' OR SUBSTR(JUBUN, 7, 1) = '2'
THEN TO_DATE(CONCAT(TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY'),
CONCAT(TO_CHAR(HIREDATE, 'MM'), TO_CHAR(HIREDATE, 'DD'))), 'YYYY-MM-DD')
WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4'
THEN TO_DATE(CONCAT(TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 2000 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY'),
CONCAT(TO_CHAR(HIREDATE, 'MM'), TO_CHAR(HIREDATE, 'DD'))), 'YYYY-MM-DD')
END AS "정년퇴직일"
, TRUNC(SYSDATE - HIREDATE) "근무일수"
, CASE WHEN SUBSTR(JUBUN, 7, 1) = '1' OR SUBSTR(JUBUN, 7, 1) = '2'
THEN TRUNC (TO_DATE(CONCAT(TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY'),
CONCAT(TO_CHAR(HIREDATE, 'MM'), TO_CHAR(HIREDATE, 'DD'))), 'YYYY-MM-DD') - SYSDATE)
WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4'
THEN TRUNC (TO_DATE(CONCAT(TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 2000 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY'),
CONCAT(TO_CHAR(HIREDATE, 'MM'), TO_CHAR(HIREDATE, 'DD'))), 'YYYY-MM-DD') - SYSDATE)
END AS "남은일수"
, SAL "급여"
, CASE WHEN (SYSDATE - HIREDATE) >= 1000 AND (SYSDATE - HIREDATE) < 2000 THEN SAL*(30/100)
WHEN (SYSDATE - HIREDATE) > 2000 THEN SAL*(50/100)
END AS "보너스"
FROM TBL_SAWON;
TO_YMINTERVAL(CONCAT(TO_CHAR(33), '-01'))
--○ 현재 시간을 기준으로 1년 2개월 3일 4시간 5분 6초 후를 조회한다.
-- TO_YMINTERVAL() : 연 월의 인터벌
-- TO_DSINTERVAL() : 일 시간 분 초의 인터벌
SELECT SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR(33), '-01'))
FROM DUAL;
SELECT HIREDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00'))
FROM TBL_SAWON;
SELECT SYSDATE "현재 시간"
-- 1년 2개월 이후
, SYSDATE + TO_YMINTERVAL('') + TO_DSINTERVAL('003 04:05:06') "연산 결과"
FROM DUAL;
--==>> 2023-10-23 11:22:26
-- 2024-12-26 15:27:32
-- 몇 년도에 60세가 되는지 확인.
-- 근무일수 (입사한 날짜를 기준으로 현재 날짜까지 얼마나 근무했는지 확인)
-- 정년퇴직까지 남은 일수를 남은 일수로
-- 보너스 (근무일수가 1000일 이상 ~ 2000일 미만 현재 급여 30%)
-- 보너스 (근무일수가 2000일 이상 현재 급여 50%)
-- 33년 후, 2038년이 정년퇴직.
-- EX) 1001 강혜성 9710171234567 남성 27 2005-01-03 2038-01-03 XXXX XXXXX 3000 1500
-- 1000~2000 : 3000의 30%
-- 2000 이상 : 3000의 50%
SELECT TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY')
FROM TBL_SAWON;
SELECT TO_DATE(CONCAT(TO_CHAR(SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00')), 'YYYY'),
CONCAT(TO_CHAR(HIREDATE, 'MM'), TO_CHAR(HIREDATE, 'DD'))), 'YYYY-MM-DD')
FROM TBL_SAWON;
SELECT SYSDATE + TO_YMINTERVAL(CONCAT(TO_CHAR((60 - (TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 1900 - TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1))), '-00'))
FROM TBL_SAWON;
/*
혜성오빠 풀이!!=================================================================================================================
SELECT SANO 사원번호, SANAME 사원명, JUBUN 주민번호
, CASE WHEN JUBUN LIKE '______1%' OR JUBUN LIKE '______3%' THEN '남' ELSE '여' END AS 성별
, TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) -
CASE WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4' THEN 2000 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1
ELSE 1900 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1
END AS 현재나이
, HIREDATE 입사일
, TO_DATE((CASE WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4' THEN 2000 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1 + 60
ELSE 1900 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1 + 60 END)||'-'||TO_CHAR(HIREDATE, 'MM')||'-'||TO_CHAR(HIREDATE,'DD'), 'YYYY-MM-DD') AS 정년퇴직일
, ROUND(SYSDATE - TO_DATE(HIREDATE, 'YYYY-MM-DD')) AS 근무일수
, TRUNC(TO_DATE((CASE WHEN SUBSTR(JUBUN, 7, 1) = '3' OR SUBSTR(JUBUN, 7, 1) = '4' THEN 2000 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1 + 60
ELSE 1900 + TO_NUMBER( SUBSTR(JUBUN, 1, 2) ) - 1 + 60 END)||'-'||TO_CHAR(HIREDATE, 'MM')||'-'||TO_CHAR(HIREDATE,'DD'), 'YYYY-MM-DD') - SYSDATE) AS 남은일수
, SAL 급여
, CASE WHEN TO_NUMBER(ROUND(SYSDATE - TO_DATE(HIREDATE, 'YYYY-MM-DD'))) >= 1000 AND TO_NUMBER(ROUND(SYSDATE - TO_DATE(HIREDATE, 'YYYY-MM-DD'))) < 2000 THEN SAL * 0.3
ELSE SAL * 0.5
END AS 보너스
FROM TBL_SAWON;
*/
-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 정년퇴직일, 근무일수, 남은일수, 급여, 보너스
-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 급여 먼저 처리
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN THEN ELSE END "성별"
FROM TBL_SAWON;
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN 주민번호 7번째자리 1개가 '1' 또는 '3' THEN '남성'
WHEN 주민번호 7번째자리 1개가 '2' 또는 '4' THEN '여성'
ELSE '성별확인불가'
END "성별"
FROM TBL_SAWON;
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1)가 '1' 또는 '4' THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1)가 '2' 또는 '4' THEN '여성'
ELSE '성별확인불가'
END "성별"
FROM TBL_SAWON;
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
-- 성별
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
-- 현재나이(만) = 현재년도 - (태어난년도 + 1) (1900년대 / 2000년대)
, CASE WHEN 주민번호 7번째 자리 1개가 '1' 또는 '2'
THEN 현재년도 - (주민번호 앞 두자리 + 1899)
WHEN 주민번호 7번째 자리 1개가 '3' 또는 '4'
THEN 현재년도 - (주민번호 앞 두자리 + 1999)
ELSE '나이확인불가'
END "현재나이"
FROM TBL_SAWON;
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
-- 성별
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
-- 현재나이(만) = 현재년도 - (태어난년도 + 1) (1900년대 / 2000년대)
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN 현재년도 - (주민번호 앞 두자리 + 1899)
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN 현재년도 - (주민번호 앞 두자리 + 1999)
ELSE '나이확인불가'
END "현재나이"
FROM TBL_SAWON;
-- CASE WHEN THEN 의 결과값이 문자로 통일됐을 때는 문제가 없지만,
-- 나이를 구하는 과정에서, ELSE의 결과값이 문자타입이므로 데이터의 형식이 일치되지 않아서 문제가 발생한다.
-- CASE WHEN THEN의 결과값의 데이터 타입이 하나로 통일 되어야 "현재나이"에 대한 컬럼의 데이터 타입을 어떤 것으로 정해야 할지 모르므로
-- 오류가 나는 것이다.
-- CASE의 구문 내에 있는 데이터 타입을 한 가지로 통일 시켜야만 에러가 나지 않는다!!!
-- 주의할 것 !!!!
-- ELSE는 THEN이 없이 단독으로 사용한다.
-- ELSE 없이 THEN만으로 CASE문을 구성하는 것은 위험하므로 지양하도록 하자.
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
-- 성별
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
-- 현재나이(만) = 현재년도 - (태어난년도 + 1) (1900년대 / 2000년대)
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)
-- ELSE '나이확인불가'
ELSE -1
END "현재나이"
-- 입사일
, HIREDATE "입사일"
-- 급여
, SAL "급여"
FROM TBL_SAWON;
--==>>
/*
1001 강혜성 9710171234567 남성 27 2005-01-03 3000
1002 박가영 9511182234567 여성 29 1999-11-23 4000
1003 박나영 9902082234567 여성 25 2006-08-10 4000
1004 최혜인 9708112234567 여성 27 2010-05-06 5000
1005 아이유 0502034234567 여성 19 2015-10-19 1000
1006 이하이 0609304234567 여성 18 2012-06-17 1000
1007 인순이 6510102234567 여성 59 1999-08-22 2000
1008 선동열 6909101234567 남성 55 1998-01-10 2000
1009 이이경 0505053234567 남성 19 2011-05-06 1500
1010 선우용녀 6611112234567 여성 58 2000-01-16 1300
1011 이윤수 9501061234567 남성 29 2009-09-19 4000
1012 선우선 0606064234567 여성 18 2011-11-11 2000
1013 남진 6511111234567 남성 59 1999-11-11 2000
1014 이주형 9904171234567 남성 25 2009-11-11 2000
1015 남궁진 0202023234567 남성 22 2010-10-10 2300
*/
-- SELECT 문의 파싱 순서때문에
-- SELECT 절에서 어떤 ALIAS가 붙었는지 알 수 없으므로.
-- ORDER BY에서는 알 수 있었지만, SELECT 문 내에서는 선행 처리가 어떻게 되었는지를 알 수 없으므로.
-- 첫 번째 컬럼이 무엇인지, 별칭이 무엇인지, 어떤 값이 담겨 있는지를 ORDER BY 는 SELECT 절 이후에 나오므로 파악이 가능하다.
SELECT EMPNO, ENAME, SAL, COMM, SAL*12+NVL(COMM, 0), 연봉*2 "연봉두배"
FROM EMP;
--==>> 에러 발생
-- (00904. 00000 - "%s: invalid identifier")
-- 서브쿼리문
-- FROM, 내부에 있는 것이 INNER VIEW이다.
-- FROM 내부에 작은 테이블이 있다는 것을 가정하고 거기서 ALIAS를 통해 값을 선택해 조회하는 것이다.
-- 이때, OUTER SELECT 문에서 EMPNO와 같이 접근하게 되면 오류가 발생한다.
-- FROM 내부에 있는 INNER VIEW 테이블에는 EMPNO 컬럼이 존재하지 않기 때문이다...
SELECT 사원번호, 사원명, 급여, 수당, 연봉, 연봉*2 "연봉두배"
FROM
(
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "수당", SAL*12+NVL(COMM, 0) "연봉"
FROM EMP
);
--==>>
/*
7369 SMITH 800 9600 19200
7499 ALLEN 1600 300 19500 39000
7521 WARD 1250 500 15500 31000
7566 JONES 2975 35700 71400
7654 MARTIN 1250 1400 16400 32800
7698 BLAKE 2850 34200 68400
7782 CLARK 2450 29400 58800
7788 SCOTT 3000 36000 72000
7839 KING 5000 60000 120000
7844 TURNER 1500 0 18000 36000
7876 ADAMS 1100 13200 26400
7900 JAMES 950 11400 22800
7902 FORD 3000 36000 72000
7934 MILLER 1300 15600 31200
*/
--○ 뷰 생성
CREATE VIEW VIEW_EMP
AS
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "수당", SAL*12+NVL(COMM, 0) "연봉"
FROM EMP;
--==>> 에러 발생
-- (ORA-01031: insufficient privileges) 권한이 불충분하다. SCOTT에게 뷰 생성 권한을 부여해야 한다.
-- SYS로 접속하자. (GRANT 완료~!!!)
--○ SYS 로 접속하여 SCOTT 계정에 CREATE VIEW 권한을 부여한 후 다시 실행
CREATE VIEW VIEW_EMP
AS
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "수당", SAL*12+NVL(COMM, 0) "연봉"
FROM EMP;
--==>> View VIEW_EMP이(가) 생성되었습니다.
-- VIEW 볼려고 하는 구문들을 한번 조회하기 위해서 한번씩 보기 위한 작업이다.
-- CREATE 구문으로 똑같은 테이블 명을 갖는 CREATE TABLE 하게 되면 이미 그 이름을 갖는 테이블이 존재하므로 오류.
-- VIEW는 가볍게 만들어서 가볍게 쓴다.
SELECT *
FROM VIEW_EMP;
-- 새로운 VIEW를 만들거나, 이름이 존재한다면 다시 덮어쓰기.
-- OR REPLACE : VIEW에 쓰이는 구문
-- 이 이름으로 ('VIEW_EMP') 로 뷰를 만들어줘
-- 만약, 이 이름이 존재한다면 덮어쓰기 해줘.
CREATE OR REPLACE VIEW VIEW_EMP
AS
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", COMM "수당", SAL*12+NVL(COMM, 0) "연봉"
FROM EMP;
--==>> View VIEW_EMP이(가) 생성되었습니다.
-- 기존에 잘못 만들어진 VIEW가 있더라도, OR REPLACE를 통해 새롭게 정의한 구조로 다시 덮어쓰여질 것이다.
SELECT *
FROM VIEW_EMP;
--※ 이렇게 작성하면 안 된다!
-- ALIAS로 접근해야 한다.
SELECT EMPNO
FROM VIEW_EMP;
-- ALIAS로 SELECT 할 경우 오류가 발생하지 않는다.
SELECT 사원번호, 사원명, 연봉
FROM VIEW_EMP;
-- 쿼리가 중첩되어 있는 것이 서브 쿼리
-- 서브 쿼리의 일종으로 INLINE VIEW
-- 사원번호, 사원명, 주민번호, 성별, 현재나이, 입사일, 정년퇴직일, 근무일수, 남은일수, 급여, 보너스
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
-- 정년퇴직일
-- 정년퇴직년도 → 해당 직원의 나이가 한국나이로 60세가 되는 해
-- 현재 나이가... 57세... 3년 후 2023 → 2026
-- 현재 나이가... 28세... 32년 후 2023 → 2055
-- ADD_MONTHS(SYSDATE, 남은년수*12)
-- ------
-- 60 - 현재나이
-- ADD_MONTHS(SYSDATE, (60 - 현재나이) * 12) → 특정 날짜
-- TO_CHAR('특정날짜', 'YYYY') → 정년퇴직 년도만 추출
-- TO_CHAR('입사일', 'MM-DD') → 입사월일만 추출
-- TO_CHAR('특정날짜', 'YYYY') || '-' || TO_CHAR('입사일', 'MM-DD') → 정년퇴직일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD') "정년퇴직일"
-- 근무일수
-- 근무일수 = 현재일 - 입사일
, TRUNC(SYSDATE - T.입사일) "근무일수"
-- 남은일수
-- 남은일수 = 정년퇴직일 - 현재일
, TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"
-- 급여
, T.급여
-- 보너스
-- 근무일수가 1000일 이상 2000일 미만 → 급여의 30%
-- 근무일수가 2000일 이상 → 급여의 50%
-- 나머지... (근무일수 1000일 미만) → 0
-----------------------------------------------------------
-- 근무일수가 1000일 이상 2000일 미만 → 급여 * 0.3
-- 근무일수가 2000일 이상 → 급여 * 0.5
-- 나머지... (근무일수 1000일 미만) → 0
-----------------------------------------------------------
-- 근무일수가 2000일 이상 → 급여 * 0.5
-- 근무일수가 1000일 이상 → 급여 * 0.3
-- 나머지... (근무일수 1000일 미만) → 0
-----------------------------------------------------------
, CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5
WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3
ELSE 0
END "보너스"
FROM
(
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)
-- ELSE '나이확인불가'
ELSE -1
END "현재나이"
, HIREDATE "입사일"
, SAL "급여"
FROM TBL_SAWON
) T;
--==>>
/*
1001 강혜성 9710171234567 남성 27 2005-01-03 2056-01-03 6867 11759 3000 1500
1002 박가영 9511182234567 여성 29 1999-11-23 2054-11-23 8735 11353 4000 2000
1003 박나영 9902082234567 여성 25 2006-08-10 2058-08-10 6283 12709 4000 2000
1004 최혜인 9708112234567 여성 27 2010-05-06 2056-05-06 4918 11883 5000 2500
1005 아이유 0502034234567 여성 19 2015-10-19 2064-10-19 2926 14971 1000 500
1006 이하이 0609304234567 여성 18 2012-06-17 2065-06-17 4145 15212 1000 500
1007 인순이 6510102234567 여성 59 1999-08-22 2024-08-22 8828 303 2000 1000
1008 선동열 6909101234567 남성 55 1998-01-10 2028-01-10 9417 1539 2000 1000
1009 이이경 0505053234567 남성 19 2011-05-06 2064-05-06 4553 14805 1500 750
1010 선우용녀 6611112234567 여성 58 2000-01-16 2025-01-16 8681 450 1300 650
1011 이윤수 9501061234567 남성 29 2009-09-19 2054-09-19 5147 11288 4000 2000
1012 선우선 0606064234567 여성 18 2011-11-11 2065-11-11 4364 15359 2000 1000
1013 남진 6511111234567 남성 59 1999-11-11 2024-11-11 8747 384 2000 1000
1014 이주형 9904171234567 남성 25 2009-11-11 2058-11-11 5094 12802 2000 1000
1015 남궁진 0202023234567 남성 22 2010-10-10 2061-10-10 4761 13866 2300 1150
*/
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD') "정년퇴직일"
, TRUNC(SYSDATE - T.입사일) "근무일수"
, TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"
, T.급여
, CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5
WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3
ELSE 0
END "보너스"
FROM
(
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)
ELSE -1
END "현재나이"
, HIREDATE "입사일"
, SAL "급여"
FROM TBL_SAWON
) T;
-- 위에서 처리한 내용을 기반으로...
-- 특정 근무일수의 사원을 확인해야 한다거나...
-- 특정 보너스 금액을 받는 사원을 확인해야 할 경우가 발생할 수 있다.
-- (즉, 추가적인 조회 조건이 발생하거나, 업무가 파생되는 경우)
-- 이와 같은 경우... 해당 쿼리문을 다시 구성해야 하는 번거로움을 줄일 수 있도록
-- 뷰(VIEW)를 만들어 저장해 둘 수 있다.
CREATE OR REPLACE VIEW VIEW_SAWON
AS
SELECT T.사원번호, T.사원명, T.주민번호, T.성별, T.현재나이, T.입사일
, TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD') "정년퇴직일"
, TRUNC(SYSDATE - T.입사일) "근무일수"
, TRUNC(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, (60 - T.현재나이) * 12), 'YYYY') || '-' || TO_CHAR(T.입사일, 'MM-DD'), 'YYYY-MM-DD') - SYSDATE) "남은일수"
, T.급여
, CASE WHEN TRUNC(SYSDATE - T.입사일) >= 2000 THEN T.급여 * 0.5
WHEN TRUNC(SYSDATE - T.입사일) >= 1000 THEN T.급여 * 0.3
ELSE 0
END "보너스"
FROM
(
SELECT SANO "사원번호", SANAME "사원명", JUBUN "주민번호"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END "성별"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1899)
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN, 1, 2)) + 1999)
ELSE -1
END "현재나이"
, HIREDATE "입사일"
, SAL "급여"
FROM TBL_SAWON
) T;
--==>> View VIEW_SAWON이(가) 생성되었습니다.
SELECT *
FROM VIEW_SAWON;
SELECT *
FROM VIEW_SAWON
WHERE 근무일수 >= 5000;
--==>>
/*
1001 강혜성 9710171234567 남성 27 2005-01-03 2056-01-03 6867 11759 3000 1500
1002 박가영 9511182234567 여성 29 1999-11-23 2054-11-23 8735 11353 4000 2000
1003 박나영 9902082234567 여성 25 2006-08-10 2058-08-10 6283 12709 4000 2000
1007 인순이 6510102234567 여성 59 1999-08-22 2024-08-22 8828 303 2000 1000
1008 선동열 6909101234567 남성 55 1998-01-10 2028-01-10 9417 1539 2000 1000
1010 선우용녀 6611112234567 여성 58 2000-01-16 2025-01-16 8681 450 1300 650
1011 이윤수 9501061234567 남성 29 2009-09-19 2054-09-19 5147 11288 4000 2000
1013 남진 6511111234567 남성 59 1999-11-11 2024-11-11 8747 384 2000 1000
1014 이주형 9904171234567 남성 25 2009-11-11 2058-11-11 5094 12802 2000 1000
*/
SELECT *
FROM VIEW_SAWON
WHERE 보너스 >= 2000;
/*
1002 박가영 9511182234567 여성 29 1999-11-23 2054-11-23 8735 11353 4000 2000
1003 박나영 9902082234567 여성 25 2006-08-10 2058-08-10 6283 12709 4000 2000
1004 최혜인 9708112234567 여성 27 2010-05-06 2056-05-06 4918 11883 5000 2500
1011 이윤수 9501061234567 남성 29 2009-09-19 2054-09-19 5147 11288 4000 2000
*/
--○ 서브 쿼리를 활용하여
-- TBL_SAWON 테이블을 대상으로 다음과 같이 조회할 수 있도록 한다.
/*
-----------------------------------------------------
사원명 성별 현재나이 급여 나이보너스
-----------------------------------------------------
*/
-- VIEW_SAWON 을 이용하는 것이 아님.
-- 나이보너스는 현재 나이가 40세 이상이면 급여의 70%
-- 30세 이상 40세 미만이면 급여의 50%
-- 20세 이상 30세 미만이면 급여의 30%로 한다.
-- 또한, 이렇게 완성된 조회 구문을 통해
-- VIEW_SAWON2 라는 이름의 뷰(VIEW)를 생성할 수 있도록 한다.
SELECT T.사원명, T.성별, T.현재나이, T.급여
, CASE WHEN T.현재나이 >= 40 THEN T.급여 *0.7
WHEN T.현재나이 >= 30 THEN T.급여 *0.5
WHEN T.현재나이 >= 20 THEN T.급여 *0.3
ELSE 0
END AS "나이보너스"
FROM
(
SELECT SANAME "사원명"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END AS "성별"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(JUBUN, 1, 2))) + 1
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - 2000 - (TO_NUMBER(SUBSTR(JUBUN, 1, 2))) + 1
ELSE -1
END AS "현재나이"
, SAL "급여"
FROM TBL_SAWON
) T;
-- VIEW 만들깅~~
CREATE OR REPLACE VIEW VIEW_SAWON2
AS
SELECT T.사원명, T.성별, T.현재나이, T.급여
, CASE WHEN T.현재나이 >= 40 THEN T.급여 *0.7
WHEN T.현재나이 >= 30 THEN T.급여 *0.5
WHEN T.현재나이 >= 20 THEN T.급여 *0.3
ELSE 0
END AS "나이보너스"
FROM
(
SELECT SANAME "사원명"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '3') THEN '남성'
WHEN SUBSTR(JUBUN, 7, 1) IN ('2', '4') THEN '여성'
ELSE '성별확인불가'
END AS "성별"
, CASE WHEN SUBSTR(JUBUN, 7, 1) IN ('1', '2')
THEN EXTRACT(YEAR FROM SYSDATE) - 1900 - (TO_NUMBER(SUBSTR(JUBUN, 1, 2))) + 1
WHEN SUBSTR(JUBUN, 7, 1) IN ('3', '4')
THEN EXTRACT(YEAR FROM SYSDATE) - 2000 - (TO_NUMBER(SUBSTR(JUBUN, 1, 2))) + 1
ELSE -1
END AS "현재나이"
, SAL "급여"
FROM TBL_SAWON
) T;
SELECT *
FROM VIEW_SAWON2;
20231023_01_sys.sql
SELECT USER
FROM DUAL;
--==>> SYS
--○ SCOTT 계정에 뷰(VIEW)를 생성할 수 있는 권한 부여 → 『CREATE VIEW』
-- REVOKE FROM : 권한 박탈
-- REVOKE --- FROM 대상
-- GRANT TO : 권한 부여
-- GRANT --- TO 대상
GRANT CREATE VIEW TO SCOTT;
--==>> Grant을(를) 성공했습니다.'[Oracle] > SQL (Program source)' 카테고리의 다른 글
| [Oracle] 20231025 [프로그램소스] - 20231025_01_scott (0) | 2023.10.25 |
|---|---|
| [Oracle] 20231024 [프로그램소스 - 그룹화] - 20231024_01_scott (0) | 2023.10.24 |
| ★ [Oracle] 20231020 [프로그램소스 - 수료일까지 남은 연, 월, 일] - 20231020_01_scott (2) (0) | 2023.10.20 |
| [Oracle] 20231020 [프로그램소스] - 20231020_01_scott (1) (0) | 2023.10.20 |
| [Oracle] 20231019 [프로그램소스] - 20231019_01_scott (1) | 2023.10.19 |