一、游标的使用
DROP PROCEDURE IF EXISTS myCURSOR;
CREATE PROCEDURE myCURSOR ()
BEGIN
DECLARE user_name VARCHAR(32);
/* 声明游标 */
DECLARE my_cursor CURSOR FOR select nickname from users;
/* 打开游标 */
OPEN my_cursor;
sale_loop: LOOP
FETCH my_cursor INTO user_name;
select user_name;
END LOOP;
-- 关闭游标
close my_cursor;
end
call myCURSOR;
二、动态游标
分析:
从语法上来讲DECLARE cur CURSOR for语句必须写在SET @sqlstr前面,这就意味着不能通过先执行一条动态语句根据结果拼凑游标的内容,而且游标在定义的时候也没有参数的概念,而是将定义的结构完全当作一个字符串直接处理,不会做任何的处理,也就是静态游标吧.
处理方式:
分析:
现在的问题就是将不确定值传给游标,简单的游标定义如下DECLARE cur CURSOR for SELECT * FROM TABLE。这里看到好多人说法是如果在for语句后面使用内置方法方法的话,方法参数可以是你传入的值,也就是说后面的SQL语句是支持方法调用的。没测试,那天测试了再补充吧。就拿后面的 select 语句来说这个语句里面都是确定的值,但是有一个我们可以控制的参数就是表名。表这里可以看作是一个临时的数据集合,如果我们可以控制里面的值,在游标OPEN之后读这个“动态的集合”,于是实现这个有临时表和视图两种途径。在这里我选择视图。
-- 第一种方法
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(IN top int)
BEGIN
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;
END
/**
第一个功能很简单,根据条件获得一个ID集合,并把集合存在tree_test_view这个视图中,这里直接在SQL编辑器写的代码,直接运行需要添加存储过程存在判断和DELIMITER转义换行符。现在我们已经获得需要要删除的树的ID集合,因为已只有两级的数,所有只需要知道根的id就可以直接删除整棵树,如果是多级的话就需要进行递归删除,当然前提是需要知道根ID,并且只知道根ID就足够了。
**/
-- ====================================================================================
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;
OPEN cur;
FETCH cur INTO temp_id;
WHILE done <> 1 DO
delete tree_test WHERE id = temp_id or parent_id = temp_id;
FETCH cur INTO temp_id;
END WHILE;
CLOSE cur;
END
-- ===================================================================================
CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)
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