Re: Cursor performance

devjnr_at_gmail.com wrote:
> Are cursors a good way to execute dynamic sql and to loop on results?
>
> I mean:
>
> sql := 'select field........ where ....;'
>
> open Cursor for sql;
> loop
> Fetch Cursor into lvfield;
> Exit When Cursor%NOTFOUND;
>
> ...
>
> end loop
>
> Are there other better ways to do this?
>
> Can you point me to some papers that eventually discuss this subject?
>
> Thx.

Generally speaking:

1 - Never use an explicit cursor for what you can just do in a SQL statement

Example: instead of
open cursor
loop
  fetech
  exit when c1%notfound;
  insert into table values ...
end loop

do:
insert into table select .....

Also look out for where you process a cursor in a cursor, that is, where for every row in the outside cursor you open a second cursor, but you really do not do anything with the rows but combine the data. Write a single join statement instead (might be a cursor, but it will be one statement instead of two)

2 - If possible use static SQL with bind variables instead of dynamic SQL 3 - Use the bulk load and unload feature over single rows

4 - If you must use dynamic SQL you should probably be doing DDL instead of DML. Make sure that you can't substitute a static SQL statement with bind variables for the statement to get the same results. Or look at working with a Reference Cursor instead of passing back single rows to the application.

HTH -- Mark D Powell -- Received on Fri Sep 29 2006 - 08:35:46 CDT