1、大批量的更新update表的数据
DECLARE
CURSOR CUR_B IS
SELECT COUNT(1)
FROM BatchCode A, BatchCode_dpp_201709071800 B
where A.entid = B.entid
and A.angleid = B.angleid
and A.goodsid = B.goodsid
and A.batchcode = B.batchcode
and A.valdate = B.valdate
ORDER BY A.ROWID; ---如果表的数据量不是很大,可以不用 order by rowid
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR ROW_B IN CUR_B LOOP
UPDATE BatchCode
SET valdate = ROW_B.valdate
WHERE ROWID = ROW_B.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
2、大批量的插入数据
方法1:
用Hint 提示减少操作时间
INSERT /*+Append*/ INTO tab1
SELECT * FROM tab2;
采用不写日志及使用Hint提示减少数据操作的时间。