Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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 ?
0
Sathish David  Kumar N
Asked:
Sathish David  Kumar N
2 Solutions
 
chaauCommented:
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) 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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now