查询所有表的所有字段包含指定关键字且替换为指定关键代码,Oracle代码库,德仔网

代码语言

代码分类

【Oracle】查询所有表的所有字段包含指定关键字且替换为指定关键

作者: / 发布于2018/5/14/ 523

查询所有表的所有字段包含指定关键字且替换为指定关键字

declare
  v_Sql   varchar2(2000);
  v_UpdateSql   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 = 'PT_QH_GL') loop
    begin
      v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||
               ' where ' || xx.column_name || ' like ''%key1%'' ';
      execute immediate v_Sql into v_count;
      if (v_count >= 1) then
        v_UpdateSql := 'update ' || xx.owner || '.' || xx.table_name || ' set ' || xx.column_name || ' = ' || 'replace(' || xx.column_name || ', ''key1'',''key2'')' || 
               ' where ' || xx.column_name || ' like ''%key1%'' ';
         execute immediate v_UpdateSql;
         commit;
        --dbms_output.put_line(xx.table_name || ':' || xx.column_name);
      end if;
    exception
      when others then
        null;
    end;
  end loop;
end;

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label