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

On Feb 14, 2:47 am, krisl..._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.orgunderREF
> > 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- Hide quoted text -
>
> - Show quoted text -

The above code is *not* row by row processing! Don't you see the BULK COLLECT?
For 'is it possible' questions you would better look up the SQL reference manual, as you would best have looked up BULK COLLECT.

--
Sybrand Bakker
Senior Oracle DBA