@sql 单元测试_SQL单元测试：使用异常

最新推荐文章于 2023-08-18 18:36:47 发布

culuo4781

最新推荐文章于 2023-08-18 18:36:47 发布

阅读量584

点赞数

文章标签：单元测试数据库 python java 人工智能

原文链接：https://www.sqlshack.com/sql-unit-testing-working-with-exceptions/

版权

@sql 单元测试

With this article, we will complete our journey with SQL Unit Testing. But first, let’s remember the main theme of the SQL Unit Testing again briefly. With the help of the SQL Unit Testing, we can detect and find out the flaws and bugs in database projects. This reduces the possible loss of time and money and reputation during the use of the product. However, it should be kept in mind that no matter how many tests are carried out, there will still be some bugs in the project, but SQL unit tests provide a greater degree of confidence. The fact that there are no bugs or flaws in the application at that time does not mean that the application is perfect. We cannot test all combinations and probabilities of the database objects. That is why in real life, considering certain parameters (risk, priority, etc.) we must ensure that a “reasonably sufficient” test is done.

通过本文，我们将完成SQL单元测试的整个过程。但是首先，让我们再次简短地记住SQL单元测试的主题。借助SQL单元测试，我们可以检测并找出数据库项目中的缺陷和错误。这样可以减少产品使用过程中可能造成的时间，金钱和声誉损失。但是，应该记住，无论执行多少测试，项目中仍然会有一些错误，但是SQL单元测试提供了更大的信心。当时应用程序中没有错误或缺陷的事实并不意味着该应用程序是完美的。我们无法测试数据库对象的所有组合和概率。这就是为什么在现实生活中，考虑某些参数（风险，优先级等），我们必须确保进行“足够合理”的测试。

Note: I suggested that you can check the previous articles (see the TOC at the bottom) It will be helpful to learn more details about SQL unit testing concept and tSQLt framework. Particularly, SQL unit testing with the tSQLt framework for beginners article can be very helpful to beginners. In this SQL unit testing article, we will go through how to test the behavior of the exceptions in the SQL unit tests. We can use two different procedure to test the behavior of the exceptions in the SQL unit tests.
注意：我建议您可以查看以前的文章（请参阅底部的TOC ）。这有助于了解有关SQL单元测试概念和tSQLt框架的更多详细信息。 特别是，针对初学者使用tSQLt框架进行SQL单元测试可能对初学者很有帮助。 在此SQL单元测试文章中，我们将介绍如何在SQL单元测试中测试异常的行为。 我们可以使用两种不同的过程来测试SQL单元测试中异常的行为。

In some cases, the result of the unit tests might be changing due to exceptions which are returned. Normally, we expect an exception in the test and if the exception occurs, the test must be successful. This type of unit testing called negative testing because we are testing unexpected and extraordinary conditions. In the tSQLt framework we can make it with help of the tSQLt.ExpectException procedure.

在某些情况下，由于返回的异常，单元测试的结果可能会发生变化。通常，我们希望测试中出现异常，如果发生异常，则测试必须成功。这种类型的单元测试称为否定测试，因为我们正在测试意外和异常的条件。在tSQLt框架中，我们可以借助tSQLt.ExpectException过程来实现它。

On the other side, we don’t expect any error in the test and if an error occurs during SQL unit testing, we want that, the unit test has to be failed. In the tSQLt framework we can make it with help of the tSQLt.ExpectNoException procedure. Now, we are going to look at how we deal with these cases and let’s learn details and usage of these methods.

另一方面，我们预计测试中不会出现任何错误，并且如果在SQL单元测试期间发生错误，我们希望单元测试必须失败。在tSQLt框架中，我们可以借助tSQLt.ExpectNoException过程来实现它。现在，我们将研究如何处理这些情况，让我们了解这些方法的详细信息和用法。

tSQLt.ExpectException概述 (tSQLt.ExpectException overview)

Suppose that, we have created a table and then we added a unique constraint to this table to provide the data integrity of the table. How can we ensure that, this unique constraint will work properly? The answer is very simple; we try to insert the same value twice and in the second insert process; it must return an error. We have found the answer, but how to apply this logic to the SQL unit test. In the following section, we will learn how to overcome this SQL Unit testing issue. At first, we will create an example table which includes a unique constraint.

假设已经创建了一个表，然后向该表添加了唯一约束以提供表的数据完整性。我们如何确保这一独特的约束条件能够正常工作？答案很简单。我们尝试在第二次插入过程中两次插入相同的值；它必须返回一个错误。我们找到了答案，但是如何将此逻辑应用于SQL单元测试。在以下部分中，我们将学习如何克服此SQL单元测试问题。首先，我们将创建一个包含唯一约束的示例表。

DROP TABLE IF EXISTS Orders; 
GO
CREATE TABLE Orders
(OrderId     INT
 PRIMARY KEY IDENTITY(1, 1), 
 OrderNumber VARCHAR(20), 
 OrderDef    VARCHAR(100)
);
GO
ALTER TABLE Orders
ADD CONSTRAINT Check_Order UNIQUE(OrderNumber);

As we mentioned, SQL unit testing tSQLt framework offers tSQLt.ExpectException method. tSQLt.ExpectException method catches the error after a row in which it is used, and it compares this error according to the entered parameters. If the occurred error matches the tSQLt.ExpectException given parameters, the SQL unit test will be passed, otherwise, the SQL unit test will be failed.

如前所述，SQL单元测试tSQLt框架提供了tSQLt.ExpectException方法。 tSQLt.ExpectException方法在使用错误的行之后捕获该错误，并根据输入的参数比较此错误。如果发生的错误与给定的参数tSQLt.ExpectException相匹配，则将通过SQL单元测试，否则，SQL单元测试将失败。

On the other hand, if the error does not occur then the SQL unit test will be failed. The SQL unit test must fulfill two requirements to be succeed.

另一方面，如果未发生错误，则SQL单元测试将失败。 SQL单元测试必须满足两个要求才能成功。

tSQLt.ExpectException line tSQLt.ExpectException行之后必须发生错误
Error detail must match the expected error
错误详细信息必须与预期的错误相匹配

Let’s create a SQL unit test for the unique constraint scenario which we mentioned before.

让我们为前面提到的唯一约束场景创建一个SQL单元测试。

tSQLt.ExpectException语法和用法 (tSQLt.ExpectException syntax and usage)

tSQLt.ExpectExpection method offers various parameters for flexible usage and all parameters of this method are optional.

tSQLt.ExpectExpection方法提供了各种参数，可以灵活使用，并且该方法的所有参数都是可选的。

tSQLt.ExpectException 
                     [  [@ExpectedMessage= ] 'expected error message']
                     [, [@ExpectedSeverity= ] 'expected error severity']
                     [, [@ExpectedState= ] 'expected error state']
                     [, [@Message= ] 'supplemental fail message']
                     [, [@ExpectedMessagePattern= ] 'expected error message pattern']
                     [, [@ExpectedErrorNumber= ] 'expected error number']

Now, we will create a SQL unit test which includes tSQLt.ExpectException method and then we can handle this query line by line with detailed explanations.

现在，我们将创建一个包含tSQLt.ExpectException方法SQL单元测试，然后可以使用详细说明逐行处理该查询。

EXECUTE tSQLt.NewTestClass 'TestOrder'
GO
CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint]
AS
EXECUTE tSQLt.FakeTable 'Orders'
EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order'
EXECUTE tsqlt.ExpectException  @ExpectedMessagePattern ='%Violation of UNIQUE KEY constraint%Check_Order%'
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
GO
EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint]'

SQL unit testing - tSQLt framework tsqlt.ExpectException result image

创建一个新的测试类 (Create a new test class)

EXECUTE tSQLt.NewTestClass 'TestOrder'

In this part of the query, we created a new test class. It creates a database schema which related to tSQLt framework.

在查询的这一部分中，我们创建了一个新的测试类。它创建与tSQLt框架相关的数据库架构。

创建一个假表并添加约束 (Create a fake table and add a constraint )

EXECUTE tSQLt.FakeTable 'Orders'
EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order'

In this part, we create fake table of the Orders table so that we obtain an empty table and, this table does not possess any constraint. For this reason, we should add unique constraints through the tSQLt.ApplyConstraint method so that Check_Order constraint will be created on the fake copy of the Orders table during the SQL unit test.

在这一部分中，我们创建Orders表的伪表，以便获得一个空表，并且该表不具有任何约束。因此，我们应该通过tSQLt.ApplyConstraint方法添加唯一约束，以便在SQL单元测试期间在Orders表的伪副本上创建Check_Order约束。

如何在SQL单元测试中使用tSQLt.ExpectException？ (How to use tSQLt.ExpectException in SQL Unit Test?)

EXECUTE tsqlt.ExpectException  @ExpectedMessagePattern ='%Violation of UNIQUE KEY constraint% %'

In this part of the query, we set the tSQLt.ExpectException parameters. In the below, we use the @ExpectedMessagePattern parameter. This parameter specifies that if the error message look like this pattern thus the expected and actual errors will be match. If we try to insert a duplicate value to the table which includes a unique constraint, an error messages occurs which looks like below:

在查询的这一部分中，我们设置了tSQLt.ExpectException参数。在下面，我们使用@ExpectedMessagePattern参数。此参数指定如果错误消息看起来像此模式，则预期错误和实际错误将匹配。如果我们尝试将包含唯一约束的重复值插入表中，则会出现错误消息，如下所示：

Msg 2627, Level 14, State 1, Line 23
Violation of UNIQUE KEY constraint ‘Unique Constraint name‘. Cannot insert duplicate key in object ‘Table name’. The duplicate key value is (Value Name).

讯息2627，第14级，州1，第23行
违反UNIQUE KEY约束“ 唯一约束名称 ”。无法在对象“ 表名”中插入重复键。重复的键值为（ 值名称 ）。

Moving from this error message we set the @ExpectedMessagePattern as “%Violation of UNIQUE KEY constraint%” so that tSQLt.ExpectException can match the actual error and expected errors in the SQL unit test. On the other hand, we will try to create an error through the following insert statement. It is obvious that the second insert statement will return a unique value duplication error.

从此错误消息开始，我们将@ExpectedMessagePattern设置为“ UNIQUE KEY约束％Violation％”，以便tSQLt.ExpectException可以与SQL单元测试中的实际错误和预期错误匹配。另一方面，我们将尝试通过以下插入语句创建错误。很明显，第二个insert语句将返回唯一的值重复错误。

INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')

At the same time, we can use a @ExpectedErrorNumber parameter instead of the @ExpectedMessagePattern. @ExpectedErrorNumber parameter takes exact number of the error as a value.

同时，我们可以使用@ExpectedErrorNumber参数代替@ExpectedMessagePattern。 @ExpectedErrorNumber参数将错误的确切编号作为值。

CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint]
AS
EXECUTE tSQLt.FakeTable 'Orders'
EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order'
EXECUTE tsqlt.ExpectException  @expectedErrorNumber =2627
 
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
 
GO
EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint]'

SQL unit testing - tSQLt framework @expectedErrorNumber usage in the tsqlt.ExpectException procedure

I want to suggest that in this type of the SQL unit tests do not use Try-Catch blocks because it will be prevented from catching the errors.

我想建议在这种类型SQL单元测试中不要使用Try-Catch块，因为这样可以防止捕获错误。

CREATE OR ALTER PROCEDURE TestOrder.[test Orders table unique constraint try_catch]
AS
EXECUTE tSQLt.FakeTable 'Orders'
EXECUTE tSQLt.ApplyConstraint 'Orders','Check_Order'
EXECUTE tsqlt.ExpectException  @expectedErrorNumber =2627
BEGIN TRY
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
INSERT INTO Orders (OrderNumber) VALUES('NewOrder')
END TRY
BEGIN CATCH
 PRINT ERROR_NUMBER(); 
END CATCH
 
GO
EXEC tSQLt.Run 'TestOrder.[test Orders table unique constraint try_catch]'

SQL unit testing - tSQLt framework tsqlt.ExpectException error

As you can see in the above image, the unit test failed because the error does not occur due to Try-Catch block.

如上图所示，单元测试失败，因为没有发生由于Try-Catch块引起的错误。

tSQLt.ExpectNoException (tSQLt.ExpectNoException)

If we don’t expect an error in the SQL unit test, we can use tSQLt.ExpectNoException method to check this circumstance. Normally, If any error occurs in the SQL unit test, this method failed to the unit test. We can use only one tSQLt.ExpectNoException per test. Now we will make a little example.

如果我们不希望在SQL单元测试中出错，则可以使用tSQLt.ExpectNoException方法检查这种情况。通常，如果SQL单元测试中发生任何错误，则此方法对单元测试失败。每个测试我们只能使用一个tSQLt.ExpectNoException。现在，我们将举一个小例子。

CREATE OR ALTER PROCEDURE TestOrder.[test math divide Exception]
AS
BEGIN
  EXEC tSQLt.ExpectNoException;
  DECLARE @Res AS FLOAT
  SET  @Res= 1/1
END;
GO
EXEC tSQLt.Run 'TestOrder.[test math divide Exception]'

SQL unit testing - tSQLt framework ExpectNoException usage

As you can see the unit test passed. On the other hand, we look at the following test, it failed.

如您所见，单元测试已通过。另一方面，我们看下面的测试，它失败了。

CREATE OR ALTER PROCEDURE TestOrder.[test math divide exception error]
AS
BEGIN
  EXEC tSQLt.ExpectNoException;
  DECLARE @Res AS FLOAT
  SET  @Res= 1/0
END;
GO
EXEC tSQLt.Run 'TestOrder.[test math divide exception error]'

tSQLt framework ExpectNoException test fail

[TestOrder].[test math divide exception error] failed: (Failure) Expected no error to be raised. Instead this error was encountered: Divide by zero error encountered.[16,1]{TestOrder.test math divide exception error,6}

[TestOrder]。[test数学除法异常错误]失败：（失败）预期不会引发任何错误。而是遇到了此错误：遇到了除以零的错误。[16,1] {TestOrder.test数学除法异常错误，6}

At this point, I want to review the message of the unit test. As you can see the tSQLt framework unit test message is very clear and it said that you don t expected any error however an error encountered.

在这一点上，我想回顾一下单元测试的消息。如您所见， tSQLt框架单元测试消息非常清晰，它表示您不希望出现任何错误，但是会遇到错误。

结论 (Conclusion)

In this article, we learned details of the tSQLt.ExpectException and tSQLt.ExpectNoException procedures. With the help of these procedures, we can test behaviors of the exception in SQL unit testing. The tSQLt framework offers various procedures however we should know the ability and parameters of these methods. In this way, we can create the most effective and useful SQL unit tests. As for the last sentence in this SQL Unit testing series, the tSQLt framework is the best utility to create SQL unit tests.

在本文中，我们了解了tSQLt.ExpectException和tSQLt.ExpectNoException过程的详细信息。借助这些过程，我们可以在SQL单元测试中测试异常的行为。 tSQLt框架提供了各种过程，但是我们应该知道这些方法的能力和参数。这样，我们可以创建最有效和有用SQL单元测试。至于该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-working-with-exceptions/

@sql 单元测试

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
@sql 单元测试_SQL单元测试：使用异常

@sql 单元测试 With this article, we will complete our journey with SQL Unit Testing. But first, let’s remember the main theme of the SQL Unit Testing again briefly. With the help of the SQL Unit Testi...
复制链接

扫一扫