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...
----------------------------------------- -------- ---------------
ID NOT NULL VARCHAR2(10)
PID VARCHAR2(10)
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
difference:=i.quantity-j.quantity
end loop;
dbms_output.put_line('Remaining store values='||' '||difference);
end;
/