存储过程get_pid_info传入无效用户ID无提示问题排查

你的存储过程当前没有输出的核心原因在于多个关键逻辑错误，我来一步步帮你拆解并修复：

1. 硬编码覆盖传入参数 #

你在游标查询里把传入的p_user_id参数注释掉了，硬写了固定值61：

AND papf.person_id = 61--p_user_id

这会导致无论你传入什么用户ID，存储过程都只会查询person_id=61的用户。修复方式是去掉注释，使用传入参数：

AND papf.person_id = p_user_id

2. SQL%NOTFOUND位置完全无效 #

SQL%NOTFOUND放在FOR循环内部毫无意义——如果游标没有返回任何数据，FOR循环根本不会执行，这个判断永远不会触发。你需要在循环结束后通过游标属性判断数据量，或者提前单独验证用户有效性。

3. 无效用户判断逻辑缺失 #

当前的NO_DATA_FOUND异常不会被触发，因为FOR循环处理空游标时不会抛出该异常。我们需要先单独验证用户是否合法，再查询关联项目。

4. 游标过滤条件与循环内判断冲突 #

游标里已经通过sysdate between xpppp.start_date_active and nvl(xpppp.end_date_active,sysdate+1)过滤了活跃用户，所以循环内ELSIF l_rec.end_date_active < SYSDATE的判断永远不会成立，属于无效逻辑，可直接移除。

完整修复后的存储过程 #

PROCEDURE get_pid_info (p_return_status_o OUT VARCHAR2, p_error_message_o OUT VARCHAR2, p_user_id IN VARCHAR2) IS
    v_is_valid_user NUMBER; -- 用于验证用户有效性的变量
    CURSOR c_pid IS
        SELECT DISTINCT xpppa.project_id
        FROM xxcas_prj_pa_projects_all xpppa, 
             xxcas_prj_pa_project_players xpppp, 
             per_all_people_f papf
        WHERE xpppa.project_id = xpppp.project_id
          AND xpppp.person_id = papf.person_id
          AND papf.person_id = p_user_id  -- 修复：使用传入参数
          AND xpppp.project_role_type = 'PROJECT MANAGER'
          AND papf.person_type_id = 6
          AND sysdate BETWEEN xpppa.start_date AND nvl(xpppa.completion_date,sysdate+1)
          AND sysdate BETWEEN xpppp.start_date_active AND nvl(xpppp.end_date_active,sysdate+1)
          AND EXISTS (SELECT 1 FROM pa_lookups 
                      WHERE lookup_type = 'XXCAS_PRJ_USER_DETAILS' 
                        AND description=papf.email_address 
                        AND enabled_flag = 'Y' 
                        AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active, SYSDATE + 1));
BEGIN
    -- 第一步：先验证用户是否有效
    SELECT COUNT(1)
    INTO v_is_valid_user
    FROM per_all_people_f papf
    WHERE papf.person_id = p_user_id
      AND papf.person_type_id = 6
      AND EXISTS (SELECT 1 FROM pa_lookups 
                  WHERE lookup_type = 'XXCAS_PRJ_USER_DETAILS' 
                    AND description=papf.email_address 
                    AND enabled_flag = 'Y' 
                    AND SYSDATE BETWEEN start_date_active AND NVL (end_date_active, SYSDATE + 1))
      AND sysdate BETWEEN papf.effective_start_date AND nvl(papf.effective_end_date, sysdate+1);

    IF v_is_valid_user = 0 THEN
        dbms_output.put_line('User is not valid');
        p_return_status_o := 'FAILURE';
        p_error_message_o := 'User is not valid';
        RETURN;
    END IF;

    -- 第二步：遍历输出用户关联的项目
    FOR l_rec IN c_pid LOOP
        dbms_output.put_line('The Projects mapped to the active user ID are : ' || l_rec.project_id);
    END LOOP;

    -- 判断是否有项目返回
    IF c_pid%ROWCOUNT = 0 THEN
        dbms_output.put_line('There are no projects mapped to the user having PM role');
        p_return_status_o := 'SUCCESS';
        p_error_message_o := 'No projects found for user';
    ELSE
        p_return_status_o := 'SUCCESS';
        p_error_message_o := 'Projects retrieved successfully';
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in get_pid: ' || SQLERRM);
        p_return_status_o := 'FAILURE';
        p_error_message_o := 'Error: ' || SQLERRM;
END get_pid_info;

调用匿名块的注意事项 #

确保开启DBMS_OUTPUT输出，同时扩大错误信息变量的长度避免截断：

declare
    status varchar2(30);
    msg varchar2(200); -- 扩大长度避免错误信息被截断
begin
    DBMS_OUTPUT.ENABLE(2000000);
    XXCAS_PRJ_PROJECT_DTLS.GET_PID_INFO(status,msg,61);
end;
/

关键修复点总结 #

• 移除硬编码，改用传入的p_user_id参数
• 新增独立的用户有效性验证步骤，精准判断无效用户
• 简化游标逻辑，去掉不必要的GROUP BY和COUNT（仅需DISTINCT去重项目ID）
• 使用游标%ROWCOUNT属性判断是否有项目返回
• 优化异常处理，确保错误信息能正确输出

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