--set_account_data 重新生成用户编号
BEGIN
DECLARE temp_id INT(8);
DECLARE temp_manager INT(8);
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
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;
UPDATE account SET leaf = 1 WHERE leaf IS NULL;
RETURN 0;
END