Re: Dynamic SQL-Command in PL/SQL

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Sat, 26 Feb 2005 18:35:50 -0000
Message-ID: <MPG.1c8ad1d81244e774989dab@news.individual.net>

In article <421f02db$0$3680$4d4ef98e_at_read.news.ch.uu.net>, COAST says...
> PROCEDURE MY_PROC(myVar IN NUMBER,) IS
> BEGIN
>
> DECLARE
>
> BEGIN
>
>
> FOR myAttributes IN ( select myAttribute into myAttributeResult from myTable
> where myNumber = myVar;)
> LOOP
>
> doSomething();
>
> END LOOP;
>
> END;
> END;
>
> Now I've got the problem if myVar is NULL then I want to have ALL entries.
>
>

Do

PROCEDURE MY_PROC(myVar IN NUMBER,) IS
BEGIN
  DECLARE
  BEGIN
  FOR myAttributes IN ( select myAttribute

                        into   myAttributeResult 
                        from   myTable         
                        where  myNumber = nvl(myVar,myNumber) 
                      )

  LOOP
    doSomething();
  END LOOP;
  END;
END; Then if myVar is null it will be matched to myNumber.

hth

-- 

jeremy

Received on Sat Feb 26 2005 - 12:35:50 CST