存储过程修复数据

--refresh_accounter_no_1（刷新账套表的用户编号）
BEGIN
  DECLARE temp_id INT;
  DECLARE temp_accounter_id INT;
  DECLARE temp_accounter_no VARCHAR(64);
    DECLARE state VARCHAR(30);
    DECLARE account_book_cursor CURSOR FOR SELECT id,accounter_id FROM account_book;
    DECLARE CONTINUE HANDLER FOR 1329
    BEGIN
        SET state = 'error';
    END; 
  OPEN account_book_cursor;
  REPEAT
        FETCH account_book_cursor INTO temp_id,temp_accounter_id;
        SELECT no INTO temp_accounter_no FROM account WHERE id = temp_accounter_id;
        UPDATE account_book SET accounter_no = temp_accounter_no WHERE id = temp_id;
        UNTIL state = 'error'
    END REPEAT;
    CLOSE account_book_cursor;
END

--refresh_accounter_no_2（刷新日志表的用户编号）
BEGIN
  DECLARE temp_id INT;
  DECLARE temp_accounter_id INT;
  DECLARE temp_accounter_no VARCHAR(64);
    DECLARE state VARCHAR(30);
    DECLARE operation_log_cursor CURSOR FOR SELECT id,accounter_id FROM operation_log;
    DECLARE CONTINUE HANDLER FOR 1329
    BEGIN
        SET state = 'error';
    END; 
    OPEN operation_log_cursor;
  REPEAT
        FETCH operation_log_cursor INTO temp_id,temp_accounter_id;
        SELECT no INTO temp_accounter_no FROM account WHERE id = temp_accounter_id;
        UPDATE operation_log SET accounter_no = temp_accounter_no WHERE id = temp_id;
        UNTIL state = 'error'
    END REPEAT;
    CLOSE operation_log_cursor;
    SELECT temp_id,temp_accounter_id,temp_accounter_no;
END

 

转载于:https://www.cnblogs.com/bchen/p/7550470.html