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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Optional (ignored) parameters in stored procedures (PL/SQL)? How to?

Re: Optional (ignored) parameters in stored procedures (PL/SQL)? How to?

From: Theo <theoa_nl_at_hotmail.com>
Date: Sun, 11 Jan 2004 23:16:28 +0100
Message-ID: <4001cb3a$0$127$e4fe514c@dreader12.news.xs4all.nl>


"Kurta" <submit_at_galleus.com> wrote in message news:efcb1994.0401100539.1a9c55e8_at_posting.google.com...
> I think this gives the same result as the COALESCE example.
>
> If the UMBS_OBJ_STT column contains NULL values those rows are not
> returned. (NULL = NULL returns false) And we should receive all rows
> when the state parameter is NULL.
>
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
news:<qomdnesa_eAWQWOi4p2dnA_at_comcast.com>...
> > OPEN resultCursor FOR
> > SELECT UMET_ID
> > FROM T_UM_BASE
> > WHERE UMBS_MEMB = memberId
> > AND UMBS_OBJ_STT = nvl(state,UMBS_OBJ_STT)

How about this:

OPEN resultCursor FOR

     SELECT UMET_ID
       FROM T_UM_BASE
       WHERE UMBS_MEMB = memberId
          AND  (UMBS_OBJ_STT = state
                OR state IS NULL)

or even this (although I don't like using dummy values):

OPEN resultCursor FOR

     SELECT UMET_ID
       FROM T_UM_BASE
       WHERE UMBS_MEMB = memberId
          AND  nvl(UMBS_OBJ_STT,'xxx') = nvl(nvl(state,UMBS_OBJ_STT),'xxx')

But why do you find it necessary to replace the old code? It may give you better performance than stuffing it in one cursor. Also, using cursor-parameters for memberId and state may perform better. But AFAIK you'll have declare your cursor explicitly for that (though I could be wrong; I never use inline-cursor like this).

-- 
Theo
Received on Sun Jan 11 2004 - 16:16:28 CST

Original text of this message

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