创建过程 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12755003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a Procedure MySQL
提问by Noah Jones
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 转换为 mysql，但在第三行出现语法错误，它说 (W_IN IN NUMBER) 并且将 IN 突出显示为语法错误。
SQL Plus:
SQL 加：
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:
我的SQL：
-- 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!
任何帮助是极大的赞赏！
回答by Ross Smith II
This compiles in MySQL 5.5.23:
这在 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
回答by Alain Collins
The doc seems to say
医生似乎说
[ IN | OUT | INOUT ] param_name type
