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