在Oracle表中进行关键词搜索的过程

DECLARE

     p_search_text      CONSTANT VARCHAR2 (1000) := 'SAMPLE SEARCH WRITE' ;

     p_schema_name      CONSTANT VARCHAR2 (1000) := 'SAMPLE SCHEMA NAME' ;

     p_table_name       CONSTANT VARCHAR2 (1000) := 'SAMPLE TABLE NAME' ; -- NULL (If FULL will work, give the table name null)

     TYPE r_column_data IS RECORD

     (

         column_name    SYS.DBA_TAB_COLUMNS.COLUMN_NAME%TYPE

     );

     TYPE tr_column_data IS TABLE OF r_column_data

         INDEX BY PLS_INTEGER;

     ltr_column_data             tr_column_data;

     TYPE tr_table IS TABLE OF VARCHAR2 (200)

         INDEX BY PLS_INTEGER;

     l_sql                       VARCHAR2 (1000);

     l_count                     NUMBER;

     ltr_table                   tr_table;

     l_found                     NUMBER := 0;

     l_column_name               VARCHAR2 (100);

BEGIN

       SELECT table_name

         BULK COLLECT INTO ltr_table

         FROM dba_tables

        WHERE      owner = p_schema_name

              AND table_name LIKE '' || p_table_name || '' || '%'

              AND ROWNUM <= 19999

     ORDER BY 1;

     DBMS_APPLICATION_INFO.set_module ( 'PV_FINDER' , NULL );

     FOR i IN 1 .. ltr_table. COUNT

     LOOP

         DBMS_APPLICATION_INFO.set_client_info (

             i || '/' || ltr_table. COUNT || ' -> ' || ltr_table (i));

           SELECT col.column_name

             BULK COLLECT INTO ltr_column_data

             FROM sys.dba_tab_columns col

                  INNER JOIN sys.dba_tables t

                      ON col.owner = t.owner AND col.table_name = t.table_name

            WHERE col.table_name = ltr_table (i) AND col.DATA_TYPE = 'VARCHAR2'

         ORDER BY col.column_id;

         FOR j IN 1 .. ltr_column_data. COUNT

         LOOP

             DBMS_APPLICATION_INFO.set_client_info (

                    j

                 || '/'

                 || ltr_column_data. COUNT

                 || ' -> '

                 || ltr_column_data (j).column_name);

             l_sql :=

                    'SELECT /*+ PARALLEL (A 8)*/ count(1) from '

                 || p_schema_name

                 || '.'

                 || ltr_table (i)

                 || ' A WHERE '

                 || ltr_column_data (j).column_name

                 || ' =' ''

                 || p_search_text

                 || '' '' ;

             EXECUTE IMMEDIATE l_sql INTO l_count;

             IF l_count > 0

             THEN

                 IF l_found = 0

                 THEN

                     DBMS_OUTPUT.put_line (

                         'Search Keyword: ' || p_search_text);

                     DBMS_OUTPUT.put_line (

                         '-------------------------------------------' );

                 END IF;

                 DBMS_OUTPUT.put_line ( 'table name found : ' ||ltr_table (i) );

                 DBMS_OUTPUT.put_line ( 'column name found : ' || ltr_column_data (j).column_name);

                 DBMS_OUTPUT.put_line ( 'count : ' || l_count);

                 DBMS_OUTPUT.put_line ( 'sql name : ' || l_sql);

                 DBMS_OUTPUT.put_line ( '*****' );

                 l_found := l_found + 1;

             END IF;

         END LOOP;

     END LOOP;

     DBMS_APPLICATION_INFO.set_module ( NULL , NULL );

     DBMS_APPLICATION_INFO.set_client_info ( NULL );

     DBMS_OUTPUT.put_line ( '-------------------------------------------' );

     DBMS_OUTPUT.put_line ( 'total number of tables searched : ' || ltr_table. COUNT );

     DBMS_OUTPUT.put_line ( 'total number of tables found : ' || l_found);

END ;

/* select module,client_info from v$session where module like '%PV_FINDER%' */

Search Keyword: SAMPLE SEARCH KEYWORD

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

table name found : TABLE - 1

column name found : COLUMN NAME

count : 4

sql name : SELECT /*+ PARALLEL (A 8)*/ count (1) from SCHEMA_NAME. TABLE -1 A WHERE TABLE -1. COLUMN = 'SAMPLE SEARCH KEYWORD'

*****

table name found : TABLE - 2

column name found : COLUMN NAME

count : 2

sql name : SELECT /*+ PARALLEL (A 8)*/ count (1) from SCHEMA_NAME. TABLE -2 A WHERE TABLE -1. COLUMN = 'SAMPLE SEARCH KEYWORD'

*****

table name found : TABLE - 3

column name found : COLUMN NAME

count : 2

sql name : SELECT /*+ PARALLEL (A 8)*/ count (1) from SCHEMA_NAME. TABLE -3 A WHERE TABLE -1. COLUMN = 'SAMPLE SEARCH KEYWORD'

*****

table name found : TABLE - 4

column name found : COLUMN NAME

count : 2

sql name : SELECT /*+ PARALLEL (A 8)*/ count (1) from SCHEMA_NAME. TABLE -4 A WHERE TABLE -1. COLUMN = 'SAMPLE SEARCH KEYWORD'

*****

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

total number of tables searched : 72

total number of tables found : 4