ストアド プロシージャ (データベース エンジン)Stored Procedures (Database Engine)

このトピックに適用されますはいSQL ServerはいAzure SQL Database[はい]Azure SQL Data Warehouseはい。並列データ ウェアハウスTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL Server のストアド プロシージャは、1 つ以上の Transact-SQLTransact-SQL ステートメント、または MicrosoftMicrosoft .NET Framework.NET Framework 共通言語ランタイム (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.

CREATE PROCEDURE ステートメントに EXECUTE AS 句を指定し、別のユーザーの権限を借用したり、ユーザーまたはアプリケーションが基のオブジェクトおよびコマンドに直接アクセスできる権限を持たなくても特定のデータベース アクティビティを実行できるようにすることができます。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
ユーザー定義プロシージャは、ユーザー定義データベース、または リソース データベースを除くすべてのシステム データベースに作成できます。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 共通言語ランタイム (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
一時プロシージャは、ユーザー定義プロシージャの 1 形式です。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. 一時プロシージャには、ローカル一時プロシージャとグローバル一時プロシージャの 2 種類があります。There are two types of temporary procedures: local and global. この 2 種類の一時テーブルでは、名前、表示設定、および可用性が異なります。They differ from each other in their names, their visibility, and their availability. ローカル一時プロシージャ名の先頭には、番号記号 (#) が 1 つ付いています。このプロシージャは、作成したユーザーの現在の接続でのみ表示され、この接続が閉じられたときに削除されます。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. グローバル一時プロシージャ名の先頭には、番号記号が 2 つ (##) 付いています。このプロシージャは、作成されるとすべてのユーザーに表示され、このプロシージャを使用する最後のセッションの終了時に削除されます。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. 物理的には内部の非表示 リソース データベースに格納されますが、論理的には各システム データベースとユーザー定義データベースの 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 などのプログラミング言語で外部ルーチンを作成できます。これらのプロシージャは DLL なので、 SQL ServerSQL Server のインスタンスで動的に読み込んで実行できます。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