We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 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 AWHERE A.CLICK_YMD = S_CLICK_YMDAND 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;/