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