I have to create a pl/sql 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 ....
(col1 = parameter1 or parameter1 is null) suggested in other answers? I see this form mentioned many times, but what is the advantage? Also, this form does not work correctly if col1 is nullable.