存储过程

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

---

BEGIN DECLARE contractNo VARCHAR(255) default null; DECLARE contractId VARCHAR(16) default null; declare strIds VARCHAR(1000) default null; DECLARE num int(10) default null; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT guid_contract_no,source_contract FROM ymall_contract_guide_relation; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET @@max_sp_recursion_depth = 10; delete from temp_contract_guide_relation; OPEN cur; read_loop: LOOP FETCH cur INTO contractNo,strIds; IF done THEN LEAVE read_loop; END IF; set num = length(strIds) - length(replace(strIds,',','')); -- 算出分隔符的总数 while num>0 DO set contractId = substr(strIds,1,instr(strIds,',')-1); set strIds = substr(strIds,length(contractId)+2); INSERT INTO temp_contract_guide_relation(contract_no,relation_id) VALUES (contractNo,contractId); set num = num - 1; end while; INSERT INTO temp_contract_guide_relation(contract_no,relation_id) VALUES (contractNo,strIds); END LOOP; CLOSE cur; end