一、背景知識(shí)

SQL Server 中的存儲(chǔ)過程是一組一個(gè)或多個(gè) Transact-SQL 語(yǔ)句的引用。過程類似于其他編程語(yǔ)言中的構(gòu)造，因?yàn)樗鼈兛梢裕?/p>

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

1.1、使用存儲(chǔ)過程的好處

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

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

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

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

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

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

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

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

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

1.2、存儲(chǔ)過程的類型

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

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

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

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

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

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

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

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

要運(yùn)行該過程，執(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

三、修改存儲(chǔ)過程

修改存儲(chǔ)過程具有如下限制：

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

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

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

使用示例：

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

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)有存儲(chǔ)過程會(huì)刪除已顯式授予該存儲(chǔ)過程的權(quán)限。請(qǐng)改用 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

要運(yùn)行修改后的存儲(chǔ)過程執(zhí)行以下：

EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';  
GO

四、刪除存儲(chǔ)過程

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

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

使用示例：

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

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ù)庫(kù)中刪除的存儲(chǔ)過程。

DROP PROCEDURE [<stored procedure name>];
GO

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

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

限制：

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

5.1、建議

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

EXEC sys.sp_who;

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

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

或者

EXEC AdventureWorks2019.dbo.uspGetEmployeeManagers 50;  
GO

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

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

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

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

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

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

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

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

USE AdventureWorks2019;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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

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

最新評(píng)論