데이터베이스 질문과 답변
//-----------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE ST_TP_TASK_STATUS(I_YEAR IN VARCHAR2,
I_TYPE IN VARCHAR2,
I_NEWNOLD IN VARCHAR2,
I_NEWNOLD_CODE IN VARCHAR2) IS
-- V_CNSLT_COUNT NUMBER :=0;
-- V_CNSLT_PROCESS_CL VARCHAR2(100) := '';
/* USER_DEFINE_EXCEPTION EXCEPTION; --사용자 전역 예외처리 변수
USER_ERROR_MESSAGE VARCHAR2(1000) := ' '; --사용자 에러구문*/
BEGIN
DELETE TP_TASK_STATUS_RECEIPT
WHERE YEAR = I_YEAR
AND TYPE = I_TYPE
AND NEWNOLD = I_NEWNOLD;
COMMIT;
IF I_TYPE = 'A' --유형별
THEN
INSERT INTO TP_TASK_STATUS_RECEIPT R
(R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
R.DIVISION2,
R.ATTRIBUTE1,
R.FACTORY_CNT,
R.FACTORY_PER,
R.SAN_SUM,
R.SAN_A,
R.SAN_B,
R.SAN_C,
R.HIGH_SUM,
R.HIGH_A,
R.HIGH_B,
R.HIGH_C,
R.NINE_SUM,
R.NINE_A,
R.NINE_B,
R.NINE_C,
R.ETC_SUM,
R.ETC_A,
R.ETC_B,
R.ETC_C,
R.PERSONAL_CNT,
R.PERSONAL_PER)
SELECT I_YEAR AS YEAR,
I_TYPE AS TYPE,
I_NEWNOLD AS NEWNOLD,
I_TYPE || 'D1' AS DIVISION1,
DECODE(V.partcptn_ty_code,
'ST5021',
'000000000000000000000000000000000000000000000000',
'ST5022',
'000000000000000000000000000',
'ST5023',
'0000000000000000000',
'ST5024',
'00000000000000',
'ST5025',
'000000000',
'000') AS DIVISION2,
-- V.partcptn_ty AS DIVISION2,
DECODE(V.partcptn_ty_code,
'ST5021',
'01',
'ST5022',
'02',
'ST5023',
'03',
'ST5024',
'04',
'ST5025',
'05',
'06') AS ATTRIBUTE1,
COUNT(V.CPR_NO) AS FACTORY_CNT,
'' AS FACTORY_PER,
SUM(V.SAN_REQUST_CUM_CO + V.WOO_REQUST_CUM_CO) AS SAN_SUM,
SUM(V.SAN_REQUST_ACTI_DTY_CO + V.WOO_REQUST_ACTI_DTY_CO) AS SAN_A,
SUM(V.SAN_REQUST_RSVDTY_CO + V.WOO_REQUST_RSVDTY_CO) AS SAN_B,
SUM(V.SAN_NOTDCD_RSVDTY_CO + V.WOO_NOTDCD_RSVDTY_CO) AS SAN_C,
SUM(V.HIGH_REQUST_CUM_CO) AS HIGH_SUM,
SUM(V.HIGH_REQUST_ACTI_DTY_CO) AS HIGH_A,
SUM(V.HIGH_REQUST_RSVDTY_CO) AS HIGH_B,
SUM(V.HIGH_NOTDCD_RSVDTY_CO) AS HIGH_C,
SUM(V.NINE_REQUST_CUM_CO) AS NINE_SUM,
SUM(V.NINE_REQUST_ACTI_DTY_CO) AS NINE_A,
SUM(V.NINE_REQUST_RSVDTY_CO) AS NINE_B,
SUM(V.NINE_NOTDCD_RSVDTY_CO) AS NINE_C,
SUM(V.ETC_REQUST_CUM_CO) AS ETC_SUM,
SUM(V.ETC_REQUST_ACTI_DTY_CO) AS ETC_A,
SUM(V.ETC_REQUST_RSVDTY_CO) AS ETC_B,
SUM(V.ETC_NOTDCD_RSVDTY_CO) AS ETC_C,
SUM(V.REQUST_CUM_CO) AS PERSONAL_CNT,
'' AS PERSONAL_PER
FROM V_TP_TASK01 V
WHERE V.TASK_TY = I_NEWNOLD_CODE -- X01008: 신규 / X01009: 기존
AND V.BSNS_YY = I_YEAR
GROUP BY DECODE(V.partcptn_ty_code,
'ST5021',
'000000000000000000000000000000000',
'ST5022',
'000000000000000000000000000000000',
'ST5023',
'000000000000000',
'ST5024',
'000000000000000',
'ST5025',
'000000000000000000000000',
'000'),
DECODE(V.partcptn_ty_code,
'ST5021',
'01',
'ST5022',
'02',
'ST5023',
'03',
'ST5024',
'04',
'ST5025',
'05',
'06');
COMMIT;
ELSIF I_TYPE = 'B' --지역별 통합
THEN
INSERT INTO TP_TASK_STATUS_RECEIPT R
(R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
R.DIVISION2,
R.ATTRIBUTE1,
R.FACTORY_CNT,
R.FACTORY_PER,
R.SAN_SUM,
R.SAN_A,
R.SAN_B,
R.SAN_C,
R.HIGH_SUM,
R.HIGH_A,
R.HIGH_B,
R.HIGH_C,
R.NINE_SUM,
R.NINE_A,
R.NINE_B,
R.NINE_C,
R.ETC_SUM,
R.ETC_A,
R.ETC_B,
R.ETC_C,
R.PERSONAL_CNT,
R.PERSONAL_PER)
SELECT I_YEAR AS YEAR,
I_TYPE AS TYPE,
I_NEWNOLD AS NEWNOLD,
I_TYPE || 'D1' AS DIVISION1,
(SELECT T.CODE_NM
FROM tc_code_value t
WHERE T.group_CL = 'TS2'
and t.use_at = 'Y'
AND T.CODE_CL = V.TASK_AREA_ID1) AS DIVISION2,
(SELECT SUBSTR(T.CODE_CL, 5)
FROM tc_code_value t
WHERE T.group_CL = 'TS2'
and t.use_at = 'Y'
AND T.CODE_CL = V.TASK_AREA_ID1) AS ATTRIBUTE1,
COUNT(V.CPR_NO) AS FACTORY_CNT,
'' AS FACTORY_PER,
SUM(V.SAN_REQUST_CUM_CO + V.WOO_REQUST_CUM_CO) AS SAN_SUM,
SUM(V.SAN_REQUST_ACTI_DTY_CO + V.WOO_REQUST_ACTI_DTY_CO) AS SAN_A,
SUM(V.SAN_REQUST_RSVDTY_CO + V.WOO_REQUST_RSVDTY_CO) AS SAN_B,
SUM(V.SAN_NOTDCD_RSVDTY_CO + V.WOO_NOTDCD_RSVDTY_CO) AS SAN_C,
SUM(V.HIGH_REQUST_CUM_CO) AS HIGH_SUM,
SUM(V.HIGH_REQUST_ACTI_DTY_CO) AS HIGH_A,
SUM(V.HIGH_REQUST_RSVDTY_CO) AS HIGH_B,
SUM(V.HIGH_NOTDCD_RSVDTY_CO) AS HIGH_C,
SUM(V.NINE_REQUST_CUM_CO) AS NINE_SUM,
SUM(V.NINE_REQUST_ACTI_DTY_CO) AS NINE_A,
SUM(V.NINE_REQUST_RSVDTY_CO) AS NINE_B,
SUM(V.NINE_NOTDCD_RSVDTY_CO) AS NINE_C,
SUM(V.ETC_REQUST_CUM_CO) AS ETC_SUM,
SUM(V.ETC_REQUST_ACTI_DTY_CO) AS ETC_A,
SUM(V.ETC_REQUST_RSVDTY_CO) AS ETC_B,
SUM(V.ETC_NOTDCD_RSVDTY_CO) AS ETC_C,
SUM(V.REQUST_CUM_CO) AS PERSONAL_CNT,
'' AS PERSONAL_PER
FROM V_TP_TASK01 V
WHERE V.TASK_TY = I_NEWNOLD_CODE -- X01008: 신규 / X01009: 기존
AND V.BSNS_YY = I_YEAR
GROUP BY V.TASK_AREA_ID1;
COMMIT;
ELSIF I_TYPE = 'C' --업종별
THEN
INSERT INTO TP_TASK_STATUS_RECEIPT R
(R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
R.DIVISION2,
R.ATTRIBUTE1,
R.FACTORY_CNT,
R.FACTORY_PER,
R.SAN_SUM,
R.SAN_A,
R.SAN_B,
R.SAN_C,
R.HIGH_SUM,
R.HIGH_A,
R.HIGH_B,
R.HIGH_C,
R.NINE_SUM,
R.NINE_A,
R.NINE_B,
R.NINE_C,
R.ETC_SUM,
R.ETC_A,
R.ETC_B,
R.ETC_C,
R.PERSONAL_CNT,
R.PERSONAL_PER)
SELECT I_YEAR AS YEAR,
I_TYPE AS TYPE,
I_NEWNOLD AS NEWNOLD,
I_TYPE || 'D1' AS DIVISION1,
DECODE(V.SPORT_REALM_CL,
NULL,
'기타',
DECODE(TRIM(V.SPORT_REALM_CL),
'',
'기타',
V.SPORT_REALM_CL)) AS DIVISION2,
'00' AS ATTRIBUTE1,
COUNT(V.CPR_NO) AS FACTORY_CNT,
'' AS FACTORY_PER,
SUM(V.SAN_REQUST_CUM_CO + V.WOO_REQUST_CUM_CO) AS SAN_SUM,
SUM(V.SAN_REQUST_ACTI_DTY_CO + V.WOO_REQUST_ACTI_DTY_CO) AS SAN_A,
SUM(V.SAN_REQUST_RSVDTY_CO + V.WOO_REQUST_RSVDTY_CO) AS SAN_B,
SUM(V.SAN_NOTDCD_RSVDTY_CO + V.WOO_NOTDCD_RSVDTY_CO) AS SAN_C,
SUM(V.HIGH_REQUST_CUM_CO) AS HIGH_SUM,
SUM(V.HIGH_REQUST_ACTI_DTY_CO) AS HIGH_A,
SUM(V.HIGH_REQUST_RSVDTY_CO) AS HIGH_B,
SUM(V.HIGH_NOTDCD_RSVDTY_CO) AS HIGH_C,
SUM(V.NINE_REQUST_CUM_CO) AS NINE_SUM,
SUM(V.NINE_REQUST_ACTI_DTY_CO) AS NINE_A,
SUM(V.NINE_REQUST_RSVDTY_CO) AS NINE_B,
SUM(V.NINE_NOTDCD_RSVDTY_CO) AS NINE_C,
SUM(V.ETC_REQUST_CUM_CO) AS ETC_SUM,
SUM(V.ETC_REQUST_ACTI_DTY_CO) AS ETC_A,
SUM(V.ETC_REQUST_RSVDTY_CO) AS ETC_B,
SUM(V.ETC_NOTDCD_RSVDTY_CO) AS ETC_C,
SUM(V.REQUST_CUM_CO) AS PERSONAL_CNT,
'' AS PERSONAL_PER
FROM V_TP_TASK01 V
WHERE V.TASK_TY = I_NEWNOLD_CODE -- X01008: 신규 / X01009: 기존
AND V.BSNS_YY = I_YEAR
GROUP BY V.SPORT_REALM_CL;
COMMIT;
ELSIF I_TYPE = 'D' --인원별
THEN
INSERT INTO TP_TASK_STATUS_RECEIPT R
(R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
R.DIVISION2,
R.ATTRIBUTE1,
R.FACTORY_CNT,
R.FACTORY_PER,
R.SAN_SUM,
R.SAN_A,
R.SAN_B,
R.SAN_C,
R.HIGH_SUM,
R.HIGH_A,
R.HIGH_B,
R.HIGH_C,
R.NINE_SUM,
R.NINE_A,
R.NINE_B,
R.NINE_C,
R.ETC_SUM,
R.ETC_A,
R.ETC_B,
R.ETC_C,
R.PERSONAL_CNT,
R.PERSONAL_PER)
SELECT I_YEAR AS YEAR,
I_TYPE AS TYPE,
I_NEWNOLD AS NEWNOLD,
I_TYPE || 'D1' AS DIVISION1,
decode(trunc(decode(V.EMPLY_CO, null, 0, V.EMPLY_CO) / 10),
0,
'5인이상 9인이하',
1,
'10인이상 19인이하',
2,
'20인이상 29인이하',
3,
'30인이상 39인이하',
4,
'40인이상 49인이하',
5,
'50인이상 59인이하',
6,
'60인이상 69인이하',
7,
'70인이상 79인이하',
8,
'80인이상 89인이하',
9,
'90인이상 99인이하',
'100인 이상') AS DIVISION2,
decode(trunc(decode(V.EMPLY_CO, null, 0, V.EMPLY_CO) / 10),
0,
'01',
1,
'02',
2,
'03',
3,
'04',
4,
'05',
5,
'06',
6,
'07',
7,
'08',
8,
'09',
9,
'10',
'11') AS ATTRIBUTE1,
COUNT(V.CPR_NO) AS FACTORY_CNT,
'' AS FACTORY_PER,
SUM(V.SAN_REQUST_CUM_CO + V.WOO_REQUST_CUM_CO) AS SAN_SUM,
SUM(V.SAN_REQUST_ACTI_DTY_CO + V.WOO_REQUST_ACTI_DTY_CO) AS SAN_A,
SUM(V.SAN_REQUST_RSVDTY_CO + V.WOO_REQUST_RSVDTY_CO) AS SAN_B,
SUM(V.SAN_NOTDCD_RSVDTY_CO + V.WOO_NOTDCD_RSVDTY_CO) AS SAN_C,
SUM(V.HIGH_REQUST_CUM_CO) AS HIGH_SUM,
SUM(V.HIGH_REQUST_ACTI_DTY_CO) AS HIGH_A,
SUM(V.HIGH_REQUST_RSVDTY_CO) AS HIGH_B,
SUM(V.HIGH_NOTDCD_RSVDTY_CO) AS HIGH_C,
SUM(V.NINE_REQUST_CUM_CO) AS NINE_SUM,
SUM(V.NINE_REQUST_ACTI_DTY_CO) AS NINE_A,
SUM(V.NINE_REQUST_RSVDTY_CO) AS NINE_B,
SUM(V.NINE_NOTDCD_RSVDTY_CO) AS NINE_C,
SUM(V.ETC_REQUST_CUM_CO) AS ETC_SUM,
SUM(V.ETC_REQUST_ACTI_DTY_CO) AS ETC_A,
SUM(V.ETC_REQUST_RSVDTY_CO) AS ETC_B,
SUM(V.ETC_NOTDCD_RSVDTY_CO) AS ETC_C,
SUM(V.REQUST_CUM_CO) AS PERSONAL_CNT,
'' AS PERSONAL_PER
FROM V_TP_TASK01 V
WHERE V.TASK_TY = I_NEWNOLD_CODE -- X01008: 신규 / X01009: 기존
AND V.BSNS_YY = I_YEAR
GROUP BY decode(trunc(decode(V.EMPLY_CO, null, 0, V.EMPLY_CO) / 10),
0,
'5인이상 9인이하',
1,
'10인이상 19인이하',
2,
'20인이상 29인이하',
3,
'30인이상 39인이하',
4,
'40인이상 49인이하',
5,
'50인이상 59인이하',
6,
'60인이상 69인이하',
7,
'70인이상 79인이하',
8,
'80인이상 89인이하',
9,
'90인이상 99인이하',
'100인 이상'),
decode(trunc(decode(V.EMPLY_CO, null, 0, V.EMPLY_CO) / 10),
0,
'01',
1,
'02',
2,
'03',
3,
'04',
4,
'05',
5,
'06',
6,
'07',
7,
'08',
8,
'09',
9,
'10',
'11');
COMMIT;
ELSE
COMMIT;
END IF;
INSERT INTO TP_TASK_STATUS_RECEIPT R
(R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
R.DIVISION2,
R.ATTRIBUTE1,
R.FACTORY_CNT,
R.FACTORY_PER,
R.SAN_SUM,
R.SAN_A,
R.SAN_B,
R.SAN_C,
R.HIGH_SUM,
R.HIGH_A,
R.HIGH_B,
R.HIGH_C,
R.NINE_SUM,
R.NINE_A,
R.NINE_B,
R.NINE_C,
R.ETC_SUM,
R.ETC_A,
R.ETC_B,
R.ETC_C,
R.PERSONAL_CNT,
R.PERSONAL_PER)
select R.YEAR,
R.TYPE,
R.NEWNOLD,
R.DIVISION1,
'합계' as DIVISION2,
'99' AS ATTRIBUTE1,
sum(R.FACTORY_CNT) as FACTORY_CNT,
sum(R.FACTORY_PER) as FACTORY_PER,
sum(R.SAN_SUM) as SAN_SUM,
sum(R.SAN_A) as SAN_A,
sum(R.SAN_B) as SAN_B,
sum(R.SAN_C) as SAN_C,
sum(R.HIGH_SUM) as HIGH_SUM,
sum(R.HIGH_A) as HIGH_A,
sum(R.HIGH_B) as HIGH_B,
sum(R.HIGH_C) as HIGH_C,
sum(R.NINE_SUM) as NINE_SUM,
sum(R.NINE_A) as NINE_A,
sum(R.NINE_B) as NINE_B,
sum(R.NINE_C) as NINE_C,
sum(R.ETC_SUM) as ETC_SUM,
sum(R.ETC_A) as ETC_A,
sum(R.ETC_B) as ETC_B,
sum(R.ETC_C) as ETC_C,
sum(R.PERSONAL_CNT) as PERSONAL_CNT,
sum(R.PERSONAL_PER) as PERSONAL_PER
from TP_TASK_STATUS_RECEIPT r
where r.year = I_YEAR
and r.type = I_TYPE
and r.newnold = I_NEWNOLD
and r.division1 = I_TYPE || 'D1'
group by R.YEAR, R.TYPE, R.NEWNOLD, R.DIVISION1;
COMMIT;
update TP_TASK_STATUS_RECEIPT R
set r.factory_per = r.factory_cnt /
(select k.factory_cnt
from TP_TASK_STATUS_RECEIPT k
where k.division2 = '합계'
and k.year = I_YEAR
and k.type = I_TYPE
and k.newnold = I_NEWNOLD
and k.division1 = I_TYPE || 'D1'),
r.personal_per = r.personal_cnt /
(select l.personal_cnt
from TP_TASK_STATUS_RECEIPT l
where l.division2 = '합계'
and l.year = I_YEAR
and l.type = I_TYPE
and l.newnold = I_NEWNOLD
and l.division1 = I_TYPE || 'D1')
where r.year = I_YEAR
and r.type = I_TYPE
and r.newnold = I_NEWNOLD
and r.division1 = I_TYPE || 'D1';
COMMIT;
--예외 처리
/*EXCEPTION
WHEN USER_DEFINE_EXCEPTION THEN
RAISE_APPLICATION_ERROR(-20001, USER_ERROR_MESSAGE);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, SQLERRM);*/
END ST_TP_TASK_STATUS;
-----------------------------------------------------------------------------------------------//
튜닝이 필요한 프로시저 인데요
한번 데이터를 받아오는데 걸리는 시간이 2시간이 넘습니다. 속도 개선을 하고싶습니다.
제가 보기엔 if ~ else 구문 안에 반복되는 셀렉트문이 문제가 되느거 같은데 어느 부분을 수정 해야할지 모르겠어요
힌트좀 부탁 드립니다
1) 개별 ROW INSERT (조건에 따라 분기)
2) 합계 ROW INSERT
3) 개별/합계값 계산하여 UPDATE
위에서 어느 부분이 오래 걸리는지 나눠서 확인해보시는게 좋겠지만
아무래도 마지막 UPDATE 부분이 의심스럽습니다.
아래 내용 적용 가능할지 한번 테스트해보세요.
1. 3단계 UPDATE문 제거하고 1단계에서 INSERT 할때 계산 (각 I_TYPE 모두 수정)
-- '' AS FACTORY_PER,
COUNT(V.CPR_NO) / SUM(COUNT(V.CPR_NO)) OVER () AS FACTORY_PER,
-- '' AS PERSONAL_PER
SUM(V.REQUST_CUM_CO) / SUM(SUM(V.REQUST_CUM_CO)) OVER () AS PERSONAL_PER
2. 불필요하다면 2단계 합계 처리부분도 제거
제거하거나 1단계에서 ROLLUP 계산
이 프로시저를 이전 사람이 짜놓고 나간거라 참 파악하기 힘드네요 .......업데이트 문 이 어디가 잘못된지 파악을 못하겠네요 ㅠㅠ
말씀 하신대로 수정해 봤는데 에러 나는거 같아여 ㅜㅜ
계산하는 로직인데
뷰 검증도 필요해보임
샐렉트절을 따로빼서 각각 셀렉트 하여서 시간 검증 해보심
또한, 업데이트절은 for loop 로 설렉트절로 하나만들어서
하단에 업데이트 절에 조건 밑 결과값만 받게 수정 필요
) 버튼을 클릭하여 작성 하시면 됩니다.