mysql 存储过程总结

 1、搜索插入

DELIMITER $$
drop PROCEDURE IF EXISTS `wk`;
CREATE PROCEDURE `wk`(i int(11))
BEGIN
DECLARE j INT(11);
DECLARE str VARCHAR(100);
SET j = i-8;
set @cnt = (SELECT count(1) FROM t_monitor_product WHERE not EXISTS(SELECT uuid FROM automate_itcomp_product WHERE productUuid=t_monitor_product.uuid));
WHILE i < @cnt+1+8 DO
		if i<10 and i>=8 then
    SET str = CONCAT('automatetypeuuid000000000000000',i);
		elseif i<100 and i>=10 then
				SET str = CONCAT('automatetypeuuid00000000000000',i);
		else
				SET str = CONCAT('automatetypeuuid0000000000000',i);
		end if;
		INSERT INTO automate_itcomp_product(uuid,productUuid,automateType) SELECT str,uuid,5 FROM t_monitor_product WHERE not EXISTS(SELECT uuid FROM automate_itcomp_product WHERE productUuid=t_monitor_product.uuid)  LIMIT j,1;
		set i = i +1;
END WHILE;
END$$

CALL wk(8);

drop PROCEDURE IF EXISTS `wk`;

 2、递归获取所有子节点

drop FUNCTION IF EXISTS `getChildList`; 
CREATE FUNCTION `getChildList`(rootId varchar(100)) RETURNS varchar(2000)  
BEGIN   
DECLARE str varchar(2000);  
DECLARE cid varchar(100);   
SET str = '$';   
SET cid = rootId;   
WHILE cid is not null DO   
    SET str = concat(str, ',', cid);   
    SELECT group_concat(uuid) INTO cid FROM t_department_info where FIND_IN_SET(depart_parent_uuid, cid) > 0;   
END WHILE;   
RETURN str;   
END