MSSQL存储过程的功能和用法(简明扼要)

电玩女神 2024-02-05 13:35 37阅读 0赞

## 官方文档 ##

[存储过程（数据库引擎） - SQL Server | Microsoft Learn][- SQL Server _ Microsoft Learn]

[Transact-SQL 参考（数据库引擎） - SQL Server | Microsoft Learn][Transact-SQL _ - SQL Server _ Microsoft Learn]

## 定义 ##

存储过程可以用**编程语言的方法**来类比，有输入输出。区别是其使用SQL表达业务过程。

举个例子(**代码源自官方文档**)

这里 **use** 指定数据库名，**go** 是SYBASE和SQL Server中用来表示事物结束，提交并确认结果，相当于ORACLE的Commit。关键字 **procedure** 后边则是对应的过程的名称及参数，**as** 后是具体内容

USE AdventureWorks2022;  
    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中高亮的关键字

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

官方文档有很详细的说明哦。

[- SQL Server _ Microsoft Learn]: https://learn.microsoft.com/zh-cn/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16
[Transact-SQL _ - SQL Server _ Microsoft Learn]: https://learn.microsoft.com/zh-cn/sql/t-sql/language-reference?view=sql-server-ver16