Home » SQL & PL/SQL » SQL & PL/SQL » Function/Procedure that returns total sum
Function/Procedure that returns total sum [message #250957] Thu, 12 July 2007 00:48 Go to next message
hoff
Messages: 2
Registered: July 2007
Junior Member
Hi! Have just begun to learn how to code plsql but need some help with this.
I have three tables, INVOICE - contains INVOICE_NR (pk), AMOUNT, CUSTOMER_ID.

ITEM_OF_INVOICE - contains INVOICE_NR, ITEM_ID, QUANTITY, ISBN (fk). INVOICE_NR & ITEM_ID is the primary key here.

BOOK - contains ISBN (pk), TITLE, IN_STOCK, PRICE, AUTHOR_ID, TYPE_ID, SUPPLIER_ID.

Now to my problem. I want to create a function/procedure that returns the total sum of an INVOICE. The inparameter is an INVOICE invoice_nr. An invoice can contain more than one item, and this is seen in the ITEM_OF_INVOICE-table with the ITEM_ID is increased for every new ITEM (new ISBN) is added to that invoice (same INVOICE_NR). I have tried with FOR -loops, and now a WHILE-loop but get the same results. That my statement is trying to fetch more than one row at a time.

CREATE OR REPLACE PROCEDURE Proc5(inv_id IN NUMBER, TOTAL_SUM OUT NUMBER)
IS
temp_rows NUMBER :=0;
book_sum NUMBER :=0;
book_price BOOK.PRICE%TYPE :=0;
my_item_id ITEM_OF_INVOICE.ITEM_ID%TYPE :=1;
temp_quantity ITEM_OF_INVOICE.QUANTITY%TYPE :=0;
total_sum NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO temp_rows FROM ITEM_OF_INVOICE WHERE ITEM_OF_INVOICE.INVOICE_NR = inv_id;

WHILE (my_item_id <= temp_rows) LOOP
SELECT b.PRICE, i.QUANTITY, i.ITEM_ID INTO book_price,temp_quantity, my_item_id FROM BOOK b, ITEM_OF_INVOICE i WHERE i.ISBN=b.ISBN;
bok_sum := (book_price * temp_quantity);
total_sum := total_sum + book_sum;
END LOOP;
DBMS_OUTPUT.PUT_LINE(total_sum);
END;
/

To find out how many rows the loop should do I use the SELECT COUNT(*) for that invoice_nr. Correct so far?

And then ITEM_ID is the key for which row that my statement shall get data from. Here is where I get my problem, but I can't figure out why?.

Really need some help here.
Best regards
/Christian
Re: Function/Procedure that returns total sum [message #250967 is a reply to message #250957] Thu, 12 July 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Managing Cursors in PL/SQL

Of course, this is just for a PL/SQL course as you know you can do it with a simple SQL statement.

Btw,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).


Regards
Michel

[Updated on: Thu, 12 July 2007 01:44]

Report message to a moderator

Re: Function/Procedure that returns total sum [message #250971 is a reply to message #250967] Thu, 12 July 2007 01:49 Go to previous message
hoff
Messages: 2
Registered: July 2007
Junior Member
OK. Just solved it with cursors. Smile But good links.
Previous Topic: pl/sql learner
Next Topic: HELP on Reading ZIP File contents using PL/SQL
Goto Forum:
  


Current Time: Fri Dec 09 06:02:27 CST 2016

Total time taken to generate the page: 0.26272 seconds