DECLARE
sqlStr VARCHAR2(2000); –拼写sql
flag int; –判断是否存在
col_name VARCHAR2(100); –字段名字
tab_name VARCHAR2(100); –表名
col_type VARCHAR2(100); –表名
operator_str VARCHAR2(100) := '=';
FindUser VARCHAR2(50) := 'HC'; –要查找的用户
FindValues VARCHAR2(50) := '陈'; –**要查找的值
Findtype1 VARCHAR2(50) := 'VARCHAR2'; –要查找的值类型 数字类型特殊处理
Findtype2 VARCHAR2(50) := 'CHAR'; –要查找的值类型
Findtype3 VARCHAR2(50) := 'VARCHAR'; –要查找的值类型
isLike boolean := true; –是否模糊查找
cursor cur is –定义游标
select *
from all_tab_columns a
where a.OWNER = FindUser
and a.Data_type in (Findtype1, Findtype2, Findtype3)
— and a.Table_Name like 'BD_%' ;–要查找的表的过滤
order by Table_Name, Data_type;
begin
if isLike then
operator_str := ' like ';
FindValues := '''%' || FindValues || '%''';
else
operator_str := ' = ';
FindValues := '''' || FindValues || '''';
end if;
for i in cur loop
tab_name := i.table_name;
col_name := i.column_name;
col_type := i.Data_type;
sqlStr := ' select count(*) from ' || tab_name || ' where ' ||
col_name || operator_str || FindValues;
dbms_output.put_line('sql: 'sqlStr || ' —–' || flag);
execute immediate sqlStr
into flag;
if flag > 0 then
dbms_output.put_line(' select ' || col_name || ' from ' || tab_name ||
' where ' || col_name || operator_str ||
FindValues || ' —' || col_type || '—' || flag);
end if;
end loop;
end;

—————————存过——————————

create or replace procedure pro_get_tabname_by_value(FindUser VARCHAR2,
FindValues VARCHAR2,
Findtype1 VARCHAR2,
Findtype2 VARCHAR2,
Findtype3 VARCHAR2,
isLike boolean default true) authid current_user is
sqlStr VARCHAR2(2000); –拼写sql
flag int; –判断是否存在
col_name VARCHAR2(100); –字段名字
tab_name VARCHAR2(100); –表名
col_type VARCHAR2(100); –表名
operator_str VARCHAR2(100) := '=';
FindValues1 VARCHAR2(100) ;

cursor cur is –定义游标
select *
from all_tab_columns a
where a.OWNER = FindUser
and a.Data_type in (Findtype1, Findtype2, Findtype3)
— and a.Table_Name like 'BD_%' ;–要查找的表的过滤
order by Table_Name, Data_type;
begin
if isLike then
operator_str := ' like ';
FindValues1 := '''%' || FindValues || '%''';
else
operator_str := ' = ';
FindValues1 := '''' || FindValues || '''';
end if;
for i in cur loop
tab_name := i.table_name;
col_name := i.column_name;
col_type := i.Data_type;
sqlStr := ' select count(*) from ' || tab_name || ' where ' ||
col_name || operator_str || FindValues1;
dbms_output.put_line('sql: '||sqlStr || ' —–' || flag);
execute immediate sqlStr
into flag;
if flag > 0 then
dbms_output.put_line(' select ' || col_name || ' from ' || tab_name ||
' where ' || col_name || operator_str ||
FindValues1 || ' —' || col_type || '—' || flag);
end if;
end loop;
end pro_get_tabname_by_value;

最后，我们来测试下:
SQL> set serveroutput on
SQL>
SQL> begin
2 — Call the procedure
3 pro_get_tabname_by_value(finduser => 'HC',
4 findvalues => '陈',
5 findtype1 => 'VARCHAR',
6 findtype2 => 'VARCHAR2',
7 findtype3 => 'VARCHAR',
8 islike => true);
9 end;
10 /

sql: select count(*) from A4_DATA where ID like '%陈%' —–
sql: select count(*) from A4_DATA where NAME like '%陈%' —–0
select NAME from A4_DATA where NAME like '%陈%' —VARCHAR2—3686
sql: select count(*) from A4_DATA where IP like '%陈%' —–3686
sql: select count(*) from A4_DATA where SB_NAME like '%陈%' —–0
sql: select count(*) from A4_DATA where USER_GROUP like '%陈%' —–0
sql: select count(*) from A4_DATA where LOGIN_DATE like '%陈%' —–0
sql: select count(*) from A4_DATA where OPERATION like '%陈%' —–0
select OPERATION from A4_DATA where OPERATION like '%陈%' —VARCHAR2—32
sql: select count(*) from A4_DATA where LOGIN_CID like '%陈%' —–32
sql: select count(*) from T1 where B like '%陈%' —–0
sql: select count(*) from TAB_BLOCK where SCN_BASE like '%陈%' —–0
sql: select count(*) from TAB_BLOCK where SCN_WRAP like '%陈%' —–0

PL/SQL procedure successfully completed