Oracle中根据值查询所在表和字段

Oracle中根据值查询所在表和字段
--------------------
-- 这⾥是查询数字型字段值
/*declare
CURSOR cur_query IS
select table_name, column_name, data_type from user_tab_columns;
a number;
sql_hard varchar2(2000);
vv number;
begin
for rec1 in cur_query loop
a:=0;
if rec1.data_type ='NUMBER' THEN
a := 1;
end if;
if a>0 then
sql_hard := '';
sql_hard := 'SELECT count(*) FROM '|| rec1.table_name ||' where '
||rec1.column_name|| '=''TYRCE0BF26AB5C586B3 '' ';
dbms_output.put_line(sql_hard);
execute immediate sql_hard INTO vv;
IF vv > 0 THEN
dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);
end if;
END IF;
end loop;
end;*/
查询字符串类型:
declare
CURSOR cur_query IS
select a.table_name, a.column_name, a.data_type from user_tab_columns a
where1=1
and (lower(a.data_type) ='varchar2'or lower(a.data_type) ='char')
and NOT a.TABLE_NAME like'%$%'
and NOT a.TABLE_NAME like'%+%'
and NOT a.TABLE_NAME like'%=%'
ORDER BY A.TABLE_NAME ASC
;
a number;
sql_hard varchar2(2000);
vv number;
rscount number;
str varchar2(2000);
num number;
findValue varchar2(500) :='R2186E3DC09B88E1AF '; -- 要查询的字符串值
begin
rscount:=0;
str:='TRUNCATE table tmp_test';
execute immediate str;
str:='drop table tmp_test';
execute immediate str;
str:='CREATE GLOBAL TEMPORARY TABLE tmp_test (tab_name varchar2(500), col_name varchar2(500)) ON COMMIT PRESERVE ROWS'; execute immediate str; ----使⽤动态SQL语句来执⾏
for rec1 in cur_query loop
rscount:= rscount +1;
a:=0;
--if rec1.data_type ='VARCHAR2' or rec1.data_type='CHAR' THEN
if rec1.data_type ='VARCHAR2'or rec1.data_type='CHAR'THEN
a :=1;
end if;
if a>0then
sql_hard :='';
sql_hard :='SELECT count(*) FROM '|| rec1.table_name ||' where '
--||rec1.column_name|| ' =''wu''';
||rec1.column_name||' ='''|| findValue ||'''';
--dbms_output.put_line(sql_hard);
--dbms_output.put_line( '**** ' || rec1.table_name||'--'||rec1.column_name);
execute immediate sql_hard INTO vv;
IF NVL(vv,0) >0THEN
dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);
str:='insert into tmp_test '
||' select '''|| rec1.table_name ||''' as tab_name,'''|| rec1.column_name ||''' as col_name from dual';
execute immediate str;
str:='commit';
execute immediate str;
end if;
END IF;
end loop;
--str:= 'select * from tmp_test';
--execute immediate str;
end;
-- 运⾏完以后执⾏
/*
select * from tmp_test;
*/。