Oracle SQL 动态拼接Where后面的筛选条件

TBL_NAME COLM_NAMEOPER_CODE RULE_CMPAR_VALUEAND_OR_OR
STORE_DISTRO RTE_ID= 22410034A
STORE_MASTER ZONE= 贵州

需要拼接成：

 STORE_DISTRO.RTE_ID='22410034' and  STORE_MASTER.ZONE='贵州' 

1. 创建临时表存放查询后的数据，临时表可以根据实际查询结果创建（create table wmedi.wave_distro_filter... ..）

2. 创建自定义函数get_wave_query_param，用来拼接完整的Where条件

3. 定义sql字符，后面接上get_wave_query_param函数返回的筛选条件，通常情况下PL/SQL是不能直接运行的，需要用到execute immediate执行SQL，写入临时表，最后从临时表读取数据，（如果直接用execute immediate ‘select ... from ...’，很难返回出结果集，所以采用临时表存放数据）

declare strsql varchar2(3000);
strwhere varchar2(1000);
begin
execute immediate 'truncate table wmedi.wave_distro_filter';
strsql:='insert into wmedi.wave_distro_filter select store_distro.* ' ||
'from store_distro  '||
'inner join store_master on store_distro.store_nbr=store_master.store_nbr' ||
' where ' ;
select get_wave_query_param('201207110076') into strwhere from dual;
strsql:=strsql || strwhere;
execute immediate strsql;
end;

create or replace function get_wave_query_param (p_wave_nbr in carton_hdr.wave_nbr%type)
return varchar as
v_query_param varchar2(1000);
begin

select replace(text,';','') into v_query_param  from (
select row_number()over(partition by groupname order by groupname,lvl desc) rn,groupname,text from (
select t.groupname,level lvl,SYS_CONNECT_BY_PATH(t.strwhere,' ; ') text from (
select row_number()over(partition by rule_sel_dtl.rule_id order by rule_sel_dtl.sel_seq_nbr) id,'sqlwhere' groupname,tbl_name||'.'||colm_name||oper_code||''''||rule_cmpar_value||''' '||(case and_or_or when 'A' then 'and' when 'O' then 'or' else '' end) strWhere
  from rule_sel_dtl
where rule_id in (select rule_id
                     from wave_rule_parm
                    where wave_parm_id in
                          (select wave_parm_id
                             from wave_parm
                            where wave_nbr = p_wave_nbr)
                      and rule_prty = 1)
) t connect by t.groupname=prior t.groupname and id-1=prior id
) t
) t where t.rn=1;

return v_query_param;
exception
  when others then
    return null;
end;