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;

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 = '000000' | SUBSTR (cust_tel, 5) WHERE cust_tellike '000000 ';

2723 rows updated.

SQL> rollback;

Rollback complete.

SQL> UPDATE dave SET cust_tel = '000000' | SUBSTR (cust_tel, 5) WHERE cust_telin (select cust_tel from dave where cust_tel like '2016 ');

2723 rows updated.

SQL> rollback;

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

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;

 

 

 

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

 

BEGIN

FOR cl IN (SELECT id from dave WHERE cust_tel LIKE '2013 ')

LOOP

UPDATE dave

SET cust_tel = '000000' | SUBSTR (cust_tel, 5)

WHERE id = cl. id;

End loop;

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

CURSOR cur IS

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 is not required.

V_COUNTER NUMBER;

BEGIN

V_COUNTER: = 0;

FOR row IN cur LOOP

UPDATE dave

SET cust_tel = '000000' | 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