一、背景知識

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

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

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

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

（9）改進的性能。
默認情況下，過程在第一次執(zhí)行時進行編譯，并創(chuàng)建一個在后續(xù)執(zhí)行中重復(fù)使用的執(zhí)行計劃。由于查詢處理器不必創(chuàng)建新計劃，因此處理該過程所需的時間通常更少。如果過程引用的表或數(shù)據(jù)發(fā)生了重大更改，則預(yù)編譯計劃實際上可能會導(dǎo)致過程執(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ù)字符號（#）;它們僅對當(dāng)前用戶連接可見，并且在連接關(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)建的，則僅當(dā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

四、刪除存儲過程

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

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

使用示例：

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

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

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

DROP PROCEDURE [<stored procedure name>];
GO

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

有兩種不同的方法來執(zhí)行存儲過程。第一種也是最常見的方法是讓應(yīng)用程序或用戶調(diào)用該過程。第二種方法是將過程設(shè)置為在 SQL Server 實例啟動時自動運行。當(dāng)應(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ù)庫引擎將按以下順序搜索該過程：

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

（3）自動執(zhí)行存儲過程。
每次 SQL Server 啟動時都會執(zhí)行標(biāo)記為自動執(zhí)行的過程，并在該啟動過程中恢復(fù)數(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)管理員 才能將過程標(biāo)記為自動執(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ù)。當(dāng)恢復(fù)所有數(shù)據(jù)庫并在啟動時記錄“恢復(fù)已完成”消息時，存儲過程的執(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)文章！

最新評論