创建一个过程MySQL [英] Creating a Procedure MySQL

问题描述

我正在尝试将过程从使用sql plus转换为mysql，但是在第三行显示语法错误，该行显示(W_IN IN NUMBER)并突出显示了IN作为语法错误.

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 ;

任何帮助将不胜感激！

推荐答案

此版本在MySQL 5.5.23中进行编译:

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

这篇关于创建一个过程MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！