sqlserver的sql单元测试存储过程
Unit testing is an essential component of the database DevOps process. Its primary goal is to test the constituent parts of the database objects in order to identify any malfunctions or flaws early in the project. This approach allows database developers to ensure the changes they have made are verified, and the project will work properly. In our article, we will mainly focus on unit testing a stored procedure in a SQL Server database and exemplify how simple it is to conduct unit tests with the dbForge Unit Test tool.
单元测试是数据库DevOps流程的重要组成部分。 其主要⽬标是测试数据库对象的组成部分，以便在项⽬早期发现任何故障或缺陷。 这种⽅法使数据库开发⼈员可以确保验证他们所做的更改，并且项⽬将正常运⾏。 在本⽂中，我们将主要集中于对SQL Server数据库中的存储过程进⾏单元测试，并举例说明使⽤dbForge单元测试⼯具进⾏单元测试有多么简单。
Previously, we discussed the process of
之前，我们讨论了的过程
Img.1. The database schema for a recruitment service
图1。 招聘服务的数据库架构
As shown above, the database contains the following entities:
如上所⽰，数据库包含以下实体：
Employee
雇员
Company
公司
Position
位置
Project
项⽬
Skill
技能
Nonetheless, in the series of articles, we somehow overlooked a crucial aspect of unit testing. So now, I suggest we have a closer look at this method and exemplify it by implementing the SearchEmployee stored procedure for an employee search based on certain skills. To ensure data integrity, we should add a unique constraint on the Skill table as follows:
但是，在系列⽂章中，我们以某种⽅式忽略了单元测试的关键⽅⾯。 因此，现在，我建议我们仔细研究此⽅法，并通过为基于某些技能的员⼯搜索实现SearchEmployee存储过程来举例说明。 为了确保数据完整性，我们应该在Skill表上添加唯⼀约束，如下所⽰：
ALTER TABLE [dbo].[Skill] ADD CONSTRAINT UniqueSkillName UNIQUE(SkillName);
However, before doing that, make sure the data in the SkillName field doesn’t contain any duplicate entries using the following query:
但是，在执⾏此操作之前，请使⽤以下查询确保SkillName字段中的数据不包含任何重复的条⽬：
SELECT
[SkillName]
FROM [JobEmpl].[dbo].[Skill]
GROUP BY [SkillName]
HAVING COUNT(*) > 1;
Supposing you have duplicate entries, you will need to normalize all records to unique values for the SkillName field relative to one another.
假设您有重复的条⽬，则需要将所有记录标准化为SkillName字段相对于彼此的唯⼀值。
Okay, one point completed: we have created a uniqueness constraint in the names of the skills.
好的，⼀点已经完成：我们在技能名称中创建了唯⼀性约束。
Now, it’s time to implement the SearchEmployee stored procedure, as shown below:
现在，是时候实现SearchEmployee存储过程了，如下所⽰：
CREATE PROCEDURE [dbo].[SearchEmployee]
@SkillList NVARCHAR(MAX),
@CountNotSkill INT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @count_skills INT;
SELECT
[value] INTO #tbl_skill_tmp
FROM STRING_SPLIT(@SkillList, N';');
SELECT
s.[SkillID]
,s.[SkillName] INTO #tbl_skill
FROM #tbl_skill_tmp AS tt
INNER JOIN [dbo].[Skill] AS s
ON s.[SkillName] = tt.[value];
SET @count_skills = (SELECT
COUNT(*)
FROM #tbl_skill);
SELECT
jh.*
,p.[ProjectName]
,p.[Description] AS [ProjectDescription]
,ts.* INTO #tbl_res0
FROM [dbo].[JobHistory] AS jh
INNER JOIN [dbo].[Project] AS p
ON p.[ProjectID] = jh.[ProjectID]
INNER JOIN [dbo].[ProjectSkill] AS ps
ON ps.[ProjectID] = p.[ProjectID]
INNER JOIN #tbl_skill AS ts
ON ps.[SkillID] = ts.[SkillID];
SELECT
[EmployeeID]
,[SkillID]
,MIN([SkillName]) AS [SkillName]
,SUM(DATEDIFF(DAY, [StartDate], COALESCE([FinishDate], GETDATE()))) AS [Days]
,
MIN([StartDate]) AS [StartDate]
,MAX(COALESCE([FinishDate], GETDATE())) AS [FinishDate] INTO #tbl_res
FROM #tbl_res0
GROUP BY [SkillID]
,[EmployeeID];
SELECT
emp.[EmployeeID]
,emp.[LastName]
,emp.[FirstName]
,r.[SkillID]
,r.[SkillName]
,
r.[StartDate]
,r.[FinishDate]
,r.[Days] / 365 AS [Years]
,(r.[Days] - (r.[Days] / 365) * 365) / 30 AS [Months]
,r.[Days] - (r.[Days] / 365) * 365 - ((r.[Days] - (r.[Days] / 365) * 365) / 30) * 30 AS [Days] INTO #tbl_res2 FROM #tbl_res AS r
INNER JOIN [dbo].[Employee] AS emp
ON emp.[EmployeeID] = r.[EmployeeID];
SELECT
[EmployeeID]
,[LastName]
,[FirstName] INTO #tbl_empl
FROM #tbl_res2;
SELECT
ts.[SkillID]
,te.[EmployeeID]
,ts.[SkillName]
,te.[LastName]
,te.[FirstName] INTO #tbl_skill_empl
FROM #tbl_skill AS ts
CROSS JOIN #tbl_empl AS te;
SELECT
tse.[EmployeeID]
,
tse.[LastName]
,tse.[FirstName]
,tse.[SkillID]
,tse.[SkillName]
,tse.[SkillName]
,tr2.[StartDate]
sql存储过程实例
,tr2.[FinishDate]
,tr2.[Years]
,tr2.[Months]
,tr2.[Days] INTO #tbl_res3
FROM #tbl_skill_empl AS tse
LEFT OUTER JOIN #tbl_res2 AS tr2
ON tse.[SkillID] = tr2.[SkillID]
AND tse.[EmployeeID] = tr2.[EmployeeID];
SELECT
[EmployeeID] INTO #tbl_empl_res
FROM (SELECT
[EmployeeID]
,[SkillID]
FROM #tbl_res3
WHERE [Months] >= 6 OR [Years]>=1
GROUP BY [EmployeeID]
,
[SkillID]) AS t
GROUP BY [EmployeeID]
HAVING COUNT(*) >= @count_skills - @CountNotSkill;
SELECT
tr2.[EmployeeID],
tr2.[LastName],
tr2.[FirstName],
tr2.[SkillID],
tr2.[SkillName],
tr2.[StartDate],
tr2.[FinishDate],
tr2.[Years],
tr2.[Months],
tr2.[Days]
FROM #tbl_empl_res AS ter
INNER JOIN #tbl_res2 AS tr2
ON ter.[EmployeeID] = tr2.[EmployeeID];
SELECT
tr2.[EmployeeID],
tr2.[LastName],
tr2.[FirstName],
tr0.[CompanyID],
(SELECT TOP(1) com.[CompanyName] FROM [dbo].[Company] AS com WHERE com.[CompanyID]=tr0.[CompanyID]) AS [CompanyName],  tr0.[PositionID],
(SELECT TOP(1) p.[PositionName] FROM [dbo].[Position] AS p WHERE p.[PositionID]=tr0.[PositionID]) AS [PositionName],
tr0.[ProjectID],
tr0.[StartDate],
tr0.[FinishDate],
tr0.[Description],
tr0.[ProjectName],
tr0.[ProjectDescription],
tr0.[SkillID],
tr0.[SkillName],
tr0.[Achievements],
tr0.[ReasonsForLeavingTheProject],
tr0.[ReasonsForLeavingTheCompany]
FROM #tbl_res2 AS tr2
INNER JOIN #tbl_res0 AS tr0
ON tr0.[EmployeeID] = tr2.[EmployeeID]
INNER JOIN #tbl_skill AS ts
ON ts.[SkillID] = tr0.[SkillID];
DROP TABLE #tbl_skill_tmp;
DROP TABLE #tbl_skill;
DROP TABLE #tbl_res;
DROP TABLE #tbl_res2;
DROP TABLE #tbl_empl;
DROP TABLE #tbl_skill_empl;
DROP TABLE #tbl_res3;
DROP TABLE #tbl_empl_res;
DROP TABLE #tbl_res0;
END
GO
Why not examine the work of the SearchEmployee stored procedure in greater detail?
为什么不更详细地检查SearchEmployee存储过程的⼯作？
For starters, it has two input parameters:
对于初学者，它具有两个输⼊参数：
1. @SkillList is the list of skills, separated by semicolons.
@SkillList是技能列表，以分号分隔。
2. @CountNotSkill indicates the number of skills that can be absent (1 by default).
@CountNotSkill指⽰可以缺少的技能数(默认为1)。
Let’s now move on to the body of the SearchEmployee stored procedure:
现在，让我们转到SearchEmployee存储过程的主体：
1. First, we define the variable @count_skills that is used to count the number of skills found in the database that
correspond to the reported number in the input parameter @SkillList.
⾸先，我们定义变量@count_skills，该变量⽤于对数据库中发现的与输⼊参数@SkillList中报告的数字相对应的技能数进⾏计数。2. Next, the @SkillList string is transformed into the temporary table #tbl_sk
ill_tmp with the built-in function .
接下来，使⽤内置函数将@SkillList字符串转换为临时表#tbl_skill_tmp。
3. Then, all the suitable skills from the Skill table are found and placed into a new temporary table named #tbl_skill_tmp.
然后，从“技能”表中到所有合适的技能，并将其放置在名为#tbl_skill_tmp的新临时表中。
4. After that, @count skills is counted in accordance with par.1.
之后，@ count技能将根据参数1进⾏计数。
5. Following that, the necessary information on the project (Project table) and the job history (JobHistory table) is
collected based on the set skills; the result goes to a temporary table called #tbl_skill_tmp.
然后，根据设置的技能，收集有关项⽬(项⽬表)和⼯作历史(作业历史表)的必要信息； 结果进⼊⼀个名为#tbl_skill_tmp的临时表。6. Next, the information obtained in par. 5 is grouped according to the identifiers of skill and employee, and the result
goes to the temporary table #tbl_res.
接下来，获取在参数中获得的信息。 根据技能和雇员的标识符对图5进⾏分组，结果进⼊临时表#tbl_res。
7. Further on, the information obtained in par. 6 is combined with the Employee table so as to get the employees’ details
(first and last name), and the result goes to the temporary table #tbl_res2. The query also counts how long each skill has been applied in years, months, and days to make the subsequent analysis more convenient.
进⼀步，获得了在标准杆中获得的信息。 将6与Employee表组合在⼀起，以获取雇员的详细信息(名字和姓⽒)，然后结果进⼊临时表＃tbl_res2。 该查询还计算了在数年，数⽉和数天内每种技能的应⽤时间，以使后续分析更加⽅便。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。