CH09_存储过程

CH09_存储过程

本章目标

• 了解存储过程的优点
• 掌握常用的系统/扩展存储过程
• 使用存储过程封装业务逻辑
• 掌握存储过程错误处理

存储过程

什么是存储过程？

在 SQL Server 中，存储过程（Stored Procedure）是一种预编译的 SQL 脚本，它可以接受参数、执行操作，并可能返回结果集。存储过程在数据库服务器上被创建并保存，可以在需要时由用户或应用程序调用。使用存储过程可以提高性能、封装复杂的业务逻辑，并减少网络流量。

创建存储过程

要创建一个存储过程，你可以使用 CREATE PROCEDURE 语句。以下是一个简单的示例，展示了如何创建一个存储过程，该过程接受一个参数并返回符合该参数值的员工信息：

CREATE PROCEDURE GetEmployeeByLastName  
    @LastName NVARCHAR(50)  
AS  
BEGIN  
    SELECT FirstName, LastName, Department  
    FROM Employees  
    WHERE LastName = @LastName;  
END

执行存储过程

要执行存储过程，你可以使用 EXEC 或 EXECUTE 语句，并传递任何必要的参数。以下是执行上面创建的存储过程的示例：

EXEC GetEmployeeByLastName @LastName = 'Smith';

或者

EXECUTE GetEmployeeByLastName 'Smith';

修改存储过程

要修改存储过程，你可以使用 ALTER PROCEDURE 语句。

-- 修改存储过程  
ALTER PROCEDURE GetEmployeeByLastName  
    @LastName NVARCHAR(50),  
    @Department NVARCHAR(50)  
AS  
BEGIN  
    SELECT FirstName, LastName, Department  
    FROM Employees  
    WHERE LastName = @LastName AND Department = @Department;  
END  

删除存储过程

要删除存储过程，你可以使用 DROP PROCEDURE 语句。

-- 删除存储过程  
DROP PROCEDURE GetEmployeeByLastName;

在使用存储过程时，重要的是要考虑性能优化、错误处理、事务管理以及安全性等因素。此外，由于存储过程是在数据库服务器上执行的，因此它们可能会受到数据库管理员设置的权限和策略的限制。

存储过程的优点

• 性能提升：存储过程是预编译的，这意味着它们在首次执行时进行编译，并在随后的调用中使用已编译的计划，这通常比每次执行时都解析和编译 SQL 语句要快。
• 代码重用：存储过程可以在多个应用程序和查询中重复使用，减少了代码冗余。
• 安全性：通过限制对基础表的直接访问，存储过程可以帮助提高安全性。你还可以通过参数化查询来防止 SQL 注入攻击。
• 维护：如果你需要修改业务逻辑，只需要更改存储过程，而不需要更改所有调用该逻辑的应用程序代码。
• 减少网络流量：对于复杂的查询，存储过程只将结果集发送到客户端，而不是整个查询语句，这减少了网络上的数据传输量。

使用存储过程封装业务逻辑

使用 SQL 语句直接进行分页

-- 获取第 @PageIndex 页的数据，每页显示 @PageSize 条记录  
DECLARE @PageIndex INT = 2;  -- 当前页码  
DECLARE @PageSize INT = 10;  -- 每页记录数  
  
SELECT   
    FirstName,   
    LastName,   
    Department  
FROM   
    Employees  
ORDER BY   
    LastName, FirstName  
OFFSET (@PageIndex - 1) * @PageSize ROWS  
FETCH NEXT @PageSize ROWS ONLY;

使用存储过程进行分页

为了将分页逻辑封装在存储过程中，你可以创建一个存储过程，如下所示：

在 SQL Server 中，使用存储过程来实现分页功能是一种常见的做法。分页通常涉及到两个参数：当前页码（PageIndex）和每页显示的记录数（PageSize）。以下是一个示例，展示了如何使用存储过程来实现分页功能。

首先，创建一个存储过程，该过程接受当前页码和每页大小作为参数，并返回指定页的数据。

CREATE PROCEDURE GetEmployeesPaged   
    @PageIndex INT,	-- 当前页码  
    @PageSize INT	-- 每页记录数   
AS    
BEGIN    
    -- 计算跳过的记录数    
    DECLARE @SkipRecords INT = (@PageIndex - 1) * @PageSize;    
    
    -- 使用 ROW_NUMBER() 函数和 CTE（公用表表达式）来实现分页    
    WITH PagedEmployees AS (    
        SELECT     
            ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNum,    
            FirstName,    
            LastName,    
            Department    
        FROM Employees    
    )    
    SELECT     
        FirstName,    
        LastName,    
        Department    
    FROM PagedEmployees    
    WHERE RowNum > @SkipRecords    
    AND RowNum <= @SkipRecords + @PageSize; -- 限制返回的行数  
END

要执行这个存储过程并获取第 2 页的数据（每页显示 10 条记录），你可以使用以下 SQL 语句：

EXEC GetEmployeesPaged @PageIndex = 2, @PageSize = 10;

请注意，上述示例中的排序逻辑（ORDER BY LastName, FirstName）应该根据你的实际需求进行调整。此外，ROW_NUMBER() 函数的性能可能不如其他分页技术（如使用 SEEK 方法），尤其是在处理大量数据时。因此，对于大型数据集，你可能需要考虑其他优化策略。

此外，确保你的分页逻辑与你的应用程序中的其他部分（如前端界面或后端逻辑）协调一致，以便正确处理和显示分页数据。

存储过程错误处理

在SQL Server中，存储过程可以通过多种方式来处理错误。错误处理的主要目标是确保在发生错误时，数据库操作能够适当地回滚，并且应用程序能够获得关于错误的足够信息以便进行调试和问题解决。

以下是在SQL Server存储过程中实现错误处理的一些常见方法：

使用TRY…CATCH块

SQL Server 2005及更高版本支持TRY…CATCH块，用于捕获和处理T-SQL代码块中的运行时错误。

CREATE PROCEDURE SampleProcedure  
AS  
BEGIN  
    TRY  
        -- 尝试执行的代码  
        BEGIN TRAN -- 开始事务  
        -- ... 数据库操作 ...  

        COMMIT -- 提交事务  
    END TRY  
    BEGIN CATCH  
        -- 错误处理代码  
        IF @@TRANCOUNT > 0  
            ROLLBACK -- 发生错误时回滚事务  

        -- 获取错误信息  
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  

        SELECT   
            @ErrorMessage = ERROR_MESSAGE(),  
            @ErrorSeverity = ERROR_SEVERITY(),  
            @ErrorState = ERROR_STATE();  

        -- 记录错误信息或将其返回给调用者  
        -- 例如: RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    END CATCH;  
END;

使用RAISERROR语句

RAISERROR语句用于在存储过程中显式引发错误。你可以使用它来通知应用程序发生了错误，并提供有关错误的详细信息。

CREATE PROCEDURE SampleProcedure  
AS  
BEGIN  
    -- ... 数据库操作 ...  

    IF (某些条件)  
    BEGIN  
        RAISERROR ('发生了一个错误', 16, 1); -- 引发错误，并提供错误消息和严重性级别  
        -- 可能还需要回滚事务等操作  
    END  
END;

课后作业

作业1

省略…