oracle怎么检查配置参数个数是否一致?

发布时间:2020-02-14编辑:脚本学堂
如何检查oracle配置参数个数是否一致?这里有一个oracle写的sql语句实例,用于检查oracle数据库中配置参数个数,需要的朋友参考下。

问题描述:
表A中保存了sql查询的脚本,其中有参数值使用:参数名称代替,如a=:A,表B配置了参数名称,如A,我想知道表A和表B的参数个数是否一致(不考虑名称是否相同,只考虑个数是否相同)。

sql语句
 

复制代码 代码示例:
with tmp_sql as( 
select 1 as rid,'from p where 1=1 and p.a1=:A1  and a.done_date >=to_date(:create_date, ''yyyy-mm-dd'') and a.done_date <to_date(:done_date, ''yyyy-mm-dd'')+1 and p.a2=:A2' as sql_str from dual union all 
select 2 as rid,'from p where a1=:A1 and a2=:A2' as sql_str from dual union all 
select 3 as rid,'from p where a1=a1' as sql_str from dual union all 
select 4 as rid,'from p where a1=:a2' as sql_str from dual), 
tmp_p as( 
select 1 as id,1 as rid,'A1' as pname from dual union all 
select 2 as id,1 as rid,'create_date' as pname from dual union all 
select 3 as id,1 as rid,'done_date' as pname from dual union all 
select 4 as id,1 as rid,'A2' as pname from dual union all 
select 5 as id,2 as rid,'A1' as pname from dual union all 
select 6 as id,3 as rid,'A2' as pname from dual union all 
select 7 as id,4 as rid,'A2' as pname from dual 

select cc.rid, cc.param_total, dd.right_param 
   from (select bb.rid, 
                --取不重复的参数个数 
                count(distinct lower(bb.result_str)) param_total 
           from (select aa.rid, 
                        aa.sql_str, 
                        level, 
                        --取:后面的参数名称 
                        regexp_substr(sql_str, ':[a-zA-Z0-9_s]*', 1, level) result_str 
                   from (select rid, 
                                --把多个sql_str合并为一个,并去除多余的空格或换行 
                                listagg(regexp_replace(replace(sql_str, 
                                                               chr(10), 
                                                               ' '), 
                                                       's{2,}', 
                                                       ' '), 
                                        '') within group(order by rid) sql_str 
                           from tmp_sql t 
                          group by t.rid) aa 
                  --取全部的:参数 
                 connect by level <= regexp_count(sql_str, ':')--:参数的个数 
                        and prior rid = rid 
                        and prior dbms_random.value is not null) bb 
          group by bb.rid) cc, 
        (select rid, count(distinct lower(pname)) right_param 
           from tmp_p 
          group by rid) dd 
  where 1 = 1 
    and cc.param_total != dd.right_param 
    and cc.rid = dd.rid 

结果,如下图:  
oracle配置参数个数是否一致