1.只适用于大量数据,小量数据效率比update慢
declare
type rowid_list is table of urowid index by binary_integer;
rowid_infosrowid_list;
i number;
cursor c_rowids is select rowid from t_target;
begin
open c_rowids;
loop
fetch c_rowidsbulk collect into rowid_infos limit 2000;
foralli in 1..rowid_infos.count
update t_target set owner=to_char(length(owner)+1)
where rowid=rowid_infos(i);
commit;
exit when rowid_infos.count<2000; -- 1w-5w;
end loop;
close c_rowids;
end;
2.设置nologging属性。在修改之后添加
alter table table_name logging;
3.
declare
TYPE T_MW IS TABLE OF ROWID;
T_T_MW T_MW;
CURSOR V_CUR IS
SELECT ROWID FROM t_bankdata;
BEGIN
OPEN V_CUR;
LOOP
FETCH V_CUR BULK COLLECT
INTO T_T_MW LIMIT 80000;
FOR C1 IN 1 .. T_T_MW.COUNT LOOP
UPDATE t_bankdata NOLOGGING
SET msdate = '2016-02-22 11:10:10'
WHERE ROWID = T_T_MW(C1);
END LOOP;
COMMIT;
EXIT WHEN V_CUR%NOTFOUND;
END LOOP;
CLOSE V_CUR;
--COMMIT;
END;