Help! Stored Procedures

[iceman709]

HI.. Thanks for taking the time to read my post.
I am a first year college student taking a database systems course. Right now I am trying to complete an assignment. I have 2 question left to solve that are causing some problems. At school we use MySQL, however the textbook uses Oracle as examples. Each have a different flavor of syntax (ughh, its so frustrating). Can somebody help me???????????

Question 21
Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8%), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.

Question 22
Create a procedure named pre_cus_balance_update that will take the invoice number as a parameter and update the customer balance. (Hint: You can use the DECLARE section to define a TOTINV numeric variable that holds the computed invoice total.)

THANKS!

[djm0219]

What have you tried that isn't working as desired? We'll be happy to help but you need to make an effort first and show us what you've tried.

[iceman709]

Hi Dave,
Thanks for your response..
Here is what I have done but its giving me errors. Most likely because I am using some examples from the textbook that is Oracle driven.

Question 21:

Create Or Replace Procedure Prc_Inv_Amounts(Inv_Number In Number) Is

Begin

Update Invoice Inv_Subtotal = (Select Sum(Line_Total)
From Invoice_Items
Where Inv_Nbr = Inv_Numnber)
Where Inv_Nbr = Inv_Number;

Update Invoice Inv_Tax =.O8 * Inv_Subtotal, Inv_Total = Inv_Subtotal + (.08 * Inv_Subtotal)
Where Inv_Nbr = Inv_Number;


End Prc_Inv_Amounts;

Question 22:
Create Or Replace Procedure Pre_Cus_Balance_Update(Inv_Number In Number) Is
Begin

Update Cus_Balance
Set Balance = Balance + (Select Invoice_Total
From Invoice
Where Inv_Nbr = Inv_Number)
Where Customer_Id = (Select Customer_Id
From Invoice
Where Inv_Nbr = Inv_Number);

End Pre_Cus_Balance_Update;

Again, Thanks for your help!

[BubikolRamios]

How did I get nicely formated text as below ? Clicked # in toolbar !


Update Cus_Balance
Set Balance = Balance + (Select Invoice_Total
From Invoice
Where Inv_Nbr = Inv_Number)
Where Customer_Id = (Select Customer_Id
From Invoice
Where Inv_Nbr = Inv_Number);


Do send us those errors.

Ahh, and this is not mysql definitely, as far as I remember it is oracle, and hence can't work.

Create Or Replace Procedure Pre_Cus_Balance_Update(Inv_Number In Number) Is

[iceman709]

Question 21:

Error
SQL query:

CREATE OR REPLACE PROCEDURE Prc_Inv_Amounts( Inv_Number
IN Number ) IS
BEGIN
UPDATE Invoice Inv_Subtotal = ( SELECT SUM( Line_Total )
FROM Invoice_Items
WHERE Inv_Nbr = Inv_Numnber )
WHERE Inv_Nbr = Inv_Number;


MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Procedure Prc_Inv_Amounts(Inv_Number In Number) Is

Begin

Update Invoice In' at line 1

Question 22:

Error
SQL query:

CREATE OR REPLACE PROCEDURE Pre_Cus_Balance_Update( Inv_Number
IN Number ) IS
BEGIN
UPDATE Cus_Balance SET Balance = Balance + ( SELECT Invoice_Total
FROM Invoice
WHERE Inv_Nbr = Inv_Number )
WHERE Customer_Id = (
SELECT Customer_Id
FROM Invoice
WHERE Inv_Nbr = Inv_Number ) ;


MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Procedure Pre_Cus_Balance_Update(Inv_Number In Number) Is
Begin

Update Cus_B' at line 1


Can someone help me convert this Oracle syntax to mySQL.

[Old Pedant]

DROP PROCEDURE IF EXISTS Prc_Inv_Amounts;
DELIMITER //
CREATE PROCEDURE Prc_Inv_Amounts( Inv_Number INT )
BEGIN
UPDATE Invoice Inv_Subtotal = ( SELECT SUM( Line_Total )
FROM Invoice_Items
WHERE Inv_Nbr = Inv_Numnber )
WHERE Inv_Nbr = Inv_Number;
END
//

DELIMITER ;


That's assuming your UPDATE syntax is correct.

Have you READ the MySQL docs???

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html