RETURN VARCHAR2 IS V_RETURN_VALUE VARCHAR2(100) := ''; BEGIN SELECT CASE WHEN IN_GBCD = '1' THEN --등록여부한글 CASE WHEN MAX(DEUNGROKINJEONG) = '1' THEN '인정' WHEN NVL(D.DEUNGROKGEUM_GYE,0) <= NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN '등록' WHEN NVL(H.HWANBUL_AMT,0) > 0 AND NVL(D.DEUNGROKGEUM_GYE,0) > NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN '환불' WHEN NVL(D.DEUNGROKGEUM_GYE,0) > NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN '미등록' END --납부등록금 WHEN IN_GBCD = '2' THEN TRIM(TO_CHAR(NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) )) --납부구분코드(Y/N) WHEN IN_GBCD = '3' THEN CASE WHEN MAX(DEUNGROKINJEONG) = '1' THEN 'Y' WHEN NVL(D.DEUNGROKGEUM_GYE,0) <= NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN 'Y' WHEN NVL(H.HWANBUL_AMT,0) > 0 AND NVL(D.DEUNGROKGEUM_GYE,0) > NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN 'N' WHEN NVL(D.DEUNGROKGEUM_GYE,0) > NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) THEN 'N' END --납부일자 WHEN IN_GBCD = '4' THEN MAX(N.DEUNGROKGEUM_NAPBU_DT) --납부구분코드(Y/N) WHEN IN_GBCD = '5' THEN CASE WHEN MAX(DEUNGROKINJEONG) = '1' THEN 'Y' WHEN NVL(SUM(N.NAPBU_DEUNGROKGEUM_GYE),0) - NVL(H.HWANBUL_AMT,0) > 0 THEN 'Y' ELSE 'N' END END AS RESULT INTO V_RETURN_VALUE FROM (SELECT D.YEAR , D.HAKGI , D.HAKBEON , D.DAESANG_GBCD , NVL(MAX(D.DEUNGROKINJEONG_YN),'0') AS DEUNGROKINJEONG , SUM(D.DEUNGROKGEUM_GYE) AS DEUNGROKGEUM_GYE FROM HR_DEUNGROK_DAESANGJA_M D WHERE YEAR = IN_YEAR AND HAKGI = IN_HAKGI AND HAKBEON = IN_HAKBEON AND DAESANG_GBCD NOT IN ('10') GROUP BY D.YEAR , D.HAKGI , D.HAKBEON , D.DAESANG_GBCD ) D LEFT OUTER JOIN HR_NAPBU_MST_M N ON D.YEAR = N.YEAR AND D.HAKGI = N.HAKGI AND D.HAKBEON = N.HAKBEON AND D.DAESANG_GBCD = N.DAESANG_GBCD LEFT OUTER JOIN ( SELECT H.YEAR , H.HAKGI , H.HAKBEON , H.DAESANG_GBCD , SUM(H.HWANBUL_GYE) AS HWANBUL_AMT FROM HR_HWANBUL_M H WHERE YEAR = IN_YEAR AND HAKGI = IN_HAKGI AND HAKBEON = IN_HAKBEON GROUP BY H.YEAR , H.HAKGI , H.HAKBEON , H.DAESANG_GBCD) H ON N.YEAR = H.YEAR AND N.HAKGI = H.HAKGI AND N.HAKBEON = H.HAKBEON AND N.DAESANG_GBCD = H.DAESANG_GBCD WHERE D.YEAR = IN_YEAR AND D.HAKGI = IN_HAKGI AND D.HAKBEON = IN_HAKBEON GROUP BY D.YEAR , D.HAKGI , D.HAKBEON , DEUNGROKGEUM_GYE , H.HWANBUL_AMT ; RETURN V_RETURN_VALUE; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('데이터없음'); RETURN ''; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OTHERS'); RETURN ''; END; /
IN값을 던져서 리턴값을 확인하는 FUNCTION 인대요 속도저하가 너무 큽니다만.
데이터가 2400건밖에안돼는데 12초나 걸리네요 . PK값싹다 맞춰서 3~4초 빨라지긴 했는데 너무 느립니다 .
혹시 퀴리에 문제가 있는걸까요 ? 질무좀드립니다