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

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 ? If I wan to INSERT the row into my order_table_bybatch, I have to do it 75 times.
Isn't it better if I can do it in single : INSERT INTO order_table_bybatch SELECT * FROM [p_cursor], is it possible ?

Thank you,
xtanto Received on Tue Feb 13 2007 - 19:47:59 CST