create or replace procedure FindStr(findStr varchar2)
is
sqlcmd varchar2(500);
table_name varchar2(50);
column_name varchar2(50);
sValue varchar2(255);
begin
for tab in
(
select t1.table_name,t1.column_name,t2.num_rows
from all_tab_cols t1 join all_tables t2 on t1.table_name=t2.table_name
and t1.owner='ZLHIS' and t2.owner='ZLHIS' and t1.data_type='VARCHAR2' and t2.num_rows>0
)
loop
sqlcmd:='select count(1) from ZLHIS."'||tab.table_name||'" where "'||tab.column_name||'"='''||findStr||'''';
execute immediate sqlcmd into sValue;
if(sValue='0') then
continue;
end if;
sqlcmd:='select '||tab.column_name||' from ZLHIS."'||tab.table_name||'" where "'||tab.column_name||'"='''||findStr||''' and rownum=1';
dbms_output.put_line(sqlcmd);
execute immediate sqlcmd into sValue;
dbms_output.put_line(tab.table_name||','||tab.column_name||','||sValue);
end loop;
end;