[Oracle]根据字段值全库搜索相关数据表和字段
2024-08-28 07:35:23
这个需求比较冷门,但对于在某些特定的情况下,还是会有这样的需要的。好在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','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 ||
' = ''' || 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_string_like(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_like;
查询数字:
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_string_like('包含测试');
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
最新文章
- Android 仿美团网,大众点评购买框悬浮效果之修改版
- java.lang.IllegalStateException异常简单分析和简单解决
- python 自定义函数
- tomcat服务器 去掉端口8080 以及项目名 直接使用IP地址访问
- 【转载】chmod u+s
- 面试题(C#基础)
- HTML注释的一些规范
- ScrollView 的使用(非原创)
- Instruments性能检测
- Objective-C 类,实例成员,静态变量,对象方法,类方法(静态方法),对象,
- 经典的C++库【转帖】
- ssh的public key的使用
- 使用C#编写SqlHelper类
- 不要在#include中使用";..";
- [零] Java 语言运行原理 JVM原理浅析 入门了解简介 Java语言组成部分 javap命令使用
- 源码安装支持ffmpeg的opencv2
- 【iCore4 双核心板_ARM】例程十二:通用定时器实验——定时点亮LED
- [No0000121]Python教程4/9-输入和输出
- learn the python the hard way习题26~30总结
- day022 python (re模块和 模块)
热门文章
- JRebel 7.0.10 for intellij IDEA 2017.1
- Hive错误记录
- 【LeetCode-面试算法经典-Java实现】【109-Convert Sorted List to Binary Search Tree(排序链表转换成二叉排序树)】
- 5.全局异常捕捉【从零开始学Spring Boot】
- IOS 开发环境,证书和授权文件是什么?
- iOS学习(项目中遇到的错误1)
- Windows服务器SYSTEM权限Webshell无法添加3389账户情况突破总结
- WebView简单使用
- javascript 数组 find
- (六)Thymeleaf的 th:* 属性之—— th: ->;text&; utext&; href