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

MySQL存储过程 游标 错误处理的示例代码--set_account_data 重新生成用户编号

BEGIN

DECLARE temp_id INT(8); /*用户id*/

DECLARE temp_manager INT(8); /*上级id*/

DECLARE temp_accounter_no VARCHAR(64); /*上级编码*/

DECLARE temp_max_no VARCHAR(64); /*上级的最大下级编码*/

DECLARE max_no VARCHAR(64); /*编码*/

DECLARE str1 VARCHAR(64); /*编码*/

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