Home » SQL & PL/SQL » SQL & PL/SQL » How to aviod usage of cursor and improve performance
How to aviod usage of cursor and improve performance [message #314958] Fri, 18 April 2008 06:44 Go to next message
sujatha.tammineni
Messages: 1
Registered: April 2008
Junior Member
My problem statement goes something like this...

I need to buy N(suppose it is 50) books. I have a stores tables which gives number of books each store has.
I need to buy these N books from first M stores.

I used cursors at present & iterate on My query would be like
create cursor stores_cursor as
select store, number from stores order by store.

whileN>0
N=N-quantity in store n1
ifN<0
quantity in store -= N
end if
end while

My problem is cursor, is becoming heavy in my application.
can I do the same without using cursor?
Re: How to aviod usage of cursor and improve performance [message #314961 is a reply to message #314958] Fri, 18 April 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SUM in its analytic form.

Regards
Michel
Re: How to aviod usage of cursor and improve performance [message #314974 is a reply to message #314961] Fri, 18 April 2008 07:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And search for PUZZLES in this forum - I seem to remember one like that.

Ross Leishman
Re: How to aviod usage of cursor and improve performance [message #314982 is a reply to message #314958] Fri, 18 April 2008 08:02 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before you have to define "first", of course.

Regards
Michel
Previous Topic: Assigning cursor to cursor?
Next Topic: Problem with FULL OUTER JOIN
Goto Forum:
  


Current Time: Thu Dec 08 22:05:15 CST 2016

Total time taken to generate the page: 0.06081 seconds