Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to return a empty cursor from a stored procedure?

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
like image 471
KeenUser Avatar asked Oct 09 '12 04:10

KeenUser


People also ask

Can we return cursor from function?

Answers. Yes, functions can return refcursors.

Will procedure return a value?

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.

How do you handle no data found exception in cursor FOR loop?

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.

What is usually returned by the cursor expression?

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.


1 Answers

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;
like image 99
Satya Avatar answered Oct 23 '22 10:10

Satya