mysql - 将 MS-SQL Server 存储过程转换为 MySQL 查询

Question

在过去的一个小时里，我被困在这个问题上，无法将这个 MSSQL Server 存储过程转换为“MySQL 查询”:

DECLARE @LedgerTbl TABLE (PARTY_ID VARCHAR(100),VRDATE VARCHAR(200),
                          VRNOA VARCHAR(200),ETYPE VARCHAR(50),
                          DESCRIPTION VARCHAR(500),DEBIT DECIMAL,
                          CREDIT DECIMAL, RunningTotal decimal) 

DECLARE @RunningTotal decimal 

SET @RunningTotal = 0 

INSERT INTO @LedgerTbl 
SELECT PARTY_ID,VRDATE,DCNO VRNOA,ETYPE,DESCRIPTION,DEBIT,CREDIT, null 
FROM PLEDGER WHERE PARTY_ID=@partyId AND VRDATE BETWEEN @from AND @to 
ORDER BY VRDATE,ETYPE,VRNOA 

UPDATE @LedgerTbl 
SET @RunningTotal = RunningTotal = @RunningTotal + (DEBIT-CREDIT) 
FROM @LedgerTbl 

SELECT * FROM @LedgerTbl 

如何将其转换为单个 MySQL 查询或 MySQL 存储过程？

更新

我试图将它转换为但它给了我下面给出的这些错误:

DELIMETER //

CREATE PROCEDURE `Acc_Ledger` ()
BEGIN
    DECLARE RunningTotal DECIMAL;
    SET RunningTotal = 0;
    CREATE TEMPORARY TABLE LedgerTbl (PARTY_ID VARCHAR(100),VRDATE VARCHAR(200),VRNOA VARCHAR(200),ETYPE VARCHAR(50),DESCRIPTION VARCHAR(500),DEBIT DECIMAL,RTotal decimal);
    INSERT INTO LedgerTbl 
    SELECT PARTY_ID,VRDATE,DCNO VRNOA,ETYPE,DESCRIPTION,DEBIT,CREDIT, null 
    FROM PLEDGER WHERE PARTY_ID=17 AND VRDATE BETWEEN '2013/12/02' AND '2010/12/02' 
    ORDER BY VRDATE,ETYPE,VRNOA; 

    UPDATE LedgerTbl 
    SET RunninTotal = RTotal = RunningTotal + (DEBIT-CREDIT) 
    FROM LedgerTbl; 

    SELECT * FROM LedgerTbl;
END//
DELIMETER;

错误如下:

您的 SQL 查询似乎有错误。下面的MySQL服务器错误输出，如果有的话，也可以帮助你诊断问题

ERROR: Unknown Punctuation String @ 10 STR: // SQL: DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;

SQL query:

DELIMETER// CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal DECIMAL;

MySQL said: Documentation

#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 'DELIMETER//

CREATE PROCEDURE Acc_Ledger () BEGIN DECLARE RunningTotal' at line 1

有人可以评论一下吗？

Answer 1

MSSQL 过程似乎计算了一个总和，
我已经在 sqlfiddle 上测试了这个过程(略有修改)，它给出了以下结果:
http://www.sqlfiddle.com/#!6/0e909/1

MSSQL 过程可能包含一个拼写错误:SELECT PARTY_ID,VRDATE,DCNO VRNOA,ETYPE, 不清楚 DCNO VRNOA 是两个单独的列，还是一列 DCNO_VRNOA 它们之间缺少下划线。
我假设它们是两个独立的列。

要在 MySql 中计算运行总计，不需要使用临时表。
这个简单的查询完成了这个任务:

SELECT PARTY_ID,VRDATE,DCNO, VRNOA,ETYPE,DESCRIPTION,
       DEBIT,
       CREDIT, 
       @RunningTotal := @RunningTotal + (DEBIT-CREDIT) RunningTotal 
FROM PLEDGER ,
     ( SELECT @RunningTotal:=0) init_variables
WHERE PARTY_ID=1 
  AND VRDATE BETWEEN '2013-11-11' AND '2013-11-11'
ORDER BY VRDATE,ETYPE,VRNOA; 

在此处查看演示:--> http://www.sqlfiddle.com/#!2/daa6e/1

过程可能是这样的:

DELIMITER /
CREATE PROCEDURE `Acc_Ledger` ()
BEGIN
    SELECT PARTY_ID,VRDATE,DCNO, VRNOA,ETYPE,DESCRIPTION,
           DEBIT,
           CREDIT, 
           @RunningTotal := @RunningTotal + (DEBIT-CREDIT) RunningTotal 
    FROM PLEDGER ,
         ( SELECT @RunningTotal:=0) init_variables
    WHERE PARTY_ID=1 
      AND VRDATE BETWEEN '2013-11-11' AND '2013-11-11'
    ORDER BY VRDATE,ETYPE,VRNOA; 
END /
DELIMITER ;