如何将以下过程转换为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
