Re: cursor looping - best practices?

On Jun 15, 11:00 am, spacemar..._at_mailinator.com wrote:
> question,
>
> is there a best practice philosophy on looping thru a cursor of
> results in SQL (to tally things and whatnot)? im just curious if its
> considered something to be avoided, or a perfectly acceptable
> practice?
>
> thanks,
> sm

In general ( and with caveats expressed by others later in the postings triggered by this question ) you want to use oracle "if you can" in this manner.

1. Give all the necessary work to the database server in 1 statement. Oracle 9i and 10g introduced some significant enhancements to aggregation capabilities well beyond the sum, max, etc basic stuff.

No you by all means don't want to loop thru the results of a cursor tallying up totals.

2) do it all in straight sql if you possibly can ... avoid plsql overhead where not necessary.

There are others that follow this general theme. Tom Kyte is one of the principal people responsible for driving and directing this philosophy so consider buying and reading several times his most recent books. Received on Fri Jun 15 2007 - 12:51:49 CDT