how to write Pl/SQL script

Hi ,

I have SQL script with 200 update .

My DB team ask me to write PL/SQl script using for loop ?

I am not understand what they asking ? can you tell me?
If possible then how to do that ?
LVL 20
Sathish David  Kumar NArchitectAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
Perhaps, your 200 update SQL script is viewed by the DBA as a very big transaction taking many resources. I think what they ask you is to write a cursor to update the table(s) row by row committing each row's update separately. This would minimise the load on the server.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I do not understand what you are asking.

What generated the script with 200 update statements?
If it was another script then you might be able to create a loop.

A PL/SQL loop is pretty simple and there are many examples on the web and even in the documentation.

For example:
begin
    for i in (select val1, val2, val3 from some_table) loop
          update some_other_table set col1=i.val1, col2=i.val2 where col3=i.val3;
   end loop;
   commit;
end;
/

Open in new window


Note: depending on the size of the transactions you might want to move the commit inside the for loop.  We cannot help with that since we do not know your database.
0
 
awking00Commented:
>>I have SQL script with 200 update .<<
Can you post that script?
0
All Courses

From novice to tech pro — start learning today.