存储过程、函数与动态 SQL
6.1 存储过程
存储过程（Stored Procedure）是预编译的 T-SQL 代码块，存储在数据库中，可被反复调用。相比直接执行 SQL，存储过程具有执行计划复用、安全性隔离、网络流量减少等优势。
6.1.1 创建与调用
-- 基本存储过程
CREATE PROCEDURE dbo.usp_GetOrdersByCustomer
@CustomerID INT,
@StartDate DATE = NULL, -- 可选参数（有默认值）
@EndDate DATE = NULL,
@MaxRows INT = 100,
@OrderCount INT OUTPUT -- 输出参数
AS
BEGIN
SET NOCOUNT ON; -- 禁止返回 "X rows affected" 消息，提升性能
-- 参数默认值处理
SET @StartDate = ISNULL(@StartDate, DATEADD(YEAR, -1, GETDATE()));
SET @EndDate = ISNULL(@EndDate, GETDATE());
-- 主查询
SELECT TOP (@MaxRows)
o.OrderID,
o.OrderDate,
o.TotalAmount,
o.Status,
c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.CustomerID = @CustomerID
AND o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY o.OrderDate DESC;
-- 设置输出参数
SELECT @OrderCount = COUNT(*)
FROM Orders
WHERE CustomerID = @CustomerID
AND OrderDate BETWEEN @StartDate AND @EndDate;
RETURN 0; -- 返回值（仅用于状态码）
END;
GO
-- 调用存储过程
DECLARE @Count INT;
EXEC dbo.usp_GetOrdersByCustomer
@CustomerID = 1001,
@StartDate = '2024-01-01',
@EndDate = '2024-12-31',
@MaxRows = 50,
@OrderCount = @Count OUTPUT;
PRINT '订单总数：' + CAST(@Count AS VARCHAR);
-- 修改和删除
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer ... ;
DROP PROCEDURE dbo.usp_GetOrdersByCustomer;
-- 安全写法：
DROP PROCEDURE IF EXISTS dbo.usp_GetOrdersByCustomer;
6.1.2 TRY…CATCH 完整错误处理
CREATE PROCEDURE dbo.usp_TransferFunds
@FromAccountID INT,
@ToAccountID INT,
@Amount DECIMAL(12,2)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- 任何错误立即回滚整个事务（推荐）
-- 参数验证
IF @Amount <= 0
THROW 50001, '转账金额必须大于0', 1;
IF @FromAccountID = @ToAccountID
THROW 50002, '转出和转入账户不能相同', 1;
BEGIN TRY
BEGIN TRANSACTION;
-- 检查余额（使用 UPDLOCK 防止并发下的超额扣款）
DECLARE @CurrentBalance DECIMAL(12,2);
SELECT @CurrentBalance = Balance
FROM Accounts WITH (UPDLOCK, ROWLOCK)
WHERE AccountID = @FromAccountID;
IF @CurrentBalance IS NULL
THROW 50003, '转出账户不存在', 1;
IF @CurrentBalance < @Amount
THROW 50004, '账户余额不足', 1;
-- 执行转账
UPDATE Accounts
SET Balance = Balance - @Amount,
UpdatedAt = GETDATE()
WHERE AccountID = @FromAccountID;
UPDATE Accounts
SET Balance = Balance + @Amount,
UpdatedAt = GETDATE()
WHERE AccountID = @ToAccountID;
IF @@ROWCOUNT = 0
THROW 50005, '转入账户不存在', 1;
-- 记录流水
INSERT INTO Transactions (FromAccountID, ToAccountID, Amount, TranDate)
VALUES (@FromAccountID, @ToAccountID, @Amount, GETDATE());
COMMIT TRANSACTION;
PRINT '转账成功';
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
-- 记录错误
INSERT INTO dbo.ErrorLog (
ErrorNumber, ErrorSeverity, ErrorState,
ErrorProcedure, ErrorLine, ErrorMessage, ErrorTime
)
VALUES (
ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
);
THROW; -- 将错误传回调用方
END CATCH;
END;
GO
6.1.3 执行计划重用与 WITH RECOMPILE
-- 查看存储过程的执行计划缓存信息
SELECT
p.name AS ProcName,
qs.execution_count,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS AvgMs,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.cached_time,
qp.query_plan
FROM sys.procedures p
JOIN sys.dm_exec_procedure_stats qs ON p.object_id = qs.object_id
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE p.name LIKE 'usp_%'
ORDER BY qs.total_elapsed_time DESC;
-- 手动清除某个存储过程的缓存计划（用于测试）
DECLARE @PlanHandle VARBINARY(64);
SELECT @PlanHandle = plan_handle
FROM sys.dm_exec_procedure_stats
WHERE object_id = OBJECT_ID('dbo.usp_GetOrdersByCustomer');
DBCC FREEPROCCACHE(@PlanHandle); -- 清除指定计划
-- DBCC FREEPROCCACHE; -- 清除全部（⚠️ 慎用于生产）
6.2 用户自定义函数（UDF）
6.2.1 标量函数（Scalar Function）
标量函数返回单个值，最大的坑是：在查询中逐行调用标量函数会使查询串行化，完全禁用并行计划，严重影响性能。
-- 创建标量函数
CREATE FUNCTION dbo.fn_FormatPhone
(
@Phone NVARCHAR(20)
)
RETURNS NVARCHAR(30)
WITH SCHEMABINDING -- 绑定模式：函数执行期间相关对象不能被修改
AS
BEGIN
-- 处理 NULL
IF @Phone IS NULL RETURN NULL;
-- 去除非数字字符
DECLARE @Cleaned NVARCHAR(20) = '';
DECLARE @i INT = 1;
WHILE @i <= LEN(@Phone)
BEGIN
IF SUBSTRING(@Phone, @i, 1) BETWEEN '0' AND '9'
SET @Cleaned += SUBSTRING(@Phone, @i, 1);
SET @i += 1;
END;
-- 格式化：138-1234-5678
IF LEN(@Cleaned) = 11
RETURN LEFT(@Cleaned, 3) + '-' + SUBSTRING(@Cleaned, 4, 4) + '-' + RIGHT(@Cleaned, 4);
RETURN @Phone; -- 无法格式化则原样返回
END;
GO
-- 调用
SELECT dbo.fn_FormatPhone('13812345678'); -- 138-1234-5678
SELECT dbo.fn_FormatPhone('+86 138-1234-5678'); -- 138-1234-5678
-- ⚠️ 标量函数的性能陷阱：
SELECT CustomerID, dbo.fn_FormatPhone(Phone) AS FormattedPhone
FROM Customers; -- 对每一行调用函数，百万行 = 百万次执行！
-- 解决方案：SQL Server 2019+ 的内联标量函数（Inlined Scalar UDF）
-- 满足条件的标量函数会被自动内联为表达式，恢复并行
-- 查看函数是否被内联
SELECT is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.fn_FormatPhone');
6.2.2 内联表值函数（Inline TVF）
内联 TVF 是最推荐的函数类型，本质上是参数化视图，优化器可以完全内联，与直接写 SQL 性能相同。
-- 内联表值函数（只有一条 RETURN SELECT 语句）
CREATE FUNCTION dbo.fn_GetCustomerOrders
(
@CustomerID INT,
@StartDate DATE,
@EndDate DATE
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
SELECT
o.OrderID,
o.OrderDate,
o.TotalAmount,
o.Status,
c.CustomerName,
c.Email
FROM dbo.Orders o
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
WHERE o.CustomerID = @CustomerID
AND o.OrderDate BETWEEN @StartDate AND @EndDate
);
GO
-- 调用（可以在 FROM 子句中使用，性能极佳）
SELECT * FROM dbo.fn_GetCustomerOrders(1001, '2024-01-01', '2024-12-31')
WHERE TotalAmount > 1000
ORDER BY OrderDate DESC;
-- 与 CROSS APPLY 结合（批量处理）
SELECT c.CustomerName, o.*
FROM dbo.Customers c
CROSS APPLY dbo.fn_GetCustomerOrders(c.CustomerID, '2024-01-01', '2024-12-31') o
WHERE o.TotalAmount > 5000;
6.2.3 多语句表值函数（Multi-Statement TVF）
-- 多语句 TVF：比内联 TVF 灵活，但优化器无法内联，需谨慎使用
CREATE FUNCTION dbo.fn_GetTopCustomers
(
@N INT,
@RegionID INT = NULL
)
RETURNS @Result TABLE (
Rank INT,
CustomerID INT,
CustomerName NVARCHAR(100),
TotalAmount DECIMAL(12,2),
OrderCount INT
)
AS
BEGIN
INSERT INTO @Result
SELECT TOP (@N)
ROW_NUMBER() OVER (ORDER BY SUM(o.TotalAmount) DESC),
c.CustomerID,
c.CustomerName,
SUM(o.TotalAmount),
COUNT(o.OrderID)
FROM dbo.Customers c
JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE (@RegionID IS NULL OR c.RegionID = @RegionID)
GROUP BY c.CustomerID, c.CustomerName
ORDER BY SUM(o.TotalAmount) DESC;
RETURN;
END;
GO
6.3 触发器
6.3.1 DML 触发器（After / Instead Of）
-- AFTER 触发器：在 DML 之后执行
CREATE TRIGGER dbo.trg_Orders_AfterInsert
ON dbo.Orders
AFTER INSERT -- 也可以是 UPDATE, DELETE, 或组合: AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- inserted 和 deleted 是触发器内的虚拟表
-- INSERT → inserted 含新行
-- DELETE → deleted 含旧行
-- UPDATE → inserted 含新行，deleted 含旧行
-- 检查插入的订单金额是否异常
IF EXISTS (SELECT 1 FROM inserted WHERE TotalAmount < 0)
BEGIN
RAISERROR('订单金额不能为负数', 16, 1);
ROLLBACK TRANSACTION; -- 触发器内的回滚会回滚整个触发器+触发DML的事务
RETURN;
END;
-- 记录审计日志（同时处理批量插入）
INSERT INTO dbo.OrderAuditLog (OrderID, Action, ActionTime, ActionBy)
SELECT i.OrderID, 'INSERT', GETDATE(), SYSTEM_USER
FROM inserted i;
-- 更新客户最后订单时间（关联更新）
UPDATE c
SET c.LastOrderDate = GETDATE(),
c.TotalOrderCount = c.TotalOrderCount + 1
FROM dbo.Customers c
JOIN inserted i ON c.CustomerID = i.CustomerID;
END;
GO
-- UPDATE 触发器：使用 UPDATE() 函数检测特定列是否被修改
CREATE TRIGGER dbo.trg_Products_AfterUpdate
ON dbo.Products
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- 只在 Price 列被修改时执行
IF UPDATE(Price)
BEGIN
INSERT INTO dbo.PriceHistory (ProductID, OldPrice, NewPrice, ChangedAt)
SELECT
d.ProductID,
d.Price AS OldPrice,
i.Price AS NewPrice,
GETDATE()
FROM deleted d
JOIN inserted i ON d.ProductID = i.ProductID
WHERE d.Price <> i.Price; -- 排除价格未实际变化的情况
END;
END;
GO
-- INSTEAD OF 触发器：替代触发它的 DML 操作
-- 常用于：可更新视图、实现自定义逻辑
CREATE TRIGGER dbo.trg_vwOrders_InsteadOfDelete
ON dbo.vwOrders -- 针对视图
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
-- 软删除：不真正删除，而是标记为已删除
UPDATE o
SET o.IsDeleted = 1, o.DeletedAt = GETDATE()
FROM dbo.Orders o
JOIN deleted d ON o.OrderID = d.OrderID;
END;
GO
6.3.2 DDL 触发器（防止意外 DDL 变更）
-- DDL 触发器：在服务器或数据库级别监控 DDL 操作
CREATE TRIGGER dbo.trg_PreventDropTable
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, DROP_INDEX
AS
BEGIN
-- 获取 DDL 事件详细信息
DECLARE @EventData XML = EVENTDATA();
DECLARE @EventType NVARCHAR(50) = @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)');
DECLARE @ObjectName NVARCHAR(100) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)');
DECLARE @LoginName NVARCHAR(100) = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)');
DECLARE @TSQLCommand NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)');
-- 记录所有 DDL 操作
INSERT INTO dbo.DDLAuditLog (EventType, ObjectName, LoginName, TSQLCommand, EventTime)
VALUES (@EventType, @ObjectName, @LoginName, @TSQLCommand, GETDATE());
-- 禁止非 DBA 账号的 DROP TABLE（在工作时间段）
IF @EventType = 'DROP_TABLE'
AND SYSTEM_USER NOT IN ('sa', 'DBAAdmin')
AND DATEPART(HOUR, GETDATE()) BETWEEN 8 AND 20
BEGIN
RAISERROR('工作时间禁止 DROP TABLE 操作，请联系 DBA', 16, 1);
ROLLBACK;
END;
END;
GO
-- 禁用/启用 DDL 触发器
DISABLE TRIGGER dbo.trg_PreventDropTable ON DATABASE;
ENABLE TRIGGER dbo.trg_PreventDropTable ON DATABASE;
6.4 动态 SQL 与 SQL 注入防护
6.4.1 sp_executesql：参数化动态 SQL
-- ❌ 危险做法：字符串拼接（SQL注入漏洞）
DECLARE @TableName NVARCHAR(50) = 'Orders';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM ' + @TableName;
EXEC (@SQL); -- 如果 @TableName = "Orders; DROP TABLE Orders;--" 则灾难！
-- ✅ 安全做法1：sp_executesql 参数化
DECLARE @CustomerID INT = 1001;
DECLARE @SQL NVARCHAR(MAX) = N'
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustID
AND Status = @Status';
EXEC sp_executesql
@SQL,
N'@CustID INT, @Status TINYINT', -- 参数定义
@CustID = @CustomerID,
@Status = 1;
-- ✅ 安全做法2：表名/列名用 QUOTENAME 转义（防止注入）
DECLARE @TableName NVARCHAR(50) = N'Orders'; -- 来自用户输入
DECLARE @ColName NVARCHAR(50) = N'OrderDate'; -- 来自用户输入
-- 验证表/列是否真实存在（白名单校验）
IF NOT EXISTS (
SELECT 1 FROM sys.tables WHERE name = @TableName AND schema_id = SCHEMA_ID('dbo')
) THROW 50010, '无效的表名', 1;
IF NOT EXISTS (
SELECT 1 FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.' + @TableName) AND name = @ColName
) THROW 50011, '无效的列名', 1;
SET @SQL = N'SELECT TOP 100 ' + QUOTENAME(@ColName) +
N' FROM ' + QUOTENAME('dbo') + '.' + QUOTENAME(@TableName);
EXEC sp_executesql @SQL;
6.4.2 动态 SQL 实战：通用查询生成器
CREATE PROCEDURE dbo.usp_DynamicSearch
@TableName NVARCHAR(100),
@WhereClause NVARCHAR(MAX) = NULL, -- 调用方负责安全；或用结构化参数
@OrderBy NVARCHAR(100) = NULL,
@PageNumber INT = 1,
@PageSize INT = 20,
@TotalRows INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- 白名单校验
IF NOT EXISTS (
SELECT 1 FROM sys.tables
WHERE SCHEMA_NAME(schema_id) + '.' + name = @TableName
) THROW 50020, '非法表名', 1;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @CountSQL NVARCHAR(MAX);
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
-- 获取总行数
SET @CountSQL = N'SELECT @Cnt = COUNT(*) FROM ' + QUOTENAME(@TableName);
IF @WhereClause IS NOT NULL
SET @CountSQL += N' WHERE ' + @WhereClause;
EXEC sp_executesql @CountSQL, N'@Cnt INT OUTPUT', @Cnt = @TotalRows OUTPUT;
-- 分页查询
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName);
IF @WhereClause IS NOT NULL SET @SQL += N' WHERE ' + @WhereClause;
IF @OrderBy IS NOT NULL SET @SQL += N' ORDER BY ' + @OrderBy;
ELSE SET @SQL += N' ORDER BY (SELECT NULL)';
SET @SQL += N' OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY';
EXEC sp_executesql @SQL,
N'@Offset INT, @PageSize INT',
@Offset = @Offset,
@PageSize = @PageSize;
END;
GO
6.4.3 常见 SQL 注入场景与防御
-- 场景1：IN 子句的注入防御
-- 应用层传来一个逗号分隔的 ID 列表：'1,2,3'
-- 危险写法：直接拼接 'WHERE OrderID IN (' + @IDs + ')'
-- 安全写法：使用表值参数（TVP）或 STRING_SPLIT
CREATE TYPE dbo.IntList AS TABLE (Value INT NOT NULL);
CREATE PROCEDURE dbo.usp_GetOrdersByIDs
@OrderIDs dbo.IntList READONLY -- 表值参数
AS
BEGIN
SELECT o.*
FROM dbo.Orders o
JOIN @OrderIDs ids ON o.OrderID = ids.Value;
END;
-- 调用端（.NET）
-- var table = new DataTable();
-- table.Columns.Add("Value", typeof(int));
-- foreach (var id in orderIds) table.Rows.Add(id);
-- cmd.Parameters.AddWithValue("@OrderIDs", table);
-- 场景2：LIKE 模糊搜索注入防御
-- % _ [ 在 LIKE 中有特殊含义，需转义
DECLARE @SearchTerm NVARCHAR(100) = N'100% 成功'; -- 用户输入
-- 转义特殊字符
SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,
'[', '[[]'), '%', '[%]'), '_', '[_]'), '^', '[^]');
SELECT * FROM Products
WHERE ProductName LIKE '%' + @SearchTerm + '%' ESCAPE '\';
6.5 小结
| 对象 | 最佳实践 |
|---|---|
| 存储过程 | SET NOCOUNT ON + SET XACT_ABORT ON；TRY…CATCH 包裹事务 |
| 标量函数 | SQL 2019+ 可内联；否则避免在大表逐行调用 |
| 内联 TVF | 首选！性能等同直接 SQL，支持优化器统计 |
| 触发器 | 必须考虑批量操作（inserted/deleted 是集合，不是单行） |
| 动态 SQL | 始终用 sp_executesql 参数化；表名/列名用 QUOTENAME + 白名单校验 |
下一章：备份、恢复与日常运维
文章版权声明：除非注明，否则均为边学边练网络文章，版权归原作者所有
