SQL Server函数、游标、存储过程与触发器详解
一、函数类型解析
SQL Server 2008支持三种自定义函数：标量函数、内联表值函数与多语句表值函数。它们的共性与差异如下：
- 共性：均采用CREATE FUNCTION定义语法，包含RETURNS子句及AS关键字
- 差异：
- 标量函数返回单一数据值，表值函数返回表结构
- 标量与多语句函数需包含BEGIN...END结构，而内联函数无需
- 调用时标量函数需使用dbo.前缀
1. 标量函数示例
USE SQL_SYSTEM
GO
CREATE FUNCTION F_GET_ID(@NAME NVARCHAR(5))
RETURNS INT
AS
BEGIN
DECLARE @ID INT
SELECT @ID = 工号 FROM T_员工信息 WHERE 姓名 = @NAME
RETURN @ID
END
GO
SELECT 姓名, dbo.F_GET_ID('李异峰') AS 工号 FROM T_员工信息 WHERE 姓名='李异峰'
2. 内联表值函数
CREATE FUNCTION F_GET_ATTENDANCE(@DATE DATE)
RETURNS TABLE
AS
RETURN (
SELECT * FROM T_考勤 WHERE 日期 = @DATE
)
GO
SELECT * FROM F_GET_ATTENDANCE('2014-02-28')
3. 多语句表值函数
CREATE FUNCTION F_GET_FULL_RECORD(@DATE DATE)
RETURNS @RESULT TABLE
(
姓名 NVARCHAR(5), 工号 INT, 职位 NVARCHAR(10), 部门 NVARCHAR(5),
是否夜班 NCHAR(1), 日期 DATE, 上班时间 FLOAT
)
AS
BEGIN
INSERT INTO @RESULT
SELECT Y.姓名, Y.工号, Y.职位, Y.部门, K.夜班, K.日期, K.上班时间
FROM T_员工信息 Y
JOIN T_考勤 K ON Y.工号 = K.工号
WHERE K.日期 = @DATE
RETURN
END
GO
SELECT * FROM F_GET_FULL_RECORD('2014-02-28')
二、游标操作
适用于逐行处理复杂数据集，类似C语言指针机制。
1. 数据读取示例
DECLARE CUR_EMPLOYEE SCROLL CURSOR FOR
SELECT 姓名, 工号, 职位, 部门 FROM T_员工信息 ORDER BY 工号
OPEN GLOBAL CUR_EMPLOYEE
DECLARE @NAME NVARCHAR(3), @ID INT, @POS NVARCHAR(10), @DEPT NVARCHAR(8)
PRINT '总记录数：' + CAST(@@CURSOR_ROWS AS NVARCHAR)
FETCH FIRST FROM CUR_EMPLOYEE INTO @NAME, @ID, @POS, @DEPT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONCAT('姓名：', @NAME, ' 工号：', @ID, ' 职位：', @POS, ' 部门：', @DEPT)
FETCH NEXT FROM CUR_EMPLOYEE INTO @NAME, @ID, @POS, @DEPT
END
CLOSE CUR_EMPLOYEE
DEALLOCATE CUR_EMPLOYEE
2. 数据更新示例
DECLARE CUR_UPDATE CURSOR FOR
SELECT 姓名, 工号, 职位, 部门 FROM T_yuangongxinxi FOR UPDATE
OPEN GLOBAL CUR_UPDATE
DECLARE @OLD_POS NVARCHAR(10), @NAME NVARCHAR(3)
PRINT '当前记录数：' + CAST(@@CURSOR_ROWS AS NVARCHAR)
FETCH NEXT FROM CUR_UPDATE INTO @NAME, @ID, @OLD_POS, @DEPT
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE T_yuangongxinxi SET 工号 = 工号 + 100 WHERE CURRENT OF CUR_UPDATE
FETCH NEXT FROM CUR_UPDATE INTO @NAME, @ID, @OLD_POS, @DEPT
END
CLOSE CUR_UPDATE
DEALLOCATE CUR_UPDATE
三、存储过程设计
使用Transact-SQL编写的可重复使用的程序模块。
1. 无参数查询
CREATE PROCEDURE SP_SELECT_EMPLOYEE
WITH RECOMPILE
AS
SELECT * FROM T_yuangongxinxi
GO
EXEC SP_SELECT_EMPLOYEE
2. 带参数更新
CREATE PROCEDURE SP_UPDATE_POSITION
@ID INT, @NEW_POS NVARCHAR(10), @MSG NVARCHAR(50) OUTPUT
AS
BEGIN
DECLARE @OLD_POS NVARCHAR(10), @NAME NVARCHAR(3)
SELECT @OLD_POS = 职位, @NAME = 姓名 FROM T_yuangongxinxi WHERE 工号 = @ID
UPDATE T_yuangongxinxi SET 职位 = @NEW_POS WHERE 工号 = @ID
SET @MSG = CONCAT('工号', @ID, '的', @NAME, '职位已从', @OLD_POS, '变更为', @NEW_POS)
END
GO
DECLARE @OUTPUT NVARCHAR(50)
EXEC SP_UPDATE_POSITION 101, '高级工程师', @OUTPUT OUTPUT
SELECT @OUTPUT AS 操作结果
四、触发器应用
用于实现数据完整性约束，分为DDL与DML两类。
1. DDL触发器
CREATE TRIGGER TR_PREVENT_DROP
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
ROLLBACK
PRINT '禁止删除操作！'
END
GO
DROP TABLE AA
2. DML触发器
CREATE TRIGGER TR_LOG_UPDATE
ON AA INSTEAD OF UPDATE
AS
BEGIN
INSERT INTO T_UPDATE_HOU SELECT * FROM inserted
INSERT INTO T_UPDATE_QIAN SELECT * FROM deleted
PRINT '更新操作已记录'
END
GO
UPDATE AA SET 职位 = '高级工程师' WHERE 工号 = 101
SELECT * FROM T_UPDATE_HOU
SELECT * FROM T_UPDATE_QIAN
