PL SQL查询NVL具有逗号分隔的多个值

Question

我有一个查询工作，允许用户按日期范围，存储（一个商店编号）和邮政编码选择，

关于商店，我希望能够输入逗号分隔的多个商店号码。

下面的代码适用于单个商店，但对于多个商店编号

不适用

      SELECT tt.id_str_rt store
           ,SUBSTR(tt.inf_ct,1,5) zip_code
           ,COUNT(tt.ai_trn) tran_count
           ,SUM(tr.mo_nt_tot) sales_value
          FROM orco_owner.tr_trn tt
           ,orco_owner.tr_rtl tr
         WHERE tt.id_str_rt = tr.id_str_rt

          AND (tt.id_str_rt IN NVL(:PM_store_number,tt.id_str_rt) OR :PM_store_number IS NULL)

           AND NVL(SUBSTR(tt.inf_ct,1,5),0) = NVL(:PM_zip_code,NVL(SUBSTR(tt.inf_ct,1,5),0))
           AND tt.id_ws = tr.id_ws
           AND tt.dc_dy_bsn = tr.dc_dy_bsn
           AND tt.ai_trn = tr.ai_trn
                  AND TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd'))
          BETWEEN NVL(:PM_date_from, TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd')))
              AND NVL(:PM_date_to,TRUNC(TO_DATE(tt.dc_dy_bsn,'yyyy-MM-dd')))
           AND LENGTH(TRIM(TRANSLATE(SUBSTR(inf_ct,1,5), '0123456789', ' '))) IS NULL
          GROUP BY tt.id_str_rt,SUBSTR(tt.inf_ct,1,5)
          ORDER BY zip_code, store

Answer 1

您可以创建一个函数，例如如何将CSV转换为Oracle中的CSV中所述的函数：

create or replace function splitter(p_str in varchar2) return  sys.odcivarchar2list
is
v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
begin
with cte as (select level  ind from dual
connect by 
level <=regexp_count(p_str,',') +1
)
select regexp_substr(p_str,'[^,]+',1,ind)
bulk collect into v_tab
from cte;
return v_tab;
end;
/

然后，您将在此类查询中使用它：

and (tt.id_str_rt in (select column_value from table(splitter(:PM_store_number)) ))

而不是这样：

AND (tt.id_str_rt IN NVL(:PM_store_number,tt.id_str_rt) OR :PM_store_number IS NULL)