我们使用如下方法测试一下:
SQL> select * from v$version;BANNER----------------------------------------------------------------------Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for 32-bit Windows: Version 11.2.0.1.0- ProductionNLSRTL Version 11.2.0.1.0 – ProductionSQL> UPDATE dave SET cust_tel = '0556'|| SUBSTR (cust_tel, 5) WHERE cust_tellike '0551%';2723 rows updated.SQL> rollback;Rollback complete.SQL> UPDATE dave SET cust_tel = '0556'|| SUBSTR (cust_tel, 5) WHERE cust_telin (select cust_tel from dave where cust_tel like '0551%');2723 rows updated.SQL> rollback;Rollback complete.
但如果在Oracle 11g中使用如下语法:
BEGINFOR cl IN (SELECT id from dave WHERE cust_tel LIKE '0551%')LOOPUPDATE daveSET cust_tel = '0556' || SUBSTR (cust_tel, 5)WHERE id = cl.id;END LOOP;END;
则会一致处于等待状态。
但是在Oracle 11g中,可以使用如下的游标来处理,这种方法也是我们之前的提高的根据rowid 来完成大量的Update操作:
DECLARECURSOR cur ISSELECT rowid as ROW_ID from dave WHEREcust_tel LIKE '0551%' ORDER BY ROWID; ---如果表的数据量不是很大,可以不用 order by rowidV_COUNTER NUMBER;BEGINV_COUNTER := 0;FOR row IN cur LOOPUPDATE daveSET cust_tel = '0556' || SUBSTR (cust_tel, 5)WHERE ROWID = row.ROW_ID;V_COUNTER := V_COUNTER + 1;IF (V_COUNTER>= 1000) THENCOMMIT;V_COUNTER := 0;END IF;END LOOP;COMMIT;END;