안녕하세요. 아래의 프로시져를 커서를 이용해 생성을 하는도중에 에러가 생기네요.. 큰문제는 없어보이는데..
찾기가 힘드네요.....
=======================================================
CREATE OR REPLACE PROCEDURE STREAM.SWIS IS
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,
(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
) AS B,
(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
) 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;
C1_REC C1%ROWTYPE;
OPEN C1;
LOOP
FETCH C1 INTO C1_REC;
EXIT
WHEN C1%NOTFOUND;
UPDATE MALLCRMN
SET CREDIT_LIMIT_TOT = VGO_PRICE,
CR_ADD_A_PRICE = VISA_PRICE,
CR_ADD_B_PRICE = VBONBU_PRICE
WHERE CREDIT_RATING_MON =
(SELECT MAX(CREDIT_RATING_MON)
FROM MALLCRMN
)
AND COM_CODE = VCUSTOMER_CD;
END LOOP;
CLOSE C1;
COMMIT;
END; |