主页 / coding / 问题 / 78783718
Accepted
hud
Asked: 2024-07-23 21:26:49 +0800 CST

即使 Oracle 程序中的表中有记录，游标也不会打印任何计数

  • 772

我创建了一个 SP，在其中运行逻辑来计算V_SPANID_COUNT数量，但即使有记录（比如说 10-20 条记录），它始终显示 0 数量

下面是我的 SP..

create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS 

V_ERRORS NVARCHAR2(3000);
V_SPANID_COUNT   NUMBER := 0;

BEGIN

        FOR CUR_NE_DATA IN
        (
            SELECT COUNT(SPAN_LINK_ID) INTO V_SPANID_COUNT from CMP_PANINDIA_VIEW_AGING_UPD 
            WHERE NE_LENGTH < ROUTE_APPROVED_BY_CMM
            AND CMM_APPROVED_DATE IS NOT NULL
            AND MISSING_ASBUILT = 0 and SPAN_TYPE <> 'FTTX'
            AND job_progress_flag = 1
        )
LOOP

          dbms_output.put_line('COUNT OF DATA: ' || V_SPANID_COUNT);
          
END LOOP;  

END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;
oracle

1 个回答

  1. Best Answer

    不要混合使用INTO和光标循环。使用其中一个，但不能同时使用：

    create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS 
  V_ERRORS NVARCHAR2(3000);
BEGIN
  FOR CUR_NE_DATA IN (
    SELECT COUNT(SPAN_LINK_ID) AS cnt
    FROM   CMP_PANINDIA_VIEW_AGING_UPD 
    WHERE  NE_LENGTH < ROUTE_APPROVED_BY_CMM
    AND    CMM_APPROVED_DATE IS NOT NULL
    AND    MISSING_ASBUILT = 0
    and    SPAN_TYPE <> 'FTTX'
    AND    job_progress_flag = 1
  )
  LOOP
    dbms_output.put_line('COUNT OF DATA: ' || CUR_NE_DATA.cnt);
  END LOOP;  
END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;

    或者：

    create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS 
  V_ERRORS NVARCHAR2(3000);
  V_SPANID_COUNT   NUMBER;
BEGIN
  SELECT COUNT(SPAN_LINK_ID)
  INTO   V_SPANID_COUNT
  FROM   CMP_PANINDIA_VIEW_AGING_UPD 
  WHERE  NE_LENGTH < ROUTE_APPROVED_BY_CMM
  AND    CMM_APPROVED_DATE IS NOT NULL
  AND    MISSING_ASBUILT = 0
  and    SPAN_TYPE <> 'FTTX'
  AND    job_progress_flag = 1;

  dbms_output.put_line('COUNT OF DATA: ' || V_SPANID_COUNT);
END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;

    小提琴

    • 4

相关问题