Hi, guys.
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?