-----对于普通表
实现: UPDATE T_PM_DEPOSIT_HIS b
SET flag = SUBSTR( flag, 1, 8 )||'4'||
CASE
WHEN term <= 365
THEN '1'
ELSE '2'
END AS flag
WHERE b.data_date>=20130101
DECLARE
CURSOR cur IS
SELECT
b.ROWID ROW_ID
FROM T_PM_DEPOSIT_HIS b
where b.data_date>=20130101
ORDER BY b.ROWID; ---如果表的数据量不是很大,可以不用 order by rowid
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE T_PM_DEPOSIT_HIS b
SET flag = SUBSTR( flag, 1, 8 )||'4'||
CASE
WHEN term <= 365
THEN '1'
ELSE '2'
END AS flag
WHERE b.data_date>=20130101 and ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 10000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;---对于分区表
可以根据分区键,拆成多个,进行并发更新。---测试更新94G的分区表,花了6个小时的样子悲剧。。。。。。。。。。。