Add values to a PL/SQL cursor

Asked

Viewed 60 times

0

Good night. I am trying to define the values of a cursor in PL/SQL, however one of the values is never filled. In this case, the qtd_daysSummary number never has content, however all others do, which I assume is because I’m using a different select for qtd_daysSummary. When testing code using anonymous blocks, all values are correct, except this one is empty.

CREATE OR REPLACE FUNCTION fncObterRegistoMensalCamareira(mes int, ano int DEFAULT (EXTRACT (year from SYSDATE()) - 1)) return SYS_REFCURSOR AS

idCamareira camareira.id%type;
nomeCamareira funcionario.nome%type;
valorTotal_consumos number;
dataPrimeiroConsumo date;
dataUltimoConsumo date;
qtd_diasSemConsumo number;

cursorConsumos SYS_REFCURSOR;

BEGIN
    
    OPEN cursorConsumos 
    
    
    FOR SELECT cam.id, func.nome, sum(linha.preco_unitario) as Valor_Total, 
    min(linha.data_registo) as DATA_PRIMEIRO_REGISTO,
    max(linha.data_registo) as DATA_ULTIMO_REGISTO
    FROM camareira cam, funcionario func, linha_conta_consumo linha
    WHERE cam.id = func.id AND EXTRACT (month FROM linha.data_registo) = mes AND EXTRACT (year FROM linha.data_registo) = ano
    AND cam.id = linha.id_camareira
    GROUP BY cam.id, func.nome, linha.quantidade;
    
    
    SELECT COUNT(*) INTO qtd_diasSemConsumo
    FROM (select to_date('01-02-2020' ,'dd-mm-yyyy')+level-1
     from dual 
     connect by level <= TO_CHAR(LAST_DAY(to_date('01-02-2020' ,'dd-mm-yyyy')),'DD')
     MINUS
     SELECT DISTINCT linhaC.data_registo
    FROM camareira cama, linha_conta_consumo linhaC
    WHERE cama.id = linhaC.id_camareira AND EXTRACT(month FROM linhaC.data_registo) = mes AND cama.id = idCamareira);

RETURN cursorConsumos;
EXCEPTION
  when no_data_found then
  return null;
END;
  • The qtd_dias is not returned or used on the cursor !? What exactly is wanted !?

  • I find it strange the two researches associated with the same CURSOR. What kind of information do you want to receive? If you want to receive two data sets you must declare the output of two SYS_REFCURSOR

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.