oracle检查配置的参数个数是否一致

       工作时候遇到一个问题，表A中保存了sql查询的脚本，其中有参数值使用:参数名称代替，如a=:A，表B配置了参数名称,如A,我想知道表A和表B的参数个数是否一致(不考虑名称是否相同，只考虑个数是否相同),随手写了个sql，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

    结果为：

     

 

     本人水平有限，欢迎各位提出更好的写法或者思路，谢谢。
     全文完。