存储过程构建执行动态SQL：多行结果存变量后无更新问题求助

核心原因 #

你的存储过程仅完成了动态SQL的拼接，但从未执行拼接好的SQL语句，这就是数据没有更新的根本原因。

修复方案 #

1. 添加动态SQL执行语句 #

在循环内拼接完SQL_STAT后，调用EXECUTE IMMEDIATE执行该语句：

FOR i IN 1..l_ROW.COUNT LOOP
    SQL_STAT := 'UPDATE DWH_ALM_GAP_STRESS_TEST SET ROW_NUM = ' || l_ROW(i).ROW_NUM || ' ' || l_QUERY(i).QUERY || ';' ;
    -- 执行动态SQL
    EXECUTE IMMEDIATE SQL_STAT;
    -- 可选：保留打印用于调试
    DBMS_OUTPUT.PUT_LINE(SQL_STAT);
END LOOP;

2. 优化游标逻辑（避免数据顺序不一致风险） #

原代码使用两个独立游标分别获取ROW_NUM和QUERY，当表数据有插入/删除时，可能出现两组数据顺序不匹配的问题。建议用单个游标同时获取成对的字段，简化逻辑同时提升可靠性：

CREATE OR REPLACE PROCEDURE SP_GET_ROW_NUM
AS
    -- 单个游标同时获取ROW_NUM和QUERY
    CURSOR AUDIT_MAP_CUR IS
        SELECT ROW_NUM, QUERY
        FROM DWH_ALM_ST_AUDIT_TRAIL_MAP;
    TYPE AUDIT_MAP_ntt IS TABLE OF AUDIT_MAP_CUR%ROWTYPE;
    l_audit_map AUDIT_MAP_ntt;
    SQL_STAT VARCHAR2(2000);
BEGIN
    OPEN AUDIT_MAP_CUR;
    FETCH AUDIT_MAP_CUR BULK COLLECT INTO l_audit_map;
    CLOSE AUDIT_MAP_CUR;

    FOR i IN 1..l_audit_map.COUNT LOOP
        SQL_STAT := 'UPDATE DWH_ALM_GAP_STRESS_TEST SET ROW_NUM = ' || l_audit_map(i).ROW_NUM || ' ' || l_audit_map(i).QUERY || ';' ;
        EXECUTE IMMEDIATE SQL_STAT;
        DBMS_OUTPUT.PUT_LINE(SQL_STAT);
    END LOOP;
    -- 若数据库未开启自动提交，需手动提交事务
    -- COMMIT;
END SP_GET_ROW_NUM;

额外注意事项 #

• 事务提交：如果你的数据库会话未开启自动提交，执行完更新后需要添加COMMIT;语句，否则变更不会持久化。
• 异常处理：建议添加异常捕获逻辑，方便排查动态SQL执行时的错误：

  BEGIN
      -- 原有执行逻辑
  EXCEPTION
      WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('执行错误: ' || SQLERRM);
          ROLLBACK;
          RAISE;
  END SP_GET_ROW_NUM;
  

• SQL注入风险：如果QUERY字段的内容来自不可信来源，需要注意SQL注入风险，建议对内容做校验或改用绑定变量方式。

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