Posted in 2016

How to Update data 10 at a time..

SYS> create user test identified by test default tablespace users ;

User created.

SYS> grant connect, resource to test ;

Grant succeeded.

SYS> grant execute on dbms_lock to test ;

Grant succeeded.

SYS> alter user test quota unlimited on users ;

User altered.

TEST> create table data (laenge number, breite number, shape number) ;

Table created.

TEST> insert into data (laenge,breite) select dbms_random.value(1,100),dbms_random.value(100,200) from dual connect by level<=100;
commit;

CREATE or REPLACE procedure EXECUTE_GEOMETRY_BUILDING_TEST
IS
CURSOR c_geodata IS SELECT LAENGE, BREITE, SHAPE FROM data ;
TYPE t_geodata_table IS TABLE OF c_geodata%rowtype INDEX by PLS_INTEGER ;
values_geodata t_geodata_table ;
BEGIN
open c_geodata ;
loop
    fetch c_geodata BULK COLLECT INTO values_geodata LIMIT 10 ;

EXIT WHEN
    values_geodata.COUNT = 0;  

for idx in 1..values_geodata.count
LOOP
    UPDATE data
    SET SHAPE = 1000 where laenge=values_geodata(idx).laenge and breite=values_geodata(idx).breite ;
/* here I update only that rows which I selected */
END LOOP ;
commit ; /* commit so I can see them in a second session, and maybe reuse them */
dbms_lock.sleep(20) ; 

END LOOP ;
close c_geodata ;

END EXECUTE_GEOMETRY_BUILDING_TEST;
/
Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s