I have OUT
parameter of a stored procedure as a REF CURSOR
. Based on a particular condition, I would want to either return a result set (already implemented).
But how do I return an empty cursor when the condition fails? Without raising an exception? Just pasting pseudo code:
IF condition = true THEN
OPEN OUT_CUR FOR
Select Some query
ELSE
Return empty OUT_CUR
END IF
Answers. Yes, functions can return refcursors.
A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.
Answer: the same way as the original one you asked about, and this one is actually a little easier. Declare a new EXCEPTION in the declarations section, perhaps call it NAME_IS_NULL. In the loop body, check to see if the EMP_NAME is NULL (before the INSERT statement). If it is, throw the NAME_IS_NULL exception.
A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function. A nested cursor is implicitly opened when the cursor expression is evaluated.
you can try this
IF condition = true THEN
OPEN OUT_CUR FOR
Select Some query;
ELSE
OPEN OUT_CUR FOR
Select * from mtable where 1=2;
END IF
return OUT_CUR;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With