pl/sql 测试函数_如何在SQL单元测试中使用伪函数？

最新推荐文章于 2021-09-25 11:44:53 发布

culuo4781

最新推荐文章于 2021-09-25 11:44:53 发布

阅读量381

点赞数

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

原文链接：https://www.sqlshack.com/how-to-use-fake-functions-with-sql-unit-testing/

版权

pl/sql 测试函数

In this article series, we are exploring SQL unit testing, in general, and also we are reinforcing details and topics with various practical examples. We are using the tSQLt framework because it is the quite powerful and handy tool in order to develop and code SQL unit tests.

在本系列文章中，我们通常将探索SQL单元测试，并且还将通过各种实际示例来加强细节和主题。我们正在使用tSQLt框架，因为它是用于开发和编码SQL单元测试的非常强大且方便的工具。

Note: I suggested that to take a glance on the previous articles (see the TOC at bottom) which are related to SQL unit testing. Especially SQL unit testing with the tSQLt framework for beginners can be a very good starting point for the newbies in SQL unit testing and tSQLt frameworks.

注意： 我建议您看一下 与SQL单元测试 有关的前几篇文章（请参阅 底部的 TOC ）。 尤其是对于初学者而言，使用tSQLt框架进行SQL单元测试对于SQL单元测试和tSQLt框架的新手而言可能是一个很好的起点。

All these articles underscore two essential benefits of the SQL unit testing;

所有这些文章都强调了SQL单元测试的两个基本好处;

SQL unit testing improves code quality and design
SQL单元测试可提高代码质量和设计
SQL unit testing enables early bug detection and defection
SQL单元测试可实现早期错误检测和清除

and also these articles mentioned about two basic principles about SQL unit testing;

这些文章还提到了有关SQL单元测试的两个基本原理;

SQL unit test must test only one individual, functional and atomic part of the code
SQL单元测试必须仅测试代码的单个，功能和原子部分
If it is possible, we should isolate the SQL unit test from dependencies
如果可能，我们应该将SQL单元测试与依赖项隔离

Moving from these ideas, there is no doubt about it, dependency isolation is a significantly valuable technique used to develop a more precise SQL unit tests. That’s why, in this article we will continue to learn new faking methods of the tSQLt framework. Particularly, we will try to find out the answer of the how to use fake functions in SQL unit testing. Actually, the answer to the question is not very complicated. The tSQLt framework offers a method called the FakeFunction, we can also the use FakeFunction in the SQL unit tests to handle these type of issues. Let learn more details about it.

从这些想法出发，毫无疑问，依赖隔离是用于开发更精确SQL单元测试的一种非常有价值的技术。因此，在本文中我们将继续学习tSQLt框架的新伪造方法。特别是，我们将尝试找出在SQL单元测试中如何使用伪函数的答案。实际上，该问题的答案不是很复杂。 tSQLt框架提供了一种称为FakeFunction的方法，我们还可以在SQL单元测试中使用FakeFunction来处理此类问题。让我们了解更多有关它的细节。

关于FakeFunction的概述 (Overview about FakeFunction)

Most often, a tested case can be quite complicated, and it may also include function(s). Under this circumstance the function result may influence the SQL unit testing behaviors because the tested condition will be dependent on the function. However, this is the worst case scenario which we do not want to see in this SQL unit testing. This violates the unit test dependency isolation pattern.

大多数情况下，经过测试的用例可能非常复杂，并且可能还包含函数。在这种情况下，函数结果可能会影响SQL单元测试行为，因为被测试的条件将取决于函数。但是，这是最坏的情况，我们不想在此SQL单元测试中看到。这违反了单元测试依赖项隔离模式。

Now we will go through a demonstration and we will explore how to use a fake function in the SQL unit testing. The following scalar-valued function makes some modulo calculations and then returns the result as odd or even.

现在，我们将进行演示，并将探索如何在SQL单元测试中使用伪函数。以下标量值函数进行一些模运算，然后将结果返回为奇数或偶数。

CREATE OR ALTER FUNCTION dbo.UDefFuncOddorEven (@n int)
 RETURNS bit
 AS
 BEGIN
     DECLARE @ModuleRes INT
 
   SET @ModuleRes = (@n%11)
   SET @ModuleRes = (@n%9)
        RETURN (@ModuleRes % 2)
 
 END

Now we will take a glance at the below, the stored procedure that uses this scalar-valued function and the result set of the stored procedure is directly affecting from this scalar-valued function result.

现在，我们将看一下下面的内容，使用该标量值函数的存储过程以及该存储过程的结果集都直接受到此标量值函数结果的影响。

DROP TABLE IF EXISTS OrderOnline
GO
CREATE TABLE OrderOnline
(Id INT PRIMARY KEY IDENTITY(1,1),
OrderName VARCHAR(100),
CustomerName VARCHAR(100))
GO
 
CREATE OR ALTER PROC SetOrders 
@OName AS VARCHAR(100),
@CName AS VARCHAR(100)
AS
BEGIN
DECLARE @RandomVal AS INT 
SET @RandomVal= FLOOR(RAND()*1000)
DECLARE @ufResult AS BIT
SELECT @ufResult=dbo.UDefFuncOddorEven(@RandomVal)
IF @ufResult=1
BEGIN
INSERT INTO OrderOnline (OrderName,CustomerName) VALUES (@OName,@CName)
END
END
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='OrderOnline'
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='SetOrders'

Test stored procedure for SQL unit testing

At first, we will examine the code flow of the query. SetOrders stored procedure generates a random number and then it determines the insert operation according to UDefFuncOddorEven scalar-valued function result of this random number. In fact, our main purpose is to check functionality of the SetOrders stored procedure, but it is SetOrders has a strong dependency to UDefFuncOddorEven function and it is directly affects code flow of the SetOrders. Shortly, we must handle dependency issue. The FakeFunction method allows us to the change original function with a dummy or faux one during the SQL unit test process so that we can determine and take the control of the concerned function result. As a general rule, fake functions return values can be hard-coded so that we can be sure about the return value and this touch makes it very simple. Now we will go through other details and usage of the FakeFunction.

首先，我们将检查查询的代码流。 SetOrders存储过程生成一个随机数，然后根据该随机数的标量值函数结果UDefFuncOddorEven确定插入操作。实际上，我们的主要目的是检查SetOrders存储过程的功能，但是SetOrders对UDefFuncOddorEven函数具有很强的依赖性，它直接影响SetOrders的代码流。不久，我们必须处理依赖性问题。 FakeFunction方法允许我们在SQL单元测试过程中使用伪或伪函数更改原始函数，以便我们可以确定并控制相关函数的结果。通常，伪函数的返回值可以进行硬编码，以便我们可以确定返回值，并且这种联系使其非常简单。现在，我们将介绍FakeFunction的其他详细信息和用法。

FakeFunction语法 (FakeFunction syntax )

FakeFunction takes two parameters as follows;

FakeFunction接受两个参数，如下所示：

tSQLt.FakeFunction [@FunctionName = ] 'function name'
                 , [@FakeFunctionName = ] 'fake function name'

@FunctionName parameter specifies the original function name which we want to replace with the fake one
@FunctionName参数指定我们要用伪造的函数替换的原始函数名
@FakeFunctionName parameter specifies the fake function name that replaces the original function name during the SQL unit test period
@FakeFunctionName参数指定伪函数名称，该伪函数名称在SQL单元测试期间将替换原始函数名称

These parameters data types are nvarchar(max).

这些参数数据类型为nvarchar（max）。

伪函数是什么样的？ (What does the fake function look like?)

In this section we will code the SQL unit test with the help of the tSQLt framework. At first, we will create the fake function which we will use instead of the original one. We should create the fake function as simple as possible in order to prevent complexity of the SQL unit test. The first choice would be a function which returns a hard coded value so that we don’t worry about the return value. At this point, we will notice one thing about fake function naming convention. We should add a fake expression to the name of the fake function so that we can easily recognize this function and also we can add the return value of the fake function to the name of it. Let’s create our first fake function.

在本节中，我们将在tSQLt框架的帮助下对SQL单元测试进行编码。首先，我们将创建将使用伪函数代替原始函数。我们应该创建尽可能简单的伪函数，以防止SQL单元测试的复杂性。首选将是一个返回硬编码值的函数，这样我们就不必担心返回值。在这一点上，我们将注意到有关假函数命名约定的一件事。我们应该在伪函数的名称上添加一个伪表达式，以便我们可以轻松识别此函数，并且还可以在伪函数的名称中添加伪函数的返回值。让我们创建第一个伪函数。

CREATE OR ALTER FUNCTION dbo.UDefFuncOddorEven_Fake_Return_1 (@n int)
 RETURNS bit
 AS
 BEGIN
     RETURN 1
 END

As you can see, we replace the complex modulo calculation part of the original scalar-valued function with a very simple hard-coded return value so that fake function every time returns the same value and we know that.

如您所见，我们用非常简单的硬编码返回值替换了原始标量值函数的复杂模计算部分，从而使伪函数每次都返回相同的值，我们知道这一点。

SQL单元测试中的伪函数用法。 (Fake function usage in SQL unit testing.)

In this SQL unit test example, we want to successfully check SetOrders stored procedure functionality, so, does it insert the insert proper values to OrderOnlLine table? Mainly because we want to test this situation. Now we will reference this idea and then write the SQL unit test through the tSQLt framework, it will look like the following query.

在此SQL单元测试示例中，我们想成功地检查SetOrders存储过程的功能，那么，它是否将适当的插入值插入到OrderOnlLine表中？主要是因为我们要测试这种情况。现在，我们将参考这个想法，然后通过tSQLt框架编写SQL单元测试，它看起来像以下查询。

EXECUTE tsqlt.NewTestClass 'TestFakeFunction'
GO
EXECUTE tSQLt.NewTestClass 'TestFakeFunction'
GO
CREATE OR ALTER PROCEDURE TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]
AS
BEGIN
DROP TABLE IF EXISTS expected
DROP TABLE IF EXISTS actual
 
EXEC tSQLt.FakeTable 'OrderOnline'
 
SELECT TOP(0) * INTO expected FROM OrderOnline
SELECT TOP(0) * INTO actual FROM OrderOnline
EXEC tSQLt.FakeFunction 'dbo.UDefFuncOddorEven' ,'dbo.UDefFuncOddorEven_Fake_Return_1'
INSERT INTO expected
(OrderName , CustomerName)
VALUES ('Pizza','Ryan Romero')
EXECUTE SetOrders 'Pizza' ,'Ryan Romero'
INSERT INTO actual
SELECT * FROM OrderOnline
EXEC tSQLt.AssertEqualsTable expected,actual
END
GO
EXEC tSQLt.Run 'TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]'

SQL unit testing - Result image of FakeFunction usage

Now we will tackle the SQL unit test line by line and make a comprehensive explanation.

现在，我们将逐行处理SQL单元测试并进行全面的解释。

创建一个假表 (Create a fake table)

EXEC tSQLt.FakeTable 'OrderOnline'

In this part of the query, we create a fake of the OrderOnline table because this table should be empty during the unit test so that it contains the rows which we inserted. Actually, we break the dependency of the SetOrders stored procedure to OrderOnline table.

在查询的这一部分中，我们创建了一个伪造的OrderOnline表，因为在单元测试期间该表应该为空，以便它包含我们插入的行。实际上，我们打破了SetOrders存储过程对OrderOnline表的依赖。

创建期望表和实际表 (Create expected and actual tables )

SELECT TOP(0) * INTO expected FROM OrderOnline
SELECT TOP(0) * INTO actual FROM OrderOnline

At the end of the SQL unit test, we compare the expected and actual table’s rows with help of the tsqlt.AssertTables expression. For this reason, we have to create expected and actual tables. The simplest way to perform table creation is to derive from the fake OrderOnline table.

在SQL单元测试结束时，我们将在tsqlt.AssertTables表达式的帮助下比较预期表行和实际表行。因此，我们必须创建预期表和实际表。执行表创建的最简单方法是从伪造的OrderOnline表派生。

伪造功能 (Fake function )

EXEC tSQLt.FakeFunction 'dbo.UDefFuncOddorEven' ,'dbo.UDefFuncOddorEven_Fake_Return_1'

In this part of the unit test, we replace original function with the fake one so that we know fake ones always return the same hard-coded value, we design SQL unit test according to the certainty.

在单元测试的这一部分中，我们用伪函数替换了原始函数，以便我们知道伪函数总是返回相同的硬编码值，我们根据确定性设计了SQL单元测试。

将值填充到预期表 ( Populate values to expected table)

INSERT INTO expected
(OrderName , CustomerName)
VALUES ('Pizza','Ryan Romero')

In this part of the SQL unit test, we populate values into expected table.

在SQL单元测试的这一部分中，我们将值填充到期望表中。

将值填充到实际表 (Populate values to actual table)

EXECUTE SetOrders 'Pizza' ,'Ryan Romero'
INSERT INTO actual
SELECT * FROM OrderOnline

In this part of the query, when we execute the SetOrders procedure, it inserts values to the fake of the OrderOnline table and we populate these values into the actual table.

在查询的这一部分中，当我们执行SetOrders过程时，它将值插入到OrderOnline表的伪表中，并将这些值填充到实际表中。

SQL单元测试结果 ( SQL unit testing result)

EXEC tSQLt.Run 'TestFakeFunction.[Test SetOrders_StoredProcedure_InsertFunction]'

This is the final and the most important step because the result of the SQL unit test is being determined at this point and according to expected and actual table’s comparison. If these tables’ values do not match, tSQLt framework unit test will be a return fail.

这是最后也是最重要的步骤，因为此时将根据预期和实际表的比较确定SQL单元测试的结果。如果这些表的值不匹配，则tSQLt框架单元测试将失败。

Attach importance to FakeFunction errors.

重视FakeFunction错误。

We must consider some points about FakeFunction, if the FakeFunction method does not find the original or the fake one, it returns an error which likes the below.

我们必须考虑有关FakeFunction的一些要点，如果FakeFunction方法找不到原始或伪造的方法，它将返回类似于以下内容的错误。

[function_name or fake_function_name] does not exist! (This includes the return type for scalar functions.)

[function_name或fake_function_name]不存在！（这包括标量函数的返回类型。）

SQL Unit testing FakeFunction does not exists error image

Another point which we should consider about FakeFunction is that, the original function and fake function parameters have to match otherwise we will experience the following error.

关于FakeFunction我们应该考虑的另一点是，原始函数和伪函数参数必须匹配，否则我们将遇到以下错误。

Parameters of both functions must match! (This includes the return type for scalar functions.)

两个函数的参数必须匹配！（这包括标量函数的返回类型。）

FakeFunction parameters of both functions must match error image.

结语 (Wrapping Up)

In this article we continued with our journey about SQL unit testing and tSQLt framework. At the same time, we learnt how to use fake functions in the SQL unit tests in order to isolate dependency of the functions. We demonstrated a very simple SQL unit test which used the FakeFunction method and also we highlighted all details of the aforementioned example.

在本文中，我们继续进行有关SQL单元测试和tSQLt框架的旅程。同时，我们学习了如何在SQL单元测试中使用伪函数，以隔离函数的依赖性。我们演示了使用FakeFunction方法的非常简单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/how-to-use-fake-functions-with-sql-unit-testing/

pl/sql 测试函数

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
pl/sql 测试函数_如何在SQL单元测试中使用伪函数？

pl/sql 测试函数 In this article series, we are exploring SQL unit testing, in general, and also we are reinforcing details and topics with various practical examples. We are using the tSQLt framework ...
复制链接

扫一扫