Re: SQL in Pl/SQL

Try the following =>

PROCEDURE foo
IS

   CURSOR some_curname(v_city IN varchar2, v_state IN varchar2)    IS
     SELECT ADID
    FROM OP_ADDRESS
    WHERE STATE IN (V_STATE, NULL);
    AND CITY IN (V_CITY, NULL);
BEGIN
   FOR thisrec IN some_curname(city,state)    LOOP

• do something in here with this_rec-- END LOOP; CLOSE some_curname; END foo;
  • you may want to check the syntax. all my books are at work -- lynn ps: assuming [possibly incorrect] that there are more city in DB than states

>
>
> In article <3547872F.3C3E590F_at_rspa.dot.gov>, Mark Aengenheyster <"'no spam
> loser' aengenhm"@rspa.dot.gov> wrote:
> >hello everybody!
> >
> >I am having a serious lapse of intelligence here.
> >
> >here is my SQL which is inside my PL/SQL function:
> > SELECT ADID INTO V_ADID FROM OP_ADDRESS
> > WHERE CITY = V_CITY
> > AND STATE = V_STATE;
> >
> >The problem is, city or state could be NULL but not necessarily.
> >How do I right the SQL so that it will return a row if the value is NULL
> >and if it is not NULL ( = 'something')?
> >
> >If it is null then I want to write:
> > SELECT ADID INTO V_ADID FROM OP_ADDRESS
> > WHERE CITY IS NULL
> > AND STATE IS NULL;
> >
> >DECODE won't work and NVL doesn't help. I don't want to get into using
> >dynamic SQL.
> >What do I do?
> >
> >Thanks in advance
> >
>
> Nicolas
>
> Nissakid_at_planete.net
> Microsoft Certified Product Specialist
> AUI Member
>
> The above represents my own opinion not my employer's and does not
> necessarily constitute the official policy of my employer. Any opinion
> developed through this account represent a private conversation.
Received on Thu Apr 30 1998 - 15:46:31 CDT