ITPub博客

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

原创 Oracle 作者:selectshen 时间:2017-01-22 21:29:56 0 删除 编辑
--存放用户的用户名,表名,列名,列类型,批次号(表中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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2132813/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-05

  • 博文量
    169
  • 访问量
    1423613