/*定义用户表游标*/ DECLARE account_cursor CURSORFORSELECT id,manager FROM account ORDERBY manager,id;
/*定义错误处理监听,用于结束游标循环*/ DECLARE CONTINUE HANDLER FOR1329 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 =1WHERE id =1; ELSE /*设置上级leaf为0*/ UPDATE account SET leaf =0WHERE id = temp_manager; /*查询上级编号*/ SELECTnoINTO temp_accounter_no FROM account WHERE id = temp_manager; /*设置上级编码*/ UPDATE account SET pno = temp_accounter_no WHERE id = temp_id; /*查询上级原有的最大下级编码*/ SELECTMAX(no) INTO temp_max_no FROM account WHERE pno = temp_accounter_no; /*如果最大下级编码为空,生成新的编码,否则把原来的编码加一*/ IF (temp_max_no ISNULL) 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 SETno= 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 =1WHERE leaf ISNULL; RETURN0; END