¡á¿¹ 5_2_8 || ÀýÂ÷ÀûÀ¸·Î ÇÁ·Î±×·¡¹ÖµÈ ÇÁ·Î½ÃÀú
CREATE OR REPLACE PROCEDURE SP_DAILY_RESULT
/* Àϸ¶°¨ÀÛ¾÷ */
/* »ý¼ºÀÏ : */
/* »ý¼ºÀÚ : */
/* ³»¿ë : ȸ¿øÁ¦¼ºñ½ºÀÌ¿ë³»¿ª ÀÓ½ÃÅ×ÀÌºí¿¡ ÀúÀåµÈ ÀڷḦ °¡Á®¿Í */
/* ȸ¿øÁ¦¼ºñ½ºÀÌ¿ë³»¿ª Å×À̺í Table ¿¡ ¹Ý¿µ */
(S_CLICK_YMD IN MEMB_SERVICE_RESULT_WORK.CLICK_YMD%TYPE, -- ÆǸÅÁ¦ÇÑÀÏÀÚ.
S_P_SABUN IN MEMB_SERVICE_RESULT.P_SABUN%TYPE, -- °ü¸®ÀÚÀÇ »ç¹øÀ» °¡Á®¿Â´Ù
SERROR OUT VARCHAR2) AS
v_barcode MEMB_SERVICE_RESULT_WORK.BARCODE%TYPE;
v_click_ymd MEMB_SERVICE_RESULT_WORK.CLICK_YMD%TYPE;
v_product_cd PRODUCT_MAST.PRODUCT_CD%TYPE;
v_link_class PRODUCT_MAST.LINK_CLASS%TYPE;
v_cp_cd PRODUCT_MAST.CP_CD%TYPE;
n_read_cnt MEMB_SERVICE_RESULT.READ_CNT%TYPE;
n_loop_cnt NUMBER(8) :=1;
sExecuDescription VARCHAR2(500); --procedure°¡ ½ÇÇàµÇ´Â À§Ä¡¸¦
USER_EXCEPT EXCEPTION;
CURSOR SERVICE_RESULT_CURSOR IS
--ȸ¿øÁ¦¼ºñ½ºÀÌ¿ë³»¿ª ÀÓ½ÃÅ×À̺í
SELECT A.CLICK_YMD, A.BARCODE, TO_NUMBER(COUNT(A.BARCODE))
FROM MEMB_SERVICE_RESULT_WORK A
WHERE A.CLICK_YMD = S_CLICK_YMD
AND A.PRODUCT_CD IN ('004','005','007','008' )
GROUP BY A.CLICK_YMD, A.BARCODE;
BEGIN
OPEN SERVICE_RESULT_CURSOR;
LOOP
FETCH SERVICE_RESULT_CURSOR
INTO v_click_ymd, v_barcode, n_read_cnt;
EXIT WHEN SERVICE_RESULT_CURSOR%NOTFOUND;
IF n_loop_cnt = 1 THEN
/* ȸ¿øÁ¦¼ºñ½ºÀÌ¿ë³»¿ª ÀÚ·á»èÁ¦ */
sExecuDescription := '(Target Table : MEMB_SERVICE_RESULT '||' SALE_YMD : '||v_click_ymd;
DELETE
FROM MEMB_SERVICE_RESULT
WHERE SALE_YMD = S_CLICK_YMD;
END IF;
/* »óÇ°¸¶½ºÅÍ¿¡¼ ÀÚ·áÁ¶È¸ */
sExecuDescription := '(Target Table : PRODUCT_MAST '||' BARCODE : '||v_barcode;
SELECT PRODUCT_CD, LINK_CLASS, CP_CD
INTO v_product_cd, v_link_class, v_cp_cd
FROM PRODUCT_MAST
WHERE BARCODE = v_barcode;
/* ȸ¿øÁ¦¼ºñ½ºÀÌ¿ë³»¿ª µî·Ï */
sExecuDescription := '(Target Table : MEMB_SERVICE_RESULT '||' SALE_YMD : '||v_click_ymd||
' BARCODE : '||v_barcode;
INSERT
INTO MEMB_SERVICE_RESULT
(SALE_YMD, BARCODE, READ_CNT, PRODUCT_CD,
LINK_CLASS, CP_CD, P_DATE, P_SABUN,
UPPER_LINK_CLASS)
VALUES (v_click_ymd, v_barcode, n_read_cnt, v_product_cd,
v_link_class, v_cp_cd, SYSDATE, S_P_SABUN,
SUBSTR(NVL(v_link_class,'00'),1,2));
n_loop_cnt := n_loop_cnt + 1;
END LOOP;
CLOSE SERVICE_RESULT_CURSOR;
COMMIT;
--Stored Procedure LogÈÀÏ »ý¼º..
SP_LOGWRITE('SP_DAILY_SERVICE_RESULT_001','»ý¼ºÀÏÀÚ:'||S_CLICK_YMD, NULL,SERROR, SERROR);
--raise USER_EXCEPT;
EXCEPTION
WHEN USER_EXCEPT THEN
ROLLBACK;
SERROR := 'user exception';
RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||' USER_EXCEPT ');
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
SERROR := 'DATA DUPLICATE!! error code:'||SQLCODE;
RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA DUPLICATE ERROR!!
error code:'||SQLCODE);
WHEN NO_DATA_FOUND THEN
ROLLBACK;
SERROR := 'DATA NOT FOUND!! error code:'||SQLCODE;
RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA NOT FOUND ERROR!!
error code:'||SQLCODE);
WHEN OTHERS THEN
ROLLBACK;
SERROR := '´ÜÀ§¾÷¹« DATA OTHERS ERROR!! error code:'||SQLCODE;
RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA OTHERS ERROR!!
error code:'||SQLCODE);
END SP_DAILY_RESULT;
/