預存程序 (Database Engine)Stored Procedures (Database Engine)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server 的預存程序是一個或多個 Transact-SQLTransact-SQL 陳述式的群組,或 MicrosoftMicrosoft .NET Framework.NET Framework Common Language Runtime (CLR) 方法的參考。A stored procedure in SQL ServerSQL Server is a group of one or more Transact-SQLTransact-SQL statements or a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method. 預存程序類似於其他程式設計語言中的建構,因為這些程序可以:Procedures resemble constructs in other programming languages because they can:

  • 接受輸入參數,並以輸出參數的形式將多個數值傳回呼叫程式。Accept input parameters and return multiple values in the form of output parameters to the calling program.

  • 包含可在資料庫中執行作業的程式陳述式。Contain programming statements that perform operations in the database. 這些功能包括呼叫其他程序。These include calling other procedures.

  • 將狀態值傳回呼叫程式,以指示成功或失敗 (及失敗原因)。Return a status value to a calling program to indicate success or failure (and the reason for failure).

使用預存程序的好處Benefits of Using Stored Procedures

下列清單說明使用這些程序的一些優點。The following list describes some benefits of using procedures.

減少伺服器/用戶端網路流量Reduced server/client network traffic
程序中的指令會以單一批次的程式碼來執行。The commands in a procedure are executed as a single batch of code. 這可以大幅降低伺服器與用戶端之間的網路流量,因為網路之間只會傳送執行程序的呼叫。This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. 若程序沒有提供程式碼封裝,則每個個別的程式碼行都會在網路上傳送。Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

更強的安全性Stronger security
多個使用者和用戶端程式都可以透過程序,在基礎資料庫物件上執行作業,即使使用者和程式不具備這些基礎物件的直接權限亦可。Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. 程序也會控制要執行哪些程序和活動,並保護基礎資料庫物件。The procedure controls what processes and activities are performed and protects the underlying database objects. 這可避免在個別物件層級授與權限的需求,而簡化安全層。This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

EXECUTE AS 子句可指定在 CREATE PROCEDURE 陳述式中,以模擬其他使用者,或讓使用者或應用程式執行特定資料庫活動,而不需要具備基礎物件和指令的直接權限。The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. 例如,有些動作 (如 TRUNCATE TABLE) 沒有可授與的權限。For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. 若要執行 TRUNCATE TABLE,使用者必須擁有指定資料表的 ALTER 權限。To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. 授與使用者資料表的 ALTER 權限可能不是很理想,因為使用者實際上將擁有不只截斷資料表的權限。Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. 透過合併模組中的 TRUNCATE TABLE 陳述式,並指定有權限修改資料表的使用者來執行該模組,您即可針對授與該模組 EXECUTE 權限的使用者擴充其權限,使其可截斷資料表。By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

當透過網路呼叫程序時,僅有執行程序的呼叫是可見的。When calling a procedure over the network, only the call to execute the procedure is visible. 因此,惡意使用者就無法查看資料表及資料庫物件名稱、內嵌自己的 Transact-SQLTransact-SQL 陳述式或搜尋重要的資料。Therefore, malicious users cannot see table and database object names, embed Transact-SQLTransact-SQL statements of their own, or search for critical data.

使用程序參數可協助預防 SQL 資料隱碼攻擊。Using procedure parameters helps guard against SQL injection attacks. 由於參數輸入會被視為常值而不是可執行的程式碼,因此更難以將指令插入程序中的 Transact-SQLTransact-SQL 陳述式以進行攻擊,並危及安全性。Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQLTransact-SQL statement(s) inside the procedure and compromise security.

您也可以加密程序,協助模糊化原始程式碼。Procedures can be encrypted, helping to obfuscate the source code. 如需詳細資訊,請參閱 SQL Server 加密For more information, see SQL Server Encryption.

重複使用程式碼Reuse of code
任何重複資料庫作業的程式碼,都是程序中非常適合封裝的項目。The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. 這可避免重複撰寫相同程式碼、減少程式碼不一致,並且可讓具備所需權限的任何使用者或應用程式存取及執行程式碼。This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

維護更簡易Easier maintenance
當用戶端應用程式呼叫程序並將資料庫作業保留在資料層中時,若基礎資料庫中有任何變更,只有程序必須更新。When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. 應用程式層會維持獨立,也不需要知道資料庫配置、關聯性或程序間有什麼變更。The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

提升效能Improved performance
依預設,第一次編譯程序時,將執行並建立執行計畫,以利後續的執行中重複使用。By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. 由於查詢處理器不需要建立新計畫,通常可以花較少的時間來處理程序。Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

若資料表或程序所參照的資料有大幅變更,先行編譯的計畫實際上可能會導致程序執行變慢。If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. 在此情況下,重新編譯程序並強制新的執行計畫可以改善效能。In this case, recompiling the procedure and forcing a new execution plan can improve performance.

預存程序類型Types of Stored Procedures

使用者定義User-defined
您可以在使用者定義的資料庫或所有系統資料庫 ( Resource 資料庫除外) 中,建立使用者定義的程序。A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. 您可以使用 Transact-SQLTransact-SQL 或使用 MicrosoftMicrosoft .NET Framework.NET Framework Common Language Runtime (CLR) 方法作為參照的形式來建立程序。The procedure can be developed in either Transact-SQLTransact-SQL or as a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method.

暫存Temporary
暫存程序是一種使用者定義的程序。Temporary procedures are a form of user-defined procedures. 暫存程序與永久程序類似,只不過暫存程序是儲存於 tempdb中。The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb. 暫存程序有兩種:本機與全域。There are two types of temporary procedures: local and global. 它們在名稱、可見性和可用性方面有些差異。They differ from each other in their names, their visibility, and their availability. 本機暫存程序是以單一數字符號 (#) 做為名稱的第一個字元;只有目前使用者連接才能看見,當連接中斷時,會將其刪除。Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed. 全域暫存程序是以兩個數字符號 (#) 做為名稱的前兩個字元;只要一建立好,任何使用者都能看見,只有當使用程序的最後一個工作階段結束時,才會將其刪除。Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

系統System
SQL ServerSQL Server中包括系統程序。System procedures are included with SQL ServerSQL Server. 它們實際上是儲存在內部隱藏的 Resource 資料庫中,但邏輯上會出現在每個系統和使用者定義資料庫的 sys 結構描述中。They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. 此外, msdb 資料庫也包含 dbo 結構描述中用於排程警示和作業的系統預存程序。In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. 因為系統程序會以 sp_ 作為前置詞開頭,因此建議您命名使用者定義的程序時不要使用此前置詞。Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. 如需系統程序的完整清單,請參閱系統預存程序 (Transact-SQL)For a complete list of system procedures, see System Stored Procedures (Transact-SQL)

SQL ServerSQL Server 支援的系統程序,可對各種維護活動提供 SQL ServerSQL Server 到外部程式的介面。supports the system procedures that provide an interface from SQL ServerSQL Server to external programs for various maintenance activities. 這些擴充程序會使用 xp_ 前置詞。These extended procedures use the xp_ prefix. 如需擴充程序的完整清單,請參閱一般擴充預存程序 (Transact-SQL)For a complete list of extended procedures, see General Extended Stored Procedures (Transact-SQL).

擴充使用者定義Extended User-Defined
擴充程序能夠在 C 這類程式設計語言中,建立外部常式。這些程序是 SQL ServerSQL Server 執行個體可以動態載入和執行的 DLL。Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL ServerSQL Server can dynamically load and run.

注意

SQL ServerSQL Server的未來版本將移除擴充預存程序。Extended stored procedures will be removed in a future version of SQL ServerSQL Server. 請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 改為建立 CLR 程序。Create CLR procedures instead. 此方法會提供更強固及安全的替代方法來撰寫擴充程序。This method provides a more robust and secure alternative to writing extended procedures.

工作描述Task Description 主題Topic
描述如何建立預存程序。Describes how to create a stored procedure. 建立預存程序Create a Stored Procedure
描述如何修改預存程序。Describes how to modify a stored procedure. 修改預存程序Modify a Stored Procedure
描述如何刪除預存程序。Describes how to delete a stored procedure. 刪除預存程序Delete a Stored Procedure
描述如何執行預存程序。Describes how to execute a stored procedure. 執行預存程序Execute a Stored Procedure
描述如何授與預存程序的權限。Describes how to grant permissions on a stored procedure. 授與預存程序的權限Grant Permissions on a Stored Procedure
描述如何從預存程序將資料傳回應用程式。Describes how to return data from a stored procedure to an application. 從預存程序傳回資料Return Data from a Stored Procedure
描述如何重新編譯預存程序。Describes how to recompile a stored procedure. 重新編譯預存程序Recompile a Stored Procedure
描述如何重新命名預存程序。Describes how to rename a stored procedure. 重新命名預存程序Rename a Stored Procedure
描述如何檢視預存程序的定義。Describes how to view the definition of a stored procedure. 檢視預存程序的定義View the Definition of a Stored Procedure
描述如何檢視預存程序的相依性。Describes how to view the dependencies on a stored procedure. 檢視預存程序的相依性View the Dependencies of a Stored Procedure
描述如何在預存程序中使用參數。Describes how Parameters are used in a stored procedure. 參數Parameters

CLR 預存程序CLR Stored Procedures
延遲名稱解析Deferred name resolution