修正MYSQL错误数据的一个存储过程

-- 添加索引

CREATE INDEX idx_STRUCTURE_ID ON t_resource_info(STRUCTURE_ID);

DROP PROCEDURE IF EXISTS `P_RepairSchemeIdError`;

CREATE  PROCEDURE `P_RepairSchemeIdError`()

BEGIN

    DECLARE v_scheme_id_int int ;

    DECLARE v_maxId int;

  DECLARE v_CurrentId int;

    set v_scheme_id_int=;

    select MAX(id), MIN(id) into v_maxId,v_CurrentId from t_resource_info where scheme_id_int=v_scheme_id_int;

  while (v_CurrentId<v_maxId) do

        UPDATE t_resource_info p INNER JOIN t_resource_structure pp ON p.STRUCTURE_ID = pp.STRUCTURE_ID

        SET p.SCHEME_ID_INT = pp.SCHEME_ID_INT WHERE p.SCHEME_ID_INT=v_scheme_id_int and p.id<v_CurrentId;

        set v_CurrentId=v_CurrentId+;

    end while;

    UPDATE t_resource_info p INNER JOIN t_resource_structure pp ON p.STRUCTURE_ID = pp.STRUCTURE_ID

        SET p.SCHEME_ID_INT = pp.SCHEME_ID_INT WHERE p.SCHEME_ID_INT=v_scheme_id_int;

END

call P_RepairSchemeIdError;