如何将SQL Server游标转换为MySQL等效

这是要转换的部分：

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

如果上面有问题,请提前抱歉

解决方法

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中完成的方式.)

这个例子引出了一个问题：为什么没有这样写(在SQL Server和MySQL中更有效)：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!