Home » SQL & PL/SQL » SQL & PL/SQL » update stock
update stock [message #211065] Tue, 26 December 2006 01:00 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have a procedure to update stock in stock table. If card_id is already there , it will update the quantity otherwise it will insert the new card_id etc in the stock table.

But it does not work properly.. please help me...

SQL> select * from purchase;

CARD_ID    QTY_PURCHASED AMOUNT_PAID DISCNT_RECD PURCHASE_ PURCHASE_ID
---------- ------------- ----------- ----------- --------- -----------
Jazz200                4         776           3 26-DEC-06          10
Jazz100               10         970           3 26-DEC-06          10
Jazz100               34        3298           3 26-DEC-06          10

SQL> select * from stock;

CARD_ID          QTY
---------- ---------
Jazz100            0

SQL> insert into purchase(card_id,qty_purchased,purchase_id) values('Jazz100',20,10);

1 row created.

SQL>  create or replace PROCEDURE ups (pur_id IN number)IS
  2     cursor c1 is select * from purchase where purchase_id=pur_id;
  3     x1 c1%rowtype;
  4     cursor c2 is select * from stock;
  5     x2 c2%rowtype;
  6     begin
  7     for x1 in c1 loop
  8     for x2 in c2 loop
  9       if x2.card_id=x1.card_id then
 10        update stock set qty=x2.qty+x1.qty_purchased;
 11     elsif x2.card_id<>x1.card_id then
 12       insert into stock values(x1.card_id,x1.qty_purchased);
 13    else
 14    null;
 15     end if;
 16     end loop;
 17     end loop;
 18     END;
 19  /

Procedure created.

SQL> exec ups(10);

PL/SQL procedure successfully completed.

SQL> select * from purchase;

CARD_ID    QTY_PURCHASED AMOUNT_PAID DISCNT_RECD PURCHASE_ PURCHASE_ID
---------- ------------- ----------- ----------- --------- -----------
Jazz200                4         776           3 26-DEC-06          10
Jazz100               10         970           3 26-DEC-06          10
Jazz100               34        3298           3 26-DEC-06          10
Jazz100               20                                            10

SQL> select * from stock;

CARD_ID          QTY
---------- ---------
Jazz100           64
Jazz100           64
Jazz100           20
Jazz100           64
Jazz200           64

SQL> 

Re: update stock [message #211080 is a reply to message #211065] Tue, 26 December 2006 02:53 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Here as I see, the solution lies not in creating a procedure , but in creating a trigger on the purchase table. since you already may have data in the purchase table, so what I did ,to simulate ur situation is to create another table to hold the already existing data, then truncate the purchase table and then create the trigger on the purchase table and then reinsert the data...you may have to think before you delete data in purchase table though...about all the impact it can have.. Here is the code:

SQL> CREATE TABLE ORIG_PURCHASE AS SELECT * FROM PURCHASE;

Table created.

SQL> TRUNCATE TABLE PURCHASE;

Table truncated.


SQL> ED
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER PURCHASE_INS_TRIG BEFORE INSERT
  2  ON PURCHASE
  3   REFERENCING NEW AS NEW OLD AS OLD
  4  FOR EACH ROW
  5  DECLARE
  6  V_COUNT NUMBER;
  7  BEGIN
  8    SELECT COUNT(*) INTO V_COUNT FROM STOCK
  9    WHERE CARD_ID= :NEW.CARD_ID;
 10    IF V_COUNT>0 THEN
 11     UPDATE STOCK SET QTY=QTY+:NEW.QTY_PURCHASED
 12      WHERE CARD_ID= :NEW.CARD_ID;
 13    ELSE
 14      INSERT INTO STOCK VALUES(:NEW.CARD_ID,:NEW.QTY_PURCHASED);
 15    END IF;
 16* END;
SQL> /

Trigger created.

--ignore the line numbers in front in each line..

SQL> insert into purchase select * from orig_purchase;

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from stock;

CARD_ID                     QTY
-------------------- ----------
Jazz100                       4
Jazz200                      44

SQL> insert into purchase(card_id,qty_purchased,purchase_id) values('Jazz100',20,10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from stock;

CARD_ID                     QTY
-------------------- ----------
Jazz100                      24
Jazz200                      44





This is the expected result right?




Re: update stock [message #211081 is a reply to message #211065] Tue, 26 December 2006 02:54 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
If you are using Oracle 9i and above then does following merge syntax help you?

--- Not tested code....
create or replace PROCEDURE ups (pur_id IN number)IS
Begin
merge into stock s
using (select card_id ,sum(qty_purchased) sum_qty from purchases where purchase_id=pur_id  group by card_id) p
on (s.card_id = p.card_id)
when matched then
update set s.qty=s.qty+p.sum_qty
when not matched then
insert (card_id,qty) values (p.card_id,p.sum_qty);
end;



[Updated on: Tue, 26 December 2006 02:55]

Report message to a moderator

Re: update stock [message #211088 is a reply to message #211080] Tue, 26 December 2006 04:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thanks a lot nirav_hyd. it works fine

rzkhan
Re: update stock [message #211092 is a reply to message #211088] Tue, 26 December 2006 04:41 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Thanks a lot nirav_hyd. it works fine


Did you check What happens when insert happens in multiuser environment?

Test like this in your development environment

--in session1 after creating trigger. 
SQL(Session1)> delete from purchases;
SQL(Session1)> delete from stock;
SQL(Session1)>commit;
SQL(Session1)> insert into purchase select * from orig_purchase;
SQL(Session1)> pause

now open new sql session  and do following

SQL(Session2)> insert into purchase select * from orig_purchase;
SQL(Session2)> commit;
SQL(Session2)> Select * from stock;

now go back to session 1 and check the  results in stock table

SQL(Session1)> pause <<press enter>>>
SQL(Session1)> commit;
SQL(Session2)> Select * from stock;





If you want to maintain Stock table automatically then I would suggest you read documentation on materialized view

[Updated on: Tue, 26 December 2006 04:50]

Report message to a moderator

Re: update stock [message #211112 is a reply to message #211092] Tue, 26 December 2006 07:12 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Quote:

Did you check What happens when insert happens in multiuser environment?

If you want to maintain Stock table automatically then I would suggest you read documentation on materialized view



Not sure of what you mean here...can you please elaborate how a trigger can create issues in multiserver environment?...please post the entire code with possible problems...Also I hope you do not want to have a materialized view created! A trigger is sufficeint for the purpose AFAIK..
Re: update stock [message #211113 is a reply to message #211112] Tue, 26 December 2006 07:22 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Did you repeat the steps I mentioned?

--in session1 after creating trigger. 
-- I am assuming orig_purchase still has old records and hence cleaning the existing records in purchases and stock
SQL(Session1)> delete from purchases;
SQL(Session1)> delete from stock;
SQL(Session1)>commit;
SQL(Session1)> insert into purchase select * from orig_purchase;
SQL(Session1)> pause

now open new sql session  and do following

SQL(Session2)> insert into purchase select * from orig_purchase;
SQL(Session2)> commit;
SQL(Session2)> Select * from stock;

now go back to session 1 and check the  results in stock table

SQL(Session1)> pause <<press enter>>>
SQL(Session1)> commit;
SQL(Session2)> Select * from stock;





Currently do not have access to Oracle so cannot show the result but can you repeat above steps and post the result of the query (select * from stock) from both the session. I think that will give you indication of issues that can be faced in multiuser environment

[Updated on: Tue, 26 December 2006 07:27]

Report message to a moderator

Re: update stock [message #211115 is a reply to message #211113] Tue, 26 December 2006 07:53 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Bonker,

Thanks a lot for this very important point! yes the results are indeed getting messed! RZKhan, the procedural approach is certainly be better due to this issues...here is the code:


Session1>delete from purchase;

4 rows deleted.

Session1>delete from stock;

2 rows deleted.

Session1>commit;

Commit complete.

Session1>insert into purchase select * from orig_purchase;

3 rows created.

Session1>
Session1>commit;

Commit complete.


Sessoin2>insert into purchase select * from orig_purchase;

3 rows created.

Sessoin2>commit;

Commit complete.

Sessoin2>select * from stock;

CARD_ID                     QTY
-------------------- ----------
Jazz100                       4
Jazz200                      44

--here is the mess!
Session1>select * from stock;

CARD_ID                     QTY
-------------------- ----------
Jazz100                       4
Jazz200                      44
Jazz100                       4
Jazz200                      44



This is due to the read consistancy issue: Since one transaction started early but did not commit, the other transaction can not see the effect of it..A very very valid point indeed! Is there any solution to this issue?

Again my thanks Bonker!,
Nirav

[Updated on: Tue, 26 December 2006 07:54]

Report message to a moderator

Re: update stock [message #211116 is a reply to message #211115] Tue, 26 December 2006 08:08 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Thanks nirav_hyd for posting the result.

That is why I feel materialized view with fast refresh on commit would be best option here. But if this has to be done via trigger then one would have to follow serialized approach which ofcourse would not scale well

i.e lock table stock in exclusive mode.

Again I would probably do the update of stock it in after insert trigger rather than before insert trigger and in before insert trigger I will lock table in exclusive mode.


   CREATE OR REPLACE TRIGGER PURCHASE_INS_TRIG before INSERT
   ON PURCHASE
     
    BEGIN

    lock table stock in exclusive mode;

  END;
/

    CREATE OR REPLACE TRIGGER PURCHASE_INS_TRIG_aft After INSERT
    ON PURCHASE
     REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    V_COUNT NUMBER;
    BEGIN
      SELECT COUNT(*) INTO V_COUNT FROM STOCK
      WHERE CARD_ID= :NEW.CARD_ID;
     IF V_COUNT>0 THEN
      UPDATE STOCK SET QTY=QTY+:NEW.QTY_PURCHASED
       WHERE CARD_ID= :NEW.CARD_ID;
     ELSE
       INSERT INTO STOCK VALUES(:NEW.CARD_ID,:NEW.QTY_PURCHASED);
     END IF;
  END;
/


ofcourse the above trigger based approach means all other transactions have to wait till the transaction that was inserting first have been committed or rollback.

I would prefer Materialized View for this kind of situation.

Re: update stock [message #211118 is a reply to message #211116] Tue, 26 December 2006 08:25 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi Bonker,

Thanks again for sharing the trigger based solution...I think that the nature of this requirement is such that it requires serialization...the sum has to be done for each transaction one at a time so I do not still understand, how using a materialized view can improve that situation..is there a distinct advantage of using a materialized view here that the trigger based solution does not provide? the trigger based solution just seems very intutive to me because we want some thing to happen automatically so trigger is what comes to the mind...

With thanks,
Nirav
Re: update stock [message #211159 is a reply to message #211118] Tue, 26 December 2006 22:27 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:


I think that the nature of this requirement is such that it requires serialization...the sum has to be done for each transaction one at a time so I do not still understand, how using a materialized view can improve that situation..is there a distinct advantage of using a materialized view here that the trigger based solution does not provide?




I am guessing OP wants to maintain update stock automatically when a purchase is made and in order to scale well MV would be suited as it will get refreshed automatically on commit if Refresh fast on Commit option is used or it can be refreshed manually by calling dbm_mview.refresh package if refresh on demand option is used.

I would do something like this

SQL> Create materalized view log on purchase with rowid,sequence (card_id) including new values
/
SQL> create materialized view stock_mv
build immediate
refresh fast on commit 
as
select card_id,sum(QTY_PURCHASED) tot_qty,count(QTY_PURCHASED),
count(*) from purchase 
group by card_id
/


Now I do not have to bother about serialization as two session can concurrently do the insert without having to lock the table thus can get the application to scale well unlike trigger based solution.

Ofcourse it is upto OP what approach he wants to use.
Re: update stock [message #211175 is a reply to message #211159] Wed, 27 December 2006 00:45 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you all. a lot of good comments. I am not using multi user environment. and the trigger works fine for me in case of insert. but how can I handle the update and delete on purchase... I think the trigger should be like this




DECLARE
V_COUNT NUMBER;
BEGIN

IF DELETING THEN
-----code here
ELSIF UPDATING THEN
-----code here
ELSE ---- DELETING
THEN
-----code here
END IF;


END;



please help me this situation...
RZKHAN
Re: update stock [message #211189 is a reply to message #211175] Wed, 27 December 2006 01:57 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Quote:

I am not using multi user environment. and the trigger works fine for me in case of insert.



I think ultimately more than one person will use the application so probably it will be multiuser only..right? so please revise the trigger and use the updated code (given by Bonker, for before insert and after insert trigger)

Now regarding delete, you have to use the exact opposite logic than what is given for insert, so please try to figure it out and for update, in the trigger, what you have to do is for whichever row, that is being updated, reduce the sum in the stock by the old value and then increase it by the new value...

also it may be more readable to write seperate triggers in this case, IMO...

[Updated on: Wed, 27 December 2006 01:58]

Report message to a moderator

Re: update stock [message #211325 is a reply to message #211189] Thu, 28 December 2006 00:10 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
nirav_hyd

If I want to use the trigger method.


I have written compiled the before delete trigger
It works fine.

but as for the before update trigger


  CREATE OR REPLACE TRIGGER PURCHASE_UPD_TRIG BEFORE UPDATE
    ON PURCHASE
     REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
    V_COUNT NUMBER;
    BEGIN
      SELECT COUNT(*) INTO V_COUNT FROM STOCK
      WHERE CARD_ID= :OLD.CARD_ID;
     IF V_COUNT>0 THEN
      UPDATE STOCK SET QTY=((NVL(QTY,0)-:OLD.QTY_PURCHASED)+(:NEW.QTY_PURCHASED))
       WHERE CARD_ID= :OLD.CARD_ID;
     end if;
  END;



I have decreased the sum in stock by the old value and increased by the new value. But the output is strange.
as before update the stock was 100 and after update it is 20. (while it should be 10).

SQL> select * from purchase;

no rows selected

SQL> select * from stock;

no rows selected

SQL> insert into purchase(card_id,qty_purchased) values('Jazz100',100);

1 row created.

SQL> select * from stock;

CARD_ID          QTY
---------- ---------
Jazz100          100

SQL> update purchase set qty_purchased=10 where card_id='Jazz100';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from stock;

CARD_ID          QTY
---------- ---------
Jazz100           20

SQL> 


I dont understand y?

plz help..
Re: update stock [message #211373 is a reply to message #211325] Thu, 28 December 2006 04:33 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
[CODE]
I modified the triggers according to bonker's comments. and all they work fine. Thank u all.

I will come back to seek your help if u people can help... thanks
rzkhan

[CODE]
Previous Topic: how do i logon to SQL*PLUS?
Next Topic: SELECT within INSERT
Goto Forum:
  


Current Time: Thu Dec 08 02:32:47 CST 2016

Total time taken to generate the page: 0.13913 seconds