[Oracle]根据字段值全库搜索相关数据表和字段

    这个需求比较冷门,但对于在某些特定的情况下,还是会有这样的需要的。好在Oracle实现还比较方便,用存储过程则轻松实现。

    查询字符串:

create or replace procedure search_string(pString in varchar) as
  cursor all_tab_cursor is
    select a.owner, a.table_name, b.column_name
      from dba_tables a, dba_tab_columns b, dba_objects c
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.table_name = c.object_name
       --and a.owner in (‘XXX‘) --用户可选
       and b.data_type in (VARCHAR2,CHAR,CLOB,NCHAR,NCLOB,NVARCHAR2)
       and c.object_type = TABLE
       order by a.owner,a.table_name,b.column_id;
  refAllTab all_tab_cursor%rowtype;

  --------------

  sSql   varchar(4000);
  nCount number;

begin
  DBMS_OUTPUT.Enable(4000000);

  open all_tab_cursor;
  loop
    fetch all_tab_cursor
      into refAllTab;
    exit when all_tab_cursor%notfound;
  
    sSql := SELECT COUNT(1) FROM  || refAllTab.Owner || . ||
            refAllTab.Table_Name ||  WHERE  || refAllTab.Column_Name ||
             LIKE ‘‘% || pString || %‘‘‘;
    --DBMS_OUTPUT.PUT_LINE(sSql);
    execute immediate sSql
      into nCount;
  
    if nCount > 0 then
      DBMS_OUTPUT.PUT_LINE(refAllTab.Owner || . || refAllTab.Table_Name || . ||
                           refAllTab.Column_Name ||  =  || nCount);
    end if;
  
  end loop;
  close all_tab_cursor;
end search_string;

    查询数字:

create or replace procedure search_number(pNumber in number) as
  cursor all_tab_cursor is
    select a.owner, a.table_name, b.column_name
      from dba_tables a, dba_tab_columns b, dba_objects c
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.table_name = c.object_name
       --and a.owner in (‘XXX‘) --用户可选
       and b.data_type in (FLOAT,NUMBER)
       and c.object_type = TABLE
       order by a.owner,a.table_name,b.column_id;
  refAllTab all_tab_cursor%rowtype;

  --------------

  sSql   varchar(4000);
  nCount number;

begin
  DBMS_OUTPUT.Enable(4000000);

  open all_tab_cursor;
  loop
    fetch all_tab_cursor
      into refAllTab;
    exit when all_tab_cursor%notfound;

    sSql := SELECT COUNT(1) FROM  || refAllTab.Owner || . ||
            refAllTab.Table_Name ||  WHERE  || refAllTab.Column_Name ||
             =  || pNumber;
    --DBMS_OUTPUT.PUT_LINE(sSql);
    execute immediate sSql
      into nCount;

    if nCount > 0 then
      DBMS_OUTPUT.PUT_LINE(refAllTab.Owner || . || refAllTab.Table_Name || . ||
                           refAllTab.Column_Name ||  =  || nCount);
    end if;

  end loop;
  close all_tab_cursor;
end search_number;

    查询范围数字:

create or replace procedure search_number_between(pStartNumber in number, pEndNumber in number) as
  cursor all_tab_cursor is
    select a.owner, a.table_name, b.column_name
      from dba_tables a, dba_tab_columns b, dba_objects c
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.table_name = c.object_name
       --and a.owner in (‘XXX‘)  --用户可选
       and b.data_type in (FLOAT,NUMBER)
       and c.object_type = TABLE
       order by a.owner,a.table_name,b.column_id;
  refAllTab all_tab_cursor%rowtype;

  --------------

  sSql   varchar(4000);
  nCount number;

begin
  DBMS_OUTPUT.Enable(4000000);

  open all_tab_cursor;
  loop
    fetch all_tab_cursor
      into refAllTab;
    exit when all_tab_cursor%notfound;

    sSql := SELECT COUNT(1) FROM  || refAllTab.Owner || . ||
            refAllTab.Table_Name ||  WHERE  || refAllTab.Column_Name ||
             BETWEEN  || pStartNumber ||  AND  || pEndNumber;
    --DBMS_OUTPUT.PUT_LINE(sSql);
    execute immediate sSql
      into nCount;

    if nCount > 0 then
      DBMS_OUTPUT.PUT_LINE(refAllTab.Owner || . || refAllTab.Table_Name || . ||
                           refAllTab.Column_Name ||  =  || nCount);
    end if;

  end loop;
  close all_tab_cursor;
end search_number_between;

    查询日期:

create or replace procedure search_date(pToDateString in varchar) as
  cursor all_tab_cursor is
    select a.owner, a.table_name, b.column_name
      from dba_tables a, dba_tab_columns b, dba_objects c
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.table_name = c.object_name
       --and a.owner in (‘XXX‘)  --用户可选
       and (b.data_type = DATE or b.data_type like TIMESTAMP%)
       and c.object_type = TABLE
     order by a.owner, a.table_name, b.column_id;
  refAllTab all_tab_cursor%rowtype;

  --------------

  sSql   varchar(4000);
  nCount number;

begin
  DBMS_OUTPUT.Enable(4000000);

  open all_tab_cursor;
  loop
    fetch all_tab_cursor
      into refAllTab;
    exit when all_tab_cursor%notfound;
  
    sSql := SELECT COUNT(1) FROM  || refAllTab.Owner || . ||
            refAllTab.Table_Name ||  WHERE  || refAllTab.Column_Name ||
             =  || pToDateString;
    --DBMS_OUTPUT.PUT_LINE(sSql);
    execute immediate sSql
      into nCount;
  
    if nCount > 0 then
      DBMS_OUTPUT.PUT_LINE(refAllTab.Owner || . || refAllTab.Table_Name || . ||
                           refAllTab.Column_Name ||  =  || nCount);
    end if;
  
  end loop;
  close all_tab_cursor;
end search_date;

    查询范围日期:

create or replace procedure search_date_between(pStartToDateString in varchar, pEndToDateString in varchar) as
  cursor all_tab_cursor is
    select a.owner, a.table_name, b.column_name
      from dba_tables a, dba_tab_columns b, dba_objects c
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.table_name = c.object_name
       --and a.owner in (‘XXX‘)  --用户可选
       and (b.data_type = DATE or b.data_type like TIMESTAMP%)
       and c.object_type = TABLE
     order by a.owner, a.table_name, b.column_id;
  refAllTab all_tab_cursor%rowtype;

  --------------

  sSql   varchar(4000);
  nCount number;

begin
  DBMS_OUTPUT.Enable(4000000);

  open all_tab_cursor;
  loop
    fetch all_tab_cursor
      into refAllTab;
    exit when all_tab_cursor%notfound;

    sSql := SELECT COUNT(1) FROM  || refAllTab.Owner || . ||
            refAllTab.Table_Name ||  WHERE  || refAllTab.Column_Name ||
             BETWEEN  || pStartToDateString ||  AND  || pEndToDateString;
    --DBMS_OUTPUT.PUT_LINE(sSql);
    execute immediate sSql
      into nCount;

    if nCount > 0 then
      DBMS_OUTPUT.PUT_LINE(refAllTab.Owner || . || refAllTab.Table_Name || . ||
                           refAllTab.Column_Name ||  =  || nCount);
    end if;

  end loop;
  close all_tab_cursor;
end search_date_between;

 

    执行范例:

    exec search_string(‘测试‘);

    exec search_number(100);

    exec search_number_between(100, 200);

    exec search_date(‘to_char(‘‘2013-01-01‘‘,‘‘yyyy-mm-dd‘‘)‘);

    exec search_date_between(‘to_char(‘‘2013-01-01‘‘,‘‘yyyy-mm-dd‘‘)‘,‘to_char(‘‘2014-01-01‘‘,‘‘yyyy-mm-dd‘‘)‘);

    以上存储过程执行完毕后,会输出格式文本:用户.表名.字段名 = 记录数,应用时根据实际情况修改。

 

    转载请注明原文地址:http://www.cnblogs.com/litou/p/3926881.html

[Oracle]根据字段值全库搜索相关数据表和字段,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。