@sql 单元测试_SQL单元测试最佳实践

@sql 单元测试

SQL unit testing is a testing method which allows us to test the smallest, atomic programmable part of a database object. SQL unit testing plays a key role in the modern database development cycle because it allows us to test individual parts of the database objects work as expected. SQL unit testing adds a great worth to the database project because unit tests are more reliable then manual test methods.

SQL单元测试是一种测试方法，它使我们可以测试数据库对象的最小原子可编程部分。 SQL单元测试在现代数据库开发周期中起着关键作用，因为它允许我们测试数据库对象的各个部分是否按预期工作。 SQL单元测试为数据库项目增加了巨大的价值，因为单元测试比手动测试方法更可靠。

I’ll now elaborate more on this idea; SQL unit testing increases confidence to the code because the developer fixes the flaws and defects of the code in initial stages of the development. The key point here is that production bug fixing is costlier than in the development phase. There is no doubt in that customers have little tolerance against errors in the applications therefore we must minimize bugs and malfunctions.

我现在将详细说明这个想法。 SQL单元测试可以提高对代码的信心，因为开发人员可以在开发的初始阶段修复代码的缺陷。 这里的关键点是修复生产错误的成本比开发阶段的成本高。 毫无疑问，客户对应用程序中的错误几乎没有容忍度，因此我们必须最大程度地减少错误和故障。

In terms of the database development, SQL unit testing is our as close to a silver bullet to minimize errors that we have available to us. In this article, you will find some suggestions for writing an effective SQL unit test and also we will make a bit of reverse engineering which means that we will examine a badly written unit test and then we will fix its errors according to the suggestions. In this way, we will clearly understand the differences between a bad and a good SQL unit test.

在数据库开发方面，SQL单元测试是我们的捷径，它可以最大限度地减少我们可以利用的错误。 在本文中，您将找到一些有关编写有效SQL单元测试的建议，并且还将进行一些逆向工程，这意味着我们将检查写得不好的单元测试，然后根据建议修复其错误。 这样，我们将清楚地理解不良和良好SQL单元测试之间的区别。

Note: If you don’t have a piece of basic knowledge about SQL unit testing and tSQLt framework details, see SQL unit testing with the tSQLt framework for beginners article.

注意： 如果您不具备有关 SQL单元测试 和 tSQLt框架 详细信息 的基本知识 ，请参阅针对初学者使用tSQLt框架SQL单元测试 。

情境 (Scenario)

You have discovered a scalar-valued function and this function is tested by a tSQLt unit test. The following script belongs to this scalar-valued function and this function’s name is GetProductName and it returns a vegetable/fruit name in a very simple case statement. This scalar-valued function unit test was written by an unknown developer. You decided to fix this SQL unit test design and logical errors.

您已经发现了标量值函数，并且该函数已通过tSQLt单元测试进行了测试。 以下脚本属于该标量值函数，该函数的名称为GetProductName，并且在非常简单的case语句中返回蔬菜/水果的名称。 此标量值函数单元测试由未知的开发人员编写。 您决定修复此SQL单元测试设计和逻辑错误。

At first we will create the scalar-valued function through the following script.

首先，我们将通过以下脚本创建标量值函数。

DROP TABLE IF EXISTS Tbl_TestName
 
CREATE TABLE Tbl_TestName
(Id INT , DefName VARCHAR(100))
 
 
INSERT INTO Tbl_TestName 
VALUES (1,'Apple') ,  (2,'Avocado'),  (3 , 'Carrot')
GO
CREATE OR ALTER FUNCTION GetProductName (@Nm INT)
RETURNS VARCHAR(100)
BEGIN
DECLARE @RetVal AS VARCHAR(100)
 
SELECT  @RetVal = CASE @Nm  WHEN 1 THEN (SELECT TOP 1 DefName FROM Tbl_TestName 
ORDER BY Id DESC)
WHEN 2 THEN 'Tomato'
WHEN 3 THEN 'Banana'
ELSE
'Not Found'
END
RETURN  @RetVal
END

Down below, we can find a very poorly written example of SQL unit testing which we mentioned in the previous section. In fact, this unit test case can be executed without any error; however, it does not fulfill the best suggestions according to unit test approach. Now, we will discuss details and straighten out defects of this unit test.

在下面，我们可以找到上一节中提到的编写非常糟糕SQL单元测试示例。 实际上，这个单元测试用例可以被执行而没有任何错误。 但是，根据单元测试方法，它不能提供最佳建议。 现在，我们将讨论细节并理清此单元测试的缺陷。

EXEC tSQLt.NewTestClass 'Func_ProductTest'
GO
CREATE OR ALTER PROCEDURE Func_ProductTest.[Test product function]
AS
DECLARE @Expected AS VARCHAR(100)='Carrot'
DECLARE @Actual AS VARCHAR(100)
SELECT @Actual = dbo.GetProductName(1)
EXEC tSQLt.AssertEquals @Expected,@Actual
SET @Expected='Tomato'
SELECT @Actual = dbo.GetProductName(2)
EXEC tSQLt.AssertEquals @Expected,@Actual
SET @Expected='Banana'