預存程序 (Database Engine)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

SQL Server 中的 預存程序是包含一或多個 SQL 陳述式的群組，或是對 Microsoft .NET Framework 通用執行階段語言 (CLR) 方法的參考。 程序類似於其他程式設計語言中的建構，因為程序可以：

  • 接受輸入參數，並以輸出參數的形式將多個數值傳回呼叫程式。

  • 包含可在資料庫中執行作業的程式陳述式。 此類作業包括呼叫其他程序。

  • 將狀態值傳回呼叫程式，以指示成功或失敗 (及失敗原因)。

使用預存程序的優點

下列清單說明使用程序的一些優點。

減少伺服器/用戶端網路流量

程序中的指令會以單一批次的程式碼來執行。 這可大幅減少伺服器與用戶端之間的網路流量，因為只有執行該程序的呼叫會透過網路傳送。 如果沒有程序所提供的程式碼封裝，每個個別程式碼都必須跨越網路。

安全性更強

多個使用者和用戶端程式都可以透過程序，在基礎資料庫物件上執行作業，即使使用者和程式不具備這些基礎物件的直接權限亦可。 程序也會控制要執行哪些程序和活動，並保護基礎資料庫物件。 這可避免在個別物件層級授與權限的需求，而簡化安全層。

EXECUTE AS 子句可以在 CREATE PROCEDURE 陳述式中指定，以啟用模擬其他使用者，或讓使用者或應用程式能夠執行特定資料庫活動，而不需要基礎物件和命令的直接權限。 例如，有些動作 (例如 TRUNCATE TABLE) 沒有可以授與的權限。 若要執行 TRUNCATE TABLE，用戶必須具有指定資料表的 ALTER 權限。 將資料表的 ALTER 權限授與使用者可能不理想，因為使用者實際上具有超出截斷資料表能力的權限。 您可以將 TRUNCATE TABLE 陳述式併入模組中，並且指定該模組以有權修改資料表的使用者身分執行，從而擴充權限，將資料表在您授與該模組 EXECUTE 權限的使用者處截斷。

當應用程式透過網路呼叫程序時，只有執行程序的呼叫可見。 因此，惡意使用者看不到資料表和資料庫物件名稱，無法內嵌自己的 Transact-SQL 陳述式，也無法搜尋重要資料。

使用程序參數有助於防範 SQL 插入式攻擊。 由於參數輸入被視為常值，而不是可執行程式碼，因此攻擊者較難將命令插入程序內的 Transact-SQL 陳述式而危及安全性。

程序可以加密，幫助混淆原始程式碼。 如需詳細資訊，請參閱 SQL Server 加密。

重複使用程式碼

任何重複資料庫作業的程式碼都是可封裝在程序中的完美候選項目。 這樣可消除相同程式碼的不必要重寫，減少程式碼不一致，並允許任何擁有必要權限的使用者或應用程式存取和執行程序碼。

更容易維護

當用戶端應用程式呼叫程序並將資料庫作業保留在資料層時，針對基礎資料庫中的任何變更，僅須更新程序。 應用程式層保持分隔，不必知道資料庫配置、關聯性或流程的任何變更。

提升效能

根據預設，程序會在第一次執行時編譯，並建立供後續執行重複使用的執行計劃。 由於查詢處理器不需要建立新計劃，因此處理程序所需的時間通常較少。

如果程序所參考的資料表或資料有重大變更，先行編譯的計劃實際上可能會導致程序執行速度變慢。 在此情況下，重新編譯程序並強制建立新的執行計劃可以改善效能。

預存程序的類型

使用者定義

使用者定義的程序可以在使用者定義的資料庫中建立，或在除 Resource 資料庫以外的所有系統資料庫中建立。 程序可以採用 Transact-SQL 開發，或做為 Microsoft .NET Framework 通用執行階段語言 (CLR) 方法的參考。

暫存

暫存程序是使用者定義程序的一種形式。 暫存程序就像是永久程序，不同之處在於前者儲存在 tempdb 中。 暫存程序有兩種：區域與全域。 它們在名稱、可見性和可用性方面有些差異。 區域暫存程序是以單一數字記號 (#) 作為名稱的第一個字元；只有目前連線的使用者才能看見這些程序，當連線關閉時，就會將其刪除。 全域暫存程序是以兩個數字記號 (##) 作為名稱的前兩個字元；在建立之後，任何使用者都能看到這些程序，在使用程序的最後一個工作階段結束時會將其刪除。

系統

系統程序隨附於資料庫引擎。 它們實際上是儲存在內部隱藏的 Resource 資料庫中，但在邏輯上是顯示在每個系統定義和使用者定義資料庫的 sys 結構描述中。 此外，msdb 資料庫 也包含 dbo 結構描述中的系統預存程序，用於警示和工作排程。 由於系統程序以前置詞 sp_ 開始，因此建議您在命名使用者定義程序時不要使用此前置詞。 如需系統程序的完整清單，請參閱系統預存程序 (Transact-SQL)。

SQL Server 支援系統程序提供從 SQL Server 到外部程式的介面，以進行各種維護活動。 這些擴充程序使用 xp_ 前置詞。 如需擴充程序的完整清單，請參閱一般擴充預存程序 (Transact-SQL)。

擴充用戶定義

擴充程序可讓您以 C 之類的程式設計語言建立外部常式。這些程序是 SQL Server 執行個體可以動態載入和執行的 DLL。

注意

SQL Server 的未來版本將移除擴充預存程序。 請勿在新的開發工作中使用此功能，並且儘速修改使用此功能的應用程式。 請改為建立 CLR 程序。 與編寫擴充程序相比，此方法是更強固且安全的替代方法。

工作描述 發行項
描述如何建立預存程序。 建立預存程序
描述如何修改預存程序。 修改預存程序
描述如何刪除預存程序。 刪除預存程序
描述如何執行預存程序。 執行預存程序
描述如何授與預存程序的權限。 授與預存程序的權限
描述如何將預存程序的資料傳回至應用程式。 從預存程序傳回資料
描述如何重新編譯預存程序。 重新編譯預存程序
描述如何重新命名預存程序。 重新命名預存程序
描述如何檢視預存程序的定義。 檢視預存程序的定義
描述如何檢視預存程序的相依性。 檢視預存程序的相依性
描述預存程序中如何使用參數。 參數

相關內容