使用bulk 来进行update 分批提交是一种比较高效可控的大量update 的方式,脚本如下:
DECLARE
CURSOR cur
IS
SELECT *
FROM SCOTT.EMP1
WHERE COMM IS NULL;
TYPE rec IS TABLE OF SCOTT.EMP1%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE)
LOOP
FETCH cur BULK COLLECT INTO recs LIMIT 100;
FORALL i IN 1 .. recs.COUNT
UPDATE SCOTT.EMP1 T
SET T.COMM = recs (i).SAL
WHERE T.SAL = recs (i).SAL AND T.COMM IS NULL;
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/