預存程序 (Database Engine)
適用於:SQL Server
Azure SQL 資料庫
Azure SQL 受控執行個體
Azure Synapse Analytics
Analytics 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 程序。 與編寫擴充程序相比,此方法是更強固且安全的替代方法。
相關工作
工作描述 | 發行項 |
---|---|
描述如何建立預存程序。 | 建立預存程序 |
描述如何修改預存程序。 | 修改預存程序 |
描述如何刪除預存程序。 | 刪除預存程序 |
描述如何執行預存程序。 | 執行預存程序 |
描述如何授與預存程序的權限。 | 授與預存程序的權限 |
描述如何將預存程序的資料傳回至應用程式。 | 從預存程序傳回資料 |
描述如何重新編譯預存程序。 | 重新編譯預存程序 |
描述如何重新命名預存程序。 | 重新命名預存程序 |
描述如何檢視預存程序的定義。 | 檢視預存程序的定義 |
描述如何檢視預存程序的相依性。 | 檢視預存程序的相依性 |
描述預存程序中如何使用參數。 | 參數 |