如何在使用 PLSQL 的動態查詢中使用“綁定變數”和“With 子句”

我有三個具有這些結構的表：

1) z_test_a(c_num   number , <other columns>)
2) z_test_b(c_num   number , Amount  number , <other columns>)
3) z_test_b(c_num   number , Amount  number , <other columns>)

受到這篇文章中的一個答案的啟發：https ://stackoverflow.com/questions/2514254/how-can-i-create-a-dynamic-where-clause ，我了解到一種使用Dynamic Query和仍然使用Bind variables的方法是用 編寫您的查詢With clause。我試圖應用這種方法，Intersect但我無法成功並且我的程序不起作用。它最終給了我這個錯誤：ORA-00904:"B"."AMOUNT1" invalid identifier對於這個輸入參數：

1)i_table_names:z_test_b,z_test_c
2)i_table_names:1000
3)i_amnt_second:1000

當存在相交或聯合或查詢必須像上面提到的文章中那樣簡單時，甚至可以使用這種方法嗎？有沒有更好的方法來編寫這個程序？也許它可以在沒有動態查詢的情況下編寫。

我的程序是：

create or replace procedure bind_variable_intersect(i_table_names in varchar2,
                                                   i_amnt_first  in number,
                                                   i_amnt_second in number,
                                                   o_out         out sys_refcursor) is

v_base_query varchar2(2000) := ' with binds as 
                                  (select :bind1 as amount1, 
                                          :bind2 as amount2                        
                                   from dual)
                                select t.c_num
                                from z_test_a t , binds b where 1=1  ';

begin

-- Check input parameter " i_table_names "
if i_table_names like '%z_test_b%' then
  v_base_query := v_base_query ||
               ' intersect select c_num from z_test_b where amount = b.amount1  ';
end if;

if i_table_names like '%z_test_c%' then
  v_base_query := v_base_query ||
               ' intersect select c_num from z_test_c where amount = b.amount2  ';
end if;

-- Debug Code
dbms_output.put_line(v_base_query);

-- Execute
open o_out for v_base_query
  using i_amnt_first,i_amnt_second;

end;

如何建構這樣的動態查詢：

with params (amount1, amount2) as
    ( select 100, 200
      from   dual )
select z.*
from   params p
      cross apply (
        select c_num from z_test_a
        intersect
        select c_num from z_test_b where amount = p.amount1  -- add dynamically
      ) z;

select下的最後一個intersect將根據 的值動態添加i_table_names，就像在您的原始版本中一樣。

那麼程序將是：

create or replace procedure bind_variable_intersect
   ( i_table_names in varchar2
   , i_amnt_first  in number
   , i_amnt_second in number
   , o_out         out sys_refcursor )
as
   v_base_query varchar2(2000) :=
   'with params (amount1, amount2) as
        ( select :b1, :b2
          from   dual )
   select z.*
   from   params p
          cross apply (
            select c_num from z_test_a';
begin
   -- Check input parameter " i_table_names "
   if i_table_names like '%z_test_b%' then
       v_base_query := v_base_query || chr(10) ||
           'intersect select c_num from z_test_b where amount = p.amount1';
   end if;

   if i_table_names like '%z_test_c%' then
       v_base_query := v_base_query || chr(10) ||
           'intersect select c_num from z_test_c where amount = p.amount2';
   end if;

   v_base_query := v_base_query || chr(10) || ') z';

   -- Debug Code
   dbms_output.put_line(v_base_query);

   -- Execute
   open o_out for v_base_query
       using i_amnt_first, i_amnt_second;

end bind_variable_intersect;

引用自：https://dba.stackexchange.com/questions/273069