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

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
Received on Tue Feb 13 2007 - 08:08:09 CST