MySQL游标和控制结构代码学习

-- Procedure to find the orderid with the largest amount
-- could be done with max,but just to illustrate stored procedure principles
-- Database movies(20140420)

DELIMITER //
CREATE PROCEDURE largest_order(OUT largest_id INT)
BEGIN
	DECLARE this_id INT;
	DECLARE this_amount FLOAT;
	DECLARE 1_amount FLOAT DEFAULT 0.0;
	DECLARE 1_id INT;
	
	DECLARE done INT DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	DECLARE c1 CURSOR FOR SELECT tag_id,kind_id FROM tmp_tags;
	
	OPEN c1;
	REPEAT
		FETCH c1 INTO this_id,this_amount;
		IF NOT done THEN
			IF this_amount > 1_amount THEN
				SET 1_amount = this_amount;
				SET 1_id = this_id;
			END IF;
		END IF;
	UNTIL DONE END REPEAT;
	CLOSE c1;
	
	SET largest_id = 1_id;
	
END
//

DELIMITER ;

在MySQL控制台运行上述代码，出现报错信息：

ERROR 1338 (42000): Cursor declaration after handler declaration

尝试下面的方法

cursor的声明放在handler前面

SQL代码变为

-- Procedure to find the orderid with the largest amount
-- could be done with max,but just to illustrate stored procedure principles
-- Database movies(20140420)

DELIMITER //
CREATE PROCEDURE largest_order(OUT largest_id INT)
BEGIN
	DECLARE this_id INT;
	DECLARE this_amount FLOAT;
	DECLARE 1_amount FLOAT DEFAULT 0.0;
	DECLARE 1_id INT;
	
	DECLARE done INT DEFAULT 0;
	DECLARE c1 CURSOR FOR SELECT tag_id,kind_id FROM tmp_tags;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	
	OPEN c1;
	REPEAT
		FETCH c1 INTO this_id,this_amount;
		IF NOT done THEN
			IF this_amount > 1_amount THEN
				SET 1_amount = this_amount;
				SET 1_id = this_id;
			END IF;
		END IF;
	UNTIL DONE END REPEAT;
	CLOSE c1;
	
	SET largest_id = 1_id;
	
END
//

DELIMITER ;

改成上面的代码之后，可以运行。

执行上述SQL语句之后，在执行下面的语句

CALL largest_order(@l);
SELECT @l;

执行结果是