创建存储过程

6 分钟

存储过程是 SQL Server 中用于封装业务逻辑和提高应用程序性能的最强大的工具之一。创建存储过程时，可以生成在服务器上执行的可重用代码块，从而减少网络流量和集中数据访问逻辑。

了解存储过程

存储过程是 SQL Server 作为单个单元存储和执行的 T-SQL 语句的已编译集合。与每次发送到服务器的计划外查询不同，存储过程是预编译和优化的，这意味着它们在后续执行时运行速度更快。

使用存储过程来封装复杂的业务逻辑，强制实施数据验证规则，并控制应用程序与数据库交互的方式。例如，在修改数据之前，可以创建存储过程来验证输入、应用业务规则和日志更改，而不是允许直接表访问。

性能优势来自查询计划缓存。使用计划外查询时，SQL Server 必须每次分析并优化每个查询。使用存储过程时，执行计划会在首次运行后缓存，从而减少重复作的开销。

创建基本存储过程

创建存储过程时，应以 CREATE PROCEDURE 语句开始，然后编写 T-SQL 逻辑。使用架构限定的标识符指定过程名称，从而提高清晰度和性能。

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

该 SET NOCOUNT ON 语句阻止有关受影响的行数的消息被发送到客户端。这可减少网络流量并提高性能，尤其是在过程执行多个语句时。

创建过程时，请使用 BEGIN 和 END 关键字清楚地定义过程正文。这使得代码更易于阅读，有助于防止以后添加或修改逻辑时出错。

使用参数

参数使存储过程灵活且可重用。定义输入参数以接受调用应用程序中的值，并输出参数以将值返回给调用方。

输入参数使用 @ 符号，后跟参数名称和数据类型。可以提供默认值以使参数可选：

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END

输出参数允许将值返回到调用应用程序。使用 OUTPUT 关键字定义它们：

CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
    
    RETURN 0;
END

调用包含输出参数的过程时，必须声明一个变量来接收值并使用 OUTPUT 语句中的 EXECUTE 关键字。

实现错误处理

可靠的存储过程包括错误处理来管理意外情况和维护数据完整性。使用 TRY...CATCH 块实现错误处理，这些块的工作方式类似于其他编程语言中的异常处理。

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END
        
        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

块 TRY 包含主逻辑，而 CATCH 块处理发生的任何错误。可以使用系统函数，例如 ERROR_MESSAGE()、ERROR_SEVERITY() 和 ERROR_STATE()，来捕获错误详细信息并将其传递给调用应用程序。

在回滚 CATCH 块中的事务之前，始终检查 @@TRANCOUNT。这可以防止在事务已完成或从未启动的情况下出现错误。

应用最佳做法

创建存储过程时，遵循已建立的最佳做法可确保它们可维护、安全且高性能。

使用架构限定的名称

对所有对象使用架构限定的名称。这样可以避免架构解析开销，消除歧义并提高性能：

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

实现参数验证

在过程开始时实现参数验证。当输入无效而不是处理错误数据时，会快速失败：

IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
    RAISERROR('CustomerID must be a positive integer.', 16, 1);
    RETURN -1;
END

避免 `SELECT *`

避免 SELECT * 在生产代码中。显式列出列以防止表结构更改并改进查询性能时出现问题：

-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders

-- Avoid
SELECT * FROM dbo.Orders

使用有意义的名称

使用描述过程用途的有意义的名称。包括指示操作的动词（Get、Insert、Update、Delete、Calculate）：

CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders

避免前缀`sp_`

不要对存储过程使用 sp_ 前缀。 SQL Server 为数据库中存储 master 的系统过程保留此前缀。使用命名过程 sp_时，SQL Server 会在检查当前数据库之前先进行搜索 master ，从而增加不必要的开销：

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

基于这些做法，可帮助你创建团队可以理解、维护和信任的存储过程，以便可靠地在生产环境中执行。

反馈

此页面是否有帮助？