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
         where 1=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>0 then
              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) > 0 THEN
               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;
*/