一、背景知識

SQL Server 中的存儲過程是一組一個或多個 Transact-SQL 語句的引用。過程類似于其他編程語言中的構(gòu)造，因為它們可以：

• 接受輸入?yún)?shù)并以輸出參數(shù)的形式向調(diào)用程序返回多個值。
• 包含在數(shù)據(jù)庫中執(zhí)行操作的編程語句。其中包括調(diào)用其他過程。
• 向調(diào)用程序返回狀態(tài)值，以指示成功或失?。ㄒ约笆〉脑颍?/li>

1.1、使用存儲過程的好處

（1）減少服務(wù)器/客戶端網(wǎng)絡(luò)流量。
過程中的命令作為單批代碼執(zhí)行。這可以顯著減少服務(wù)器和客戶端之間的網(wǎng)絡(luò)流量，因為只有執(zhí)行過程的調(diào)用才會通過網(wǎng)絡(luò)發(fā)送。如果沒有過程提供的代碼封裝，每一行代碼都必須跨網(wǎng)絡(luò)。

（2）更強的安全性。
多個用戶和客戶端程序可以通過一個過程對基礎(chǔ)數(shù)據(jù)庫對象執(zhí)行操作，即使用戶和程序?qū)@些基礎(chǔ)對象沒有直接權(quán)限也是如此。該過程控制執(zhí)行哪些流程和活動，并保護基礎(chǔ)數(shù)據(jù)庫對象。這消除了在單個對象級別授予權(quán)限的要求，并簡化了安全層。

（3）可以在 CREATE PROCEDURE 語句中指定 EXECUTE AS 子句，以啟用模擬其他用戶，或者使用戶或應(yīng)用程序能夠執(zhí)行某些數(shù)據(jù)庫活動，而無需對基礎(chǔ)對象和命令具有直接權(quán)限。

（4）通過網(wǎng)絡(luò)調(diào)用過程時，只有執(zhí)行過程的調(diào)用可見。因此，惡意用戶無法查看表和數(shù)據(jù)庫對象名稱、嵌入自己的 Transact-SQL 語句或搜索關(guān)鍵數(shù)據(jù)。

（5）使用過程參數(shù)有助于防范 SQL 注入攻擊。由于參數(shù)輸入被視為文本值而不是可執(zhí)行代碼，因此攻擊者更難將命令插入過程內(nèi)的 Transact-SQL 語句并危及安全性。

（6）過程可以加密，有助于混淆源代碼。

（7）代碼的重用。
任何重復數(shù)據(jù)庫操作的代碼都是過程中封裝的完美候選項。這消除了對相同代碼的不必要重寫，減少了代碼不一致，并允許擁有必要權(quán)限的任何用戶或應(yīng)用程序訪問和執(zhí)行代碼。

（8）更易于維護。
當客戶端應(yīng)用程序調(diào)用過程并將數(shù)據(jù)庫操作保留在數(shù)據(jù)層中時，只有過程必須針對基礎(chǔ)數(shù)據(jù)庫中的任何更改進行更新。應(yīng)用層保持獨立，不必知道對數(shù)據(jù)庫布局、關(guān)系或進程的任何更改。

（9）改進的性能。
默認情況下，過程在第一次執(zhí)行時進行編譯，并創(chuàng)建一個在后續(xù)執(zhí)行中重復使用的執(zhí)行計劃。由于查詢處理器不必創(chuàng)建新計劃，因此處理該過程所需的時間通常更少。如果過程引用的表或數(shù)據(jù)發(fā)生了重大更改，則預(yù)編譯計劃實際上可能會導致過程執(zhí)行速度變慢。在這種情況下，重新編譯過程并強制使用新的執(zhí)行計劃可以提高性能。

1.2、存儲過程的類型

（1）User-defined。
可以在User-defined數(shù)據(jù)庫中或在除 Resource 數(shù)據(jù)庫之外的所有系統(tǒng)數(shù)據(jù)庫中創(chuàng)建用戶定義過程。

（2）Temporary。
Temporary過程是用戶定義過程的一種形式。臨時過程類似于永久過程，只是臨時過程存儲在 tempdb 中。有兩種類型的臨時過程：本地和全局。它們在名稱、可見性和可用性方面彼此不同。地方臨時程序的名稱的第一個字符為一個數(shù)字符號（#）;它們僅對當前用戶連接可見，并且在連接關(guān)閉時將被刪除。全局臨時程序有兩個數(shù)字符號 （##） 作為其名稱的前兩個字符;創(chuàng)建后，任何用戶都可以看到它們，并且使用該過程在最后一個會話結(jié)束時將其刪除。

（3）System。
System過程包含在 SQL Server 中。它們以物理方式存儲在內(nèi)部隱藏的資源數(shù)據(jù)庫中，并在邏輯上出現(xiàn)在每個系統(tǒng)和用戶定義數(shù)據(jù)庫的 sys 模式中。此外，msdb 數(shù)據(jù)庫還包含 dbo 架構(gòu)中用于計劃警報和作業(yè)的系統(tǒng)存儲過程。由于系統(tǒng)過程以前綴 sp_ 開頭，因此建議您在命名用戶定義過程時不要使用此前綴。

（4）Extended User-Defined。
Extended User-Defined過程允許使用編程語言（如 C）創(chuàng)建外部例程。這些過程是 SQL Server 實例可以動態(tài)加載和運行的 DLL。

二、創(chuàng)建存儲過程

需要數(shù)據(jù)庫中的“創(chuàng)建過程”權(quán)限，以及對在其中創(chuàng)建過程的架構(gòu)的“更改”權(quán)限。

示例：使用不同的過程名稱創(chuàng)建存儲過程。

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

要運行該過程，執(zhí)行如下指令：

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

三、修改存儲過程

修改存儲過程具有如下限制：

不能將事務(wù)處理 SQL 存儲過程修改為 CLR 存儲過程，反之亦然。

如果以前的過程定義是使用 WITH ENCRYPTION 或 WITH RECOMPILE 創(chuàng)建的，則僅當這些選項包含在 ALTER PROCEDURE 語句中時，才會啟用這些選項。

需要的權(quán)限：需要對過程具有“更改過程”權(quán)限。

使用示例：

（1）創(chuàng)建的過程返回 Adventure Works Cycle 數(shù)據(jù)庫中所有供應(yīng)商的名稱、他們提供的產(chǎn)品、他們的信用評級和可用性。

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

注意：刪除并重新創(chuàng)建現(xiàn)有存儲過程會刪除已顯式授予該存儲過程的權(quán)限。請改用 ALTER。

（2）修改了該過程。刪除該子句并修改過程的主體，以僅返回提供指定產(chǎn)品的供應(yīng)商。和函數(shù)自定義結(jié)果集的外觀。

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

要運行修改后的存儲過程執(zhí)行以下：

EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';  
GO

四、刪除存儲過程

限制：刪除過程可能會導致依賴對象和腳本在對象和腳本未更新以反映過程的刪除時失敗。但是，如果創(chuàng)建了同名和相同參數(shù)的新過程來替換已刪除的過程，則引用它的其他對象仍將成功處理。

權(quán)限：需要對過程所屬的架構(gòu)具有 ALTER 權(quán)限，或?qū)^程具有 CONTROL 權(quán)限。

使用示例：

（1）獲取要在當前數(shù)據(jù)庫中刪除的存儲過程的名稱。

SELECT name AS procedure_name
    , SCHEMA_NAME(schema_id) AS schema_name
    , type_desc
    , create_date
    , modify_date
FROM sys.procedures;

（2）從當前數(shù)據(jù)庫中刪除的存儲過程。

DROP PROCEDURE [<stored procedure name>];
GO

五、執(zhí)行存儲過程

有兩種不同的方法來執(zhí)行存儲過程。第一種也是最常見的方法是讓應(yīng)用程序或用戶調(diào)用該過程。第二種方法是將過程設(shè)置為在 SQL Server 實例啟動時自動運行。當應(yīng)用程序或用戶調(diào)用過程時，將在調(diào)用中顯式聲明 Transact-SQL EXECUTE 或 EXEC 關(guān)鍵字。如果該過程是 Transact-SQL 批處理中的第一個語句，則可以在沒有 EXEC 關(guān)鍵字的情況下調(diào)用和執(zhí)行該過程。

限制：

• 匹配系統(tǒng)過程名稱時使用調(diào)用數(shù)據(jù)庫排序規(guī)則。因此，在過程調(diào)用中始終使用系統(tǒng)過程名稱的確切大小寫。
• 如果用戶定義過程與系統(tǒng)過程同名，則用戶定義過程可能永遠不會執(zhí)行。

5.1、建議

（1）執(zhí)行系統(tǒng)存儲過程。
系統(tǒng)過程以前綴ysy開頭。由于它們在邏輯上出現(xiàn)在所有用戶和系統(tǒng)定義的數(shù)據(jù)庫中，因此可以從任何數(shù)據(jù)庫執(zhí)行它們，而不必完全限定過程名稱。但是，建議使用架構(gòu)名稱對所有系統(tǒng)過程名稱進行架構(gòu)限定，以防止名稱沖突。下面的示例演示調(diào)用系統(tǒng)過程的建議方法。

EXEC sys.sp_who;

（2）執(zhí)行用戶定義的存儲過程。
執(zhí)行用戶定義的過程時，建議使用架構(gòu)名稱限定過程名稱。這種做法可以稍微提高性能，因為數(shù)據(jù)庫引擎不必搜索多個架構(gòu)。如果數(shù)據(jù)庫在多個架構(gòu)中具有同名的過程，它還可以防止執(zhí)行錯誤的過程。

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;
GO

或者

EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO

如果指定了非限定的用戶定義過程，數(shù)據(jù)庫引擎將按以下順序搜索該過程：

• 當前數(shù)據(jù)庫的架構(gòu)。
• 調(diào)用方的默認架構(gòu)（如果它是在批處理中還是在動態(tài) SQL 中執(zhí)行）。或者，如果非限定過程名稱出現(xiàn)在另一個過程定義的正文中，則接下來將搜索包含此其他過程的架構(gòu)。
• 當前數(shù)據(jù)庫中的架構(gòu)。

（3）自動執(zhí)行存儲過程。
每次 SQL Server 啟動時都會執(zhí)行標記為自動執(zhí)行的過程，并在該啟動過程中恢復數(shù)據(jù)庫。將過程設(shè)置為自動執(zhí)行對于執(zhí)行數(shù)據(jù)庫維護操作或使過程作為后臺進程連續(xù)運行非常有用。
自動執(zhí)行的過程使用與 sysadmin 固定服務(wù)器角色成員相同的權(quán)限進行操作。該過程生成的任何錯誤消息都將寫入 SQL Server 錯誤日志。
可以擁有的啟動過程數(shù)量沒有限制，但請注意，每個啟動過程在執(zhí)行時都會消耗一個工作線程。如果必須在啟動時執(zhí)行多個過程，但不需要并行執(zhí)行它們，請將一個過程設(shè)置為啟動過程，并讓該過程調(diào)用其他過程。這僅使用一個工作線程。

（4）設(shè)置、清除和控制自動執(zhí)行。
只有系統(tǒng)管理員 才能將過程標記為自動執(zhí)行。此外，該過程必須位于數(shù)據(jù)庫中，并且不能具有輸入或輸出參數(shù)。
使用sp_procoption可以：

將現(xiàn)有過程指定為啟動過程。

停止在 SQL Server 啟動時執(zhí)行過程。

5.2、使用 Transact-SQL執(zhí)行存儲過程

（1）示例一，執(zhí)行存儲過程：示如何執(zhí)行需要一個參數(shù)的存儲過程。該示例使用指定為參數(shù)的值 6 執(zhí)行存儲過程。

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

（2）示例二，設(shè)置或清除自動執(zhí)行的過程：啟動過程必須位于數(shù)據(jù)庫中，并且不能包含 INPUT 或 OUTPUT 參數(shù)。當恢復所有數(shù)據(jù)庫并在啟動時記錄“恢復已完成”消息時，存儲過程的執(zhí)行將開始。

EXEC sp_procoption @ProcName = N'<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';
GO

（3）示例三，阻止過程自動執(zhí)行：使用 sp_procoption 停止過程自動執(zhí)行。

EXEC sp_procoption @ProcName = N'<procedure name>'      
    , @OptionName = 'startup'
    , @OptionValue = 'off';
GO

六、授予對存儲過程的權(quán)限

可以將權(quán)限授予數(shù)據(jù)庫中的現(xiàn)有用戶、數(shù)據(jù)庫角色或應(yīng)用程序角色。

授予者（或使用 AS 選項指定的主體）必須具有具有 GRANT OPTION 的權(quán)限本身，或者具有暗示要授予的權(quán)限的更高權(quán)限。需要對過程所屬的架構(gòu)具有 ALTER 權(quán)限，或?qū)^程具有 CONTROL 權(quán)限。

6.1、授予對存儲過程的權(quán)限

示例：向應(yīng)用程序角色授予對存儲過程的權(quán)限。

USE AdventureWorks2012;   
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo  
    TO Recruiting11;  
GO

6.2、授予對架構(gòu)中所有存儲過程的權(quán)限

示例：向架構(gòu)中存在或?qū)⒁嬖诘乃写鎯^程授予應(yīng)用程序角色的權(quán)限。

USE AdventureWorks2012;   
GRANT EXECUTE ON SCHEMA::HumanResources
    TO Recruiting11;  
GO

總結(jié)

不要從自動執(zhí)行的過程返回任何結(jié)果集。由于該過程由 SQL Server 而不是應(yīng)用程序或用戶執(zhí)行，因此結(jié)果集無處可去。

以上就是SQL Server存儲過程(數(shù)據(jù)庫引擎)使用詳解的詳細內(nèi)容，更多關(guān)于SQL Server存儲過程的資料請關(guān)注腳本之家其它相關(guān)文章！