将sql server存储过程重写为mysql(mariadb)

ncecgwcz 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(312)

我需要重写存储过程从mssqlserver语法到mysql的mariadb standart。我已经挣扎了好几个小时了。我从来没想过会有这么多的不同。有什么工具可以自动完成吗？这是存储过程的代码

CREATE PROCEDURE sp_Validate @username nvarchar(50), @email nvarchar(100) = NULL
AS
BEGIN
SELECT * INTO #tmp FROM Users WHERE Username = @username

IF((SELECT COUNT(*) FROM #tmp) < 1)
    BEGIN               
        INSERT INTO Users(Username, Email, FirstLaunch) VALUES (@username, @email, GETDATE())
        SELECT 1
    END
ELSE IF(DATEDIFF(DAY, GETDATE(), (SELECT FirstLaunch FROM #tmp)) > 8)
    BEGIN
        UPDATE Users SET Email = @email WHERE Username = @username
        SELECT 0
    END
ELSE
    BEGIN
        UPDATE Users SET Email = @email WHERE Username = @username
        SELECT 1
    END

IF OBJECT_ID('#tmp') IS NOT NULL  
    DROP TABLE #tmp
END

它检查用户是否在users表中，以及他是否在7天前首次登录。

mysql sql-server mariadb stored-procedures

来源：https://stackoverflow.com/questions/51819953/rewrite-sql-server-stored-procedure-to-mysqlmariadb

1条答案

按热度按时间

qyswt5oh1#

存储过程语言的实现各不相同。mssqlserver的语言（transact-sql）最初是由sybase开发的。mysql的方言有点不同。不是一个真正的客户锁定，只是随着时间的推移，事情发生了变化。
要在mysql中实现该过程，可以使用：

CREATE PROCEDURE sp_Validate( 
in_username varchar(50), 
in_email varchar(100)
)
begin

declare v_cnt int;
declare v_first_launch datetime;

select 1, FirstLaunch into v_cnt, v_first_launch
from Users
where Username = in_username;

if (v_cnt = 1) then
  update Users
  set Email = in_email 
  where Username = in_username;

  if (datediff(now(), v_first_launch) > 8) then
    select 0;
  else
    select 1;
  end if;
else
  insert into Users(Username, Email, FirstLaunch) 
  values (in_username, in_email, now());

  select 1;
end if;

end

赞(0）回复(0）举报 2021-06-23

我来回答

将sql server存储过程重写为mysql(mariadb)

1条答案

相关问题

热门标签

最新问答