Oracle存储过程问题：比较计数变量并将布尔值存入游标

原代码存在的问题 #

• 错误混用OPEN OUTPUTTABLE FOR与SELECT INTO：游标打开语句后应直接定义返回的查询逻辑，不能同时执行赋值查询
• 直接给游标对象赋值布尔值：游标是结果集容器，需通过查询语句返回数据，而非直接赋值
• IF语句未闭合：缺少END IF导致语法错误
• 重复查询同一张表：两次全表扫描影响性能

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE CHECK_REJECTED_SITES_CR 
(
  P_CHANGEREQUESTID IN TBL_CMP_EMG_DATA_INFO.CHANGE_REQUEST_ID%TYPE,
  OUTPUTTABLE OUT SYS_REFCURSOR
)
AS 
  VAR_ROW_COUNT        NUMBER;
  VAR_REJECTED_COUNT    NUMBER;
BEGIN
  -- 单次查询获取总条数与拒绝条数，提升效率
  SELECT 
    COUNT(*),
    SUM(CASE WHEN APPROVE_REJECT = 'Rejected' THEN 1 ELSE 0 END)
  INTO VAR_ROW_COUNT, VAR_REJECTED_COUNT
  FROM TBL_CMP_EMG_DATA_INFO 
  WHERE CHANGE_REQUEST_ID = P_CHANGEREQUESTID;

  -- 打开游标并返回布尔结果
  OPEN OUTPUTTABLE FOR
    SELECT CASE 
             WHEN VAR_ROW_COUNT = VAR_REJECTED_COUNT THEN TRUE 
             ELSE FALSE 
           END AS IS_ALL_REJECTED
    FROM DUAL;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 无匹配数据时返回FALSE
    OPEN OUTPUTTABLE FOR
      SELECT FALSE AS IS_ALL_REJECTED FROM DUAL;
  WHEN OTHERS THEN
    -- 保留原有异常抛出逻辑，可根据需求调整
    RAISE;
END CHECK_REJECTED_SITES_CR;
/

关键修正说明 #

1. 参数模式调整：将P_CHANGEREQUESTID改为IN模式，过程未修改该参数，无需OUT
2. 查询优化：合并两次表扫描为一次，通过SUM(CASE...)统计拒绝记录数，减少数据库IO开销
3. 游标正确使用：通过SELECT ... FROM DUAL生成单行结果集，将布尔判断结果存入游标返回
4. 异常处理：添加NO_DATA_FOUND捕获逻辑，避免无匹配数据时抛出异常，确保游标始终能返回有效结果

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