PL/SQL null value problem in query

I have to create a pl/s开发者_JAVA百科ql function which takes seven input parameters.

Problem is user can pass any of these parameters, in any combination such that remaining parameters would be null. Thus a number of combinations is possible. And I have to query a table using these parameters after removing null parameters.

Is there a way to do this without using number of nested ifs for checking null values?

Also inculde checking the input parameters is null or not in the WHERE clause

For example , suppose the input parameters are parameter1 , parameter2 ,parameter3

select * from tableA
where (col1 = parameter1 or parameter1  is null)
and (col2 = parameter2 or parameter2  is null)
and (col3 = parameter3 or parameter3  is null)

If the parameter1 is null , it will always return true for this parameter 's conditon.

1 solution is to use logic in your where clause to short circuit the conditions

e.g:

SELECT blah WHERE (param1 IS NOT NULL AND col1=param1)
     OR (param2 IS NOT NULL AND col2=param2) ….etc

Note: no parameters = no results

or

    SELECT blah WHERE (param1 IS NULL OR col1=param1)
     AND (param2 IS NULL OR col2=param2) ….etc

Note: no parameters = all results

You can use

select * from table t where 
(param1 is null or t.column1 = param1) and 
(param2 ....)

But this will NOT use any indices on the table or, in the worst case, use an inappropriate one (due cursor sharing, can't be avoided before 11G. In 11G it sometimes may work but it is not something you should rely on).

To get a better solution you have to use a dynamic query. In PLSQL this is done using execute immediate l_query; (or open cursor for l_query;) for most situations or via more powerfull (but much more complex to use) DBMS_SQL (required in very limited number of occurences).

Simpler (but using literals and therefore requiring a hard parse virtually any time it is run) solution is

procedure dynamic_query_literals (param1 ... param7)
is
  l_query varchar2(1000);
  l_cursor sys_refcursor;
begin
  l_query := 'select * from table t where 1=1';
if (param1 is not null) then 
  l_query := l_query || ' and t.column1 = param1';
end if;
if (param2 ...)
  open l_cursor for l_query;

  -- do whatever needed with the result set in the cursor. 
  --The procedure can even return this cursor...
end;

More complex but more performant (particularly if this procedure is called MANY times) solution requires binding of parameters (which gets done automatically and transparently when you use static sql in plsql)

procedure dynamic_query_binding (param1 ... param7)
is
  l_query varchar2(1000);
  l_cursor sys_refcursor;
begin
  l_query := 'select * from table t where 1=1';

if (param1 is null) then 
  --this will get optimised away in the process but is required 
  --syntactically for use of "using" later
  l_query := l_query || ' and 1=1 or :param1 is null';
else
  l_query := l_query || ' and t.column1 = :param1';
end if;

if (param2 ...)

open l_cursor for l_query
using param1, param2, ... param7;

  -- do whatever needed with the result set in the cursor. 
  -- The procedure can even return this cursor...
end;

This version will use indices on the table as appropriate and still get only one hard parse per unique combination of "nullacy" of parameters.

The downside of both dynamic version is that you don't get compile time syntax checking.

You can use the following:

select * from tableA
where col1 = nvl(parameter1, col1) 
and ....