[쿼리문/오라클] Oracle 저장 프로시저 샘플

프로그래밍이란 게 뭐든 샘플만 있으면 못할 게 없죠.

저장프로시져 만들때 샘플이 없어서 네이버를 찾아헤매던 기억이 있어서 샘플을 남깁니다.

 

CREATE OR REPLACE PROCEDURE YOUNG.DAILYSUM_DATA
 IS

    CURSOR cur IS
         SELECT CREATE_DATE, CUST_NO, COLLECT_AMT
         FROM YOUNG.T_COLLECT;
        
     CURSOR cur2 IS
         SELECT SUBSTR(CREATE_DATE,1,6) AS CREATE_DATE, CUST_NO, SUM(COLLECT_AMT) AS COLLECT_AMT
         FROM YOUNG.T_COLLECT GROUP BY SUBSTR(CREATE_DATE,1,6), CUST_NO;

BEGIN
     DBMS_OUTPUT.ENABLE;

    --고객일원장    
     FOR cur_list IN cur LOOP
         INSERT INTO YOUNG.T_DAILYSUM (IDATE, CUST_NO, COLLECT_AMT)
         VALUES (cur_list.CREATE_DATE, cur_list.CUST_NO, cur_list.COLLECT_AMT);
     END LOOP;
    
   --월원장
    FOR cur_list2 IN cur2 LOOP
        INSERT INTO YOUNG.T_MONTHSUM (IMONTH, CUST_NO, COLLECT_AMT)
        VALUES (cur_list2.CREATE_DATE, cur_list2.CUST_NO, cur_list2.COLLECT_AMT);
     END LOOP;   

    COMMIT;
    
     EXCEPTION
         WHEN OTHERS THEN
         ROLLBACK;
         DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

    DBMS_OUTPUT.PUT_LINE('데이터 입력 완료');

END DAILYSUM_DATA;

실행할 때는 이걸로...

 SET SERVEROUTPUT ON;
 EXEC YOUNG.DAILYSUM_DATA;