oracle - How to handle optional parameters in SQL query?

Question

Say I have a sample table:

 id_pk  value
------------
 1       a
 2       b
 3       c

And I have a sample PL/SQL block, which has a query that currently selects a single row into an array:

declare

  type t_table is table of myTable%rowtype;

  n_RequiredId myTable.id_pk%type := 1;  
  t_Output t_table := t_table();

begin

  select m.id_pk, m.value
    bulk collect into t_Output
    from myTable m 
   where m.id_pk = n_RequiredId;

end;

What I need to do is to implement an ability to select a single row into an array, as shown in the block above, OR to select all rows into an array, if n_RequiredID, which is actually a user-input parameter, is set to null.

And, the question is, what's the best practice to handle such situation?

I can think of modifying where clause of my query to something like this:

where m.id_pk = nvl(n_RequiredId, m.id_pk);

but I suppose that's going to slow down the query if the parameter won't be null, and I remember Kyte said something really bad about this approach.

I can also think of implementing the following PL/SQL logic:

if n_RequiredId is null then 

  select m.id_pk, m.value bulk collect into t_Output from myTable m;

else

  select m.id_pk, m.value bulk collect
    into t_Output
    from myTable m
   where m.id_pk = n_RequiredId;

end if;

But would become too complex if I encounter more than one parameter of this kind.

What would you advice me?

Answer 1

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.