oracle动态拼接表,Oracle 用拼接字符串更新表 测试

先看我们的数据：

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;

有关这种方法的具体说明参考：

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog:   http://blog.csdn.net/tianlesoftware

Weibo:            http://weibo.com/tianlesoftware

Skype:            tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474  DBA8群：102954821