mysql - 我正在尝试将 MS SQL Server 存储过程转换为 MySQL

Question

我在 MSSQL 中有以下存储过程

USE [db_DEV]
GO

/****** Object:  StoredProcedure [dbo].[DeleteInvoice]    Script Date: 11-02-2021 15:29:39 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteInvoice]
    @InvoiceNo AS VARCHAR(50),
    @ResultOutput BIT OUTPUT
AS

DECLARE @InvoiceId UNIQUEIDENTIFIER
DECLARE @PayrollId UNIQUEIDENTIFIER
DECLARE @CustomerId UNIQUEIDENTIFIER
DECLARE @Result AS BIT
DECLARE @TransactionTypeId AS INT
DECLARE @InvoiceStatus AS INT

BEGIN
    SET XACT_ABORT ON
    BEGIN TRY
            SELECT @InvoiceId = Id, @CustomerId = CustomerId, @TransactionTypeId = TransactionTypeId, @InvoiceStatus = [Status] FROM Invoice WHERE InvoiceNo=@InvoiceNo;

            /* Check if an invoice is in Open, In Review or Pending Approval status and if it's not of payment type */
            IF (@InvoiceStatus = 1 OR @InvoiceStatus = 2 OR @InvoiceStatus = 3) AND (@TransactionTypeId < 6)
                BEGIN
                    
                    BEGIN TRAN
                    
                    SELECT @PayrollId = Id FROM Payroll WHERE InvoiceId = @InvoiceId;

                    /* Check if an invoice is a payroll */
                    IF @TransactionTypeId = 1
                        BEGIN
                            DELETE FROM PayrollItemFee WHERE PayrollItemId IN (SELECT Id from PayrollItem WITH (NOLOCK) WHERE PayrollId = @PayrollId);
                            DELETE FROM PayrollItemBenefits WHERE PayrollItemId IN (SELECT Id from PayrollItem WITH (NOLOCK) WHERE PayrollId = @PayrollId);
                            DELETE FROM PayrollChangeItem WHERE PayrollId = @PayrollId;
                            DELETE FROM PayrollItem WHERE PayrollId = @PayrollId;
                            DELETE FROM PayrollFee WHERE PayrollId = @PayrollId;
                            DELETE FROM Payroll WHERE Id = @PayrollId;
                            DELETE FROM Activity WHERE CustomerId = @CustomerId AND [DATA] = 'New Payroll invoice added ' + @InvoiceNo
                        END
            
                    DELETE FROM TransactionHistory WHERE RecordId = @InvoiceId
                    DELETE FROM OpenTask WHERE RelatedEntityId = @InvoiceId
                    DELETE FROM InvoiceDocument WHERE InvoiceId = @InvoiceId;
                    DELETE FROM InvoiceNote WHERE InvoiceId = @InvoiceId;
                    DELETE FROM InvoiceRelated WHERE InvoiceId = @InvoiceId;
                    DELETE FROM InvoiceRelated WHERE RelatedInvoiceId = @InvoiceId;
                    DELETE FROM InvoiceItem WHERE InvoiceId = @InvoiceId;
                    DELETE FROM Invoice WHERE Id = @InvoiceId;  

                    SET @Result=1

                    COMMIT TRAN
                END
            ELSE
                SET @Result = 0
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
            PRINT(ERROR_MESSAGE())
            SET @Result=0
        END
    END CATCH

    SELECT @Result AS ResultOutput
END
GO

我试图将其转换为 MySQL

USE db_DEV;
 

DELIMITER //

CREATE PROCEDURE DeleteInvoice (
    InvoiceNo VARCHAR(50),
    OUT ResultOutput TINYINT )
BEGIN

DECLARE InvoiceId CHAR(36);
DECLARE PayrollId CHAR(36);
DECLARE CustomerId CHAR(36);
DECLARE Result TINYINT;
DECLARE TransactionTypeId INT;
DECLARE InvoiceStatus INT;

BEGIN
    SET XACT_ABORT ON
    BEGIN TRY
            SELECT Id, CustomerId, TransactionTypeId, Status INTO InvoiceId, CustomerId, TransactionTypeId, InvoiceStatus FROM Invoice WHERE InvoiceNo=InvoiceNo;

            IF (InvoiceStatus = 1 OR InvoiceStatus = 2 OR InvoiceStatus = 3) AND (TransactionTypeId < 6)
                THEN
                    
                    START TRANSACTION;
                    
                    SELECT Id INTO PayrollId FROM Payroll WHERE InvoiceId = InvoiceId;

                    IF TransactionTypeId = 1
                        THEN
                            DELETE FROM PayrollItemFee WHERE PayrollItemId IN (SELECT Id from PayrollItem WITH; (NOLOCK) WHERE PayrollId = PayrollId);
                            DELETE FROM PayrollItemBenefits WHERE PayrollItemId IN (SELECT Id from PayrollItem WITH; (NOLOCK) WHERE PayrollId = PayrollId);
                            DELETE FROM PayrollChangeItem WHERE PayrollId = PayrollId;
                            DELETE FROM PayrollItem WHERE PayrollId = PayrollId;
                            DELETE FROM PayrollFee WHERE PayrollId = PayrollId;
                            DELETE FROM Payroll WHERE Id = PayrollId;
                            DELETE FROM Activity WHERE CustomerId = v_CustomerId AND `DATA` = CONCAT('New Payroll invoice added ' , p_InvoiceNo);
                        END IF;
            
                    DELETE FROM TransactionHistory WHERE RecordId = InvoiceId;
                    DELETE FROM OpenTask WHERE RelatedEntityId = InvoiceId;
                    DELETE FROM InvoiceDocument WHERE InvoiceId = InvoiceId;
                    DELETE FROM InvoiceNote WHERE InvoiceId = InvoiceId;
                    DELETE FROM InvoiceRelated WHERE InvoiceId = InvoiceId;
                    DELETE FROM InvoiceRelated WHERE RelatedInvoiceId = InvoiceId;
                    DELETE FROM InvoiceItem WHERE InvoiceId = InvoiceId;
                    DELETE FROM Invoice WHERE Id = InvoiceId;   

                    SET Result=1;

                    COMMIT ;
            ELSE
                SET Result = 0;
            END IF;
    END; TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        THEN
            ROLLBACK;
            SET Result=0;
        END IF;
    END; CATCH

    SELECT Result AS ResultOutput;
END//
//

DELIMITER ;

但我收到以下错误。

错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 14 行的“ON BEGIN TRY SELECT Id, CustomerId, TransactionTypeId, Status INTO”附近使用正确的语法

我怀疑该错误是否与使用 SET XACT_ABORT ON

在使用 EXIT HANDLER 转换 TRY CATCH 时也需要一些帮助

我对数据库比较陌生，我正在尝试在没有我们数据库团队帮助的情况下完成这项工作。有人可以指导我在这里做错什么吗？

标签： mysqlsqlsql-serverdatabase-migrationmysql-error-1064