判断存储过程是否存在并删除：DROP PROCEDURE IF EXISTS CRM_Sort_LgcAsset
创建存储过程： CREATE PROCEDURE CRM_Sort_LgcAsset()
声明变量： DECLARE id1 int;
DECLARE supassortmentstr1 VARCHAR(1000);
定义游标： DECLARE rs CURSOR FOR select id,supassortmentid,supassortmentstr from LgcAssetAssortment ORDER BY id asc;
定义完成标志： DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
打开游标： OPEN rs;
定义循环： read_loop: LOOP ****** END LOOP read_loop ;
将游标的值赋给变量： FETCH NEXT from rs INTO id1,supassortmentid1,supassortmentstr1;
判断游标是否已经到头IF done THEN
LEAVE read_loop;
END IF;
判断语句： IF *****THEN IF******THEH
******** **********
END IF; ELSE
**********
END IF
字符串拼接：concat(supassortmentstr2,supassortmentid1,"|");
关闭游标： CLOSE rs;
delimiter :DELIMITER$$ *********END$$ DELIMITER;
因为可能输入较多的语句，且语句中包含有分号。
默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。
因为mysql一遇到分号，它就要自动执行。
这种情况下，就可以使用delimiter，把delimiter后面换成其它符号，如$$。
此时，delimiter作用就是对整个小段语句做一个简单的封装。定义了一个结束标志$$
执行存储过程：call CRM_Sort_LgcAsset()
一个简单的例子
DROP PROCEDURE IF EXISTS CRM_Sort_LgcAsset
;
DELIMITER$$
create PROCEDURE CRM_Sort_LgcAsset()
BEGINDECLARE id1 int;DECLARE supassortmentid1 int;DECLARE supassortmentstr1 VARCHAR(1000); DECLARE supassortmentstr2 VARCHAR(1000); DECLARE done INT DEFAULT FALSE;DECLARE rs CURSOR FOR select id,supassortmentid,supassortmentstr from LgcAssetAssortment ORDER BY id asc;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN rs;
read_loop: LOOP FETCH NEXT from rs INTO id1,supassortmentid1,supassortmentstr1;IF done THENLEAVE read_loop;END IF;
IF supassortmentid1=0 THENSET supassortmentstr1=concat('0|');UPDATE LgcAssetAssortment SET supassortmentstr=supassortmentstr1 WHERE id=id1;
ELSE
BEGIN
DECLARE donet INT DEFAULT FALSE;DECLARE rsTemp CURSOR FOR select supassortmentstr from LgcAssetAssortment where id=supassortmentid1;DECLARE CONTINUE HANDLER FOR NOT FOUND SET donet = TRUE;OPEN rsTemp;_loop:LOOPFETCH NEXT from rsTemp INTO supassortmentstr2;IF donet THENLEAVE _loop;END IF;SET supassortmentstr1=concat(supassortmentstr2,supassortmentid1,"|");UPDATE LgcAssetAssortment SET supassortmentstr=supassortmentstr1 WHERE id=id1;END LOOP _loop;CLOSE rsTemp;
END;
END IF;
END LOOP read_loop ;
CLOSE rs;
END$$
DELIMITER;call CRM_Sort_LgcAsset()