问题描述：
表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
结果，如下图：
