oracle非绑定变量忽略,oracle对非使用绑定变量的语句去重

通过如下过程可以对非使用绑定变量的语句去重

create or replace procedure query_sql is

cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count

from sys.LARGE2S_TABLE

where FORCE_MATCHING_SIGNATURE > 0

and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE

group by FORCE_MATCHING_SIGNATURE

having count(1) > 100

order by 2;

v_fms number;

v_sql01 varchar2(3999);

v_sql02 varchar2(3999);

begin

for i in fms loop

v_sql01:='insert into shsnc.long_sql(sql_id,FORCE_MATCHING_SIGNATURE,sql_text)  select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from (select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from sys.LARGE2S_TABLE where FORCE_MATCHING_SIGNATURE='||i.fms||' and sql_id  is not null and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2';

v_sql02:='update shsnc.long_sql set count='||i.count ||' where FORCE_MATCHING_SIGNATURE='||i.fms;

execute immediate v_sql01;

commit;

execute immediate v_sql02;

commit;

end loop;

end;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1331287/，如需转载，请注明出处，否则将追究法律责任。