Oracle存储过程日志插入异常：V_STEP_NUM无法递增原因排查

我正在编写一个包含多段SQL代码的Oracle存储过程，希望在各SQL块之间向LOG_TBL表插入递增的步骤编号V_STEP_NUM和SYSTIMESTAMP。但实际运行时，V_STEP_NUM仅在赋值给V_INSERT_LOG_SQL时计算一次，导致日志表中STEP_NUMBER字段全为0。

若将EXECUTE IMMEDIATE V_INSERT_LOG_SQL;替换为以下显式动态SQL，则步骤号能正常递增，符合预期：

EXECUTE IMMEDIATE 
    'INSERT INTO LOG_TBL (STEP_NUMBER,BEGIN_TIME) VALUES 
    ('||V_STEP_NUM||',SYSTIMESTAMP)'

附存储过程代码：

CREATE OR REPLACE PROCEDURE my_procedure
IS
    V_STEP_NUM NUMBER(2);
    V_INSERT_LOG_SQL CLOB;

BEGIN
    V_STEP_NUM := 0;

    V_INSERT_LOG_SQL := 'INSERT INTO LOG_TBL (STEP_NUMBER,BEGIN_TIME) VALUES ('||V_STEP_NUM||',SYSTIMESTAMP)';

    BEGIN
        V_STEP_NUM:= V_STEP_NUM + 1;
    END;    

    BEGIN
        EXECUTE IMMEDIATE V_INSERT_LOG_SQL;
    COMMIT;

    [SOME MORE SQL CODE]

    BEGIN
        V_STEP_NUM:= V_STEP_NUM + 1;
    END;    

    BEGIN
        EXECUTE IMMEDIATE V_INSERT_LOG_SQL;
        COMMIT;
    END; 

    [SOME MORE SQL CODE]
END;

核心原因 #

你在存储过程开头就把V_STEP_NUM（此时值为0）拼接到了V_INSERT_LOG_SQL字符串中，这个字符串一旦生成就固定了——里面的STEP_NUMBER值已经是0，后续修改V_STEP_NUM的数值，不会改变已经生成的SQL语句内容。每次执行EXECUTE IMMEDIATE V_INSERT_LOG_SQL，都是在执行那条固定插入0的SQL，自然日志里全是0。

而你替换后的写法，每次执行EXECUTE IMMEDIATE时才会用当前最新的V_STEP_NUM值拼接SQL，所以能得到递增的步骤号。

推荐解决方法 #

不推荐每次重复拼接SQL字符串，更规范高效的方式是使用绑定变量，既避免SQL注入风险，也不用每次重新生成SQL：

1. 定义带有绑定变量的SQL模板：

V_INSERT_LOG_SQL := 'INSERT INTO LOG_TBL (STEP_NUMBER,BEGIN_TIME) VALUES (:step_num, SYSTIMESTAMP)';

2. 每次执行时传入当前的V_STEP_NUM值：

EXECUTE IMMEDIATE V_INSERT_LOG_SQL USING V_STEP_NUM;

修改后的完整存储过程示例：

CREATE OR REPLACE PROCEDURE my_procedure
IS
    V_STEP_NUM NUMBER(2);
    V_INSERT_LOG_SQL CLOB;

BEGIN
    V_STEP_NUM := 0;
    -- 定义带绑定变量的SQL模板
    V_INSERT_LOG_SQL := 'INSERT INTO LOG_TBL (STEP_NUMBER,BEGIN_TIME) VALUES (:step_num, SYSTIMESTAMP)';

    -- 步骤1
    V_STEP_NUM:= V_STEP_NUM + 1;
    EXECUTE IMMEDIATE V_INSERT_LOG_SQL USING V_STEP_NUM;
    COMMIT;

    [SOME MORE SQL CODE]

    -- 步骤2
    V_STEP_NUM:= V_STEP_NUM + 1;
    EXECUTE IMMEDIATE V_INSERT_LOG_SQL USING V_STEP_NUM;
    COMMIT;

    [SOME MORE SQL CODE]
END;

如果你坚持用字符串拼接的方式，也可以在每次执行前重新生成V_INSERT_LOG_SQL，但这种方式不如绑定变量优雅：

-- 每次递增步骤号后，重新拼接SQL
V_STEP_NUM:= V_STEP_NUM + 1;
V_INSERT_LOG_SQL := 'INSERT INTO LOG_TBL (STEP_NUMBER,BEGIN_TIME) VALUES ('||V_STEP_NUM||',SYSTIMESTAMP)';
EXECUTE IMMEDIATE V_INSERT_LOG_SQL;
COMMIT;

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