sql 存储过程并发测试_SQL单元测试模拟存储过程

最新推荐文章于 2024-05-25 13:37:05 发布

culuo4781

最新推荐文章于 2024-05-25 13:37:05 发布

阅读量923

点赞数

文章标签：单元测试数据库 python java 编程语言

原文链接：https://www.sqlshack.com/sql-unit-testing-mocking-stored-procedures/

版权

sql 存储过程并发测试

In this SQL Unit testing article, we will continue our journey with SQL unit testing. In the previous articles of this series (see the TOC at the bottom), we mentioned about SQL unit testing essentials and designing approaches as well and then we reinforce these notions with various tSQLt framework practical examples. In this article, we will continue to learn how to mock stored procedures in the tSQLt framework and we will provide it with two examples.

在这篇SQL单元测试文章中，我们将继续进行SQL单元测试。在本系列的前几篇文章中（请参见底部的TOC ），我们还提到了SQL单元测试的要点和设计方法，然后通过各种tSQLt框架实际示例来强化这些概念。在本文中，我们将继续学习如何在tSQLt框架中模拟存储过程，并将提供两个示例。

Note: I want to suggest that if you don’t have any knowledge or idea about SQL unit testing please read the SQL unit testing with the tSQLt framework for beginners article and also you can take glance at other articles for more details (see the TOC at the bottom) about SQL unit testing and tSQLt framework.

注意 ：我想建议，如果您对SQL单元测试没有任何知识或想法，请阅读tSQLt框架初学者文章中的SQL单元测试，并且您也可以浏览其他文章以了解更多详细信息 （ 请参阅 TOC） 在底部 ） 关于SQL单元测试和tSQLt框架。

In some cases, a stored procedure, which we want to test, includes and invokes another stored procedure(s). The crucial point here is that, the stored procedure to be tested might depend on the invoked one. Also, the result of the stored procedure which is invoked can directly influence the unit test result. There is no doubt in that, if we try to write a unit test without eliminating the dependency issue, it might create a more complicated and fragile SQL unit test. It would be really tedious to struggle this type of SQL unit test. On the other hand, unit test maintenance and management operations will require more effort than usual. Certainly, these type of unit test designs are located opposed side to the unit test design concept.

在某些情况下，我们要测试的存储过程包括并调用另一个存储过程。这里的关键点是，要测试的存储过程可能取决于调用的存储过程。同样，被调用的存储过程的结果可以直接影响单元测试结果。毫无疑问，如果我们尝试编写一个单元测试而不消除依赖关系问题，那么它可能会创建一个更加复杂和脆弱SQL单元测试。挣扎这种类型SQL单元测试真的很乏味。另一方面，单元测试的维护和管理操作将比平时花费更多的精力。当然，这些类型的单元测试设计位于单元测试设计概念的相对侧。

Briefly, if a stored procedure, to be tested, invokes another one and the invoked stored procedure result has an influence upon the unit test, we must isolate it in the unit test. For this reason, we will learn how to eliminate these type of dependencies in the tSQLt framework. Now, let’s learn how to deal with this issue in tSQLt framework;

简而言之，如果要测试的存储过程调用了另一个存储过程，并且所调用的存储过程的结果对单元测试有影响，则必须在单元测试中将其隔离。因此，我们将学习如何在tSQLt框架中消除这些类型的依赖关系。现在，让我们学习如何在tSQLt框架中处理此问题。

SpyProcedure概述 (Overview of SpyProcedure )

tSQLt framework provides us a fantastic method which is called as SpyProcedure. SpyProcedure method creates a mock or in other words spy ((Test spy allow us to store methods execution for the later verification) procedure. During the SQL unit test mock procedure sit in for the original one and imitate it. At the same time, SpyProcedure stores the parameter values in a special table.

tSQLt框架为我们提供了一种奇妙的方法，称为SpyProcedure 。 SpyProcedure方法创建一个模拟或换句话说，就是间谍（（ 测试间谍 允许我们存储方法的执行以用于以后的验证 ）过程。在SQL单元测试模拟过程中，原始过程被模拟并模仿它。同时， SpyProcedure将参数值存储在特殊表中。

Note: In the following test scenario, we are supposing that the Database Mail configuration is disabled. You can check it with the following query, but do not disable it in the production environment without being sure.

注意： 在以下测试方案中，我们假设数据库邮件配置被禁用。 您可以使用以下查询来检查它，但请确保在生产环境中不要禁用它。

exec sp_configure 'show advanced options',1
Go
Reconfigure
go
sp_configure 'Database Mail XPs'

Let’s try to explain the usage of the SpyProcedure with an example. Imagine that we want to test a stored procedure and this store procedure inserts client orders to a table. If the insert process succeeds, it sends an email to the clients through the sp_send_dbmail system stored procedure. However, in the development environment, Database Mail is not configured due to security considerations. Under these circumstances, if we want to write a SQL unit test of the stored procedure, we need to eliminate the dependency of the database mail configuration. Otherwise, it returns an error regarding to database mail configuration. In this scenario, the stored procedures will look like the following;

让我们尝试通过一个示例来解释SpyProcedure的用法。假设我们要测试存储过程，并且此存储过程将客户订单插入到表中。如果插入过程成功，它将通过sp_send_dbmail向客户端发送电子邮件。系统存储过程。但是，在开发环境中，出于安全考虑，未配置数据库邮件。在这种情况下，如果要编写存储过程SQL单元测试，则需要消除数据库邮件配置的依赖性。否则，它将返回有关数据库邮件配置的错误。在这种情况下，存储过程将如下所示；

DROP TABLE IF EXISTS Orders;
GO
CREATE TABLE Orders
(ClientName VARCHAR(50), 
 ClientMail VARCHAR(50), 
 OrderId    UNIQUEIDENTIFIER DEFAULT(NEWID())
);
GO
CREATE OR ALTER PROCEDURE SendOrderMail
(@OrderMail AS VARCHAR(50), 
 @OrderId AS   UNIQUEIDENTIFIER
)
AS
     DECLARE @MailSubject AS VARCHAR(300);
     DECLARE @MailBody AS VARCHAR(300);
     SET @MailSubject = CONCAT(CAST(@OrderId AS VARCHAR(50)), '  order information');
     SET @MailBody = CONCAT(CAST(@OrderId AS VARCHAR(50)), '  order is taken');
     EXEC msdb.dbo.sp_send_dbmail 
          @profile_name = 'Mymail', 
          @recipients = @OrderMail, 
          @subject = @MailSubject, 
          @body = @MailBody;
GO
CREATE OR ALTER PROCEDURE InsertOrder
(@CName AS   VARCHAR(50), 
 @CMail   VARCHAR(50), 
 @OrderId AS UNIQUEIDENTIFIER
)
AS INSERT INTO Orders
   (ClientName, 
    ClientMail, 
    OrderId
   )
   VALUES
   (@CName, 
    @CMail, 
    @OrderId
   );
     EXEC SendOrderMail 
          @CMail, 
          @OrderId

As you can see, InsertOrder stored procedure performs an insert operation and then it executes the SendOrderMail. Stored procedure uses sp_send_dbmail system procedure in order to send an email. Under these circumstances, if we want to test InsertOrder functionality, the best method is to eliminate SendOrderMail procedure dependency.

如您所见， InsertOrder存储过程执行插入操作，然后执行SendOrderMail。 存储过程使用sp_send_dbmail系统过程来发送电子邮件。在这种情况下，如果我们要测试InsertOrder功能，最好的方法是消除SendOrderMail过程的依赖性。

SpyProcedure用法SQL单元测试 (SpyProcedure usage the SQL unit testing)

SpyProcedure takes two parameters:

SpyProcedure具有两个参数：

tSQLt.SpyProcedure [@ProcedureName = ] 'procedure name'
                [, [@CommandToExecute = ] 'command' ]

@ProcedureName parameter specifies the procedure name which we want to mock.

@ProcedureName参数指定我们要模拟的过程名称。

@CommandToExecute is an optional parameter which helps us to execute a command when the procedure is invoked.

@CommandToExecute是一个可选参数，可帮助我们在调用过程时执行命令。

At first, we will write the unit test and then we will handle it line by line.

首先，我们将编写单元测试，然后逐行处理它。

EXECUTE tSQLt.NewTestClass 
        'TestOrderProc';
GO
CREATE OR ALTER PROCEDURE TestOrderProc.[test InsertOrder stored procedure insert Order table]
AS DROP TABLE IF EXISTS actual;
     DROP TABLE IF EXISTS expected;
     EXEC tSQLt.FakeTable 
          'Orders';
     SELECT middle 0 *
     INTO actual
     FROM Orders;
     SELECT middle 0 *
     INTO expected
     FROM Orders;
     EXEC tSQLt.SpyProcedure 
          'SendOrderMail';
     INSERT INTO expected
     (ClientName, 
      ClientMail, 
      OrderId
     )
     VALUES
     ('Popeye', 
      'spinach@spinach.com', 
      '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3'
     );
     EXECUTE InsertOrder 
             'Popeye', 
             'spinach@spinach.com', 
             '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3';
     INSERT INTO actual
            SELECT *
            FROM Orders;
     EXECUTE tSQLt.AssertEqualsTable 
             'expected', 
             'actual';
GO
EXECUTE tsqlt.Run 
        'TestOrderProc.[test InsertOrder stored procedure insert Order table]';

In the following table, we can find out all the SQL unit test query parts detailed explanations. These explanations are very important to understand logic of the SpyProcedure.

在下表中，我们可以找到所有SQL单元测试查询部分的详细说明。这些说明对于理解SpyProcedure的逻辑非常重要。

`EXECUTE tSQLt.NewTestClass 'TestOrderProc'`	We create a new test class
	We give an understandable name to test method
`DROP TABLE IF EXISTS actual DROP TABLE IF EXISTS expected EXEC tSQLt.FakeTable 'Orders' SELECT middle 0 * INTO actual FROM Orders SELECT middle 0 * INTO expected FROM Orders`	In this part of the query, we create the fake Orders table and then we derive the actual and expected table based on our fake Orders table
	We mock the SendOrderMail stored procedure so that we avoid the dependency of it
`INSERT INTO expected (ClientName,ClientMail, OrderId) VALUES ('Popeye','spinach@spinach.com','1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3') EXECUTE InsertOrder 'Popeye','spinach@spinach.com','1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3' SELECT * FROM SendOrderMail_SpyProcedureLog INSERT INTO actual SELECT * FROM Orders`	We populate the actual and expected tables. The reason for doing so is that we want to test insert functionality of the InsertOrder stored procedure
	This is the final step of the unit test and in this step, we compare the expected and actual tables. In this way, we learn if the insert functionality of the stored procedure is working correctly

`EXECUTE tSQLt.NewTestClass 'TestOrderProc'`	我们创建一个新的测试类
	我们给测试方法起一个易于理解的名称
`DROP TABLE IF EXISTS actual DROP TABLE IF EXISTS expected EXEC tSQLt.FakeTable 'Orders' SELECT middle 0 * INTO actual FROM Orders SELECT middle 0 * INTO expected FROM Orders`	在查询的这一部分中，我们创建伪造的Orders表，然后基于伪造的Orders表导出实际表和期望表
	我们模拟 SendOrderMail存储过程，以便避免依赖它
`INSERT INTO expected (ClientName,ClientMail, OrderId) VALUES ('Popeye','spinach@spinach.com','1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3') EXECUTE InsertOrder 'Popeye','spinach@spinach.com','1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3' SELECT * FROM SendOrderMail_SpyProcedureLog INSERT INTO actual SELECT * FROM Orders`	我们填充实际表和预期表。这样做的原因是我们要测试InsertOrder的插入功能存储过程
	这是单元测试的最后一步，在这一步中，我们将预期表与实际表进行比较。通过这种方式，我们可以了解存储过程的插入功能是否正常运行

After all these comprehensive explanations, I want to mark one point; if the mocked stored procedure invoked the tSQLt framework. Then, it creates a special table and it stores the called parameters into this table. The table name is created by adding “_SpyProcedureLog” prefix to the end of the mocked stored procedure. This name is based like this SendOrderMail_SpyProcedureLog for the previous example. Now, we will prove it in the following example;

在所有这些全面的解释之后，我想指出一点。如果模拟的存储过程调用了tSQLt框架。然后，它创建一个特殊表，并将调用的参数存储到该表中。通过在模拟的存储过程的末尾添加“ _ SpyProcedureLog ”前缀来创建表名。对于上一示例，此名称基于此SendOrderMail_SpyProcedureLog 。现在，我们将在下面的示例中对此进行证明。

EXECUTE tSQLt.NewTestClass 
        'TestOrderProc';
GO
CREATE OR ALTER PROCEDURE TestOrderProc.[test InsertOrder stored procedure insert Order table]
AS DROP TABLE IF EXISTS actual;
     DROP TABLE IF EXISTS expected;
     EXEC tSQLt.FakeTable 
          'Orders';
     SELECT middle 0 *
     INTO actual
     FROM Orders;
     SELECT middle 0 *
     INTO expected
     FROM Orders;
     EXEC tSQLt.SpyProcedure 
          'SendOrderMail';
     INSERT INTO expected
     (ClientName, 
      ClientMail, 
      OrderId
     )
     VALUES
     ('Popeye', 
      'spinach@spinach.com', 
      '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3'
     );
     EXECUTE InsertOrder 
             'Popeye', 
             'spinach@spinach.com', 
             '1C3903F7-ADC4-45CE-9810-4EB8B3C00DE3';
     SELECT *
     FROM SendOrderMail_SpyProcedureLog;
     INSERT INTO actual
            SELECT *
            FROM Orders;
     EXECUTE tSQLt.AssertEqualsTable 
             'expected', 
             'actual';
GO
EXECUTE tsqlt.Run 
        'TestOrderProc.[test InsertOrder stored procedure insert Order table]';

SQL Unit testing results with tSQLt using SpyProcedureLog

As you can see, the OrderMail and OrderId parameter values stored to the SendOrderMail_SpyProcedureLog table. According to your SQL unit test case, you can use this table. The purpose of this method of use is to illustrate only the functionality of the SpyProcedureLog table.

如您所见， OrderMail和OrderId参数值存储到SendOrderMail _ SpyProcedureLog表中。根据您SQL单元测试用例，您可以使用此表。这种使用方法的目的是仅说明SpyProcedureLog表的功能。

SpyProcedure在SQL单元测试中的高级用法 (SpyProcedure advanced usage in the SQL unit testing)

In the SpyProcedure usage in the SQL unit testing section, we mentioned about an optional parameter whose name is @CommandToExecute. Now we will explore this parameter’s usage in the tSQLt framework. Suppose that, we have a stored procedure and it generates an alert message when the tempdb size over a value. Our aim is to test the accuracy of the alert message in the unit test. However, tempdb size calculates by another procedure so we should manipulate the return value of this stored procedure. At first, we will create the stored procedures with the following queries:

在SQL单元测试部分的SpyProcedure用法中 ，我们提到了一个名为@CommandToExecute的可选参数。现在，我们将在tSQLt 框架中探索该参数的用法。假设我们有一个存储过程，并且当tempdb大小超过某个值时，它会生成一条警报消息。我们的目的是在单元测试中测试警报消息的准确性。但是，tempdb的大小是由另一个过程计算的，因此我们应该操纵此存储过程的返回值。首先，我们将使用以下查询创建存储过程：

CREATE OR ALTER PROCEDURE CalcTempSize(@Size AS BIGINT OUT)
AS SELECT @Size = SUM(size) / 128
   FROM tempdb.sys.database_files;
     RETURN @Size;
GO
CREATE OR ALTER PROCEDURE TempSizeAlert(@AlertMessage AS VARCHAR(500) OUT)
     AS
          DECLARE @TempDbSize AS BIGINT;
  
          EXECUTE CalcTempSize 
                  @Size = @TempDbSize OUT;
          IF @TempDbSize > 512
              BEGIN
                  SET @AlertMessage = CONCAT('tempdb size is ', @TempDbSize);
          END;

As you can see in the above, CalcTempSize calculates the tempdb file size and TempSizeAlert creates an alert message. The crucial point is that, unless CalcTempSize does not return a value over 512, the TempSizeAlert stored procedure does not create a message. Let’s create the SQL unit test and execute it.

如您在上面看到的， CalcTempSize计算tempdb文件的大小， TempSizeAlert创建警报消息。关键点在于，除非CalcTempSize返回的值不超过512， 否则TempSizeAlert存储过程不会创建消息。让我们创建SQL单元测试并执行它。

EXECUTE tSQLt.NewTestClass 
        'TempdbAlert';
GO
CREATE OR ALTER PROCEDURE TempdbAlert.[Test TempSizeAlert_StoredProcedure Alert Message Val_555]
AS
     DECLARE @expectedmessage AS VARCHAR(500)= 'tempdb size is 555';
     DECLARE @actualmessage AS VARCHAR(500);
     EXECUTE tSQLt.SpyProcedure 
             'dbo.CalcTempSize', 
             'SET @Size=555';
     EXECUTE TempSizeAlert 
             @AlertMessage = @actualmessage OUT;
     EXECUTE tSQLt.AssertEquals 
             @expectedmessage, 
             @actualmessage;
GO
EXECUTE tsqlt.Run 
        'TempdbAlert.[Test TempSizeAlert_StoredProcedure Alert Message Val_555]';

Now, we will handle the previous SQL unit test line by line;

现在，我们将逐行处理以前SQL单元测试；

`EXECUTE tSQLt.NewTestClass 'TempdbAlert'`	We create a new test class
	We declare the expected and actual values
`EXECUTE tSQLt.SpyProcedure 'dbo.CalcTempSize' , 'SET @Size=555'`	We manipulate the CalcTempSize output value so that we obtain the desired value
	We assign the stored procedure return value to actual value
`EXECUTE tSQLt.AssertEquals @expectedmessage,@actualmessage`	We compare the expected and actual message value so that the test result occurs

	我们创建一个新的测试类
`DECLARE @expectedmessage AS VARCHAR(500) DECLARE @actualmessage AS VARCHAR(500) SET @expectedmessage= 'tempdb size is 555'`	我们声明期望值和实际值
	我们操纵CalcTempSize输出值，以便获得所需的值
`EXECUTE TempSizeAlert @AlertMessage = @actualmessage OUT`	我们将存储过程的返回值分配给实际值
	我们比较预期和实际消息值，以便进行测试结果

As a result, we completed two different examples to test the usage of the SpyProcedure. In the first one, we isolated the dependency and in the second one, we manipulated the output of the mocked stored procedure. According to the SQL unit test cases, you can determine usage method of the SpyProcedure.

结果，我们完成了两个不同的示例来测试SpyProcedure的用法。在第一个实例中，我们隔离了依赖关系，在第二个实例中，我们操纵了模拟存储过程的输出。根据SQL单元测试用例，可以确定SpyProcedure的使用方法。

结论 (Conclusion)

The idea behind of the mocking object is very basic, the database objects should be tested without their dependencies, so it means that the dependencies must be eliminated in the unit tests. The tSQLt framework allows us various method to achieve this idea us, however in this article particularly we learned how to mock stored procedure and usage of the SpyProcedure method. SpyProcedure provides a huge advantage and adds flexibility for our unit tests. Above all, we can create isolated, induvial and independent SQL unit tests. Because of features and capability like this, IMHO, tSQLt framework is the best utility to create SQL unit tests.

模拟对象背后的思想是非常基本的，应该测试数据库对象而没有它们的依赖关系，因此这意味着必须在单元测试中消除依赖关系。 tSQLt框架允许我们使用各种方法来实现这一想法，但是，特别是在本文中，我们学习了如何模拟存储过程和SpyProcedure方法的用法。 SpyProcedure提供了巨大的优势，并为我们的单元测试增加了灵活性。最重要的是，我们可以创建隔离的，行业的和独立SQL单元测试。由于类似这样的特性和功能，恕我直言， tSQLt框架是创建SQL单元测试的最佳工具。

目录 (Table of contents)

SQL Unit Testing with SSDT (SQL Server Data Tools)

SQL unit testing with the tSQLt framework for beginners

SQL Unit testing with the tSQLt framework and SQL Server Database Project integration

SQL unit testing best practices

How to use fake tables in SQL unit testing?

How to use fake functions with SQL unit testing?

SQL Unit Testing Mocking Stored Procedures

SQL Unit Testing: Working with exceptions

使用SSDT进行SQL单元测试（SQL Server数据工具）

使用tSQLt框架SQL单元测试面向初学者

使用tSQLt框架和SQL Server Database Project集成进行SQL单元测试

SQL单元测试模拟存储过程

翻译自: https://www.sqlshack.com/sql-unit-testing-mocking-stored-procedures/

sql 存储过程并发测试

culuo4781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql 存储过程并发测试_SQL单元测试模拟存储过程

sql 存储过程并发测试 In this SQL Unit testing article, we will continue our journey with SQL unit testing. In the previous articles of this series (see the TOC at the bottom), we mentioned about SQL unit ...
复制链接

扫一扫