LoginSignup
2
0

More than 3 years have passed since last update.

SQLServer ストアドプロシージャで回帰テスト

Last updated at Posted at 2019-08-19

はじめに

ストアドプロシージャで回帰テストを実施したので書き留めたいと思います。

実施するテスト内容

カーソルによる繰り返しの更新処理から、カーソルを使用しない更新への処理変更に伴う回帰テスト

修正前(カーソル使用)
DECLARE
    @column1    int,
    @column2    int,
    @column3    int,
    @Error      int

     SET @Error = 0

BEGIN
    BEGIN TRANSACTION

    DECLARE
        val_cursor CURSOR FAST_FORWARD FOR

        SELECT tableA.column1
            ,tableA.column2
            ,tableA.column3
        FROM tableA 
        JOIN tableB ON tableA.column1 = tableB.column1
        WHERE IsNull(tableA.column1, - 999999) <> IsNull(tableB.column1, - 999999)
            OR IsNull(tableA.column2, - 999999) <> IsNull(tableB.column2, - 999999)
            OR IsNull(tableA.column3, 'NULL') <> IsNull(tableB.column3, 'NULL')
        ORDER BY tableA.column1

    OPEN val_cursor
    FETCH NEXT FROM val_cursor INTO
        @column1,
        @column2,
        @column3

    WHILE @@fetch_status = 0
    BEGIN

        UPDATE tableB
        SET column2 = @column2
            ,column3 = @column3
        WHERE column1 = @column1

        IF @@ERROR <> 0
            SET @Error = 1

        FETCH NEXT FROM val_cursor INTO
            @column1,
            @column2,
            @column3
    END

    CLOSE val_cursor
    DEALLOCATE val_cursor

    ROLLBACK TRANSACTION
END
修正後(カーソル未使用)
BEGIN
    BEGIN TRANSACTION
        UPDATE tableB
        SET column2 = tableA.column2
            ,column3 = tableA.column3
        FROM tableA 
        JOIN tableB ON tableA.column1 = tableB.column1
        WHERE IsNull(tableA.column2, - 999999) <> IsNull(tableB.column2, - 999999)
            OR IsNull(tableA.column3, 'NULL') <> IsNull(tableB.column3, 'NULL')
    ROLLBACK TRANSACTION
END

テスト実施の流れ

①データを退避する

①更新テーブルを退避する
SELECT *
INTO result1
FROM tableB

②ストアドプロシージャの修正

修正前の更新結果と、修正後の更新結果を退避する。

②ストアドプロシージャの修正
DECLARE
    @column1    int,
    @column2    int,
    @column3    int,
    @Error      int

     SET @Error = 0

BEGIN
    BEGIN TRANSACTION

    DECLARE
        val_cursor CURSOR FAST_FORWARD FOR

        SELECT tableA.column1
            ,tableA.column2
            ,tableA.column3
        FROM tableA 
        JOIN tableB ON tableA.column1 = tableB.column1
        WHERE IsNull(tableA.column1, - 999999) <> IsNull(tableB.column1, - 999999)
            OR IsNull(tableA.column2, - 999999) <> IsNull(tableB.column2, - 999999)
            OR IsNull(tableA.column3, 'NULL') <> IsNull(tableB.column3, 'NULL')
        ORDER BY tableA.column1

    OPEN val_cursor
    FETCH NEXT FROM val_cursor INTO
        @column1,
        @column2,
        @column3

    WHILE @@fetch_status = 0
    BEGIN

        --修正前の更新結果をresult1 に退避
        UPDATE result1
        SET column2 = @column2
            ,column3 = @column3
        WHERE column1 = @column1

        IF @@ERROR <> 0
            SET @Error = 1

        FETCH NEXT FROM val_cursor INTO
            @column1,
            @column2,
            @column3
    END

    CLOSE val_cursor
    DEALLOCATE val_cursor

    --修正版のクエリ
    UPDATE tableB
    SET column2 = tableA.column2
        ,column3 = tableA.column3
    FROM tableA 
    JOIN tableB ON tableA.column1 = tableB.column1
    WHERE IsNull(tableA.column2, - 999999) <> IsNull(tableB.column2, - 999999)
        OR IsNull(tableA.column3, 'NULL') <> IsNull(tableB.column3, 'NULL')

    --修正版の更新結果をresult2に退避
    SELECT *
    INTO result2
    FROM tableB

    ROLLBACK TRANSACTION
END

③実行したテーブルの差分チェック

差分0ならOK
SELECT * FROM result1
EXCEPT
SELECT * FROM result2

SELECT * FROM result2
EXCEPT
SELECT * FROM result1
2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
0