■예 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;
/