超市Locations表存储过程新增过道/行时多生成过道的问题求助

问题背景 #

有一张名为Locations的表，用于存储超市的过道（Aisles）和行（Rows）信息，表结构如下：

初始状态下指定超市的Locations表无数据，通过自定义存储过程插入对应超市的过道和行数据，过道用字母（A、B、C...）表示，行用数字（1、2、3...）表示。例如超市1初始有2个过道3行，插入的数据如下：

编写两个存储过程用于新增过道或行时出现异常：

• 从2过道3行增加到4过道5行时，插入结果正确，过道A-D各有1-5行；
• 从4过道5行增加到5过道6行时，生成了A-F共6个过道，每个过道有6行，数据不符合预期。

现有存储过程代码 #

SP_UPDATE_LOCATIONS #

CREATE OR ALTER PROCEDURE [dbo].[SP_UPDATE_LOCATIONS]
    @supermarketId BIGINT, -- Warehouse Id
    @noAisles BIGINT, -- New number of aisles
    @noRows BIGINT -- New number of rows
    
AS
BEGIN

    DECLARE @oldNoAisles BIGINT;
    DECLARE @oldNoRows BIGINT;

    SELECT 

        @oldNoAisles = WAR.Aisles,
        @oldNoRows = WAR.Rows

    FROM SUPERMARKTETS WAR
    WHERE WAR.SupermarketId = @supermarketId

    IF (@oldNoRows > @noRows)
    BEGIN
        DELETE FROM AR_WAREHOUSE_LOCATIONS
        WHERE SupermarketId = @supermarketId
        AND WAL_N_ROW = @oldNoRows;
    END
    ELSE
        BEGIN
            --EXECUTE [dbo].[AR_SP_ADD_WAREHOUSE_LOCATIONS] @supermarketId, @noAisles, @noRows
            EXECUTE [dbo].[SP_ADD_LOCATIONS] @supermarketId, @oldNoAisles, @noRows

        END

    IF (@oldNoAisles > @noAisles)
    BEGIN
        DECLARE @aisleToDelete VARCHAR(50) = CHAR(@oldNoAisles + 64);
    
        DELETE FROM AR_WAREHOUSE_LOCATIONS
        WHERE SupermarketId = @supermarketId
        AND WAL_CH_AISLE = @aisleToDelete;
    END
    ELSE
        BEGIN
            EXECUTE [dbo].[SP_ADD_LOCATIONS] @supermarketId, @noAisles, @noRows
        END

    UPDATE SUPERMARKTETS
    SET Aisles = @noAisles,
        Rows = @noRows
    WHERE SupermarketId = @supermarketId;

END;

SP_ADD_LOCATIONS #

CREATE OR ALTER PROCEDURE [dbo].[SP_ADD_LOCATIONS]
    @supermarketId bigint,
    @NewAisleCount bigint,
    @NewRowCount bigint
AS
BEGIN
    -- Determine the starting aisle for new aisles (e.g., 'D' for the 4th aisle)
    DECLARE @StartingAisle nvarchar(50);
    SET @StartingAisle = CHAR(ASCII('A') + (SELECT MAX(ASCII(Aisles)) - ASCII('A') + 1 FROM Locations WHERE SupermarketId = @supermarketId));

    -- Insert new aisles and rows for the new aisles
    DECLARE @NewAisle nvarchar(50);
    SET @NewAisle = @StartingAisle;
    DECLARE @RowNumber bigint;
    SET @RowNumber = 1;

    WHILE @RowNumber <= @NewRowCount
    BEGIN
        INSERT INTO Locations (SupermarketId, Aisles, Rows)
        VALUES (@supermarketId, @NewAisle, @RowNumber);

        SET @RowNumber = @RowNumber + 1;
    END;

    -- Insert rows for existing aisles
    DECLARE @ExistingAisle nvarchar(50);
    SET @ExistingAisle = 'A';

    WHILE ASCII(@ExistingAisle) <= ASCII(@StartingAisle)
    BEGIN
        DECLARE @RowCount bigint;
        SET @RowCount = (SELECT MAX(Rows) FROM Locations WHERE SupermarketId = @supermarketId AND Aisles = @ExistingAisle);

        WHILE @RowCount < @NewRowCount
        BEGIN
            INSERT INTO Locations (SupermarketId, Aisles, Rows)
            VALUES (@supermarketId, @ExistingAisle, @RowCount + 1);

            SET @RowCount = @RowCount + 1;
        END;

        SET @ExistingAisle = CHAR(ASCII(@ExistingAisle) + 1);
    END;
END;

问题排查与修复 #

核心问题分析 #

1. 重复调用存储过程：当同时需要增加行数和过道数时，SP_UPDATE_LOCATIONS会先后两次调用SP_ADD_LOCATIONS，第一次调用修改了Locations表数据后，第二次调用会基于已改变的状态错误生成额外过道。
2. 起始过道计算逻辑依赖实时表数据：SP_ADD_LOCATIONS通过查询当前表中最大过道来计算新过道起始值，重复调用时会因为前一次插入的数据导致起始值计算错误。
3. 循环范围错误：处理现有过道新增行时，循环包含了刚插入的新过道，导致不必要的重复操作。

修复方案 #

1. 修改SP_UPDATE_LOCATIONS：合并新增逻辑，避免重复调用

CREATE OR ALTER PROCEDURE [dbo].[SP_UPDATE_LOCATIONS]
    @supermarketId BIGINT,
    @noAisles BIGINT,
    @noRows BIGINT
AS
BEGIN
    DECLARE @oldNoAisles BIGINT;
    DECLARE @oldNoRows BIGINT;

    SELECT 
        @oldNoAisles = WAR.Aisles,
        @oldNoRows = WAR.Rows
    FROM SUPERMARKTETS WAR
    WHERE WAR.SupermarketId = @supermarketId

    -- 处理行数减少：删除所有超过新行数的行
    IF (@oldNoRows > @noRows)
    BEGIN
        DELETE FROM AR_WAREHOUSE_LOCATIONS
        WHERE SupermarketId = @supermarketId
        AND WAL_N_ROW > @noRows;
    END

    -- 处理过道数减少：删除所有超过新过道数的过道
    IF (@oldNoAisles > @noAisles)
    BEGIN
        DECLARE @currentAisleToDelete VARCHAR(50) = CHAR(@noAisles + 65);
        WHILE ASCII(@currentAisleToDelete) <= ASCII(CHAR(@oldNoAisles + 64))
        BEGIN
            DELETE FROM AR_WAREHOUSE_LOCATIONS
            WHERE SupermarketId = @supermarketId
            AND WAL_CH_AISLE = @currentAisleToDelete;
            SET @currentAisleToDelete = CHAR(ASCII(@currentAisleToDelete) + 1);
        END
    END

    -- 仅当需要新增数据时调用一次SP_ADD_LOCATIONS
    IF (@oldNoRows < @noRows OR @oldNoAisles < @noAisles)
    BEGIN
        EXECUTE [dbo].[SP_ADD_LOCATIONS] @supermarketId, @oldNoAisles, @noAisles, @oldNoRows, @noRows;
    END

    UPDATE SUPERMARKTETS
    SET Aisles = @noAisles,
        Rows = @noRows
    WHERE SupermarketId = @supermarketId;
END;

2. 修改SP_ADD_LOCATIONS：基于新旧参数计算新增量，不依赖实时表数据

CREATE OR ALTER PROCEDURE [dbo].[SP_ADD_LOCATIONS]
    @supermarketId bigint,
    @oldAisleCount bigint,
    @newAisleCount bigint,
    @oldRowCount bigint,
    @newRowCount bigint
AS
BEGIN
    -- 新增过道：从旧过道数+1到新过道数，每个过道插入1到新行数
    DECLARE @currentNewAisleIndex bigint = @oldAisleCount + 1;
    WHILE @currentNewAisleIndex <= @newAisleCount
    BEGIN
        DECLARE @newAisle VARCHAR(50) = CHAR(@currentNewAisleIndex + 64);
        DECLARE @rowNum bigint = 1;
        WHILE @rowNum <= @newRowCount
        BEGIN
            INSERT INTO Locations (SupermarketId, Aisles, Rows)
            VALUES (@supermarketId, @newAisle, @rowNum);
            SET @rowNum = @rowNum + 1;
        END
        SET @currentNewAisleIndex = @currentNewAisleIndex + 1;
    END

    -- 为现有过道新增行：从旧行数+1到新行数
    DECLARE @currentExistingAisleIndex bigint = 1;
    WHILE @currentExistingAisleIndex <= @oldAisleCount
    BEGIN
        DECLARE @existingAisle VARCHAR(50) = CHAR(@currentExistingAisleIndex + 64);
        DECLARE @rowNum bigint = @oldRowCount + 1;
        WHILE @rowNum <= @newRowCount
        BEGIN
            INSERT INTO Locations (SupermarketId, Aisles, Rows)
            VALUES (@supermarketId, @existingAisle, @rowNum);
            SET @rowNum = @rowNum + 1;
        END
        SET @currentExistingAisleIndex = @currentExistingAisleIndex + 1;
    END
END;

修复说明 #

• SP_UPDATE_LOCATIONS现在仅在需要新增数据时调用一次SP_ADD_LOCATIONS，避免重复操作导致的异常。
• SP_ADD_LOCATIONS不再依赖Locations表的实时状态，直接通过传入的新旧参数计算新增的过道和行，逻辑更稳定。
• 修正了删除行数和过道的逻辑，确保删除所有超出新数量的行/过道，而非仅删除最后一个。

内容的提问来源于stack exchange，提问作者Chin