首页 > 代码库 > [Oracle]根据字段值全库搜索相关数据表和字段
[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
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。