|Function/Procedure that returns total sum [message #250957]
||Thu, 12 July 2007 00:48
Registered: July 2007
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)
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;
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;
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.