Mysql存储过程，游标，嵌套循环

判断存储过程是否存在并删除：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()
BEGIN
	DECLARE  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 THEN
                LEAVE read_loop;
 END IF;
IF supassortmentid1=0 THEN
	 SET 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:LOOP
	FETCH  NEXT from rsTemp INTO supassortmentstr2;
  IF donet THEN
                LEAVE _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()