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;
/
====================