Home » SQL & PL/SQL » SQL & PL/SQL » Help in Cursor!!!!
Help in Cursor!!!! [message #370] Thu, 07 February 2002 14:58 Go to next message
Keanna
Messages: 1
Registered: February 2002
Junior Member
hi..
could you pls help me with this cursor...i got my data update but not the first recordset...is something with my cursor?

DROP PROCEDURE PR_UPDATE_TOTAL;
CREATE PROCEDURE PR_UPDATE_TOTAL IS
T_InvoiceNumber NUMBER(6,0);
T_ItemNumber NUMBER(5,0);
T_Quantity NUMBER(3,0);
T_Cost NUMBER(6,2);
T_Discount NUMBER(5,2);
T_TempTotal NUMBER(6,2);
CURSOR C_InvoiceTotal IS
SELECT Invoice_Number,Item_Number,Quantity
FROM Invoice_Items;
BEGIN
--excute query,return set of records
--it goes to the first line of cursors, not the first recordset
OPEN C_InvoiceTotal;
FETCH C_InvoiceTotal INTO T_InvoiceNumber,T_ItemNumber,T_Quantity;
WHILE C_InvoiceTotal%FOUND LOOP
IF T_ItemNumber = 99 THEN
SELECT Cost INTO T_Cost FROM Sites,Registrations,Invoices,Invoice_Items
WHERE Invoice_Items.Invoice_Number=Invoices.Invoice_Number AND
Invoices.Registration_Number=Registrations.Registration_Number AND
Registrations.Site_Number=Sites.Site_Number AND Item_Number=T_ItemNumber;
T_TempTotal:= T_Cost*T_Quantity;
ELSE
SELECT Cost,Discount INTO T_Cost,T_Discount FROM Items
WHERE Item_Number=T_ItemNumber;
T_TempTotal:=(T_Cost-(T_Cost * T_Discount))*T_Quantity;
END IF;
UPDATE Invoices
SET Invoice_Total= T_TempTotal + NVL(Invoice_Total,0)
WHERE Invoice_Number=T_InvoiceNumber;
FETCH C_InvoiceTotal INTO T_InvoiceNumber,T_ItemNumber,T_Quantity;
END LOOP;
CLOSE C_InvoiceTotal;
END PR_UPDATE_TOTAL;
/
Re: Help in Cursor!!!! [message #373 is a reply to message #370] Thu, 07 February 2002 16:57 Go to previous message
seng
Messages: 191
Registered: February 2002
Senior Member
If the condition of selected data is correct then this data should be updated (i mean if first record is fill the condition). I don't find any problem of your cursor, but you have Fetch twice in procedure, in after Open cursor and before End loop. please take out second and try again. maybe this is the cause. hope this will help you. thanks
Previous Topic: One question about SQL
Next Topic: TRUNCATE & DROP
Goto Forum:
  


Current Time: Thu Mar 28 08:37:21 CDT 2024