搜索oracle表里的内容.txt

declare
--表内容搜索
--有N个表，查找哪个表里有自己需要的字符。例如，我要找哪些表里的数据有“飞信”。共2处需要改动。
--1这里需要确定哪些表
    cursor cur_tab is
        select table_name, owner
          from dba_tables
         where 1 = 1
           and owner = 'DEV_VGOP'
          -- and table_name = 'TD_B_SERVICE'
           and 1=1;

    cursor cur_col(v1 varchar2, v2 varchar2) is
        SELECT column_name, data_type, column_id
          FROM DBA_TAB_COLUMNS
         where owner = v1
           and table_name = v2
         order by column_id;
    v_column varchar2(4000);
    v_ct number;
     v_ct2 number;
     v_column_name varchar2(30);
begin

    for cr_tab in cur_tab loop
        v_column := null;
        for cr_col in cur_col(cr_tab.owner, cr_tab.table_name) loop
            
        if cr_col.data_type='VARCHAR2' OR cr_col.data_type='CHAR' THEN
            v_column_name:=cr_col.column_name;

        ELSE 
        v_column_name:=0;
        END IF;    
            
            if cr_col.column_id = 1 then
                v_column := v_column_name;
            else
                v_column := v_column || '||' || v_column_name;
            end if;
        end loop;

--2确定搜索内容        
          --  v_column:='select sum(1)||''/''||sum(case when aaa like ''%飞信%'' then 1 else 0 end ) from (select '||v_column||' aaa from '||cr_tab.owner||'.'||cr_tab.table_name||')  ';
            v_column:='select sum(1),sum(case when aaa like ''%飞信%'' then 1 else 0 end ) from (select '||v_column||' aaa from '||cr_tab.owner||'.'||cr_tab.table_name||')  ';
      
      execute immediate v_column into v_ct,v_ct2 ;
    if v_ct2>=1 then 
        dbms_output.put_line(cr_tab.table_name||'总条数:'||v_ct||'/关键词条数:'||v_ct2 );
    end if;
    end loop;
 --dbms_output.put_line(v_column);
end;