oracle 搜索,Oracle数据库搜索工具

当我们在学习一套新系统时，需要熟悉后台的表结构以及该系统的数据模型时，

我们往往是在前台操作，然后到后台查看表中的数据变化。

我们又怎么能够知道是哪些表呢？当然有人告诉你是效率最高的，

但是如果没人告诉你那就只好自己解决了，现在我写的这个工具，就是根据你在前台看到的一些信息，比如说名字，

然后你直接到后台去搜索名字，该工具会搜索出你指定条件的范围，并且提供一个生成好的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;