Re: Dynamic SQL-Command in PL/SQL

From: <fitzjarrell_at_cox.net>
Date: 25 Feb 2005 07:01:29 -0800
Message-ID: <1109343297.132880.138400@g14g2000cwa.googlegroups.com>

COAST wrote:
> Hi NG
>
> I've got the following sql-statement:
>
>
> 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.
> So the I need:
>
> PROCEDURE MY_PROC(myVar IN NUMBER,) IS
> BEGIN
>
> DECLARE
>
> BEGIN
>
> IF myVar is not null THEN
> FOR myAttributes IN ( select myAttribute into myAttributeResult
from myTable
> where myNumber = myVar;)
> LOOP
>
> doSomething();
>
> END LOOP;
> ELSE
> FOR myAttributes IN ( select myAttribute into myAttributeResult
from myTable)
>
> LOOP
>
> doSomething();
>
> END LOOP;
> END IF;
>
> END;
> END;
>
> That means I have to write twice the same!! Is there a way to write
this case with one sql-statement.
>
> Specially, if you have more than one argument in the proc (which you
have to test on NULL),
> your code will multiply and the code will not be very handsom if you
have any changes.
>
> Thanks for any hint
> Peter

Theh following should work for you, and eliminates the 'necessity' of 'duplicating' your code:

PROCEDURE MY_PROC(myVar IN NUMBER,) IS
BEGIN   DECLARE   myVal varchar2(10):='%';

  BEGIN   if myVar is not null then

     myVal := myVar;
  end if;

  FOR myAttributes IN ( select myAttribute into myAttributeResult from myTable

                                             where myNumber like

myVal;)
  LOOP     doSomething();

  END LOOP;   END;
END; Yes, it uses implicit conversion on the select statement however the LIKE construct allows you to return all rows if myVar is NULL since myVal remains '%', and it also allows you to return data for a specific match as LIKE without a wildcard character behaves as '='. As I said it is not the most efficient code, but it DOES provide you the option of less typing. Personally I'd rather code two separate conditions to eliminate any implicit or explicit conversions; you want something different and I believe my example provides a solution.

I hope this helps.

David Fitzjarrell Received on Fri Feb 25 2005 - 09:01:29 CST