详解在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