이상무 사항...

1. 커서를이용한 프로시져 생성 완료. (개발DB)

2. 잡스케쥴등록 완료. (개발DB)

3. 실제 잡스케쥴 돌고 데이터 정상적으로 변경 확인.

===================================================

이상사항.

1. 프로시져의 Update 수정내용의 빼먹은 부분이 있어 기존 프로시져 삭제 / 잡스케쥴삭제 하여 2가지 재생성.

   재생성한 프로시져 강제실행(토드의 번개모양 프로시져 익스큐트) or 프로시져 실행 쿼리로 실행했을때 에러발생. 에러내용은 아래 4가지와 같습니다.

   Error Msg : ORA-06550 : line 2, column 10;

                       PLS-00303 : component "MALLCRMN_UPDATE" must be declared

                       ORA-06550 : line 2, colum 3;

                       PL/SQL : Statement ignored

=================================================

제가 생성한 프로시져내용은 아래와 같습니다. 혹시 무언가를 빠뜨린건지....ㅠ.ㅠ. 조언부탁드리겠습니다. 선배님들

CREATE OR REPLACE PROCEDURE "MALLCRMN_UPDATE"
IS

    VCUSTOMER_CD VARCHAR2(20);
    VGO_PRICE VARCHAR2(20);
    VISA_PRICE VARCHAR2(20);
    VBONBU_PRICE VARCHAR2(20);

    CURSOR C1
    IS
SELECT M.COM_CODE,
       M.CREDIT_LIMIT+
       NVL((SELECT NVL(SUM(S1.REQUEST_CREDIT_AMOUNT),0) AS REQUEST_CREDIT_AMOUNT
         FROM TBL_CREDIT_REQUEST S1
        WHERE RECORD_STATUS <> 'D'
              AND S1.CUSTOMER_CODE = M.COM_CODE
              AND S1.REQUEST_CREDIT_STATUS = '079003'
              AND TO_CHAR(S1.EXPIRATION_DATE,'YYYYMMDD') >= TO_CHAR(SYSDATE,'YYYYMMDD')
        GROUP BY S1.CUSTOMER_CODE
       ),0) AS A, --총여신금액
       NVL((SELECT NVL(SUM(S2.REQUEST_CREDIT_AMOUNT),0) AS REQUEST_CREDIT_AMOUNT
         FROM TBL_CREDIT_REQUEST S2
        WHERE RECORD_STATUS <> 'D'
              AND S2.CONFIRM_GB = '002'
              AND S2.CUSTOMER_CODE = M.COM_CODE
              AND S2.REQUEST_CREDIT_STATUS = '079003'
              AND TO_CHAR(S2.EXPIRATION_DATE,'YYYYMMDD') >= TO_CHAR(SYSDATE,'YYYYMMDD')
        GROUP BY S2.CUSTOMER_CODE
       ),0) AS B, --추가여신(이사)
       NVL((SELECT NVL(SUM(S3.REQUEST_CREDIT_AMOUNT),0) AS REQUEST_CREDIT_AMOUNT
         FROM TBL_CREDIT_REQUEST S3
        WHERE RECORD_STATUS <> 'D'
              AND S3.CONFIRM_GB = '001'
              AND S3.CUSTOMER_CODE = M.COM_CODE
              AND S3.REQUEST_CREDIT_STATUS = '079003'
              AND TO_CHAR(S3.EXPIRATION_DATE,'YYYYMMDD') >= TO_CHAR(SYSDATE,'YYYYMMDD')
        GROUP BY S3.CUSTOMER_CODE
       ),0) AS C --추가여신(본부장)
  FROM MALLCRMN M,
       TBL_CREDIT_REQUEST R
 WHERE M.COM_CODE = R.CUSTOMER_CODE
       AND M.CREDIT_RATING_MON =
       (SELECT MAX(CREDIT_RATING_MON)
         FROM MALLCRMN
       )
       AND R.REQUEST_CREDIT_STATUS = '079003'
       AND R.EXPIRATION_DATE IS NOT NULL
       AND R.RECORD_STATUS <> 'D'
       AND SUBSTR(R.EXPIRATION_DATE,1,6) >= SUBSTR(SYSDATE,1,6)
 GROUP BY M.COM_CODE,
       M.CREDIT_LIMIT;
BEGIN
        OPEN C1;

    DBMS_OUTPUT.PUT_LINE('여신금액회수');
    LOOP
    FETCH C1 INTO VCUSTOMER_CD, VGO_PRICE, VISA_PRICE, VBONBU_PRICE;

        EXIT
    WHEN C1%NOTFOUND;
--        DBMS_OUTPUT.PUT_LINE(VEMP.총여신한도||''||VEMP.추가여신_이사||''||VEMP.추가여신_본부장);

        UPDATE MALLCRMN SET CREDIT_LIMIT_TOT = VGO_PRICE,
        CR_ADD_A_PRICE = VISA_PRICE,
        CR_ADD_B_PRICE = VBONBU_PRICE,
        CHANGE_DATE = TO_CHAR(SYSDATE,'yyyyMMdd'),
        CHANGE_TIME = TO_CHAR(SYSDATE,'HH24:MI:SS'),
        CHANGE_USER_ID = 'BATCH'
        WHERE CREDIT_RATING_MON = (SELECT MAX(CREDIT_RATING_MON) FROM MALLCRMN)
        AND COM_CODE = VCUSTOMER_CD;

    END LOOP;

      COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR');

END;
/