Nilesh Sanyal

Nilesh Sanyal

  • NA
  • 187
  • 58.3k

Subtracting quantity of store table from cart table

Oct 16 2015 2:57 AM

I have two tables named as store and cart

The store table has following fields....

==========================================================

Name    Null?    Type

----------------------------------------- -------- -------------------

PID    NOT NULL    VARCHAR2(10)

PRODUCTNAME    VARCHAR2(30)

QUANTITY              NUMBER

PRICE                      NUMBER

==========================================================

The cart table has following fields...

==========================================================

Name     Null?          Type

----------------------------------------- -------- ---------------

ID          NOT NULL    VARCHAR2(10)

PID                             VARCHAR2(10)

PRODUCTNAME       VARCHAR2(30)

QUANTITY                   NUMBER

PRICE                          NUMBER

==========================================================

Special mention

-------------------

I have created a sequence named as id_seq which will act as the ID column of the cart table.

DATA OF CART TABLE

=========================

ID    PID                     PRODUCTNAME    QUANTITY    PRICE

---------- ---------- ------------------------------ ---------- ----------

1     NOKIA_5636    NOKIA 5636              2                   3000

2    NOKIA_2529    NOKIA 2529             3                      4000

3    NOKIA_6616    NOKIA 6616             4                      5000

21 NOKIA_1617    NOKIA 1617              5                       6000

=========================

DATA OF STORE TABLE

====================================

PID                     PRODUCTNAME    QUANTITY     PRICE

---------- ------------------------------ ---------- ----------

NOKIA_5636    NOKIA 5636              20                3000

NOKIA_2529       NOKIA 2529          20                 4000

NOKIA_6616          NOKIA 6616       20                 5000

NOKIA_1617          NOKIA 1617       20                 6000

====================================

Question

----------------

I want to write a stored procedure which will subtract quantity of store talbe from the quantity of cart

table and then that result of subtraction will be stored in the quantity field of store table.

-----------------

I have written following stored procedure, but it is not working, please help me.

====================

create or replace procedure updateQtyPro(SYSTEM.store.quantity in number,SYSTEM.cart.quantity in

number) is

quantity1 SYSTEM.store.quantity%type;

quantity2 SYSTEM.cart.quantity%type;

difference number;

cursor qtystore is select quantity from SYSTEM.store;

cursor qtycart is select quantity from SYSTEM.cart;

begin

for i in SYSTEM.store.quantity

loop

for j in SYSTEM.cart.quantity

loop

difference:=i.quantity-j.quantity

end loop;

end loop;

dbms_output.put_line('Remaining store values='||' '||difference);

end;

/

====================


Answers (1)