Reputation: 2317
I have some data retrieving logic into a stored procedure. which returns a cursor with the content of an SQL query defined in this stored procedure.
Something of this format :
function search_foo(param1 varchar2, param1 varchar3)
return g_ref -- a ref cursor
is
l_return g_ref;
{
open l_return for
select col1_i_need, col2_i_need
from foo
join bar on --[...]
where [...];
return l_return;
}
But when the dataset returned is huge, I want to count the number of rows before fetching all the dataset (if too much rows, stop the retrieving).
So the easy way to do that could be to define another function like that
function search_foo(param1 varchar2, param1 varchar3)
return number
is
l_return number;
{
select count(*) into l_return
from foo
join bar on --[...]
where [...];
return l_return;
}
But it seems bad : code redondancy and so it increase the risk of forgotting one of the method when an update has to be made.
So my question is : Is it possible to make a stored procedure who returns the number of rows of a returned by another stored procedure ?
How can I refactor the SQL code of my query for being able to count the rows ? The easiest but solution would be to write 2 separate SQL queries : one for counting and one for getting the data but this seems ugly to me (code redondancy and so more bugs risk).
Thanks by advance.
Upvotes: 1
Views: 1290
Reputation:
You can do like this, pass a variable to function and function would store amount of rows in it:
function search_foo(param1 varchar2, param1 varchar3, PARAM_ROWCOUNT OUT NUMBER) -- here
return g_ref -- a ref cursor
is
l_return g_ref;
{
open l_return for
select col1_i_need, col2_i_need
from foo
join bar on --[...]
where [...];
PARAM_ROWCOUNT := l_return%ROWCOUNT; -- and here
return l_return;
}
UPD: Sorry, didn't notice that you want to find out the count of rows before fetching. Unfortunatelly, it might not be possible. But if you want your stored procedure NOT to return too many rows, you can always add a WHERE ROWNUM <= 1000 clause to the query.
Upvotes: 1