合 将游标修改为建表来提高效率
记录日期: 2014-07-30 14:25:27
题记:
最近一个同事说一个更新语句很慢求助与我,我看了下,这类语句的优化具有典型的代表性,于是记录下来和大家共享下。
原sql语句:
DECLARE
V_PARTYNO VARCHAR2(20);
V_APP_MP VARCHAR2(20);
V_INTIME DATE;
V_CNT INT;
BEGIN
FOR CUR IN (SELECT APPLICATION_NO FROM FRAUD_SCORE) LOOP
SELECT MAX(PARTY_NO),
MAX(MOBILE_PHONE_NO),
MAX(IMPORT_CPPCDM_TIME)
INTO V_PARTYNO,
V_APP_MP,
V_INTIME
FROM RISKREPT.RKO_CDM_PROCESS
WHERE APPLICATION_NO = CUR.APPLICATION_NO;
SELECT COUNT(1)
INTO V_CNT
FROM RISKREPT.RKO_CDM_PROCESS
WHERE PARTY_NO = V_PARTYNO
AND MONTHS_BETWEEN(V_INTIME,
IMPORT_CPPCDM_TIME) <= 12;
UPDATE FRAUD_SCORE
SET APP_LOANCARD_NUM = V_CNT
WHERE APPLICATION_NO = CUR.APPLICATION_NO;
COMMIT;
END LOOP;
INSERT INTO FRAUD_SCORE_LOG
(VAR_NAME,
FINISH_TIME,
SEQ_NO)
VALUES
('APP_LOANCARD_NUM',