存储过程_游标

CREATE DEFINER="root"@"%" PROCEDURE "sp_mall_category_move"(in parentId int,in level int)BEGIN  DECLARE _id int;  DECLARE _parentId int;  DECLARE _name varchar(255);  DECLARE _path varchar(255);  DECLARE _parentPath varchar(255) DEFAULT '0.';  DECLARE _level varchar(255);  -- 遍历数据结束标志  DECLARE done INT DEFAULT FALSE;  -- 游标  DECLARE cur CURSOR FOR SELECT id,name,parent_id FROM mall_category where parent_id = parentId;  -- 将结束标志绑定到游标  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 设置递归最大的深度  SET @@max_sp_recursion_depth = 10;   -- 打开游标  OPEN cur;  -- 开始循环  read_loop: LOOP    -- 提取游标里的数据，这里只有一个，多个的话也一样；    FETCH cur INTO _id,_name,_parentId;    -- 声明结束的时候    IF done THEN      LEAVE read_loop;    END IF;    IF parentId != 0 THEN      SELECT path into _parentPath from ymall_category where id = parentId;    END IF;    set _path = CONCAT(_parentPath,_id,'.');    -- 插入数据    INSERT INTO ymall_category(id,name,parent_id,path,level,sort,relations_num,deleted) VALUES (_id,_name,_parentId,_path,level,_id,0,0);    set _level = level+1;    -- 遍历所有子节点    call sp_mall_category_move(_id,_level);  END LOOP;  -- 关闭游标  CLOSE cur;END