先看我们的数据：
SQL> select count(1) from dave where cust_tel like '0551%';
COUNT(1)
----------
2723
在我们的Dave表里，cust_tel 电话以0551 开头的有2723条记录，现在我们把这些记录换成0556.
我们使用如下方法测试一下：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0- Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> 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.
写法很多，关键要注意效率问题，尤其是要更新的数据量比较大时。
另测试发现Oracle10g 和11g在语法支持上的一些区别：
在Oracle 10g上可以使用如下语法：
BEGIN
FOR cl IN (SELECT object_name from d1 WHEREobject_type='TABLE')
LOOP
UPDATE d1
SET object_name = 'D' || SUBSTR (object_name, 2)
WHERE object_name = cl.object_name;
END LOOP;
END;
但如果在Oracle 11g中使用如下语法：
BEGIN
FOR cl IN (SELECT id from dave WHERE cust_tel LIKE '0551%')
LOOP
UPDATE dave
SET cust_tel = '0556' || SUBSTR (cust_tel, 5)
WHERE id = cl.id;
END LOOP;
END;
则会一致处于等待状态。
但是在Oracle 11g中，可以使用如下的游标来处理，这种方法也是我们之前的提高的根据rowid 来完成大量的Update操作：
DECLARE
CURSOR cur IS
SELECT rowid as ROW_ID from dave WHEREcust_tel LIKE '0551%' ORDER BY ROWID; ---如果表的数据量不是很大,可以不用order by rowid
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR row IN cur LOOP
UPDATE dave
SET cust_tel = '0556' || SUBSTR (cust_tel, 5)
WHERE ROWID = row.ROW_ID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER>= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
Weibo: http://weibo.com/tianlesoftware Email: tianlesoftware@gmail.com Skype: tianlesoftware