在表插入中使用用户定义函数并通过存储过程进行初始化
使用 UDF 和存储过程生成唯一的列值。
概述
本文（我的第一篇），演示了如何使用 INSERT 语句自动设置列值。我展示了两种方法：第一种方法使用用户定义函数在 Table INSERT 上自动生成唯一代码；第二种方法使用存储过程将行插入到具有指定或默认索引编号的表中。
背景
您是否需要为索引列（如任务编号或序列中的步骤）进行 seeding？ 如何自动为客户生成唯一代码？ 以下是我对这些问题的解决方案。 这些可能不是实现此功能的最佳或最优雅的方式，但它是一个很好的入门。
我们要做的第一件事是打开 SQL Management Studio (SSMS) 并创建一个名为 DemoTaskList 的示例数据库；然后在新的查询窗口中，我们创建我们的第一个表
USE DemoTaskList
GO
CREATE TABLE Person(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) UNIQUE NOT NULL
)
-- We will add some sample data...
INSERT Person([Name]) VALUES('Richard Nixon')
INSERT Person([Name]) VALUES('Bill Clinton')
GO
接下来我们需要创建一个用户定义函数来执行 string 操作，以便从 Person Table 中的 Person.Name 列和 Job Table 中的 Job 的 ID 的组合中自动创建一个唯一值（即将推出...）
CREATE FUNCTION udf_SET_JobCode(
@PersonID INT,
@ID INT
)
RETURNS VARCHAR(10)
AS
BEGIN
-- Get the Person referenced by @PersonID
DECLARE @Person VARCHAR(100) = (SELECT [Name]
FROM Person
WHERE ID = @PersonID)
-- A VARCHAR copy of the @ID input
DECLARE @RET VARCHAR(10) = CAST(@ID AS VARCHAR(10))
-- CHAR Length of the @RET variable
DECLARE @LEN INT = LEN(@RET)
SET @RET = UPPER(LEFT(@Person,3)) + -- Magic (see BOL for
STUFF(@RET,1,0,LEFT('0000000',7 - @LEN)) -- Function
-- definitions/syntax
RETURN @RET
END
-- Now the all important Job table that calls our UDF as a reference
CREATE TABLE Job(
ID INT Identity(1,1) NOT NULL PRIMARY KEY,
PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID),
JobCode AS dbo.udf_SET_JobCode(PersonID, ID), -- UDF Reference
JobDescription VARCHAR(200) NOT NULL
)
正如您在上面这一行中看到的：JobCode AS dbo.udf_SET_JobCode(PersonID, ID)，JobCode 的列定义引用了我们的用户定义函数并传递了两个参数：PersonID 和 ID，并为我们返回了自定义值。
UDF 返回的自定义值由人名的前三个字母和填充到 7 位的自动生成的 Identity 值组成。 阅读 BOL 文档中关于 STUFF 函数的说明，了解其工作原理 - 无需多说，它会将一个 String 填充到另一个 String 中。
让我们通过为 Richard Nixon 添加几个新 Job 来测试一下
INSERT Job(PersonID,JobDescription)
VALUES(1,'Become President of the USA')
INSERT Job(PersonID,JobDescription)
VALUES(1,'Become Infamous')
-- Select the data...
SELECT * FROM Job
GO
RESULT:
===========================================================
ID PersonID JobCode JobDescription
---- ------------------- ---------- ---------------------
1 1 RIC0000001 Become President of the USA
2 1 RIC0000002 Become Infamous
正如预期的那样，为我们的 Person 自动为每个 INSERT 生成了一个 JobCode。 让我们为 Bill Clinton 添加一个 job
INSERT Job(PersonID, JobDescription)
VALUES(2, 'Achieve Notoriety')
GO
-- Select the data...
SELECT * FROM Job
GO
RESULT:
===========================================================
ID PersonID JobCode JobDescription
---- ------------------- ---------- ---------------------
1 1 RIC0000001 Become President of the USA
2 1 RIC0000002 Become Infamous
3 2 BIL0000003 Achieve Notoriety
一切都清楚了吗？ 现在我想构建一个 Table ，它将保存完成分配给某个人的 Job 所需的任务。 我们希望在查询表时按顺序列表任务，并且我们希望能够在列表中的指定索引处插入新任务。 例如，如果某个 Job 不存在任何任务，我们将在任务编号 1 处添加新任务。但是，如果确实存在任务，我们可以根据您的标准在列表中的指定位置或列表末尾插入任务。 让我们看一下脚本
CREATE TABLE Task(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
JobID INT NOT NULL FOREIGN KEY REFERENCES Job(ID),
TaskNo INT NOT NULL,
TaskDescription VARCHAR(200)
)
GO
CREATE PROCEDURE usp_INSERT_Task(
@JobCode VARCHAR(10),
@TaskNo INT = NULL,
@TaskDescr VARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON;
-- Get the JobID
DECLARE @JobID INT = (SELECT ID
FROM Job
WHERE JobCode = @JobCode)
-- Get the largest TaskNo for the job
DECLARE @MAX INT = (SELECT MAX(TaskNo)
FROM Task t
JOIN Job j
ON t.JobID = j.ID
WHERE j.JobCode = @JobCode)
-- Fancy error handling block
BEGIN TRY
BEGIN TRANSACTION
-- Check TaskNo is valid else set defaults
SET @TaskNo = (
SELECT CASE ISNULL(@TaskNo,0) -- if @TaskNo is null then check @MAX
WHEN 0
THEN
CASE ISNULL(@MAX,0) -- if max is null then set @TaskNo = 1
WHEN 0
Then 1
ELSE @MAX + 1
END
ELSE -- Else if @TaskNo is not null Then
Case WHEN @TaskNo > @MAX -- if @TaskNo > @MAX
-- set to @MAX + 1
THEN @MAX + 1
ELSE @TaskNo -- else do nothing
END
END)
DECLARE @Count INT = @MAX
WHILE @Count >= @TaskNo -- If @MAX >= @TaskNo Update TaskNos >=
BEGIN -- @TaskNo
UPDATE Task
SET TaskNo = TaskNo + 1
WHERE JobID = @JobID
AND TaskNo = @Count
SET @Count = @Count - 1 -- Decrement Counter
END
INSERT Task(JobID, TaskNo, TaskDescription) -- Perform Insert
VALUES(@JobID, @TaskNo, @TaskDescr)
COMMIT TRANSACTION -- Commit transaction
END TRY
-- Perform Error Handling
BEGIN CATCH
ROLLBACK
BEGIN
RAISERROR('Error when processing INSERT statement or Task Table.',15,1)
RETURN -100
END
END CATCH
RETURN
END
这就是它的全部内容 - 我们迭代遍历 TaskNo，将它们递增 1，然后在指定位置 INSERT 新记录，就像这样...
EXEC dbo.usp_INSERT_Task 'RIC0000001',NULL,'Run for Class President' -- Default position
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001',1,'Join Student Union'
GO
EXEC dbo.usp_INSERT_Task 'RIC0000001',5, 'Run for President of USA'
GO
SELECT p.[Name], j.JobCode, t.TaskNo, t.TaskDescription
FROM Task t
JOIN Job j
ON t.JobID = j.ID
JOIN Person p
ON j.PersonID = p.ID
WHERE p.ID = 1
ORDER BY p.[Name], j.JobCode, t.TaskNo
GO
RESULT:
=======================================================================================
Name JobCode TaskNo TaskDescription
---------------------------------------------------------------------------------------
Richard Nixon RIC0000001 1 Join Student Union
Richard Nixon RIC0000001 2 Run for Class President
Richard Nixon RIC0000001 3 Run for President
正如您所看到的，存储过程能够根据 @MAX 的值确定默认位置，并根据我们的规则使用正确的 TaskNo 值 INSERT 新行。
关注点
UDF 使用各种 String 函数：CAST、LEN、LEFT 和 STUFF。 有关如何使用这些内置函数的详细说明，请参阅联机丛书。
谢谢
感谢 UTS/SSW.com.au 的 Andy Taslim 和 Peter Gfader 在过去几周提供的宝贵建议，以及 CodeProject 上的所有朋友们，他们给了我发布第一篇文章的信心。
版本
- 版本 1.0