转载

oracle实用sql(12)--查询数据是否在某个用户下各个表的各个列

--存放用户的用户名，表名，列名，列类型,批次号(表中50个列分一个批次)。owner根据自己的需求改
--drop table tb_all_column
create table tb_all_column as
select owner, table_name, column_name, data_type,trunc(row_number() over (partition by owner,table_name order by 1)/50) lot
from dba_tab_columns a
where table_name not like 'BIN$%'
   and data_type in('VARCHAR2','CHAR','NVARCHAR2','NCHAR','CLOB')
   and owner='SCOTT'

--存放用户的用户名，表名，批次号，is_search是否检查过，is_exists表中是否存在满足条件的表。owner根据自己的需求改
--后面可以通过查询表tb_search，来确认有没有检查完，有没有满足条件的表
--drop table tb_search
create table tb_search as
select distinct owner, table_name,lot, 'N' is_search, 'N' is_exists
from tb_all_column
order by owner, table_name,lot;

--处理
declare
v_search varchar2(50) := 'xxx';
i_count integer;
begin
--对于没有检查过的表进行检查
for i in (select owner,
                   table_name,
                   lot,
                    'select /*+parallel(t 8)*/ count(1) from ' || owner || '.' || table_name ||
                   ' t where 1=2 ' || col_where i_sql
              from (select a.owner,
                           a.table_name,
                           a.lot,
                           to_char(replace(wmsys.wm_concat('or ' ||
                                                           a.column_name ||
                                                           ' like ' || '''%' ||
                                                           v_search || '%'' '),
                                           ',',
                                           '')) col_where
                      from tb_all_column a, tb_search b
                     where a.owner = b.owner
                       and a.table_name = b.table_name
                       and a.lot = b.lot
                       and b.is_search = 'N'
                     group by a.owner, a.table_name, a.lot)) loop
    --这里我只是把满足条件的表找出来，也可以通过rowid的方式把表中指定的行记录下来。
    execute immediate i.i_sql
      into i_count;
    --如果没有满足条件的记录，只标记 is_search = 'Y';如果有满足条件的记录，标记 is_search = 'Y', is_exists = 'Y'
    if i_count = 0 then
      begin
        update tb_search
           set is_search = 'Y'
         where owner = i.owner
           and table_name = i.table_name
           and lot = i.lot;
        commit;
      end;
    else
      begin
        update tb_search
           set is_search = 'Y', is_exists = 'Y'
         where owner = i.owner
           and table_name = i.table_name
           and lot = i.lot;
        commit;
      end;
    end if;
end loop;
end;

正文到此结束