Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor parameter or PL/SQL variable?
Those with formal computer science training may prefer your first approach; those without prefer the second.
In an isolated case, it doesn't make any difference. In fact the first code may run slightly more slowly due to the parameter passing overhead. If you want to pass different values to c_parameter in one run of your procedure, obviously the first code is much better because you don't have to create new cursors simply changing the id number passed in.
Yong Huang
yhuang_at_indigopool.com
Erika Grondzakova <Erika.Grondzakova_at_cern.ch> wrote in message
news:39AA33D3.F2A89581_at_cern.ch...
> 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
Received on Mon Aug 28 2000 - 11:34:12 CDT