Im trying to convert a procedure from using sql plus to mysql but cannot get it to work.
SQL Plus:
CREATE OR REPLACE PROCEDURE PRC_CUS_BALANCE_UPDATE (W_IN IN NUMBER) AS
W_CUS NUMBER := 0;
W_TOT NUMBER := 0;
BEGIN
-- GET THE CUS_CODE
SELECT CUS_CODE INTO W_CUS
FROM INVOICE
WHERE INVOICE.INV_NUMBER = W_IN;
-- UPDATES CUSTOMER IF W_CUS > 0
IF W_CUS > 0 THEN
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE +
(SELECT INV_TOTAL FROM INVOICE WHERE INV_NUMBER = W_IN)
WHERE CUS_CODE = W_CUS;
END IF;
END;
mySQL:
DELIMITER $$
CREATE PROCEDURE prc_cus_balance_update (IN W_IN NUMBER) AS
W_CUS NUMBER = 0;
W_TOT NUMBER = 0;
BEGIN
-- GET CUS_CODE
SELECT CUS_CODE INTO W_CUS
FROM INVOICE
WHERE INVOICE.INV_NUMBER = W_IN;
-- UPDATES CUSTOMER IF W_CUS > 0
IF W_CUS > 0 THEN
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE +
(SELECT INV_TOTAL FROM INVOICE WHERE INV_NUMBER = W_IN)
WHERE CUS_CODE = W_CUS;
END IF;
END $$
DELIMITER ;
I keep getting an error on the line "CREATE PROCEDURE prc_cus_balance_update (IN W_IN DOUBLE) AS" and get an syntax error over AS.
Any help is greatly appreciated!