Solved

how to write Pl/SQL script

Posted on 2014-12-17
4
194 Views
Last Modified: 2014-12-21
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
Comment
4 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 40506304
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40506861
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
 
LVL 31

Expert Comment

by:awking00
ID: 40509208
>>I have SQL script with 200 update .<<
Can you post that script?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now