Oracle 查询库中所有表中匹配指定关键字的数据的总数量

CREATE OR REPLACE
PROCEDURE PRODUCE_QUERY_KEY_WORD_RECORD  (
  V_KEY_WORD IN VARCHAR2
) Authid Current_User  --加上该句是为解决存储过程中新建的表，进行访问时提示权限不足的问题
AS 
  V_SQL     VARCHAR2(4000);							
  V_TB_COL  VARCHAR2(512);
	
	--记录每个匹配列中含有关键字记录总数
  V_CNT     NUMBER(18,0);	
	
	--记录每个有匹配表的总匹配记录数
	V_COUNT_OF_TABLE NUMBER(18,0) DEFAULT 0;		
	
	--记录总的匹配记录条数
	V_TOTAL   NUMBER(18, 0);
	
	--记录上次查询字段所属表
	V_LASTTABLE VARCHAR2(150) DEFAULT 'null'; 
	
	--记录当前查询字段所属表
	V_CURTABLE  VARCHAR2(150) DEFAULT 'null';  
	
	--创建表语句
	V_SQL_CREATE_TMP_TABLE VARCHAR2(500);
	
	--临时表旧数据删除语句
	V_SQL_TRUNCATE_TMP_TABLE VARCHAR2(100);
	
	--单个表匹配数据总量
	V_TEP_TABLE_COUNT VARCHAR2(100);

  
  --使用游标，将所有表的每个字段的每行数据值与关键字匹配的查询拼接为sql
  CURSOR CUR_SQLS IS 
    SELECT 'SELECT ''' || X.TABLE_NAME || '.' || X.COLUMN_NAME || ''' AS COL_NAME, NVL(COUNT(' || X.COLUMN_NAME || '),0) AS CNT FROM ' || X.TABLE_NAME 
           || ' A WHERE A.' || X.COLUMN_NAME || ' LIKE ''%' || V_KEY_WORD || '%''' AS SQL_STR 
    FROM COLS X JOIN USER_OBJECTS Y ON X.TABLE_NAME = Y.OBJECT_NAME
    WHERE Y.TEMPORARY <> 'Y' AND OBJECT_TYPE = 'TABLE' 
          AND X.DATA_TYPE IN ('CHAR','VARCHAR2','VARCHAR') 
    ORDER BY X.TABLE_NAME,COLUMN_ID;


BEGIN

	select count(*)
    into V_TEP_TABLE_COUNT
    from user_tables
   where table_name = UPPER('tmp_keyword_info');

	-- 创建表tmp用于存放查询结果
	V_SQL_CREATE_TMP_TABLE := 'create table tmp_keyword_info (
															table_and_col VARCHAR(64),
															count_sql varchar(200)
													)';
	 
	V_SQL_TRUNCATE_TMP_TABLE := 'TRUNCATE TABLE tmp_keyword_info';
	
	IF V_TEP_TABLE_COUNT < 1 THEN 
		BEGIN
			EXECUTE IMMEDIATE V_SQL_CREATE_TMP_TABLE;	
		END;
	END IF;
	
	EXECUTE IMMEDIATE V_SQL_TRUNCATE_TMP_TABLE;

  FOR CV_SQL IN CUR_SQLS LOOP
    V_SQL := CV_SQL.SQL_STR; 
    BEGIN
      EXECUTE IMMEDIATE V_SQL INTO V_TB_COL, V_CNT;
			--获取当前查询表名
			V_CURTABLE := SUBSTR(V_TB_COL,1,INSTR(V_TB_COL,'.',1,1) - 1);
      IF V_CNT > 0 THEN
				
				CASE WHEN V_LASTTABLE != 'null' AND V_LASTTABLE != V_CURTABLE THEN
					--如果当前查询表与上次查询表非同一张表，则
					--插入上张匹配表的总匹配记录数
					EXECUTE IMMEDIATE 'INSERT INTO tmp_keyword_info VALUES(''表【'||V_LASTTABLE||'】总匹配记录数'',' || V_COUNT_OF_TABLE|| ')';
					--改变最后一次查询匹配表的表名
					V_LASTTABLE := SUBSTR(V_TB_COL,1,INSTR(V_TB_COL,'.',1,1) - 1);
					--归零表总匹配记录数
					V_COUNT_OF_TABLE := V_CNT;
				ELSE
					IF V_LASTTABLE = 'null' THEN
						--若当前为第一次查询匹配记录，则更新最后一次查询表名
						V_LASTTABLE := V_CURTABLE;
						V_COUNT_OF_TABLE := 0;
						DBMS_OUTPUT.PUT_LINE('V_COUNT_OF_TABLE 初始化:' || V_COUNT_OF_TABLE);
					END IF;	
					--更新表总匹配记录数
					V_COUNT_OF_TABLE := V_COUNT_OF_TABLE+V_CNT;
					DBMS_OUTPUT.PUT_LINE('V_TB_COL:' || V_TB_COL || ', V_CNT' || V_CNT);
					DBMS_OUTPUT.PUT_LINE('V_COUNT_OF_TABLE:' || V_COUNT_OF_TABLE);
				END CASE;	
				
				--统计总的匹配记录数
				V_TOTAL := V_TOTAL+V_CNT;
				--插入新的匹配记录
				EXECUTE IMMEDIATE 'INSERT INTO tmp_keyword_info VALUES('''|| V_TB_COL || ''',replace(''' || replace(V_SQL, chr(39), chr(34)) || ''',chr(34), chr(39)))';
				
      END IF;
    EXCEPTION WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('sqlcode='||sqlcode);
			DBMS_OUTPUT.PUT_LINE('sqlerrm='||sqlerrm);
    END;
  END LOOP;
	
	--插入最后一张表的匹配记录
	EXECUTE IMMEDIATE 'INSERT INTO tmp_keyword_info VALUES(''表【'||V_LASTTABLE||'】总匹配记录数'',' || V_COUNT_OF_TABLE|| ')';

END PRODUCE_QUERY_KEY_WORD_RECORD;

该存储过程创建新建一张表，并将统计结果插入到新建的表中。该表名为：tmp_keyword_info。

使用方法：

1、现在数据库中执行以上存储过程创建sql。

2、调用存储过程：call PRODUCE_QUERY_KEY_WORD_RECORD('关键字')；

3、查询结果表：select * from tmp_keyword_info。

 

注意：

上例存储过程统计的列类型限制为了：CHAR、VARCHAR2、VARCHAR。若期望查询不同的类型的字段，可修改上例中的游标中查询语句的条件。