当我们在学习一套新系统时，需要熟悉后台的表结构以及该系统的数据模型时，
我们往往是在前台操作，然后到后台查看表中的数据变化。
我们又怎么能够知道是哪些表呢？当然有人告诉你是效率最高的，
但是如果没人告诉你那就只好自己解决了，现在我写的这个工具，就是根据你在前台看到的一些信息，比如说名字，
然后你直接到后台去搜索名字，该工具会搜索出你指定条件的范围，并且提供一个生成好的SQL给你，以方便使用者：
CREATE OR REPLACE PROCEDURE P_DB_SEARCH_TOOL(TABLE_OWNER VARCHAR2 DEFAULT '.*',
COLUMN_TYPE VARCHAR2,
TARGET_STRING VARCHAR2) IS
/************************************************************************************
* Oracle search tool ( Version 1.0 ) *
* *
* TABLE_OWNER : The user that the tool will search. *
* By default, search all the users(exclude system users) in the DB *
* COLUMN_TYPE : The target column type that will be searched (fuzzy match) *
* TARGET_STRING : The target string that will be searched (fuzzy match) *
* *
* Note: Strongly suggest compile in user SYS, to avoid evitable troubles *
* By Jeans 2013-4-25 *
************************************************************************************/
/*----------------------------------------------------------------------------------------------------
Demo:
SET serveroutput ON;
exec P_DB_SEARCH_TOOL(TABLE_OWNER => 'JEANS', COLUMN_TYPE => 'NVARCHAR', TARGET_STRING => '康');
----------------------------------------------------------------------------------------------------*/
V_USER_EXIST_FLAG VARCHAR2(200);
/*-----------------------------------------------------------
Generate dynamic SQLs those will be used to search the DB
-----------------------------------------------------------*/
CURSOR CS IS
SELECT 'SELECT /*+ FULL*/ COUNT(1), ''' || T.OWNER || '.' ||
T.TABLE_NAME || ''', ''' || T.COLUMN_NAME || ''' FROM ' ||
T.OWNER || '."' || T.TABLE_NAME || '" WHERE ' ||
T.COLUMN_NAME || ' LIKE ''%' || TARGET_STRING || '%''' SQL_TEXT
FROM DBA_TAB_COLS T
WHERE REGEXP_LIKE(T.OWNER, UPPER(TABLE_OWNER))
AND REGEXP_LIKE(T.DATA_TYPE, UPPER(COLUMN_TYPE))
AND T.OWNER
/*------------------
Not system users
------------------*/
NOT IN ('OE', 'PM', 'BI', 'SH', 'IX', 'HR', 'SCOTT', 'OWBSYS_AUDIT', 'OWBSYS',
'APEX_PUBLIC_USER', 'FLOWS_FILES', 'APEX_030200', 'MGMT_VIEW',
'SYSMAN', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'MDDATA',
'OLAPSYS', 'MDSYS', 'SI_INFORMTN_SCHEMA','ORDDATA', 'ORDSYS', 'ORDPLUGINS',
'XS$NULL', 'ANONYMOUS', 'XDB', 'CTXSYS', 'EXFSYS', 'WMSYS', 'APPQOSSYS',
'DBSNMP', 'ORACLE_OCM', 'DIP', 'OUTLN', 'SYS', 'SYSTEM');
V_SQL VARCHAR2(2000);
ROW_CNT NUMBER;
TARGET_TABLE VARCHAR2(2000);
TARGET_COLUMN VARCHAR2(2000);
BEGIN
/*--------------------------
To avoid buffer overflow
--------------------------*/
DBMS_OUTPUT.ENABLE(10000000);
/*----------------------------------------------------------
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
Check current user
----------------------------------------------------------*/
SELECT COUNT(1) INTO V_USER_EXIST_FLAG
FROM DBA_USERS T
WHERE REGEXP_LIKE(T.USERNAME, UPPER(TABLE_OWNER))
AND T.USERNAME
NOT IN ('OE', 'PM', 'BI', 'SH', 'IX', 'HR', 'SCOTT', 'OWBSYS_AUDIT', 'OWBSYS',
'APEX_PUBLIC_USER', 'FLOWS_FILES', 'APEX_030200', 'MGMT_VIEW',
'SYSMAN', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'MDDATA',
'OLAPSYS', 'MDSYS', 'SI_INFORMTN_SCHEMA','ORDDATA', 'ORDSYS', 'ORDPLUGINS',
'XS$NULL', 'ANONYMOUS', 'XDB', 'CTXSYS', 'EXFSYS', 'WMSYS', 'APPQOSSYS',
'DBSNMP', 'ORACLE_OCM', 'DIP', 'OUTLN', 'SYS', 'SYSTEM');
/*--------------------------------------------------
To judge whether the given user exists in the DB
--------------------------------------------------*/
IF V_USER_EXIST_FLAG = 0 THEN
DBMS_OUTPUT.PUT_LINE('User:' || TABLE_OWNER || ' does not exist!');
RETURN;
END IF;
FOR V_SQL IN CS LOOP
/*-------------------------------------------------------------------------------------------------
Execute dynamic SQLs in a loop block, to search the DB and return all the corresponding results
-------------------------------------------------------------------------------------------------*/
BEGIN
EXECUTE IMMEDIATE V_SQL.SQL_TEXT INTO ROW_CNT, TARGET_TABLE, TARGET_COLUMN;
IF ROW_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(V_SQL.SQL_TEXT);
DBMS_OUTPUT.PUT_LINE('');
END IF;
IF ROW_CNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('Result: SELECT T.' || TARGET_COLUMN || ', T.*, T.ROWID FROM ' ||
TARGET_TABLE || ' T WHERE T.' || TARGET_COLUMN ||
' LIKE ''%' || TARGET_STRING || '%''; ' ||
'Row count:' || ROW_CNT );
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
RETURN;
END P_DB_SEARCH_TOOL;