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 !?
– Motta
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– chegancasb