Oracle 用拼接字串更新表 測試

roominess發表於2012-03-30
Oracle字串

先看我們的資料：

SQL> select count(1) from dave where cust_tel like '0551%';

 

 COUNT(1)

----------

     2723

 

在我們的Dave表裡，cust_tel 電話以0551 開頭的有2723條記錄，現在我們把這些記錄換成0556.

 

我們使用如下方法測試一下：

[sql] view plaincopy
<embed>
  1. SQL> select * from v$version;  
  2.    
  3. BANNER  
  4. ----------------------------------------------------------------------  
  5. Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production  
  6. PL/SQL Release 11.2.0.1.0 - Production  
  7. CORE   11.2.0.1.0      Production  
  8. TNS for 32-bit Windows: Version 11.2.0.1.0- Production  
  9. NLSRTL Version 11.2.0.1.0 – Production  
  10.    
  11. SQL> UPDATE dave SET cust_tel = '0556'|| SUBSTR (cust_tel, 5) WHERE  cust_tellike '0551%';  
  12. 2723 rows updated.  
  13.    
  14. SQL> rollback;  
  15. Rollback complete.  
  16.    
  17. SQL> UPDATE dave SET cust_tel = '0556'|| SUBSTR (cust_tel, 5) WHERE  cust_telin (select cust_tel from dave where cust_tel like '0551%');  
  18. 2723 rows updated.  
  19.    
  20. SQL> rollback;  
  21. Rollback complete.  

寫法很多，關鍵要注意效率問題，尤其是要更新的資料量比較大時。

 

另測試發現Oracle10g 和11g 在語法支援上的一些區別：

 

在Oracle 10g上可以使用如下語法：

[sql] view plaincopy
<embed>
  1. BEGIN  
  2.    FOR cl IN (SELECT object_name from d1 WHEREobject_type='TABLE')  
  3.    LOOP  
  4.       UPDATE d1  
  5.          SET object_name = 'D' || SUBSTR (object_name, 2)  
  6.        WHERE object_name = cl.object_name;  
  7.    END LOOP;  
  8. END;  


 

但如果在Oracle 11g中使用如下語法：

[sql] view plaincopy
<embed>
  1. BEGIN  
  2.    FOR cl IN (SELECT id from dave WHERE cust_tel LIKE '0551%')  
  3.    LOOP  
  4.       UPDATE dave  
  5.          SET cust_tel = '0556' || SUBSTR (cust_tel, 5)  
  6.        WHERE id = cl.id;  
  7.    END LOOP;  
  8. END;  


 

則會一致處於等待狀態。

 

但是在Oracle 11g中，可以使用如下的遊標來處理，這種方法也是我們之前的提高的根據rowid 來完成大量的Update操作：

[sql] view plaincopy
<embed>
  1. DECLARE  
  2.   CURSOR cur IS  
  3.     SELECT rowid as ROW_ID from dave WHEREcust_tel LIKE '0551%' ORDER BY ROWID;  ---如果表的資料量不是很大,可以不用 order by rowid  
  4.   V_COUNTER NUMBER;  
  5. BEGIN  
  6.   V_COUNTER := 0;  
  7.   FOR row IN cur LOOP  
  8.     UPDATE dave  
  9.        SET cust_tel = '0556' || SUBSTR (cust_tel, 5)  
  10.      WHERE ROWID = row.ROW_ID;  
  11.    V_COUNTER := V_COUNTER + 1;  
  12.     IF (V_COUNTER>= 1000) THEN  
  13.       COMMIT;  
  14.      V_COUNTER := 0;  
  15.     END IF;  
  16.   END LOOP;  
  17.  COMMIT;  
  18. END;  


 

 

有關這種方法的具體說明參考：

Oracle 利用 rowid 提升 update 效能

http://blog.csdn.net/tianlesoftware/article/details/6576156

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-719945/，如需轉載，請註明出處，否則將追究法律責任。

相關文章