Oracle 中 根據值 查詢 所在 表和欄位

標籤：

---------------------- 這裡是查詢 數字型欄位值/*declareCURSOR 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;*/

 

查詢字串類型:

declareCURSOR 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;*/

 

Oracle 中 根據值 查詢 所在 表和欄位