• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 33
Eclipse IDE MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;
/

====================
 
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 33
Eclipse IDE MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 33
Eclipse IDE MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
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.
 
So there I was, trapped in the jungle. And at the last minute, I was saved by this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic