如何在所有类型为字符串的表格中修剪所有行的所有列？

Question

在Oracle 10g中，有没有一种方法可以在PL/SQL中实现以下操作？

for each table in database
  for each row in table
    for each column in row
      if column is of type 'varchar2'
        column = trim(column)

谢谢！

- daveslab

---

请查看 USER_TABLES 和 USER_TAB_COLUMNS 系统表：http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_5443.htmhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_5429.htm#REFRN26277我认为你应该能够使用它们来构建一个动态查询，以实现你想要的功能。 - Paul Michaels

@pm_2 非常感谢您的建议，看起来就像下面的人所做的那样。 - daveslab

Answer 1

当然，进行大规模的动态更新可能是危险和耗时的。但以下是您可以生成想要的命令的方法。这是针对单个架构的，只会构建命令并输出它们。您可以将它们复制到脚本中并在运行之前进行审查。或者，您可以将 dbms_output.put_line( ... ) 更改为 EXECUTE IMMEDIATE ...，以使此脚本执行所有生成的语句。

SET SERVEROUTPUT ON

BEGIN
  FOR c IN
    (SELECT t.table_name, c.column_name
       FROM user_tables t, user_tab_columns c
       WHERE c.table_name = t.table_name
         AND data_type='VARCHAR2')
  LOOP

    dbms_output.put_line(
                      'UPDATE '||c.table_name||
                      ' SET '||c.column_name||' = TRIM('||c.column_name||') WHERE '||
                      c.column_name||' <> TRIM('||c.column_name||') OR ('||
                      c.column_name||' IS NOT NULL AND TRIM('||c.column_name||') IS NULL)'
                     );
  END LOOP;
END;

- Dave Costa

---

USER_TAB_COLUMNS 包含表和视图中的列。为使其正常工作，您需要过滤掉视图。 - Allan

如果您的数据库包含许多表和VARCHAR2类型的列，则dbms_output.put_line的缓冲区可能不足以包含所有UPDATE语句，因此最好使用INSERT语句将UPDATE语句存储在Oracle表中，而不是使用DBMS_OUTPUT.PUT_LINE语句。 - UltraCommit

同意，但是对于我的表格来说，只有几个，我只是增加了输出缓冲区，就可以正常工作了。 - daveslab

Answer 2

我猜您希望对模式中的每个列进行此操作，而不是对整个数据库进行操作。尝试对字典表执行此操作是一个不好的主意...

declare
  v_schema varchar2(30) := 'YOUR_SCHEMA_NAME';
  cursor cur_tables (p_schema_name varchar2) is
    select owner, table_name, column_name 
    from all_tables at,
      inner join all_tab_columns atc
        on at.owner = atc.owner 
          and at.table_name = atc.table_name
    where atc.data_type = 'VARCHAR2'
      and at.owner = p_schema;
begin
  for r_table in cur_tables loop
    execute immediate 'update ' || r.owner || '.' || r.table_name
      || ' set ' || r.column_name || ' = trim(' || r.column_name ||');';
  end loop;
end;

这仅适用于在第一次设置时为VARCHAR2的字段。如果您的数据库包含CHAR字段，则无法使用此方法，因为CHAR字段总是填充到其最大长度。

- Allan