Is it possible to query conditionally based on the current value in a list of strings using either SQL or PL/SQL? I am working with an Oracle Database if that helps?
Example of desired functionality (pseudo-code):
for stringVal in ('string1', 'string2', 'string3'):
if (stringVal == 'string2'):
select * from SCHEMA.TABLE where CONDITION;
else:
select * from OTHER_SCHEMA.OTHER_TABLE where CONDITION;
CodePudding user response：
Depending on what version you are on you can do it using SQL Macros. Those are available since 19.6 or 19.7 .. simple example, you get the idea:
SQL> create or replace function what_shall_i_do(tab dbms_tf.table_t, col dbms_tf.columns_t)
2 return varchar2 SQL_Macro is
3 stmt clob := 'select ';
4 cnm clob;
5 begin
6 for i in 1 .. col.count loop
7 cnm := col(i);
8 stmt := stmt || cnm || ',';
9 end loop;
10
11 return rtrim(stmt, ',') || ' from tab';
12
13 end;
14
15* /
Function WHAT_SHALL_I_DO compiled
SQL> select * from what_shall_i_do(dual, columns(dummy));
2*
DUMMY
________
X
SQL> select * from what_shall_i_do(sh.customers, columns(cust_id)) fetch first 5 rows only;
2*
CUST_ID
__________
49671
3228
6783
10338
13894
SQL>
CodePudding user response：
Is it possible? Yes. For example:
SQL> declare
2 l_result number;
3 begin
4 for stringval in (select 'string1' col from dual union all
5 select 'string2' from dual union all
6 select 'string3' from dual
7 )
8 loop
9 if stringval.col = 'string2' then
10 select max(sal)
11 into l_result
12 from scott.emp;
13 else
14 select min(deptno)
15 into l_result
16 from mike.dept;
17 end if;
18 dbms_output.put_line('Stringval.col = ' || stringval.col || '; result = ' || l_result);
19 end loop;
20 end;
21 /
Stringval.col = string1; result = 10
Stringval.col = string2; result = 5000
Stringval.col = string3; result = 10
PL/SQL procedure successfully completed.
SQL>
Note, however, that
IFbelongs to PL/SQLSELECThas to have itsINTO; my example is a simple one and returns just a single value into a scalar variable. Depending on what your queries really return, you might not be able to do that but use e.g. a collection or a ref cursor instead
CodePudding user response：
If the condition doesn't depend on input data and the result set contains the same columns, then you may use simple view consisting of unions with constant as additional column and filter by that column. Optimizer is smart to figure it out and avoid access to the filtered out objects (see starts column).
create view v_test as
select
'table_1' as src, t1.*
from t1
where val = 3
union all
select 'table_2', t2.*
from t2
where id < 10
select /* gather_plan_statistics*/ * from v_test where src = 'table_1'SRC | ID | VAL | STR :------ | -: | --: | :--------- table_1 | 3 | 3 | KJVK9GPV5D table_1 | 8 | 3 | 5VQT72ICVR table_1 | 13 | 3 | 7YIYAMOAA7 table_1 | 18 | 3 | NXGVIP1CFX
select * from table(dbms_xplan.display_cursor(format => 'TYPICAL ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | | :--------------------------------------------------------------------------------------------------------------------------------- | | SQL_ID bfc7qqct9vamf, child number 0 | | ------------------------------------- | | select /* gather_plan_statistics*/ * from v_test where src = 'table_1' | | | | Plan hash value: 2711242194 | | | | ---------------------------------------------------------------------------------------------------------------------------------- | | | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | | | ---------------------------------------------------------------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 2 | 1 | | | | 1 | VIEW | V_TEST | 1 | 5 | 10185 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | 1 | | | | 2 | UNION-ALL | | 1 | | | | | 4 |00:00:00.01 | 2 | 1 | | | |* 3 | TABLE ACCESS FULL | T1 | 1 | 4 | 68 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | 1 | | | |* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 | | | |* 5 | TABLE ACCESS FULL| T2 | 0 | 10 | 220 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | | | ---------------------------------------------------------------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 3 - filter("VAL"=3) | | 4 - filter(NULL IS NOT NULL) | | 5 - filter("ID"<10) | | |
db<>fiddle here
If condition is dynamic and depends on input data, then I will go for modern SQL Macro as posted in another answer.