SQL Server存储过程(数据库引擎)使用详解


    目录
  • 一、背景知识
    • 1.1、使用存储过程的好处
    • 1.2、存储过程的类型
  • 二、创建存储过程
  • 三、修改存储过程
  • 四、删除存储过程
  • 五、执行存储过程
    • 5.1、建议
    • 5.2、使用 Transact-SQL执行存储过程
  • 六、授予对存储过程的权限
    • 6.1、授予对存储过程的权限
    • 6.2、授予对架构中所有存储过程的权限
  • 总结

    一、背景知识
    SQL Server 中的存储过程是一组一个或多个 Transact-SQL 语句的引用。过程类似于其他编程语言中的构造，因为它们可以：
    
  • 接受输入参数并以输出参数的形式向调用程序返回多个值。
  • 包含在数据库中执行操作的编程语句。其中包括调用其他过程。
  • 向调用程序返回状态值，以指示成功或失败（以及失败的原因）。

    1.1、使用存储过程的好处
    （1）减少服务器/客户端网络流量。
    过程中的命令作为单批代码执行。这可以显著减少服务器和客户端之间的网络流量，因为只有执行过程的调用才会通过网络发送。如果没有过程提供的代码封装，每一行代码都必须跨网络。
    （2）更强的安全性。
    多个用户和客户端程序可以通过一个过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限也是如此。该过程控制执行哪些流程和活动，并保护基础数据库对象。这消除了在单个对象级别授予权限的要求，并简化了安全层。
    （3）可以在 CREATE PROCEDURE 语句中指定 EXECUTE AS 子句，以启用模拟其他用户，或者使用户或应用程序能够执行某些数据库活动，而无需对基础对象和命令具有直接权限。
    （4）通过网络调用过程时，只有执行过程的调用可见。因此，恶意用户无法查看表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
    （5）使用过程参数有助于防范 SQL 注入攻击。由于参数输入被视为文本值而不是可执行代码，因此攻击者更难将命令插入过程内的 Transact-SQL 语句并危及安全性。
    （6）过程可以加密，有助于混淆源代码。
    （7）代码的重用。
    任何重复数据库操作的代码都是过程中封装的完美候选项。这消除了对相同代码的不必要重写，减少了代码不一致，并允许拥有必要权限的任何用户或应用程序访问和执行代码。
    （8）更易于维护。
    当客户端应用程序调用过程并将数据库操作保留在数据层中时，只有过程必须针对基础数据库中的任何更改进行更新。应用层保持独立，不必知道对数据库布局、关系或进程的任何更改。
    （9）改进的性能。
    默认情况下，过程在第一次执行时进行编译，并创建一个在后续执行中重复使用的执行计划。由于查询处理器不必创建新计划，因此处理该过程所需的时间通常更少。如果过程引用的表或数据发生了重大更改，则预编译计划实际上可能会导致过程执行速度变慢。在这种情况下，重新编译过程并强制使用新的执行计划可以提高性能。
    1.2、存储过程的类型
    （1）User-defined。
    可以在User-defined数据库中或在除 Resource 数据库之外的所有系统数据库中创建用户定义过程。
    （2）Temporary。
    Temporary过程是用户定义过程的一种形式。临时过程类似于永久过程，只是临时过程存储在 tempdb 中。有两种类型的临时过程：本地和全局。它们在名称、可见性和可用性方面彼此不同。地方临时程序的名称的第一个字符为一个数字符号（#）;它们仅对当前用户连接可见，并且在连接关闭时将被删除。全局临时程序有两个数字符号 （##） 作为其名称的前两个字符;创建后，任何用户都可以看到它们，并且使用该过程在最后一个会话结束时将其删除。
    （3）System。
    System过程包含在 SQL Server 中。它们以物理方式存储在内部隐藏的资源数据库中，并在逻辑上出现在每个系统和用户定义数据库的 sys 模式中。此外，msdb 数据库还包含 dbo 架构中用于计划警报和作业的系统存储过程。由于系统过程以前缀 sp_ 开头，因此建议您在命名用户定义过程时不要使用此前缀。
    （4）Extended User-Defined。
    Extended User-Defined过程允许使用编程语言（如 C）创建外部例程。这些过程是 SQL Server 实例可以动态加载和运行的 DLL。
    二、创建存储过程
    需要数据库中的“创建过程”权限，以及对在其中创建过程的架构的“更改”权限。
    示例：使用不同的过程名称创建存储过程。
    
USE AdventureWorks;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO

    要运行该过程，执行如下指令：
    
EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
-- Or  
EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO

    三、修改存储过程
    修改存储过程具有如下限制：
    不能将事务处理 SQL 存储过程修改为 CLR 存储过程，反之亦然。
    如果以前的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的，则仅当这些选项包含在 ALTER PROCEDURE 语句中时，才会启用这些选项。
    需要的权限：需要对过程具有“更改过程”权限。
    使用示例：
    （1）创建的过程返回 Adventure Works Cycle 数据库中所有供应商的名称、他们提供的产品、他们的信用评级和可用性。
    
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL   
    DROP PROCEDURE Purchasing.uspVendorAllInfo;  
GO  
CREATE PROCEDURE Purchasing.uspVendorAllInfo  
WITH EXECUTE AS CALLER  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS Vendor, p.Name AS 'Product name',   
      v.CreditRating AS 'Rating',   
      v.ActiveFlag AS Availability  
    FROM Purchasing.Vendor v   
    INNER JOIN Purchasing.ProductVendor pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product p  
      ON pv.ProductID = p.ProductID   
    ORDER BY v.Name ASC;  
GO

    注意：删除并重新创建现有存储过程会删除已显式授予该存储过程的权限。请改用 ALTER。
    （2）修改了该过程。删除该子句并修改过程的主体，以仅返回提供指定产品的供应商。和函数自定义结果集的外观。
    
ALTER PROCEDURE Purchasing.uspVendorAllInfo  
    @Product varchar(25)   
AS  
    SET NOCOUNT ON;  
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name',   
    'Rating' = CASE v.CreditRating   
        WHEN 1 THEN 'Superior'  
        WHEN 2 THEN 'Excellent'  
        WHEN 3 THEN 'Above average'  
        WHEN 4 THEN 'Average'  
        WHEN 5 THEN 'Below average'  
        ELSE 'No rating'  
        END  
    , Availability = CASE v.ActiveFlag  
        WHEN 1 THEN 'Yes'  
        ELSE 'No'  
        END  
    FROM Purchasing.Vendor AS v   
    INNER JOIN Purchasing.ProductVendor AS pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID   
    WHERE p.Name LIKE @Product  
    ORDER BY v.Name ASC;  
GO

    要运行修改后的存储过程执行以下：
    
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';  
GO

    四、删除存储过程
    限制：删除过程可能会导致依赖对象和脚本在对象和脚本未更新以反映过程的删除时失败。但是，如果创建了同名和相同参数的新过程来替换已删除的过程，则引用它的其他对象仍将成功处理。
    权限：需要对过程所属的架构具有 ALTER 权限，或对过程具有 CONTROL 权限。
    使用示例：
    （1）获取要在当前数据库中删除的存储过程的名称。
    
SELECT name AS procedure_name
    , SCHEMA_NAME(schema_id) AS schema_name
    , type_desc
    , create_date
    , modify_date
FROM sys.procedures;

    （2）从当前数据库中删除的存储过程。
    
DROP PROCEDURE [<stored procedure name>];
GO

    五、执行存储过程
    有两种不同的方法来执行存储过程。第一种也是最常见的方法是让应用程序或用户调用该过程。第二种方法是将过程设置为在 SQL Server 实例启动时自动运行。当应用程序或用户调用过程时，将在调用中显式声明 Transact-SQL EXECUTE 或 EXEC 关键字。如果该过程是 Transact-SQL 批处理中的第一个语句，则可以在没有 EXEC 关键字的情况下调用和执行该过程。
    限制：
    
  • 匹配系统过程名称时使用调用数据库排序规则。因此，在过程调用中始终使用系统过程名称的确切大小写。
  • 如果用户定义过程与系统过程同名，则用户定义过程可能永远不会执行。

    5.1、建议
    （1）执行系统存储过程。
    系统过程以前缀ysy开头。由于它们在逻辑上出现在所有用户和系统定义的数据库中，因此可以从任何数据库执行它们，而不必完全限定过程名称。但是，建议使用架构名称对所有系统过程名称进行架构限定，以防止名称冲突。下面的示例演示调用系统过程的建议方法。
    
EXEC sys.sp_who;

    （2）执行用户定义的存储过程。
    执行用户定义的过程时，建议使用架构名称限定过程名称。这种做法可以稍微提高性能，因为数据库引擎不必搜索多个架构。如果数据库在多个架构中具有同名的过程，它还可以防止执行错误的过程。
    
USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;
GO

    或者
    
EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO

    如果指定了非限定的用户定义过程，数据库引擎将按以下顺序搜索该过程：
    
  • 当前数据库的架构。
  • 调用方的默认架构（如果它是在批处理中还是在动态 SQL 中执行）。或者，如果非限定过程名称出现在另一个过程定义的正文中，则接下来将搜索包含此其他过程的架构。
  • 当前数据库中的架构。

    （3）自动执行存储过程。
    每次 SQL Server 启动时都会执行标记为自动执行的过程，并在该启动过程中恢复数据库。将过程设置为自动执行对于执行数据库维护操作或使过程作为后台进程连续运行非常有用。
    自动执行的过程使用与 sysadmin 固定服务器角色成员相同的权限进行操作。该过程生成的任何错误消息都将写入 SQL Server 错误日志。
    可以拥有的启动过程数量没有限制，但请注意，每个启动过程在执行时都会消耗一个工作线程。如果必须在启动时执行多个过程，但不需要并行执行它们，请将一个过程设置为启动过程，并让该过程调用其他过程。这仅使用一个工作线程。
    （4）设置、清除和控制自动执行。
    只有系统管理员 才能将过程标记为自动执行。此外，该过程必须位于数据库中，并且不能具有输入或输出参数。
    使用sp_procoption可以：
    将现有过程指定为启动过程。
    停止在 SQL Server 启动时执行过程。
    5.2、使用 Transact-SQL执行存储过程
    （1）示例一，执行存储过程：示如何执行需要一个参数的存储过程。该示例使用指定为参数的值 6 执行存储过程。
    
USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

    （2）示例二，设置或清除自动执行的过程：启动过程必须位于数据库中，并且不能包含 INPUT 或 OUTPUT 参数。当恢复所有数据库并在启动时记录“恢复已完成”消息时，存储过程的执行将开始。
    
EXEC sp_procoption @ProcName = N'<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

    （3）示例三，阻止过程自动执行：使用 sp_procoption 停止过程自动执行。
    
EXEC sp_procoption @ProcName = N'<procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

    六、授予对存储过程的权限
    可以将权限授予数据库中的现有用户、数据库角色或应用程序角色。
    授予者（或使用 AS 选项指定的主体）必须具有具有 GRANT OPTION 的权限本身，或者具有暗示要授予的权限的更高权限。需要对过程所属的架构具有 ALTER 权限，或对过程具有 CONTROL 权限。
    6.1、授予对存储过程的权限
    示例：向应用程序角色授予对存储过程的权限。
    
USE AdventureWorks2012;   
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo  
    TO Recruiting11;  
GO

    6.2、授予对架构中所有存储过程的权限
    示例：向架构中存在或将要存在的所有存储过程授予应用程序角色的权限。
    
USE AdventureWorks2012;   
GRANT EXECUTE ON SCHEMA::HumanResources
    TO Recruiting11;  
GO

    总结
    不要从自动执行的过程返回任何结果集。由于该过程由 SQL Server 而不是应用程序或用户执行，因此结果集无处可去。
相关文章！