SQL存储过程测试（4）——创建T-SQL测试套件

问题

 

      如何创建一个T-SQL测试套件用于测试SQL存储过程。

 

设计

 

      首无，通过插入大量测试平台数据准备好一个包含待测存储过程的底层数据库。接下来，使用一个SQL游标(cursor)遍历这个测试用例数据表。针对每个测试用例，调用待测存储过程并且取得它的返回值，把实际返回值与期望值进行比较，从而判定测试结果是通过与否，然后显示或保存测试结果。

 

方案

 

——testAuto.sql

 

——为dbEmployees填充数据

truncate table dbEmployees.dbo.tblEmployees

 

insert into dbEmployees.dbo.tblEmployees values('e11','Adams','15/10/2009')

insert into dbEmployees.dbo.tblEmployees values('e22','Baker','15/10/2009')

insert into dbEmployees.dbo.tblEmployees values('e33','Young','15/10/2009')

insert into dbEmployees.dbo.tblEmployees values('e44','Zetta','15/10/2009')

——此处插入更多数据

 

declare tCursor cursor fast_forward

    for select caseID,input,expected

    from dbTestCasesAndResults.dbo.tblTestCases

    order by caseID

 

declare @caseID char(4),@input char(3),@expected int

declare @actual int,@whenRun datetime

declare @resultLine varchar(50)

 

set @whenRun = getdate()

 

open tCursor

fetch next

    from tCursor

    into @caseID,@input,@expected

 

while @@fetch_status = 0

    begin

    exec @actual = dbEmployees.dbo.usp_StatusCode @input

 

    if (@actual = @expected)

        begin

            set @resultLine = @caseID + ': Pass'

            print @resultLine

        end

    else

        begin

            set @resultLine = @caseID + ': FAIL'

            print @resultLine

        end

    fetch next

        from tCursor

        into @caseID,@input,@expected

end

 

close tCursor

deallocate tCursor

 

——end script

 

      如果待测存储过程依赖于外部数据（大多数情况下都是这样的），则必须把大量的测试平台数据填充到底层的数据库表。在SQL测试环境下，通常有两个数据库：开发数据库供开发者在编写代码时使用，测试数据库供测试者在测试时使用。因为对存储过程进行测试经常会改变包含存储过程的这个数据库（因为存储过程经常对数据进行插入、更新、删除），因此不希望在开发数据襄轴上运行测试程序。因此，应该复制一份开发数据库的拷贝，并且使用这个拷贝来做测试。这样，开发数据库的表里面保存的就是供开发者使用的数据。这些数据对于在开发待测系统（SUT）的过程中进行基本的验证测试(verification testing)是非常有必要的。但是，这些数据对于进行全面而严格的测试来说，通常是不够丰富的。

 

      有好几种方法可以遍历一个存储测试用例数据的数据库表，使用SQL游标是其他简单有效的一种。SQL游标被设计用来处理单个数据而不是像其他SQL操作（比如SELECT和INSERT）那样处理行集（rowset）。首先应该声明一个指向保存测试用例数据的SQL表的游标：

 

declare tCursor cursor fast_forward

    for select caseID,input,expected

    from dbTestCasesAndResults.dbo.tblTestCases

    order by caseID

 

      注意：与其他SQL变量不同，游标变量的名字前面并没有@字符。可供声明的游标有好几种。FAST_FORWARD最适合用来读取测试用例数据。其他游标类型包括FORWARD_ONLY,READ_ONLY,以及OPTIMISTIC。FAST_FORWARD实际上就是FORWARD_ONLY加上READ_ONLY的别名。

 

      在使用游标之前，必须先打开游标。然后，如果想要遍历整个数据库表，则必须通过fetch next语句预读取数据库表的第一行：

 

open tCursor

fetch next

    from tCursor

    into @caseID,@input,@expected

 

      对第一行进行预读取是为了对下面的循环进行控制，使用变量@@fetch_status来控制用于读取的这个循环，这个变量表示最近一次fetch操作的状态。如果fetch操作成功，则@@fetch_status值为0 。如果值为-1或-2则意味fetch失败。因此可以像下面这样每次一行地遍历整个数据库表：

 

while @@fetch_status = 0

begin

 

——运行测试用例

 

    fetch next

        from tCursor

        into @caseID,@input,@expected

end

 

      在主循环内部，需要调用待测存储过程，并且把测试用例输入数据传给它。接下来取回返回值并打印pass或fail消息：

 

    exec @actual = dbEmployees.dbo.usp_StatusCode @input

 

    if (@actual = @expected)

        begin

            set @resultLine = @caseID + ': Pass'

            print @resultLine

        end

    else

        begin

            set @resultLine = @caseID + ': FAIL'

            print @resultLine

        end

 

      使用完一个SQL游标之后，必须要关闭这个游标并且调用deallocate命令把它作为一个资源进行释放：

 

close tCursor

deallocate tCursor

 

      如果忘了对游标进行deallocate操作，下一次执行测试套件脚本需要声明游标的时候，脚本就会报错。

 

注解

 

      除了打印pass/fail信息之外，还可以把测试结果插入到SQL数据表里：

 

——declare @actual int,@whenRun datetime

——set @whenRun = getdate()

 

while @@fetch_status = 0

begin

    exec @actual = dbEmployees.dbo.usp_StatusCode @input

 

    if (@actual = @expected)

        insert into dbTestCasesAndResults.dbo.tblResults values (@caseID,'Pass',@whenRun)   

    else

        insert into dbTestCasesAndResults.dbo.tblResults values (@caseID,'FAIL',@whenRun)   

    

    fetch next

        from tCursor

        into @caseID,@input,@expected   

end

 

    如果不采用通过待测存储过程以及硬编码的语句来生成底层数据库，也可以使用BULK INSERT语句：

 

——为dbEmployees填充数据

 

truncate table dbEmployees.dbo.tblEmployees

 

bulk insert dbEmployees.dbo.tblEmployees

    from 'C:/somewhere/richTestbedData.dat'

    with (formatfile = 'C:/somewhere/richTestbedDate.fmt')   

 

      使用这种方法的好处是，可以让测试套件模块化程度更高并且更为灵活，但是它也有不好的一面：它在这个原本已经有很多对象的测试套件系统里又引入了一个文件，从而增加了复杂性。