This issue has been brought up again and again over the years: how to handle queries that are the same except for the WHERE clause, whose conditions can vary in number and type.
Why is this a PL/SQL question? PL/SQL cannot fetch the data and return it to the client as is! At best, it can open a REF CURSOR and let the client do the fetching. If the client can do the fetching, why not let it build the dynamic query, which it can do much more easily than PL/SQL?
Besides, if the number and type of the bind variables can vary, then the query must be built using DBMS_SQL, then the resulting query must be converted to a REF CURSOR. What fun...
Finally, the PL/SQL function that does this has to handle the varying inputs, so the API it provides will probably be clunky anyway.
Personally, I would just build, execute and fetch from the query using whatever language accesses the database.
A compromise position would be to use a SQL macro. The client would still execute and fetch from the query, and it would have to provide logic to handle varying inputs, but there would be some guardrails to promote the use of bind variables.
Here is an example:
- the API would include parameters for all possible
values, but they would be optional (and they can be bind variables);
- the client would have to explicitly say which
conditions are to be tested;
- in the end, the client is executing and fetching from a query, so the PL/SQL is no longer a clunky middleman.
SQL> create or replace function emp_filtered(
2 p_filter_cols in dbms_tf.columns_t default null,
3 p_empno in number default null,
4 p_deptno in number default null,
5 p_mgr in number default null
6 ) return varchar2 sql_macro is
7
8 type tt_filters is table of varchar2(255) index by varchar2(130);
9 lt_filters tt_filters := new tt_filters(
10 '"EMPNO"' => ' and empno = p_empno',
11 '"DEPTNO"' => ' and deptno = p_deptno',
12 '"MGR"' => ' and mgr = p_mgr'
13 );
14 l_sql varchar2(4000) := 'select * from emp where 1=1';
15
16 begin
17 if p_filter_cols is not null then
18 for i in 1..p_filter_cols.count loop
19 l_sql := l_sql || lt_filters(p_filter_cols(i));
20 end loop;
21 end if;
22 dbms_output.put_line(l_sql);
23 return l_sql;
24
25 end emp_filtered;
26 /
Function EMP_FILTERED compiled
SQL> var v_deptno number;
SQL> var v_mgr number;
SQL> begin
2 :v_deptno := 30;
3 :v_mgr := 7839;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from emp_filtered(
2 p_filter_cols => columns(deptno, mgr),
3 p_deptno => :v_deptno,
4 p_mgr => :v_mgr
5 );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
--generated query: select * from emp where 1=1 and deptno = p_deptno and mgr = p_mgr