I have a function that returns the sum of an order through a for, but I doubt, where do I control the %ISOPEN %NOFOUND exceptions, within the for loop or after the BEGIN in the EXCEPTION?
CREATE OR REPLACE FUNCTION CALCULAR_PEDIDO
(P_CODIGOPEDIDO IN NUMBER)
RETURN NUMBER
AS
TOTAL_PEDIDO NUMBER := 0;
CURSOR P1 IS (SELECT DE.CODIGOPEDIDO, DE.UNIDADESPEDIDAS, PR.PRECIOVENTA
FROM E_DETALLEPEDIDOS DE INNER JOIN E_PRODUCTOS PR
ON DE.CODIGOPRODUCTO = PR.CODIGOPRODUCTO
WHERE DE.CODIGOPEDIDO = 1);
BEGIN
FOR I IN P1 LOOP
TOTAL_PEDIDO := TOTAL_PEDIDO + (I.UNIDADESPEDIDAS * I.PRECIOVENTA);
END LOOP;
RETURN TOTAL_PEDIDO;
END CALCULAR_PEDIDO;
/
Exceptions always go inside the BEGIN..END block. They are normally specified at the end of the block. For example:
There are also predefined exceptions that use the codes they indicate, such as
TOO_MANY_ROWS
,NO_DATA_FOUND
, among others . Check the documentation so you know what built-in exceptions you can use. Essentially, any statement within the blockBEGIN..END
that throws some kind of exception, built-in or custom, is going to direct code execution to the blockEXCEPTION
and look for aWHEN excepción_lanzada THEN
. If there isn't, it will terminate execution and return the exception as output.When you refer to %ISOPEN, %NOTFOUND and %ROWCOUNT, those are not built-in exceptions but cursor attributes, so they do not correspond to the EXCEPTION block. They are also not used alone, which is why they have a
%
at the beginning, while the predefined exception names do not, as I just showed you.A classic example of usage for cursor attributes would be:
See how the cursor is opened and the normal FETCH is done, adding an exit condition when there are no more records to fetch (the fetch is record by record). The usage is then
nombre_cursor%ATRIBUTO
. I hope it's clearer now.You can review the documentation .