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

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 13 Feb 2007 10:22:29 -0800
Message-ID: <1171390947.357529@bubbleator.drizzle.com>

krislioe_at_gmail.com wrote:

> Hi Mr Bakker,
> 
>> in your procedure just
>> open <mypackage>.mycursor(12345);
>> fetch into <whatever>
> This what I try to avoid : row by row operation, my order detail could
> be 75 - 100 items.
> 
> I want to do the stock taking in one single query, and Insert the
> resultset  to Order_By_Batch Table.
> 
> Isn't it inefficient to fetch row by row compared to single query ?
> 
> Thank you,
> xtanto
> 
> 
> 
> sybrandb wrote:
>> 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

Please do not top post.

If your function receives parameters and returns a ref cursor. Where would you need to do single-row processing?

You could process the ref cursor in a FOR LOOP just as is demonstrated in Morgan's Library at www.psoug.org under REF CURSORS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Feb 13 2007 - 12:22:29 CST