이상무 사항...
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;
/ |