mysql - 将 SQL Server 存储过程转换为 Mysql 时出错

Question

当我将整个数据库迁移到 Mysql 时，我正在尝试将 SQL Server 存储过程转换为 Mysql

但是我无法转换一些使用 XML 交互的存储过程。我不是 Mysql 人。有人可以帮我吗？

提前致谢。

我在 SQL Server 中的存储过程如下所示:

ALTER PROCEDURE [dbo].[usp_MemberToDoList_UpdateForMember]
(
    @xml nvarchar(max),
    @login varchar(255)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @doc int;
    DECLARE @now datetime = GETUTCDATE();

    EXEC [sp_xml_preparedocument] @doc OUTPUT, @xml;

    UPDATE
        [mtdl]
    SET
        [taskCompleteDate] = CASE WHEN [isCompleted] = CONVERT(bit, 1) THEN @now ELSE NULL END,
        [updatedBy] = @login,
        [dateUpdated] = GETUTCDATE()
    FROM
        [MemberToDoList] [mtdl]
    JOIN
        OPENXML (@doc, '/todos/todo') WITH
    (
        [id] int,
        [isCompleted] bit
    ) [x] ON [x].[id] = [mtdl].[memberToDoListId];

    EXEC [sp_xml_removedocument] @doc;
END

当我转换为 Mysql 时，它看起来像

CREATE PROCEDURE `conversion`.`usp_MemberToDoList_UpdateForMember` (xml longtext,
    login varchar(255))
BEGIN
    DECLARE v_doc int;
    DECLARE v_now datetime(3);
    set v_now = UTC_TIMESTAMP();

    CALL sp_xml_preparedocument(@doc)
    PREPARE stmt FROM @stmt_str;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
      v_doc OUT, xml;

    UPDATE
        mtdl
    SET
        `taskCompleteDate` = CASE WHEN `isCompleted` = CONVERT(1,UNSIGNED) THEN v_now ELSE NULL END
 ,`updatedBy` = @login,
        `dateUpdated` = UTC_TIMESTAMP()
    FROM
        `MemberToDoList` `mtdl`
    JOIN
        ExtractValue(@doc, '/todos/todo') WITH
    (
        `id` int,
        `isCompleted` bit
    ) `x` ON [x].[id] = `mtdl`.`memberToDoListId`;

    SET @stmt_str =  `sxml_removedocument`;
    PREPARE stmt FROM @stmt_str;
    EXECUTE stmt;`enter code here`
    DEALLOCATE PREPARE stmt; @doc;
END

但让我给出错误:

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 'END' at line 1

仅供引用，我使用的是 Mysql 版本 5.6

Answer 1

试试这个:

您创建的 Nor sql 查询，如果您想创建 sql 过程，则必须在查询的开头和结尾添加分隔符。并;添加像我的查询END;一样的查询如下:

delimiter //
CREATE PROCEDURE `conversion`.`usp_MemberToDoList_UpdateForMember` (xml longtext,
    login varchar(255))
BEGIN
    DECLARE v_doc int;
    DECLARE v_now datetime(3);
    set v_now = UTC_TIMESTAMP();

    CALL sp_xml_preparedocument(@doc)
    PREPARE stmt FROM @stmt_str;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
      v_doc OUT, xml;

    UPDATE
        mtdl
    SET
        `taskCompleteDate` = CASE WHEN `isCompleted` = CONVERT(1,UNSIGNED) THEN v_now ELSE NULL END
 ,`updatedBy` = @login,
        `dateUpdated` = UTC_TIMESTAMP()
    FROM
        `MemberToDoList` `mtdl`
    JOIN
        ExtractValue(@doc, '/todos/todo') WITH
    (
        `id` int,
        `isCompleted` bit
    ) `x` ON [x].[id] = `mtdl`.`memberToDoListId`;

    SET @stmt_str =  `sxml_removedocument`;
    PREPARE stmt FROM @stmt_str;
    EXECUTE stmt;`enter code here`
    DEALLOCATE PREPARE stmt; @doc;
END; //
delimiter ; 

<强> http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html