从语法上来讲DECLARE cur CURSOR for语句必须写在SET @sqlstr前面,这就意味着不能通过先执行一条动态语句根据结果拼凑游标的内容,而且游标在定义的时候也没有参数的概念,而是将定义的结构完全当作一个字符串直接处理,不会做任何的处理,也就是静态游标吧。关于dynamic cursor的内容官方有解释http://dev.mysql.com/doc/refman/5.6/en/connector-odbc-usagenotes-functionality.html#connector-odbc-usagenotes-functionality-dynamic-cursor我从5.1找到5.6只有这一个地方讲dynamic cursor而且内容一模一样。
解决方法:
思路 1、将自己想要传递的变量暂时存储在一个视图或则临时的表中,将变量放入视图
2、定义游标时直接查询这个视图(因为不可以传递变量,但是我们可以在视图中包含变量)
3、调用上面两个存储过程
实现:
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE temp_id INT;
DECLARE cur CURSOR for( SELECT id from tree_test_view);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP VIEW IF EXISTS tree_test_view;
SET @sqlstr = "CREATE VIEW tree_test_view as ";
SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM tree_test WHERE parent_id = 0 LIMIT ", top);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
OPEN cur;
FETCH cur INTO temp_id;
WHILE done <> 1 DO
DELETE FROM tree_test WHERE id = temp_id or parent_id = temp_id;
FETCH cur INTO temp_id;
END WHILE;
CLOSE cur;
END