Test the table update function by concatenating strings in Oracle.

 

First look at our data:

SQL> select count (1) from Dave where cust_tel like '2013 ';

 

Count (1)

----------

2723

 

In our Dave table, cust_tel had 0551 records starting with 2723. Now we have changed these records to 0556.

 

Let's test it using the following method:

SQL> select * from V $ version; </P> <p> banner <br/> production <br/> Oracle Database 11g enterprise editionrelease 11.2.0.1.0-production <br/> PL/SQL release 11.2.0.1.0-production <br/> core 11.2.0.1.0 production <br/> TNS for 32-bit windows: version 11.2.0.1.0-production <br/> nlsrtl version 11.2.0.1.0-production </P> <p> SQL> Update Dave set cust_tel = '000000' | substr (cust_tel, 5) where cust_tellike '000000'; <br/> 0551% rows updated. </P> <p> SQL> rollback; <br/> rollback complete. </P> <p> SQL> Update Dave set cust_tel = '000000' | substr (cust_tel, 5) where cust_telin (select cust_tel from Dave where cust_tel like '000000 '); <br/> 2723 rows updated. </P> <p> SQL> rollback; <br/> rollback complete.

There are many writing methods, and the key is to pay attention to efficiency issues, especially when the data volume to be updated is large.

 

In addition, we found some differences in syntax support between Oracle10g and 11g:

 

You can use the following syntax on Oracle 10 GB:

Begin <br/> for Cl in (select object_name from D1 whereobject_type = 'table ') <br/> loop <br/> Update D1 <br/> set object_name = 'D' | substr (object_name, 2) <br/> where object_name = Cl. object_name; <br/> end loop; <br/> end;

 

However, if the following syntax is used in Oracle 11g:

Begin <br/> for Cl in (select ID from Dave where cust_tel like '20140901 ') <br/> loop <br/> Update Dave <br/> set cust_tel = '000000' | substr (cust_tel, 5) <br/> where id = Cl. ID; <br/> end loop; <br/> end;

 

It is in the waiting state.

 

However, in Oracle 11g, you can use the following cursor for processing. This method is also our previous improvement based on rowid to complete a large number of update operations:

Declare <br/> cursor cur is <br/> select rowid as row_id from Dave wherecust_tel like '000000' order by rowid; --- if the table data volume is not large, order by rowid <br/> v_counter number; <br/> begin <br/> v_counter: = 0; <br/> for row in cur loop <br/> Update Dave <br/> set cust_tel = '000000' | substr (cust_tel, 5) <br/> where rowid = row. row_id; <br/> v_counter: = v_counter + 1; <br/> If (v_counter> = 1000) Then <br/> commit; <br/> v_counter: = 0; <br/> end if; <br/> end loop; <br/> commit; <br/> end;

 

 

For details about this method, refer:

Oracle uses rowid to improve update Performance

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

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

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

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 dba8 group: 102954821