在Oracle中更新所有表中的所有行

Question

我有一个字段（clientID），它在我的大多数表格中都重复出现（大约100个左右），我正在尝试使用oracle 11g一次性更新所有表格。我能够获取表格的名称，但无法使用它。以下是代码：

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        -- Update Name.TABLE_NAME set ClientID = 1 Where ClientID = 2; --This line does not work message "Table or view does not exist"
   END LOOP;
END;

- Icaro

Answer 1

您可以尝试使用EXECUTE IMMEDIATE来使用动态SQL。类似于以下内容：

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = 1 Where ClientID = 2';
   END LOOP;
END;

如果您需要动态设置要更新的值，则可以使用动态SQL绑定参数的方式。我只是一时想不起来如何做。但如果您需要，请在评论中提出，我会找到相关信息。

编辑

我认为如果您要绑定这些值，它可能看起来像这样：

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = :1 Where ClientID = :2'
                          USING 1, 2;
   END LOOP;
END;

...在这里，您可以使用变量而不是硬编码常数值，代替USING 1,2。

更多信息请参见：EXECUTE IMMEDIATE。

编辑2

如果您需要跳过没有所需列的表，请使用以下调整后的查询：

BEGIN
   FOR Name IN (
     SELECT t.TABLE_NAME 
     FROM all_tables t
     where t.TABLESPACE_NAME='MyTableSpace'
     and exists (
       select null
       from all_tab_columns c
       where c.table_name = t.table_name
       and c.column_name = 'CLIENTID' -- put the right column name here
     )
   ) LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = 1 Where ClientID = 2';
   END LOOP;
END;

您可能需要稍微调整一下，因为我面前没有Oracle数据库。 我可能拼错了几个单词。

- sstan

---

嗨@sstan，它几乎可以工作了。我有很少的表没有这个字段，它会崩溃进程，我可能需要找到一种方法在执行SQL之前检查表中是否存在该字段。 - Icaro

它没有起作用，表达式返回nil，这非常奇怪，即使我运行select * from all_tab_columns c where c.column_name = 'ClientID'，我也得到了一个空结果。 - Icaro

你试过将 CLIENTID 全部大写吗？默认情况下，Oracle 将所有列名存储为大写。或者，只需执行 SELECT column_name FROM all_tab_columns WHERE table_name = '选择一个表名'，并检查 Oracle 认为的列名是什么。听起来你已经很接近了，你只需要弄清楚如何查询列元数据。 - sstan

是的，谢谢，那就是问题所在。我应该使用全部大写字母，现在它可以工作了。变量在“EXECUTE IMMEDIATE”中无法工作，但我硬编码后它就可以正常工作了（也许你想编辑一下答案）。再次感谢！ - Icaro