by 마농 FUNCTION 재귀함수 Recursive Function 재귀호출 [2015.08.12]
이번 퀴즈로 배워보는 SQL 시간에는 근무 종료시간을 구하는 재귀 호출 함수 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
문제
통상 근무시간은 평일 오전 9시부터 오후 6시까지이며 그외 시간은 근무시간이 아닙니다. 또한 오후 12시부터 1시는 점심시간으로 근무시간에서 제외됩니다. 토, 일요일 및 휴일도 근무시간에서 제외됩니다.
휴일은 별도의 테이블로 관리됩니다. <표 1>의 휴일테이블(t_holiday)을 참조하여 근무 시작시간(i_t)과 총소요시간(i_h)을 입력 받아 근무 종료시간(o_t)을 반환하는 사용자 함수(f_Quiz)를 생성하는 문제입니다. <표 2>의 결과테이블 형태로 결과를 도출하는 사용자 함수를 작성하세요.
- [리스트 1] 휴일 테이블 생성 및 조회
-
CREATE TABLE t_holiday AS SELECT '20140101' dt FROM dual UNION ALL SELECT '20140130' FROM dual UNION ALL SELECT '20140131' FROM dual UNION ALL SELECT '20140201' FROM dual UNION ALL SELECT '20140301' FROM dual UNION ALL SELECT '20140505' FROM dual UNION ALL SELECT '20140506' FROM dual UNION ALL SELECT '20140606' FROM dual ; SELECT * FROM t_holiday;
- [표 1] 휴일 테이블
-
DT ----------- 20140101 20140130 20140131 20140201 20140301 20140505 20140506 20140606
- [리스트 2] 사용자 함수 결과 조회
-
WITH t AS ( SELECT 1 no, '2014/01/01 12:30' t, 2 h FROM dual UNION ALL SELECT 2, '2014/01/30 12:30', 4 FROM dual UNION ALL SELECT 3, '2014/03/04 07:30', 2 FROM dual UNION ALL SELECT 4, '2014/03/04 12:30', 2 FROM dual UNION ALL SELECT 5, '2014/03/04 18:30', 2 FROM dual UNION ALL SELECT 6, '2014/03/04 09:30', 5 FROM dual UNION ALL SELECT 7, '2014/03/07 17:00', 2 FROM dual ) SELECT no, t, h , f_Quiz(t, h) x FROM t ;
- [표 2] 결과 테이블
-
NO T H X -------- ------------------ ---------- ------------------ 1 2014/01/01 12:30 2 2014/01/02 11:00 2 2014/01/30 12:30 4 2014/02/03 14:00 3 2014/03/04 07:30 2 2014/03/04 11:00 4 2014/03/04 12:30 2 2014/03/04 15:00 5 2014/03/04 18:30 2 2014/03/05 11:00 6 2014/03/04 09:30 5 2014/03/04 15:30 7 2014/03/07 17:00 2 2014/03/10 10:00
문제설명
<리스트2>의 근무시작시간(t)와 소요시간(h)를 입력값으로 함수(f_Quiz)의 수행결과(x)를 도출하는 문제입니다.
<표 2>의 수행 결과를 보면 첫번째 행의 시작 시간인 2014-01-01 12:30의 2시간 후 종료시간은 2014-01-01 14:30이지만, 1월 1일은 휴일이므로 제외되고 다음날 아침 9시부터 근무시간이 시작되므로 2시간 뒤 종료시간은 2014-01-02 11:00이 됩니다.
이와 마찬가지로 나머지 행도 근무 종료시간을 구하는 문제입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 3] 정답 리스트
CREATE OR REPLACE FUNCTION f_Quiz(i_t VARCHAR2, i_h NUMBER)
RETURN VARCHAR2
IS
d_t DATE := TO_DATE(i_t, 'yyyy/mm/dd hh24:mi');
hh24mi VARCHAR2(5) := TO_CHAR(d_t, 'hh24:mi');
o_t VARCHAR2(16); -- 다음시작시간
o_h NUMBER; -- 다음남은시간
flag NUMBER(1) := 0; -- 재귀호출여부
huil NUMBER(1) := 0; -- 휴일여부
BEGIN
-- 휴일여부 가져오기
SELECT COUNT(*)
INTO huil
FROM t_holiday
WHERE dt = TO_CHAR(d_t, 'yyyymmdd')
;
IF TO_CHAR(d_t, 'd') IN ('1', '7') OR huil = 1 THEN
-- 휴일엔 다음날 아침 8시로 재호출
o_h := i_h;
o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00';
flag := 1;
ELSIF hh24mi < '09:00' THEN
-- 평일 9시 이전엔 9시로 재호출
o_h := i_h;
o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 09:00';
flag := 1;
ELSIF hh24mi BETWEEN '09:00' AND '11:59'
AND d_t + i_h/24 >= TRUNC(d_t) + 12/24 THEN
-- 오전 근무시간중 점심시간 오버하면
-- 오버된 시간만큼 13시로 재호출
o_h := ( (d_t + i_h/24) - (TRUNC(d_t) + 12/24) )*24;
o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 13:00';
flag := 1;
ELSIF hh24mi BETWEEN '12:00' AND '12:59' THEN
-- 점심시간엔 13시로 재호출
o_h := i_h;
o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 13:00';
flag := 1;
ELSIF hh24mi BETWEEN '13:00' AND '17:59'
AND d_t + i_h/24 >= TRUNC(d_t) + 18/24 THEN
-- 오후 근무시간중 저녁시간 오버하면
-- 오버된 시간만큼 다음날 9시로 재호출
o_h := ( (d_t + i_h/24) - (TRUNC(d_t) + 18/24) )*24;
o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00';
flag := 1;
ELSIF hh24mi >= '18:00' THEN
-- 18시 이후엔 다음날 8시로 재호출
o_h := i_h;
o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00';
flag := 1;
END IF;
IF flag = 1 THEN
o_t := f_Quiz(o_t, o_h); -- 재귀호출
ELSE
o_t := TO_CHAR(d_t + i_h/24, 'yyyy/mm/dd hh24:mi');
END IF;
RETURN o_t;
END;
/
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 SQL 작성 문제가 아닌, PL/SQL로 사용자 함수(user function)를 작성하는 문제입니다. 문제를 풀기 위한 접근 방법과 <리스트 3>의 정답 구문에 대해 설명해 보도록 하겠습니다.
이 문제를 풀기 위해 우선 몇가지 유형별 접근방법을 살펴보겠습니다. 시작시간이 비근무시간일 경우, 다음 최초로 도래하는 근무시간까지는 소요시간이 줄지 않게 됩니다.
첫 번째 행의 경우 1월1일 휴일을 지나 최초 근무시작시간은 다음날인 1월 2일 오전 9시가 됩니다. 이후 2시간이 소요되면 근무시간 내에 있으므로 11시를 반환하게 됩니다. 이러한 규칙을 정리해 보면
- - 휴일은 다음날 오전 9시 시작
- - 평일 9시 이전엔 오전 9시 시작
- - 평일 점심시간엔 오후 1시 시작
- - 평일 18시 이후엔 다음날 9시 시작
- - 나머지 근무시간 중에는 그 시간 그대로 시작
다음 규칙은 6번째 행의 자료를 토대로 도출해 보도록 하겠습니다. 시작시간인 9:30은 근무시간 중이므로 그대로 시작됩니다. 그런데 소요시간인 5시간을 적용시켜 종료시간인 14:30을 구하게 되면 점심시간이 제외되지 않았습니다.
점심시간을 제외하면 시간은 9:30~12:00 이렇게 2시간 30분을 소요한 뒤 다시 13:00에 시작하여 앞에서 소요하고 남은 시간 2시간 30분을 다시 소요하면 15:30으로 결과가 도출됩니다.
정리해보면 근무시간 중에는 다음 최초로 도래하는 근무 제외시간까지 시간을 소요한 뒤, 제외시간이 끝나는 시점, 즉 그다음 도래하는 근무시작시간을 재시작지점으로 하여 앞에서 소요한 시간을 제외하고 남은 시간만큼을 다시 소요시키는 형태가 됩니다.
자 그러면 <리스트 3>의 정답 구문에 이 규칙들이 어떻게 적용되는지 살펴볼까요? 시작시간(t)과 소요시간(h)를 입력받는 변수는 각각 i_t 와 i_h 로 선언합니다.
- [리스트 4] 함수 선언 및 입력변수 선언
-
CREATE OR REPLACE FUNCTION f_Quiz(i_t VARCHAR2, i_h NUMBER)
날짜와 시간을 다루는 함수이므로 반복적으로 자주 사용될 날짜변수를 선언하고 초기값을 지정해 줍니다.
- 리스트 5] 자주 쓰는 변수 선언
-
d_t DATE := TO_DATE(i_t, 'yyyymmddhh24mi'); hh24mi VARCHAR2(4) := TO_CHAR(d_t, 'hh24mi');
다음 시작시간과, 남은 소요시간이 저장될 변수 및 재귀호출여부 변수, 휴일여부 변수를 선언합니다.
- [리스트 6] 재귀호출에 입력값으로 쓰일 변수 선언
-
o_t VARCHAR2(12); -- 다음시작시간 o_h NUMBER; -- 다음남은시간
재귀호출여부 및 휴일여부 변수를 선언하고 초기값을 지정합니다.
- [리스트 7] 재귀호출여부
-
flag NUMBER(1) := 0; -- 재귀호출여부 huil NUMBER(1) := 0; -- 휴일여부 SELECT COUNT(*) INTO huil FROM t_holiday WHERE dt = TO_CHAR(d_t, 'yyyymmdd') ;
다음은 각 조건에 따라서 다음시작시간(o_t)과 남은소요시간(o_h)을 산출하고 재귀호출여부(flag)를 지정합니다.
- [리스트 8] 6가지 유형에 따른 조건 분기
-
IF TO_CHAR(d_t, 'd') IN ('1', '7') OR huil = 1 THEN -- 1. 휴일엔 다음날 아침 8시로 재호출 o_h := i_h; o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00'; flag := 1; ELSIF hh24mi < '09:00' THEN -- 2. 평일 9시 이전엔 9시로 재호출 o_h := i_h; o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 09:00'; flag := 1; ELSIF hh24mi BETWEEN '09:00' AND '11:59' AND d_t + i_h/24 >= TRUNC(d_t) + 12/24 THEN -- 3. 오전 근무시간중 점심시간 오버하면 -- 오버된 시간만큼 13시로 재호출 o_h := ( (d_t + i_h/24) - (TRUNC(d_t) + 12/24) )*24; o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 13:00'; flag := 1; ELSIF hh24mi BETWEEN '12:00' AND '12:59' THEN -- 4. 점심시간엔 13시로 재호출 o_h := i_h; o_t := TO_CHAR(d_t, 'yyyy/mm/dd')||' 13:00'; flag := 1; ELSIF hh24mi BETWEEN '13:00' AND '17:59' AND d_t + i_h/24 >= TRUNC(d_t) + 18/24 THEN -- 5. 오후 근무시간중 저녁시간 오버하면 -- 오버된 시간만큼 다음날 9시로 재호출 o_h := ( (d_t + i_h/24) - (TRUNC(d_t) + 18/24) )*24; o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00'; flag := 1; ELSIF hh24mi >= '18:00' THEN -- 6. 18시 이후엔 다음날 8시로 재호출 o_h := i_h; o_t := TO_CHAR(d_t + 1, 'yyyy/mm/dd')||' 09:00'; flag := 1; END IF;
다음 6가지 유형별로 다음시작시간(o_t)과 남은소요시간(o_h)을 산출합니다.
- 1. 휴일엔 다음날 아침 8시로 재호출
- 2. 평일 9시 이전에는 9시로 재호출
- 3. 오전 근무시간 중 점심시간을 오버하면 오버된 시간만큼 13시로 재호출
- 4. 점심시간엔 13시로 재호출
- 5. 오후 근무시간중 저녁시간 오버하면 오버된 시간만큼 다음날 9시로 재호출
- 6. 18시 이후엔 다음날 8시로 재호출
- [리스트 9] 함수 선언 및 입력변수 선언
-
IF flag = 1 THEN o_t := f_Quiz(o_t, o_h); -- 재귀호출 ELSE o_t := TO_CHAR(d_t + i_h/24, 'yyyy/mm/dd hh24:mi'); END IF; RETURN o_t;
이렇게 산출된 다음시작시간(o_t)과 남은소요시간(o_h)을 이용하여 다시 한번 자기 자신의 함수(f_Quiz)를 호출합니다. 이때 위에서 판별된 6가지 유형에 속하는 경우에는 자기 자신의 함수를 호출합니다.
이렇게 자기 자신을 호출하는 함수를 재귀함수(Recursive Function)이라고 합니다.
재호출된 함수에서는 같은 작업(유형판별 및 함수 재호출)을 반복합니다. 6가지 유형에 속하는 경우(flag = 1)엔 재귀호출을 반복하다가 6가지 유형에 속하지 않게 될 때(flag = 0) 결과를 반환하게 됩니다.
최초 시작시간과 소요시간의 유형에 따라 다음 시작시간, 남은 소요시간, 재귀호출여부가 결정되고 그에 따라 자기 자신의 함수를 재귀적으로 호출하는 방식으로 정답이 완성됐습니다.
이번시간에는 재귀함수(Recursive Function)를 이용해 문제를 풀었습니다. 재귀함수는 이와 같이 특정 작업을 반복수행하는 경우 매우 유용하게 사용될 수 있습니다.
- 강좌 URL : http://www.gurubee.net/lecture/2865
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
CREATE OR REPLACE FUNCTION F_QUIZ
(
t IN VARCHAR2, -- 계산시작시간
h IN NUMBER -- 총근무시간
) RETURN VARCHAR2
IS
ishol VARCHAR2(1);
h1 NUMBER := 0; -- 오전근무시간
h2 NUMBER := 0; -- 오후근무시간
ph NUMBER := h;
tdate DATE := TO_DATE(t, 'YYYY/MM/DD HH24:MI');
lsdat DATE := TO_DATE(TO_CHAR(TO_DATE(t, 'YYYY/MM/DD HH24:MI'),'YYYYMMDD'),'YYYYMMDD') + 1/24*12;
ledat DATE := TO_DATE(TO_CHAR(TO_DATE(t, 'YYYY/MM/DD HH24:MI'),'YYYYMMDD'),'YYYYMMDD') + 1/24*13;
wedat DATE := TO_DATE(TO_CHAR(TO_DATE(t, 'YYYY/MM/DD HH24:MI'),'YYYYMMDD'),'YYYYMMDD') + 1/24*18;
strdat DATE; -- 근무시작시간
sRtnDate VARCHAR2(30); -- 근무종료시간(리턴값)
rtnyn VARCHAR2(1) := 'N';
BEGIN
SELECT CASE WHEN
COUNT(DT) > 0 OR TO_CHAR(tdate,'d') IN('1','7') THEN 'Y'
ELSE 'N' END INTO ishol
FROM T_HOLIDAY
WHERE DT = TO_CHAR(tdate,'YYYYMMDD');
IF ishol = 'N' THEN
IF TO_NUMBER(TO_CHAR(tdate,'HH24MI')) < 900 THEN
strdat := TO_DATE(TO_CHAR(tdate, 'YYYYMMDD'),'YYYYMMDD') + 1/24*9;
h1 := (lsdat - strdat) * 24;
h2 := (wedat - ledat) * 24;
ELSIF TO_NUMBER(TO_CHAR(tdate,'HH24MI')) > 1200 AND TO_NUMBER(TO_CHAR(tdate,'HH24MI')) < 1300 THEN
strdat := TO_DATE(TO_CHAR(tdate, 'YYYYMMDD'),'YYYYMMDD') + 1/24*13;
h2 := (wedat - ledat) * 24;
ELSIF TO_NUMBER(TO_CHAR(tdate,'HH24MI')) > 1800 THEN
strdat := TO_DATE(TO_CHAR(tdate, 'YYYYMMDD'),'YYYYMMDD') + (1 + (1/24*9) );
sRtnDate := F_QUIZ(TO_CHAR(strdat,'YYYY/MM/DD HH24:MI'),h);
rtnyn := 'Y';
ELSE
strdat := tdate;
IF strdat <= lsdat THEN
h1 := (lsdat - strdat) * 24;
h2 := (wedat - ledat) * 24;
ELSE
h1 := 0;
h2 := (wedat - strdat) * 24;
END IF;
END IF;
IF rtnyn = 'N' THEN
IF h1 + h2 < h THEN
IF h1 = 0 THEN
ph := h - h2;
ELSE
ph := h - (h1+h2);
END IF;
sRtnDate := F_QUIZ( TO_CHAR(TO_DATE(TO_CHAR(strdat+1,'YYYYMMDD'),'YYYYMMDD') + 1/24*9,'YYYY/MM/DD HH24:MI'), ph );
ELSIF h1 + h2 = h THEN
sRtnDate := TO_CHAR(wedat,'YYYY/MM/DD HH24:MI');
ELSIF h1 + h2 > h THEN
IF h1 >= h THEN
sRtnDate := TO_CHAR(strdat + 1/24*h, 'YYYY/MM/DD HH24:MI');
ELSE
sRtnDate := TO_CHAR(ledat + 1/24*(h-h1), 'YYYY/MM/DD HH24:MI');
END IF;
END IF;
END IF;
ELSE
sRtnDate := F_QUIZ(TO_CHAR(tdate + 1,'YYYY/MM/DD'), h);
END IF;
RETURN sRtnDate;
END F_QUIZ;
좋은강의 감사합니다. 많이 배워갑니다.
) 버튼을 클릭하여 작성 하시면 됩니다.