存储过程内代码运行异常，外部执行正常的技术求助

我来帮你解决这个问题！你遇到的情况是典型的PL/SQL变量名与表列名冲突导致的逻辑错误。

问题重现 #

你当前存储过程返回的结果：

|-------------|------------|
| test_set    | is_sovlp   |
|-------------|------------|
| 1           | 1          |
| 2           | 1          |
|-------------|------------|

而预期结果应该是：

|-------------|------------|
| test_set    | is_sovlp   |
|-------------|------------|
| 1           | 0          |
| 2           | 1          |
|-------------|------------|

关键矛盾点：单独执行代码时结果正确，但放到存储过程里就出错。

问题根源 #

你在存储过程中定义了变量 test_set NUMBER := 1;，而你的 TEMP_INPUT_OVLP 和 TEMP_OUTPUT_OVLP 表中都存在同名的 TEST_SET 列。在PL/SQL的标识符解析规则中，当局部变量名与表列名重名时，解析器会优先将其视为列名，而不是你定义的变量。

这就导致你所有的条件判断 WHERE TEST_SET = test_set 实际上变成了 WHERE TEST_SET = TEST_SET（列值等于自身），等于没有筛选条件！所有test_set的记录都被插入到临时表并参与聚合，最终聚合后的flags匹配了正则表达式 HH|EE|HS|SE，返回了1，和预期不符。

而你单独执行代码时，应该是手动指定了TEST_SET = 1的固定条件，没有变量名冲突的问题，所以结果正确。

修复方法 #

只需要修改变量名，避免和列名重复即可，比如将变量名改为 v_test_set，然后替换存储过程中所有引用该变量的地方：

create or replace PROCEDURE IS_OVLP AS
    is_sovlp VARCHAR2(1);
    v_test_set NUMBER := 1; -- 重命名变量，避免和列名冲突
BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TEMP_OUTPUT_OVLP');

    -- 填充临时表，使用修改后的变量名
    INSERT INTO TEMP_OUTPUT_OVLP
    SELECT ESD, 'E', TEST_SET
    FROM TEMP_INPUT_OVLP
    WHERE ESD IS NOT NULL AND TEST_SET = v_test_set
    UNION ALL
    SELECT TD, CASE IS_DB WHEN 0 THEN 'S' WHEN 1 THEN 'H' END AS FLAG, TEST_SET
    FROM TEMP_INPUT_OVLP
    WHERE TD IS NOT NULL AND TEST_SET = v_test_set;

    -- 聚合并匹配正则，使用修改后的变量名
    SELECT COUNT(*) into is_sovlp
    FROM (
        SELECT listagg(FLAG) WITHIN GROUP (ORDER BY DT) as flags
        FROM TEMP_OUTPUT_OVLP
        WHERE TEST_SET = v_test_set
    ) x
    WHERE regexp_like(flags, 'HH|EE|HS|SE');

    dbms_output.put_line('Soft Overlap: ' || is_sovlp);
END IS_OVLP;

验证效果 #

修改后重新执行存储过程，test_set=1的记录会被正确筛选，聚合后的flags不会匹配正则模式，返回0；test_set=2的记录正常返回1，完全符合你的预期结果。

另外，建议你在写PL/SQL代码时，养成给变量加前缀的习惯（比如v_代表局部变量，p_代表参数），这样可以有效避免这类命名冲突问题。

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