[Oracle] 遍历数据库查找字符串

信息差Pro

已于 2024-01-23 09:12:23 修改

阅读量438

点赞数 11

分类专栏： Oracle 文章标签： oracle 数据库数据分析

于 2024-01-19 09:21:18 首次发布

本文链接：https://blog.csdn.net/qq_39194322/article/details/135689071

版权

Oracle 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

declare
  v_Sql   varchar2(2000);
  v_count number;
begin
  for xx in (select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME 
             from dba_tab_columns t 
             where t.OWNER = upper('限制用户')
             and TABLE_NAME in (select distinct TABLE_NAME from dba_tab_columns where OWNER = upper('限制用户')
                                and TABLE_NAME like upper('限制表%')
                                and COLUMN_NAME = upper('限制字段')
                                )
             ) loop
      v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||' where ' 
               || xx.column_name || ' like ''输入要查找的数据'' and data_dt = ''yyyy-mm-dd'' '; --限制搜索日期

      execute immediate v_Sql into v_count;
      if (v_count >= 1) then
        dbms_output.put_line(xx.table_name || ':' || xx.column_name);
      end if;

  end loop;
end;