时间戳

首页 > 解决方案 > 收藏品进入循环

sql - 收藏品进入循环

问题描述

我想更新表格，但是，集合进入循环。需要更新 500 000 条记录，但这需要很多时间..如果可以做点什么..

CREATE OR REPLACE PROCEDURE PROC_ACCOUNT_STATUS AS
  CURSOR C1 IS 
    SELECT ACCOUNTS1, 
           abs((PREVIOUS_DELINQUENCIES - CURRENT_DELINQUENCIES)) AS DIFF_DEL 
    FROM TEMP_LOAN;

  TYPE COLL_ACCOUNT_STATUS IS TABLE OF C1%ROWTYPE;
  COLL_STAB1 COLL_ACCOUNT_STATUS := COLL_ACCOUNT_STATUS();
  COLL_STAB2 COLL_ACCOUNT_STATUS := COLL_ACCOUNT_STATUS();
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT INTO COLL_STAB2 LIMIT 500;
    EXIT WHEN COLL_STAB2.COUNT = 0;
    COLL_STAB1 := COLL_STAB2;
  END LOOP;
  CLOSE C1;

  FOR I IN 1..COLL_STAB1.COUNT
  LOOP
    IF(COLL_STAB1(I).DIFF_DEL>=30) AND(COLL_STAB1(I).DIFF_DEL>=31) THEN
      COLL_STAB1.EXTEND();
      COLL_STAB1(COLL_STAB1.COUNT):=COLL_STAB1(I);
    END IF;
  END LOOP;

  FORALL I IN 1..COLL_STAB1.COUNT
    UPDATE TEMP_LOAN 
    SET ACCOUNT_STATUS = 'STAB' 
    WHERE ACCOUNTS1 = COLL_STAB1(I).ACCOUNTS1;

  COLL_STAB1.DELETE;
  COLL_STAB2.DELETE;
  COMMIT;
END;

标签： sqloracleperformance

解决方案

直接更新有什么问题？

update temp_loan
set ACCOUNT_STATUS = 'STAB' 
WHERE SUBSTR((PREVIOUS_DELINQUENCIES - CURRENT_DELINQUENCIES),2) >= 31

可能这不是您想要的答案，特别是如果（根据我的评论）您在问题中发布的逻辑不代表您实际正在实施的逻辑。

推荐阅读