批量提交,试下这个方法:declare -- 数组变量,保存查询条件和更新列值 TYPE t_id IS TABLE OF jbxx2.id%TYPE; TYPE t_bh IS TABLE OF jbxx2.bh%TYPE;
v_t_id t_id; v_t_bh t_bh; CURSOR c IS SELECT id,bh FROM jbxx2; BEGIN OPEN c; LOOP -- 批量更新,一次更新10000条数据 fetch c bulk collect into v_t_id,v_t_bh LIMIT 10000; -- 这里用forall效率更高 FORALL i IN 1 .. v_t_id.COUNT UPDATE jbxx1 SET bh = v_t_bh(i) WHERE id = v_t_id(i);
-- 数组变量,保存查询条件和更新列值
TYPE t_id IS TABLE OF jbxx2.id%TYPE;
TYPE t_bh IS TABLE OF jbxx2.bh%TYPE;
v_t_id t_id;
v_t_bh t_bh; CURSOR c IS
SELECT id,bh FROM jbxx2;
BEGIN
OPEN c;
LOOP
-- 批量更新,一次更新10000条数据
fetch c bulk collect into v_t_id,v_t_bh LIMIT 10000;
-- 这里用forall效率更高
FORALL i IN 1 .. v_t_id.COUNT
UPDATE jbxx1 SET bh = v_t_bh(i) WHERE id = v_t_id(i);
-- 提交
COMMIT;
-- 循环退出
exit when c%NOTFOUND;
END LOOP;
COMMIT;
end;
另外象寻提到的可以用批量提交的方法处理数据。