Início / coding / Perguntas / 78783718
Accepted
hud
Asked: 2024-07-23 21:26:49 +0800 CST

O cursor não está imprimindo nenhuma contagem mesmo que haja registro na tabela no Oracle Procedure

  • 772

Eu criei um SP onde estou executando uma lógica para trazer a V_SPANID_COUNTcontagem, mas sempre mostra 0 contagem, mesmo que haja algum registro, digamos 10-20 registros

Abaixo está meu 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 respostas

  1. Best Answer

    Não misture INTOloops de cursor. Use um ou outro, mas não ambos:

    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;

    ou:

    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;

    violino

    • 4

relate perguntas