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

发布于 2024-09-01 18:00:46 字数 224 浏览 8 评论 0原文

在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)

谢谢！

原文

In Oracle 10g, is there a way to do the following in 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)

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

水晶透心 2024-09-08 18:00:46

当然，进行大规模动态更新可能存在危险且耗时。但您可以通过以下方式生成所需的命令。这是针对单个模式的，只会构建命令并输出它们。您可以将它们复制到脚本中并在运行之前检查它们。或者，您可以将 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;

Of course, doing large-scale dynamic updates is potentially dangerous and time-consuming. But here's how you can generate the commands you want. This is for a single schema, and will just build the commands and output them. You could copy them into a script and review them before running. Or, you could change dbms_output.put_line( ... ) to EXECUTE IMMEDIATE ... to have this script execute all the statements as they are generated.

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;

回复收藏 0 原文

半枫 2024-09-08 18:00:46

大概您希望对架构中的每一列执行此操作，而不是对数据库中的每一列执行此操作。尝试对字典表执行此操作是一个坏主意...

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 字段始终会填充到其最大长度。

Presumably you want to do this for every column in a schema, not in the database. Trying to do this to the dictionary tables would be a bad idea...

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;

This will only work for fields that are VARCHAR2s in the first place. If your database contains CHAR fields, then you're out of luck, because CHAR fields are always padded to their maximum length.

回复收藏 0 原文

~没有更多了~