如何将SQL Server游标转换为MySQL等价物 [英] How to convert a SQL Server cursor to MySQL equivalent

问题描述

如何将以下过程转换为MySQL格式？

How can I convert the procedure below to MySQL format?

这里是要转换的部分：

DECLARE @CurrentFirstName varchar(300)
DECLARE @CurrentAge INT

DECLARE CursorName CURSOR FAST_FORWARD FOR 
    SELECT Firstname,Age 
    FROM Customers

OPEN CursorName
FETCH NEXT FROM CursorName INTO @CurrentFirstName, @CurrentAge

WHILE @@FETCH_STATUS = 0
BEGIN
      IF @AGE>60 /*this is stupid but we can apply any complex condition here*/ BEGIN
    insert into ElderCustomers values (@CurrentFirstName,@CurrentAge)
      END


    FETCH NEXT FROM CursorName INTO @CurrentFirstname,@CurrentAge
END

CLOSE CursorName
DEALLOCATE CursorName

如果上面有错误，请提前处理

Sorry in advance if there is something wrong above

推荐答案

将是这样的：

BEGIN
  DECLARE CurrentFirstName VARCHAR(300);
  DECLARE CurrentAge INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE CursorName CURSOR FOR
    SELECT FirstName, Age FROM Customers;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN CursorName;
  myloop: LOOP
    FETCH CursorName INTO CurrentFirstName, CurrentAge;
    IF done THEN
      LEAVE myloop;
    END IF;
    IF CurrentAge > 60 THEN
      insert into ElderCustomers values (CurrentFirstName,CurrentAge);
    END IF;
  END LOOP;
  CLOSE CursorName;
END;

最大的区别在于循环，使用CONTINUE HANDLER来设置一个标志。要获取的行，并且在设置标志时退出循环。 （这看起来很丑陋，但这是在MySQL中的做法。）

The big difference is in the loop, using the CONTINUE HANDLER to set a flag when there are no more rows to fetch, and exiting the loop when the flag is set. (That looks ugly, but that's the way it's done in MySQL.)

这个例子提出了为什么这不是写的问题MySQL）as：

This example begs the question why this isn't written (more efficiently, in both SQL Server and MySQL) as:

INSERT INTO ElderCustomers (FirstName, Age)
SELECT FirstName, Age
  FROM Customers
 WHERE Age > 60

这篇关于如何将SQL Server游标转换为MySQL等价物的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！