Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor parameter or PL/SQL variable?
I've always found the first case (passing parameters) to be the best long term solution. It's easier for someone else to read, keep track of the parameters, and for reuse.
Erika Grondzakova wrote:
>
> Hello,
>
> I didn't find any discussions for advantages or disadvantage when
> calling a cursor with a cursor parameter or query in cursor refers to
> other PL/SQL variables within its scope.
>
> First case when cursor is calling with a parameter...
> PROCEDURE my_procedure(p_parameter IN NUMBER := NULL)
> IS
> CURSOR c_my_cursor(c_parameter NUMBER)
> IS
> SELECT name FROM my_table
> WHERE id = c_parameter;
>
> BEGIN
> FOR l_object IN c_my_cursor(p_parameter) LOOP
> ...
> END LOOP;
>
> END;
>
> Second case when query in cursor refers to procedure parameter...
> PROCEDURE my_procedure(p_parameter IN NUMBER := NULL)
> IS
> CURSOR c_my_cursor
> IS
> SELECT name FROM my_table
> WHERE id = p_parameter;
>
> BEGIN
> FOR l_object IN c_my_cursor LOOP
> ...
> END LOOP;
>
> END;
>
> Thamks in advance.
>
> Erika
-- --------------------------------------------------------------------- Chad Thompson, Programmer Analyst: VB, VC++, PLSQL, Oracle HRMS, Security Apps Home: thomp901_at_micron.net Thou shalt not tick off the dragon.... for thou art crunchy and taste good with ketchup. --------------------------------------------------------------------- --Received on Tue Aug 29 2000 - 21:22:36 CDT