Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cursor parameter or PL/SQL variable?

Re: Cursor parameter or PL/SQL variable?

From: Yong <yhuang_at_indigopool.com>
Date: Mon, 28 Aug 2000 11:34:12 -0500
Message-ID: <8oe4g9$cvh$1@news.sinet.slb.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US