Stored Procedure cursor error handling (regenerate user ID), stored procedure user ID

Stored Procedure cursor error handling (regenerate user ID), stored procedure user ID

-- Set_account_data regenerate user id begin declare temp_id INT (8);/* User id */DECLARE temp_manager INT (8);/* parent id */DECLARE temp_accounter_no VARCHAR (64 ); /* upper-level encoding */DECLARE temp_max_no VARCHAR (64);/* upper-level maximum lower-level encoding */DECLARE max_no VARCHAR (64 ); /* encoding */DECLARE str1 VARCHAR (64);/* encoding */DECLARE temp_no INT (8);/* encoding */DECLARE temp_level INT (8 ); /* times */DECLARE state VARCHAR (30);/* error handling listening variable * // * defines the user table CURSOR */DECLARE account_cursor cursor for select id, manager FROM account order by manager, id;/* defines the error handling listener to END the cursor loop */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/* SET the upper-level leaf to 0 */UPDATE account SET leaf = 0 WHERE id = temp_manager;/* query the upper-level id */SELECT no INTO temp_accounter_no FROM account WHERE id = temp_manager; /* SET the upper-level encoding */UPDATE account SET pno = temp_accounter_no WHERE id = temp_id;/* query the original upper-level maximum lower-level encoding */select max (no) INTO temp_max_no FROM account WHERE pno = temp_accounter_no;/* IF the maximum lower-level encoding is null, a new encoding IS generated. Otherwise, add the original encoding plus */IF (temp_max_no is null) then set max_no = concat (temp_accounter_no, '000000'); else set str1 = SUBSTR (temp_max_no, LENGTH (temp_max_no)-0001); 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; /* If leaf IS changed to null, 1 */UPDATE account SET leaf = 1 WHERE leaf is null; RETURN 0; END