PL/SQL code for subtracting quantity field of store table from quantity field of cart table

[Nilesh Sanyal]

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 for each product.
-----------------

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

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

[Tapas Chand]

I am afraid this is not the correct way to find the difference.

HINT: Use Join
Have you learnt Join yet?

[Edit] Moreover you can not subtract cursors like that.

[Nilesh Sanyal]

I have learnt join, but I don't know how to apply those stuff in the stored procedure for doing the operation, please help by writing the PL/SQL codes for the stored procedure.

[Tapas Chand]

Nilesh Sanyal wrote:I have learnt join, but I don't know how to apply those stuff in the stored procedure for doing the operation...

Forget about Stored Procedure for 2 minutes.
Try to find the solution by writing the query using Join to find the difference.
Once you are able to write the query properly, then use that query to create Store Procedure.

If you face any difficulties, post your query and we will try to help.

[Nilesh Sanyal]

I have written following PL/SQL codes

set serveroutput on
declare
cursor matchqty is select pid,productname,quantity,price from store;
cursor match2qty is select pid,productname,quantity,price from cart;
diff number;
pid1 store.pid%type;
pid2 cart.pid%type;
pname1 store.productname%type;
pname2 cart.productname%type;
qty1 store.quantity%type;
qty2 cart.quantity%type;
price1 store.price%type;
price2 cart.price%type;
begin
open matchqty;
if(matchqty%isopen) then
loop
fetch matchqty into pid1,pname1,qty1,price1;
exit when matchqty%notfound;
end loop;
end if;

open match2qty;
if(match2qty%isopen) then
loop
fetch match2qty into pid2,pname2,qty2,price2;
exit when match2qty%notfound;
end loop;
end if;

diff:=qty1-qty2;

dbms_output.put_line(diff);
close matchqty;
close match2qty;
end;
/

But it is displaying difference of quantity for only last record, please help me by telling how to display difference of quantities of all records.

[Tapas Chand]

OMG...you have gone too far to solve this.
I had told you to write a simple SELECT query first and then use that query to write SP.
Execute the following query

SELECT store.pid, store.quantity, cart.quantity, (store.quantity-cart.quantity) diff FROM store, cart WHERE store.pid=cart.pid;

Tell whether this query produces required output. If yes, use this to create your SP.

You can add/remove any column from SELECT clause or add more conditions in WHERE clause as per your requirements.