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

空指针发布于 2013/08/29 11:42

阅读 641

答案 7

Oracle

如题，想要整个表空间下任何表中任何字段值=A的替换为字段值=B。

加载中

空指针

2013/08/29 17:13

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) 引用此答案举报

苗哥

2013/08/29 11:58

那表的主键和外键也都替换掉？这样会导致不可知的问题的，你能详细解释下这么做的背景和原因嘛？

评论 (2) 引用此答案举报

空指针

2013/08/29 13:28

大哥有方法吗？谢谢

回复举报

空指针

2013/08/29 12:51

当然，实际中肯定不会替换主键。

回复举报

苗哥

2013/08/29 13:48 Android

我知道 oracle 有个功能可以以 sysadmin 的身份查询到指定 schema 下所有表名，包括系统表，语法我现在不记得，不过你可以在 google 上搜索下，然后你可以在此基础上遍历出所有非系统表的字段，循环替换，后面的步骤你可以写個存储过程来实现，如果数据量很大的话，建议批量多次执行存储过程……

评论 (0) 引用此答案举报

opal

2013/08/29 14:36

要点：

select * from user_tables

select * from user_tab_columns

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

评论 (0) 引用此答案举报

空指针

2013/08/29 14:38

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:文字与格式字符串不匹配” 的错误。不明白是为什么，确实要替换和被替换的字段都是字符串类型的。

评论 (3) 引用此答案举报

空指针

2013/08/29 15:42

回复 @铂金小熊 : 试过了！错误依旧是那个。

回复举报

苗哥

2013/08/29 14:52

回复 @空指针 : 你有没有试过将变量 tablecolumns.column_name 和 alltables.table_name 转化成字符串，将它们写成 to_char(tablecolumns.column_name) 和 to_char(alltables.table_name) 再试试？

回复举报

空指针

2013/08/29 14:43

@opal @铂金小熊

回复举报

opal

2013/08/29 14:50

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

评论 (1) 引用此答案举报

空指针

2013/08/29 15:43

打印了，发现打印出来的update语句中有DB的关键字，是不是因为这个呢，也不知道怎么确定？

回复举报

苗哥

2013/08/29 16:07

我刚才在自己机器上调试了下，你说的问题在我的机器上没有遇到过，分析可能是因为版本不同引起的，我这边的环境是 oracle 11g，不知道楼主那边是哪个版本。另外，我发现如果直接使用 dbms_output.put_line() 函数打印 SQL 语句，会出现 buffer overflow 错误，你可以先把缓冲区的容量设置大壹些直接用这种方式打印出所有的 SQL 语句，然后将它们复制出来执行。

评论 (1) 引用此答案举报

空指针

2013/08/29 17:10

呵呵，就是因为生成的语句中有DATA 类型，所以加了一句判断，过滤了一下：if(tablecolumns.DATA_TYPE = 'VARCHAR2' ) then。我用的也是oracle 11g。谢谢了。

回复举报