declare
num1 PLS_INTEGER;
numt number;
str varchar2(200);
v_sql varchar2(1000);
v_sql2 varchar2(1000);
vtab varchar2(1000);
vcol varchar2(1000);
begin
vtab := 'branch';
str := '消化科(门)';
num1 := 3;
numt := 0;
for c_tab in (select distinct table_name
from user_tab_columns
where table_name in (select table_name from user_tables)
-- and rownum < 2
-- and table_name='BRANCH'
order by table_name) loop
for c_col in (select column_name
from user_tab_columns
where table_name = c_tab.table_name order by column_name) loop
numt := numt + 1;
vcol := c_col.column_name;
v_sql := 'select count(*) from ' ||c_tab.table_name||
' where '||vcol||'=''消化科(门)''';
begin
execute immediate v_sql
into num1;
Exception
when others then null;
end;
if num1 > 0 then
dbms_output.put_line(c_tab.table_name);
dbms_output.put_line(vcol);
dbms_output.put_line(num1);
end if;
end loop;
end loop;
end;
/