创建过程mysql_创建一个过程MySQL

Im trying to convert a procedure from using sql plus to mysql but am getting a syntax error on the third line where it says (W_IN IN NUMBER) and it has IN highlighted as the syntax error.

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:

-- Trigger DDL Statements

DELIMITER $$

CREATE PROCEDURE prc_cus_balance_update (W_IN 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 ;

Any help is greatly appreciated!

解决方案

This compiles in MySQL 5.5.23:

-- Trigger DDL Statements

DELIMITER $$

DROP PROCEDURE IF EXISTS prc_cus_balance_update;

CREATE PROCEDURE prc_cus_balance_update (IN W_IN INT UNSIGNED)

BEGIN

DECLARE W_CUS INT UNSIGNED DEFAULT 0;

DECLARE W_TOT DOUBLE DEFAULT 0; -- NOT USED?

-- 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 ;

Of course, in this case, a stored procedure is not needed, as the following query will perform the same function much faster (and easier to understand):

UPDATE

CUSTOMER c

INNER JOIN

INVOICE i ON i.CUS_CODE = c.CUS_CODE

SET

c.CUS_BALANCE = c.CUS_BALANCE + i.INV_TOTAL

WHERE

i.INV_NUMBER = W_IN