如何让oracle数据库中整个表空间下任何表中任何字段值=A的替换为字段值=B?

空指针 发布于 2013/08/29 11:42
阅读 641
收藏 1
如题,想要整个表空间下任何表中任何字段值=A的替换为字段值=B。
加载中
0
空指针
空指针
declare
  str_sql varchar2(1000);
begin
  for alltables in (select table_name from user_tables) loop
    for tablecolumns in (select column_name, DATA_TYPE
                           from user_tab_columns
                          where table_name = to_char(alltables.table_name)) loop
      --dbms_output.put_line(to_char(tablecolumns.DATA_TYPE));
      if (tablecolumns.DATA_TYPE = 'VARCHAR2') then
        str_sql := 'update ' || to_char(alltables.table_name) || ' set ' ||
                   to_char(tablecolumns.column_name) ||
                   ' = ''B'' where ' ||
                   to_char(tablecolumns.column_name) ||
                   ' = ''A''';
        --dbms_output.put_line(str_sql);
        execute immediate str_sql;
      end if;
    end loop;
  end loop;
  commit;
end;
最终形成的脚本。 谢谢。
0
苗哥
苗哥
那表的主键和外键也都替换掉?这样会导致不可知的问题的,你能详细解释下这么做的背景和原因嘛?
空指针
空指针
大哥 有方法吗?谢谢
空指针
空指针
当然,实际中肯定不会替换主键。
0
苗哥
苗哥
我知道 oracle 有个功能可以以 sysadmin 的身份查询到指定 schema 下所有表名,包括系统表,语法我现在不记得,不过你可以在 google 上搜索下,然后你可以在此基础上遍历出所有非系统表的字段,循环替换,后面的步骤你可以写個存储过程来实现,如果数据量很大的话,建议批量多次执行存储过程……
0
opal
opal

要点:

select * from user_tables

select * from user_tab_columns

execute immediate('update table set col=''B'' where col=''A'')

0
空指针
空指针
declare
  str_sql varchar2(1000);
begin
  for alltables in (select table_name from user_tables) loop
    for tablecolumns in (select column_name
                         from user_tab_columns
                         where table_name = alltables.table_name) loop
      str_sql := 'update ' || alltables.table_name || ' set ' ||
                 tablecolumns.column_name ||
                 ' = ''B'' where ' ||
                 tablecolumns.column_name ||
                 ' = ''A''';
      --dbms_output.put_line(str_sql);
      execute immediate str_sql;
    end loop;
  end loop;
  commit;
end;
@ 铂金小熊  这段脚本执行时报:“ORA-01861:文字与格式字符串不匹配”  的错误。不明白是为什么,确实要替换和被替换的字段都是字符串类型的。
空指针
空指针
回复 @铂金小熊 : 试过了 !错误依旧是那个。
苗哥
苗哥
回复 @空指针 : 你有没有试过将变量 tablecolumns.column_name 和 alltables.table_name 转化成字符串,将它们写成 to_char(tablecolumns.column_name) 和 to_char(alltables.table_name) 再试试?
0
opal
opal

用 dbms_output.put_line(str_sql)打出来,然后单独执行,等等

空指针
空指针
打印了,发现打印出来的update语句中有DB的关键字,是不是因为这个呢,也不知道怎么确定?
0
苗哥
苗哥
我刚才在自己机器上调试了下,你说的问题在我的机器上没有遇到过,分析可能是因为版本不同引起的,我这边的环境是 oracle 11g,不知道楼主那边是哪个版本。另外,我发现如果直接使用 dbms_output.put_line() 函数打印 SQL 语句,会出现 buffer overflow 错误,你可以先把缓冲区的容量设置大壹些直接用这种方式打印出所有的 SQL 语句,然后将它们复制出来执行。
空指针
空指针
呵呵,就是因为生成的语句中有DATA 类型,所以加了一句判断,过滤了一下:if(tablecolumns.DATA_TYPE = 'VARCHAR2' ) then。 我用的也是oracle 11g。谢谢了。
返回顶部
顶部