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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With