如何将以下过程转换为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
如果上面有问题,请提前抱歉
解决方法
MysqL等价物将是这样的：
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
