在Oracle中寻找一个值

Posted by Alei Blog on August 12, 2016

当你接手一个文档缺失的项目,使用了Oracle数据库,表间关系不清楚,偶尔在报错的日志中看到一些值,你可能需要这项技能

问题

  • 只知道一个值,比如主键
  • 不知道这个值属于哪张表哪一列

遍历整个数据库所有表所有列

  • 耗时很长
SET SERVEROUTPUT ON SIZE 100000

DECLARE
  match_count INTEGER;
  -- 改成你想要搜索的Oracle用户,注意使用大写
  v_owner VARCHAR2(255) :='CT0805';

  -- 设置要搜索的数据类型,比如VARCHAR2, CHAR, NUMBER等,注意使用大写
  v_data_type VARCHAR2(255) :='VARCHAR2';

  -- 设置要搜索的值
  v_search_string VARCHAR2(4000) :='000118100000000008BW';

  BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

  EXECUTE IMMEDIATE 
  'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
  INTO match_count
  USING v_search_string;

  IF match_count > 0 THEN
  dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
  END IF;

  END LOOP;
  END;
  /

说明

  • 只能在sqlplus中,或者pl/sql developer的命令窗口中执行,不能在sql窗口中执行
  • SET SERVEROUTPUT ON SIZE 100000 是设置缓存大小
  • dbms是oracle处理信息的一个包,相当于java的System.out
  • 执行完成之后,控制台会输出结果
  • v_owner的值,修改成需要搜索的Oracle用户
  • 搜索主键或字符串的话,v_data_type设置成varchar2或char,可能要分别搜索
  • v_search_string是要搜索的值