Oracle：修改指定模式下所有表SDO_GEOMETRY的SRID为NULL

你在批量处理LANDWERTZONEN用户下表的过程中遇到的问题，核心在于没有正确引用游标属性以及未使用动态SQL执行带变量表名的语句——Oracle静态SQL不支持动态指定表名，必须通过EXECUTE IMMEDIATE来执行拼接好的动态SQL语句。

修正后的批量处理代码 #

SET SERVEROUTPUT ON;
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  FOR my_tables IN (
    SELECT TABLE_NAME 
    FROM all_tables 
    WHERE OWNER = 'LANDWERTZONEN' 
      AND TABLE_NAME NOT LIKE 'GOOM%' 
      AND TABLE_NAME NOT LIKE '%BKP'
      -- 额外过滤：确保表包含geometrie列，避免执行时报错
      AND EXISTS (
        SELECT 1 
        FROM all_tab_columns 
        WHERE OWNER = 'LANDWERTZONEN' 
          AND TABLE_NAME = my_tables.TABLE_NAME 
          AND COLUMN_NAME = 'GEOMETRIE'
      )
  ) LOOP
    -- 拼接动态SQL语句
    v_sql := 'UPDATE ' || my_tables.TABLE_NAME || ' t SET t.geometrie.sdo_srid = null';
    
    DBMS_OUTPUT.PUT_LINE('执行语句: ' || v_sql);
    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
    
    -- 打印当前表的影响行数
    DBMS_OUTPUT.PUT_LINE('影响行数: ' || SQL%ROWCOUNT);
  END LOOP;
  -- 提交事务（根据业务场景决定是否自动提交，若需手动控制可注释此行）
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('处理表 ' || my_tables.TABLE_NAME || ' 时出错: ' || SQLERRM);
    ROLLBACK;
    RAISE;
END;
/

关键修正点说明 #

• 游标属性正确引用：原代码直接用my_tables拼接字符串是错误的，必须通过my_tables.TABLE_NAME获取游标中的表名。
• 动态SQL执行：使用EXECUTE IMMEDIATE执行拼接好的SQL，这是Oracle中处理动态表名/列名的标准方式。
• 列存在性校验：添加EXISTS子句过滤出确实包含geometrie列的表，避免出现"列不存在"的运行时错误。
• 异常处理：增加异常捕获逻辑，能快速定位出错的表，同时通过回滚保证数据一致性。

注意事项 #

1. 测试先行：建议先在测试环境执行，或者仅打印要执行的语句确认无误后，再实际执行更新操作。
2. 事务控制：如果表数量较多，可考虑分批提交；若业务不允许部分更新，务必保留异常中的回滚逻辑。
3. 权限检查：确保当前用户拥有LANDWERTZONEN用户下目标表的UPDATE权限，以及查询all_tables和all_tab_columns的权限。

内容的提问来源于stack exchange，提问作者Pramisters