存储过程中Loop循环未执行的原因排查及调试求助

问题场景 #

创建了如下PL/SQL存储过程：

CREATE OR REPLACE PROCEDURE "REF_COPY_DB"()
AS
   concept varchar2(10000);
BEGIN
    dbms_output.put_line('hello test 1');
    FOR concept IN (
        SELECT
            table_name
        FROM
            all_tables
        WHERE
                owner = 'WLCR_DEV08'
            AND table_name LIKE '%_REF'
            AND table_name IN (
'CURRENCY_ROLE_WS_REF')
  
    ) LOOP
       
        dbms_output.put_line('hello test 2'); -- 未打印
       
    END LOOP;
END;
/  

使用call ref_copy_db()执行该过程后，script output显示hello test 1 call completed，dbms output窗口仅输出hello test 1。

该过程在WLCR_DEV02数据库调用，但循环内的hello test 2始终未打印。然而在WLCR_DEV02单独执行循环内的查询语句：

SELECT
    table_name
FROM
    all_tables
WHERE
    owner = 'WLCR_DEV08'
    AND table_name LIKE '%_REF'
    AND table_name IN ('CURRENCY_ROLE_WS_REF')   

能得到如下结果：

TABLE_NAME  
--------------------
CURRENCY_ROLE_WS_REF

说明查询本身有数据，但存储过程未进入循环执行，以下是可能的原因及调试思路：

可能的原因 #

• 权限差异：单独执行查询的用户和执行存储过程的用户权限不一致。ALL_TABLES仅展示当前用户有权限访问的表，若存储过程以定义者权限（默认模式）执行，定义者可能没有WLCR_DEV08下目标表的访问权限，导致循环查询无结果。
• 变量名冲突：声明的concept varchar2(10000)与循环变量concept重名。虽然PL/SQL允许循环变量覆盖外层变量，但可能引发隐式类型转换或解析异常，导致循环逻辑未正确触发。
• 用户/表名大小写不匹配：数据库中WLCR_DEV08用户或CURRENCY_ROLE_WS_REF表是带双引号创建的（大小写敏感），但存储过程中用大写字符串匹配，导致查询无结果；而单独查询时客户端自动转大写，恰好匹配成功。
• 存储过程编译依赖问题：存储过程编译时，执行用户无对应权限或ALL_TABLES中无目标数据，后续权限/数据更新后未重新编译，导致存储过程仍使用旧的执行计划。

调试思路 #

• 验证执行用户的查询权限：在执行存储过程的用户会话中，直接执行循环内的查询语句，确认是否能返回数据。如果查不到，需给该用户授予SELECT权限（针对WLCR_DEV08.CURRENCY_ROLE_WS_REF）或SELECT ANY TABLE权限，或者将存储过程改为调用者权限（添加AUTHID CURRENT_USER）。
• 修复变量名冲突：修改循环变量或外层变量的名称，避免重名，比如：

CREATE OR REPLACE PROCEDURE "REF_COPY_DB"()
AS
BEGIN
    dbms_output.put_line('hello test 1');
    FOR rec_concept IN (
        SELECT table_name FROM all_tables WHERE ...
    ) LOOP
        dbms_output.put_line('hello test 2: ' || rec_concept.table_name);
    END LOOP;
END;
/

• 强制重新编译存储过程：执行ALTER PROCEDURE REF_COPY_DB COMPILE;，确保存储过程使用最新的权限和元数据。
• 添加详细调试输出：在循环前统计查询结果行数，或在循环内打印具体表名，确认数据是否进入循环：

CREATE OR REPLACE PROCEDURE "REF_COPY_DB"()
AS
    v_row_count NUMBER;
BEGIN
    dbms_output.put_line('hello test 1');
    SELECT COUNT(*) INTO v_row_count 
    FROM all_tables 
    WHERE owner = 'WLCR_DEV08' 
      AND table_name LIKE '%_REF' 
      AND table_name IN ('CURRENCY_ROLE_WS_REF');
    dbms_output.put_line('Query returned ' || v_row_count || ' rows');
    
    FOR concept IN (
        SELECT table_name FROM all_tables WHERE ...
    ) LOOP
        dbms_output.put_line('Processing table: ' || concept.table_name);
    END LOOP;
END;
/

• 检查用户/表名的实际大小写：执行SELECT username FROM all_users WHERE username LIKE '%WLCR_DEV08%';和SELECT table_name FROM all_tables WHERE owner = 'WLCR_DEV08' AND table_name LIKE '%CURRENCY%';，确认实际名称的大小写，调整存储过程中的匹配字符串。
• 切换存储过程权限模式：修改存储过程为调用者权限，确保执行时使用当前用户的权限：

CREATE OR REPLACE PROCEDURE "REF_COPY_DB"()
AUTHID CURRENT_USER
AS
BEGIN
    -- 原有逻辑
END;
/

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