Re: Can I write this without using Cursors?

SatarNag wrote:
>
> 29-July-1997
>
> Hi,
> We are using Oracle 7.3, and are doing huge transactions (updates,
> inserts, ect..). I was wondering if there was a way to re-write this
> program without using cursors, So that we may increase the efficiency.
[code snipped]

I'm not a DBA so take my comments for what you think they are worth. The code you show is fairly straight-forward and simple. Just glancing over it, I think you could do it all with a single update with subqueries within subqueries.

But why? This looks like something that is run during the evening and, really, is efficiency that important to you? Look at what you would be losing:

1 - Maintainability: just try going into an update statement with multi-level subqueries and make a change without breaking it. 2 - Control: you are able to control how often the updates are commited. If something should go wrong, you lose changes only from the last commit. With a single statement, you are in an all-or-nothing situation.
3 - Robustness: closely related to Maintainability. How easy could you make it handle underlying changes in the database?

Assuming your tables are adaquately indexed and that you have taken whatever other steps you can to increase efficiency, I would leave well enough alone. The only change I would make (other than a *few* nit-picky changes to the code) would be to make it a stored procedure. That way, at least, you save having to recompile it every time you run it.

Of course, I really don't know your situation and you may not have much choice in the matter. In that case...never mind!

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"