We have created a awesome themeFar far away,behind the word mountains, far from the countries
INFO ½ÇÀü DB¸ðµ¨¸µ°ú SQL°úÁ¤
¡á¿¹ 5_2_9 || ÀýÂ÷ÀûÀ¸·Î ÇÁ·Î±×·¡¹ÖÀ» ÇÑ °³ÀÇ SQL·Î ÀÛ¼ºÇÑ ÇÁ·Î½ÃÀú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
sExecuDescription VARCHAR2(500); --procedure°¡ ½ÇÇàµÇ´Â À§Ä¡¸¦USER_EXCEPT EXCEPTION;BEGIN DELETE FROM MEMB_SERVICE_RESULT WHERE SALE_YMD = S_CLICK_YMD; INSERT INTO MEMB_SERVICE_RESULT (PARTNER_CD,SALE_YMD,BARCODE,SUB_BARCODE,PRODUCT_CD,MENU_GB,LINK_CLASS, UPPER_LINK_CLASS,READ_CNT,CP_CD,P_DATE,P_SABUN)____SELECT '00001', A.CLICK_YMD, A.BARCODE, A.SUB_BARCODE, DECODE(A.MENU_GB,'100','100',B.PRODUCT_CD) PRODUCT_CD, A.MENU_GB, B.LINK_CLASS, SUBSTR(NVL(B.LINK_CLASS,'00'),1,2) UPPER_LINK_CLASS, A.READ_CNT, B.CP_CD, SYSDATE, S_P_SABUN FROM( SELECT A.CLICK_YMD, A.BARCODE, A.SUB_BARCODE, A.MENU_GB, TO_NUMBER(COUNT(A.BARCODE)) READ_CNT FROM____ MEMB_SERVICE_RESULT_WORK A, MEMB_BASE B WHERE A.CLICK_YMD = S_CLICK_YMD AND ( A.MENU_GB ='004' OR A.MENU_GB ='005' OR A.MENU_GB = '011' OR A.MENU_GB = '100') AND A.MEMB_NO = B.MEMB_NO AND B.MEMB_GB <> '005' --¹«·áȸ¿øÀº °É·¯³½´Ù GROUP BY A.CLICK_YMD, A.BARCODE, A.SUB_BARCODE,A.MENU_GB ) A, PRODUCT_MAST B WHERE A.BARCODE = B.BARCODE );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; SP_ERRLOGWRITE('SP_DAILY_SERVICE_RESULT_001','ÆǸÅÀÏÀÚ:'||S_CLICK_YMD, S_P_SABUN,'ERR', SQLERRM,SERROR); SERROR := 'DATA DUPLICATE!! error code:'||SQLCODE; RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA DUPLICATE ERROR!! error code:'||SQLCODE); WHEN NO_DATA_FOUND THEN ROLLBACK; SP_ERRLOGWRITE('SP_DAILY_SERVICE_RESULT_001','ÆǸÅÀÏÀÚ:'||S_CLICK_YMD, S_P_SABUN,'ERR', SQLERRM,SERROR); SERROR := 'DATA NOT FOUND!! error code:'||SQLCODE; RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA NOT FOUND ERROR!! error code:'||SQLCODE);WHEN OTHERS THEN ROLLBACK; SP_ERRLOGWRITE('SP_DAILY_SERVICE_RESULT_001','ÆǸÅÀÏÀÚ:'||S_CLICK_YMD, S_P_SABUN,'ERR', SQLERRM,SERROR); SERROR := '´ÜÀ§¾÷¹« DATA OTHERS ERROR!! error code:'||SQLCODE; RAISE_APPLICATION_ERROR ( -20001, sExecuDescription ||'->DATA OTHERS ERROR!! error code:'||SQLCODE);END SP_DAILY_RESULT;/