Re: How can I pass Recordsets as input parameter to a Function / Stored Proc ?

On Feb 13, 12:53 pm, krisl..._at_gmail.com wrote:
> Hi Gurus,
> I want to create a function that receive recordsets as input parameter
> also returns recordsets as output.
>
> I have a requirement to do stock taking for all order items of one
> Order number in one single query execution (to avoid row by row
> basis).
> I want to WRAP the query into a Function / Stored Proc so that it can
> be RE-USED by other transaction that need stock taking also.
>
> My question is : how to pass the recordsets as input parameter to that
> function / SP ?
> (because I could have 75 rows item in one order number)
>
> Below is the (simplified) DDL and the query :
> create table stocks (Product char(4), Warehouse char(5), expireddate
> date, qty_available number)
> insert into stocks values('P001', 'WH001', '01-dec-2006', 30)
> insert into stocks values('P001', 'WH002', '01-dec-2006', 50)
> insert into stocks values('P001', 'WH002', '01-jan-2007', 50 )
> ....
>
> CREATE TABLE Order_Detail (PRODUCT_ORD CHAR(4), QTY_ORD number,
> Priority_WH CHAR(5) )
> INSERT INTO Order_Detail VALUES ('P001', 75, 'WH003') // previously
> 'WH002'
> INSERT INTO Order_Detail VALUES ('P002', 45, 'WH002')
> INSERT INTO Order_Detail VALUES ('P003', 55, NULL)
>
> The query for stock taking :
> select product,warehouse,expireddate, least(qty_available-(sm -
> qty_ord),qty_available) qty
> from ( select product,warehouse,expireddate,qty_available,qty_ord,
> sum(qty_available) over(partition by product order by
> s.product,expireddate,decode(warehouse,priority_wh,0,1),warehouse )
> sm
> from stocks s,order_detail o
> where s.product = o.product_ord order by
> s.product,expireddate,decode(warehouse,priority_wh,0,1) )
> where (sm - qty_ord) < qty_available
>
> Thank you very much,
> xtanto

First of all, as far as I know you can't use a recordset as an input parameter. You could use a collection as input parameter, and bulk collect into that collection,
but that would still be pretty inefficient and not needed for your requirement, as you could do with a CURSOR defined in a package SPEC (not in a body), which can be used by multiple procedures and get opened and opened again. The orderno would be the parameter to the cursor
cursor my_cursor(p_orderno in number) is select * from orderhist
where orderno = p_orderno;

in your procedure just
open <mypackage>.mycursor(12345);
fetch into <whatever>

--
Sybrand Bakker
Senior Oracle DBA