SQL Server 自定义函数、存储过程与触发器实战详解

一、自定义函数 (FUNCTION)

在 SQL Server 中，自定义函数主要分为三类：标量函数（Scalar）、内联表值函数（Inline Table-Valued）和多语句表值函数（Multi-Statement Table-Valued）。它们共同点是都使用 CREATE FUNCTION 定义名称、参数及返回值类型，但具体结构和调用方式存在显著区别。

第1步：创建并调用标量函数

标量函数返回单个数据类型的值。适用于需要基于输入参数返回单一结果的场景。

• 目的：根据员工姓名查询工号。
• 代码示例：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
RETURNS INT 
AS
BEGIN
DECLARE @GONGHAO INT
SET @GONGHAO =(SELECT Y.工号 FROM[T_员工信息] AS Y WHERE Y.姓名 =@XINGMING )
RETURN @GONGHAO
END
GO
/* 调用时注意模式前缀 dbo */
SELECT [姓名],DBO.F_GONGHAO ('李异峰')AS 工号 FROM[T_员工信息] WHERE [姓名]='李异峰'
GO

执行结果如下：

第2步：创建并调用内联表值函数

内联表值函数直接返回一个结果集（Table），语法上没有 BEGIN...END 块，而是直接用 RETURN (SELECT ...)。它类似于一个可带参数的视图。

• 目的：筛选特定日期所有员工的打卡记录。
• 代码示例：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_DAKAJILU(@RIQI DATE)
RETURNS TABLE
AS
RETURN(
SELECT* FROM[T_考勤]AS K WHERE K.日期 =@RIQI 
)
GO
/* 调用方式 */
SELECT*
FROM [F_DAKAJILU]('2014/02/28')
GO

执行结果如下：

第3步：创建多语句表值函数

多语句表值函数允许在函数体内执行多条 SQL 语句，并将结果插入到一个预定义的表变量中。它结合了标量函数的过程化和表值函数的结果集特性。

• 目的：返回指定日期的打卡记录及对应的员工个人信息。
• 关键点：必须先用 RETURNS @TABLE TABLE (...) 定义表变量的列结构。

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)
RETURNS @TEMP_TABLE TABLE( 
[姓名]NVARCHAR(5) NOT NULL,
[工号]INT NOT NULL,
[职位]NVARCHAR(10) NOT NULL,
[部门]NVARCHAR(5) NOT NULL,
[是否夜班]NCHAR(1) NOT NULL,
[日期]DATE NOT NULL,
[当天上班时间]FLOAT(1)NOT NULL
) 
AS
BEGIN
INSERT INTO @TEMP_TABLE 
SELECT Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 ,K.夜班 ,K.日期 ,K.当天上班时间 
FROM[T_员工信息] AS Y CROSS JOIN [T_考勤] AS K 
WHERE Y.工号 =K.工号 AND K.日期=@RIQI 
RETURN 
END
GO
SELECT*
FROM[F_D_DAKAJILU]('2014/02/28')
GO

执行结果如下：

二、Cursor 游标的使用

当需要对数据进行逐行处理（如复杂计算或逐条更新）时，可以使用游标。其基本生命周期包括：声明、打开、读取、关闭和释放。

第1步：声明并读取只读数据

• 目的：遍历员工信息表，打印每条记录的姓名、工号等。
• 关键步骤：使用 FETCH ... INTO 将数据存入局部变量，并通过 WHILE (@@FETCH_STATUS = 0) 控制循环。

USE SQL_SYSTEM 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION CURSOR_READ_T_员工信息
DECLARE CUR_T_员工信息 SCROLL CURSOR FOR
SELECT Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 FROM[T_员工信息]AS Y ORDER BY Y.工号 ASC 
COMMIT TRANSACTION CURSOR_READ_T_员工信息
GO
/* 打开游标 */
OPEN GLOBAL CUR_T_员工信息
GO
/* 读取数据 */
DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8)
PRINT'当前数据表中有'+CAST(@@CURSOR_ROWS AS NVARCHAR(6))+'行数据。'
FETCH FIRST FROM[CUR_T_员工信息]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
WHILE(@@FETCH_STATUS =0)
BEGIN
PRINT'姓名：'+@XINGMING+' 工号：'+CONVERT(VARCHAR(3),@GONGHAO)+' 职位：'+@ZHIWEI+' 部门：'+@BUMEN
FETCH NEXT FROM[CUR_T_员工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
END
GO

执行输出如下：

第2步：利用游标进行数据更新

若需修改数据，声明游标时需使用 FOR UPDATE。在循环中使用 WHERE CURRENT OF cursor_name 定位当前行进行更新。

• 风险提醒：批量更新操作前务必备份数据或确认测试环境，避免误改生产数据。
• 代码示例：

USE SQL_SYSTEM 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TR_UPDATE_YG 
DECLARE CUR_T_YG CURSOR FOR
SELECT YG.姓名 ,YG.工号 ,YG.职位 ,YG.部门 FROM[T_yuangongxinxi]AS YG FOR UPDATE 
OPEN GLOBAL CUR_T_YG
COMMIT TRANSACTION TR_UPDATE_YG
GO
/* 更新操作 */
PRINT'当前有'+CONVERT(VARCHAR(3),@@CURSOR_ROWS)+'条数据行。'
FETCH NEXT FROM[CUR_T_YG]
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE[T_yuangongxinxi] 
SET[工号] =[工号]+100
WHERE CURRENT OF CUR_T_YG
FETCH NEXT FROM[CUR_T_YG] 
END
GO
/* 查看更新后的结果 */
SELECT * FROM [T_yuangongxinxi]

更新后数据验证如下：

注意：操作完成后，务必执行 CLOSE 和 DEALLOCATE 以释放资源。

三、存储过程 (PROCEDURE)

存储过程是预编译的 SQL 集合，支持参数传递，可用于封装复杂的业务逻辑。

第1步：无参数存储过程

• 目的：查询某张表的所有数据，并演示如何将执行结果插入新表。

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_SELECT_T
WITH RECOMPILE 
AS
SELECT* FROM[T_yuangongxinxi] 
GO
/* 执行 */
EXECUTE PRO_SELECT_T
GO

执行结果如下：

可以将执行结果导入新表：INSERT INTO AA EXECUTE PRO_SELECT_T。

第2步：带输出参数的存储过程

• 目的：修改员工职位，并返回修改前后的状态信息。
• 关键点：参数定义时需标记为 OUTPUT，调用时也需指定 OUTPUT。

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_DAUP_ZHIWEI
@GONGHAO INT, @ZHIWEI NVARCHAR(10), @RETURNS NVARCHAR(50) OUTPUT
AS
BEGIN
DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR(10),@XINGMING NVARCHAR(3)
SELECT @QIAN_RETURN_ZHIWEI =AA.职位 ,@XINGMING =AA.姓名 
FROM[AA] WHERE AA.工号 =@GONGHAO 
UPDATE[AA] SET[职位] =@ZHIWEI WHERE AA.工号 =@GONGHAO 
SET @RETURNS = '已经成功将工号为：【'+CONVERT(VARCHAR(3),@GONGHAO)+'】,姓名为：【'+@XINGMING+'】,职位【'+
@QIAN_RETURN_ZHIWEI+'】更新为：【'+@ZHIWEI
END
/* 调用示例 */
DECLARE @PRINTF NVARCHAR(50);
EXECUTE PRO_DAUP_ZHIWEI 101,'SQL工程师',@PRINTF OUTPUT 
SELECT @PRINTF AS '更新消息'

执行反馈结果如下：

四、触发器 (TRIGGER)

触发器是一种特殊的存储过程，它在特定的数据库事件（如 INSERT、UPDATE、DELETE）发生时自动执行。分为 DDL 触发器和 DML 触发器。

第1步：DDL 触发器保护表

• 目的：防止用户删除指定的表。

USE SQL_SYSTEM 
GO
CREATE TRIGGER DROP_T
ON DATABASE FOR DROP_TABLE 
AS
BEGIN
ROLLBACK TRANSACTION 
PRINT'不能删除的,因为我已经添加了触发保护！'
END
GO
/* 尝试删除会失败 */
DROP TABLE AA

触发拦截效果如下：

第2步：DML 触发器审计数据更新

DML 触发器在处理数据变更时，可以访问两个虚拟表：inserted（保存新增或更新后的数据）和 deleted（保存旧数据或被删除的数据）。这对于数据审计非常有用。

• 原理图示：

- Insert 操作：

- Delete 操作：

- Update 操作：

第3步：编写 Update 触发器

• 目的：在更新员工职位时，自动将更新前和更新后的数据分别保存到历史记录表中。
• 代码逻辑：

USE [SQL_SYSTEM]
GO
CREATE TRIGGER [dbo].[UPDATE_T]
ON [dbo].[AA] INSTEAD OF UPDATE
AS
BEGIN
/* 将新数据存入历史表 */
INSERT INTO T_UPDATE_HOU SELECT* FROM[inserted] 
/* 将旧数据存入历史表 */
INSERT INTO T_UPDATE_QIAN SELECT* FROM[deleted]
PRINT'更新完毕，更新前的数据已经写入到【T_UPDATE_QIAN】，更新后的数据插入到【T_UPDATE_HOU】。'
END
GO
/* 触发更新 */
UPDATE[AA] SET AA.职位 ='SQL高级工程师' WHERE AA.工号=101
GO
/* 检查结果 */
SELECT* FROM[T_UPDATE_HOU] -- 显示更新后的数据
SELECT* FROM[T_UPDATE_QIAN] -- 显示更新前的数据

查看更新后的结果表：

查看更新前的结果表：

以上就是 SQL Server 自定义函数、存储过程与触发器应用的详细内容，更多关于数据库开发优化的资料请关注本站其它相关文章！