MySQL存储过程游标错误处理的示例代码

--set_account_data 重新生成用户编号

BEGIN
 DECLARE temp_id INT(8); /*用户id*/
 DECLARE temp_manager INT(8); /*上级id*/
 DECLARE temp_accounter_no VARCHAR(); /*上级编码*/
 DECLARE temp_max_no VARCHAR(); /*上级的最大下级编码*/
 DECLARE max_no VARCHAR(); /*编码*/
 DECLARE str1 VARCHAR(); /*编码*/
 DECLARE temp_no INT(8); /*编码*/
 DECLARE temp_level INT(8); /*级次*/
 DECLARE state VARCHAR(30); /*错误处理监听变量*/

 /*定义用户表游标*/
 DECLARE account_cursor CURSOR FOR SELECT id,manager FROM account ORDER BY manager,id;

 /*定义错误处理监听，用于结束游标循环*/
 DECLARE CONTINUE HANDLER FOR 1329
 BEGIN
 SET state = 'error';
 END; 

 OPEN account_cursor;
 REPEAT
 FETCH account_cursor INTO temp_id,temp_manager;
 IF (temp_id = 1) THEN
 UPDATE account SET leaf = 0,no = '01',level = 1 WHERE id = 1;
 ELSE
 /*设置上级leaf为0*/
 UPDATE account SET leaf = 0 WHERE id = temp_manager;
 /*查询上级编号*/
 SELECT no INTO temp_accounter_no FROM account WHERE id = temp_manager;
 /*设置上级编码*/
 UPDATE account SET pno = temp_accounter_no WHERE id = temp_id;
 /*查询上级原有的最大下级编码*/
 SELECT MAX(no) INTO temp_max_no FROM account WHERE pno = temp_accounter_no;
 /*如果最大下级编码为空，生成新的编码，否则把原来的编码加一*/
 IF (temp_max_no IS NULL) THEN
 SET max_no = concat(temp_accounter_no, '0001');
 ELSE 
 SET str1 = SUBSTR(temp_max_no,LENGTH(temp_max_no)-3,4);
 SET temp_no = str1;
 SET temp_no = temp_no + 1;
 SET str1 = temp_no;
 IF (LENGTH(str1) = 1) THEN
 SET str1 = concat('000', str1);
 ELSEIF (LENGTH(str1) = 2) THEN
 SET str1 = concat('00', str1);
 ELSEIF (LENGTH(str1) = 3) THEN
 SET str1 = concat('0', str1); 
 END IF;
 SET max_no = concat(temp_accounter_no, str1);
 END IF;
 UPDATE account SET no = max_no WHERE id = temp_id;
 SET temp_level = (LENGTH(max_no) + 2) / 4;
 UPDATE account SET level = temp_level WHERE id = temp_id;
 END IF;
 UNTIL state = 'error'
 END REPEAT;
 CLOSE account_cursor;
 /*修改leaf为null的为1*/
 UPDATE account SET leaf = 1 WHERE leaf IS NULL;
 RETURN 0;
END