Re: PL/SQL

SP,

It seems you have attempted to combine two processes into one, incorrectly. The current query, if it worked, would create new id numbers every single time you attempt to select the customer information.

> select cusid, cn, ref_ from shopper where custid=(select myfunction
 (custid)
> from shopper);
 

> create function myfunction(cn in shopper.cn%type, ref_ in
 shopper.ref_%type)
> return shopper%rowtype
> as
> begin
> newcustID shopper.custid%custid;
> select max(custid)+1 into newcustid from shopper;
> insert into shopper(custid,cn,ref_)
> values (newcustid,cn,ref_);
> return newcustid;
> end myfunction;
>
 

> [clip].... I just want to do the
> insert in the table and return the inserted fields to a EXEC
 statement or
> through a function call.
> custid cn ref
> ------ --- ---
> 123 John Tom

If you had wanted a Select with a Function, you could do this easily

   Select a,b,c
   From shopper
   Where xyz = SomeFunction(a,b,c,...);

But, since you want the result above, to get a new ID and return it, you must use other methods. There are a number of ways... but you won't want to perform the updates while attempting a Select.

1. Have you considered the use of a sequence generator? Can you have multiple people attempting to do Max+1 while another Max+1 process is inserting-but-not-yet-COMMITed?
2. AddNewCust(cusid, ref_by, Cn); Where AddNewCust is something like Create procedure AddNewCust( cusid out shopper.cusid%type, refby in shopper.ref_%Type, cn in shopper.cn%type) is ..... Select max(cusid)+1 into ... from shopper; Insert into shopper .... COMMIT ???? end;
3. a function: NewCusid := AddNewCust(ref_by, cn) Create function AddNewCust( refby in shopper.ref_%Type, cn in shopper.cn%type) Return shopper.cusid%type is ..... Select max(cusid)+1 into ... from shopper; Insert into shopper .... COMMIT ???? Return newcusid; end;

Hope that helps,
Robert Proffitt
Beckman Coulter
Brea California

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 08 2000 - 00:00:00 CDT