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

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 14 Feb 2007 14:50:55 -0800
Message-ID: <1171493452.693295@bubbleator.drizzle.com>

krislioe_at_gmail.com wrote:

> On Feb 14, 1:22 am, DA Morgan <damor..._at_psoug.org> wrote:
>> krisl..._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 atwww.psoug.orgunder REF
>> CURSORS.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> Hi Mr Morgan,
> 
> Thank you for your reply.
> I have read in : http://www.psoug.org/reference/ref_cursors.html.
> 
> ....
> BEGIN
>   FETCH p_cursor BULK COLLECT INTO rec_array;
> 
>   FOR i IN rec_array.FIRST .. rec_array.LAST
>   LOOP
>     dbms_output.put_line(rec_array(i));
>   END LOOP;
> END pass_ref_cur;
> 
> I still see above code as row by row processing. If the p_cursor has
> 75 rows, I have to process the row 75 times , is it correct ?

What do you mean by process? This is a very ambiguous term and nothing you've written defines what it is you are trying to do.

> If I wan to INSERT the row into my order_table_bybatch, I have to do > it 75 times.

Why not use FORALL?

-- 
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 Wed Feb 14 2007 - 16:50:55 CST