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

SQL Server のストアド プロシージャは、1 つ以上の Transact-SQL ステートメントのグループ、または Microsoft .NET Framework 共通ランタイム言語 (CLR) メソッドへの参照です。 プロシージャは、次のような可能性があるため、他のプログラミング言語のコンストラクトに似ています。

• 入力パラメーターを受け取り、出力パラメーターの形式で複数の値を呼び出し元のプログラムに返します。

• データベースで操作を実行するプログラミング ステートメントが含まれています。 これには、他のプロシージャの呼び出しが含まれます。

• 成功または失敗 (および失敗の理由) を示す状態値を呼び出し元のプログラムに返します。

ストアド プロシージャを使用する利点

次の一覧では、プロシージャを使用する利点について説明します。

サーバー/クライアント ネットワーク トラフィックの削減
プロシージャ内のコマンドは単一バッチのコードとして実行されます。 これにより、ネットワークではプロシージャを実行するための呼び出しのみが送信されるため、サーバーとクライアント間でのネットワーク トラフィックを大幅に低減できます。 プロシージャによって提供されるコードカプセル化がないと、個々のコード行がネットワークを通過する必要があります。

セキュリティの強化
ユーザーおよびクライアント プログラムが基になるデータベース オブジェクトに直接アクセスできる権限を持っていない場合でも、プロシージャにより複数のユーザーおよびクライアント プログラムが基になるデータベース オブジェクトに操作を実行できるようになります。 プロシージャにより、実行するプロセスとアクティビティが制御され、基になるデータベース オブジェクトが保護されます。 これにより個々のオブジェクト レベルで権限を与える必要がなくなり、セキュリティ階層が簡素化されます。

CREATE PROCEDURE ステートメントで EXECUTE AS 句を指定して、別のユーザーを偽装したり、ユーザーまたはアプリケーションが、基になるオブジェクトとコマンドに対する直接のアクセス許可を必要とせずに特定のデータベース アクティビティを実行したりできます。 たとえば、TRUNCATE TABLE などの一部のアクションには、許可可能なアクセス許可がありません。 TRUNCATE TABLE を実行するには、指定したテーブルに対する ALTER 権限が必要です。 テーブルに対する ALTER 権限をユーザーに付与することは理想的でない場合があります。これは、ユーザーがテーブルを切り捨てる機能をはるかに超えるアクセス許可を実質的に持つことになるためです。 TRUNCATE TABLE ステートメントをモジュールに組み込み、そのモジュールがテーブルを変更する権限を持つユーザーとして実行されるように指定することで、モジュールに対して EXECUTE 権限を付与するユーザーにテーブルを切り捨てる権限を拡張できます。

ネットワーク経由でプロシージャを呼び出すと、プロシージャを実行する呼び出しのみが表示されます。 そのため、悪意のあるユーザーは、テーブルとデータベース のオブジェクト名を表示したり、独自のステートメント Transact-SQL 埋め込んだり、重要なデータを検索したりできません。

プロシージャ パラメーターを使用すると、SQL インジェクション攻撃を防ぐことができます。 パラメーター入力は実行可能コードではなくリテラル値として扱われるので、攻撃者がプロシージャ内の Transact-SQL ステートメントにコマンドを挿入し、セキュリティを侵害することはより困難です。

プロシージャは暗号化でき、ソース コードを難読化するのに役立ちます。 詳細については、「 SQL Server の暗号化」を参照してください。

コードの再利用
繰り返し実行されるデータベース操作のコードは、プロシージャのカプセル化に最適な候補です。 これにより、同じコードの不必要な書き換えが不要になり、コードの不整合が減少し、必要なアクセス許可を持つユーザーまたはアプリケーションがコードにアクセスして実行できるようになります。

メンテナンスの簡素化
クライアント アプリケーションがプロシージャを呼び出し、データ層内にデータベース操作を維持する場合は、基のデータベースの変更に対してプロシージャのみを更新することが必要になります。 アプリケーション層は分離されたままであり、データベース レイアウト、リレーションシップ、またはプロセスに対する変更について知る必要はありません。

パフォーマンスの向上
既定では、プロシージャは初回実行時にコンパイルし、後続の実行に再利用される実行プランを作成します。 クエリ プロセッサは新しいプランを作成する必要がないため、通常、プロシージャの処理にかかる時間が短くなります。

プロシージャによって参照されるテーブルまたはデータが大幅に変更された場合、プリコンパイル済みプランによってプロシージャの実行速度が低下する可能性があります。 この場合、プロシージャを再コンパイルし、新しい実行プランを強制すると、パフォーマンスが向上する可能性があります。

ストアド プロシージャの種類

ユーザー定義
ユーザー定義プロシージャは、ユーザー定義データベースまたは リソース データベースを除くすべてのシステム データベースに作成できます。 このプロシージャは、Transact-SQL または Microsoft .NET Framework 共通ランタイム言語 (CLR) メソッドへの参照として開発できます。

一時
一時プロシージャは、ユーザー定義プロシージャの一種です。 一時プロシージャは永続的なプロシージャに似ていますが、一時プロシージャは tempdb に格納されます。 一時プロシージャには、ローカルプロシージャとグローバルプロシージャの 2 種類があります。 この 2 種類の一時テーブルでは、名前、表示設定、および可用性が異なります。 ローカルの一時プロシージャには、名前の最初の文字として 1 つの数字記号 (#) があります。これらは現在のユーザー接続にのみ表示され、接続が閉じられると削除されます。 グローバル一時プロシージャは、名前の最初の2文字として2つの数字記号 (##) を持ちます。これらのプロシージャは作成された後に任意のユーザーに表示され、使用した最後のセッションが終了すると削除されます。

システム
システム プロシージャは SQL Server に含まれています。 これらは、内部の非表示 リソース データベースに物理的に格納され、論理的には、すべてのシステムおよびユーザー定義データベースの sys スキーマに表示されます。 さらに、 msdb データベースには、アラートとジョブのスケジュール設定に使用される dbo スキーマのシステム ストアド プロシージャも含まれています。 システム プロシージャはプレフィックス sp_で始まるため、ユーザー定義プロシージャに名前を付ける場合は、このプレフィックスを使用しないことをお勧めします。 システム プロシージャの完全な一覧については、システム ストアド プロシージャ (Transact-SQL) を参照してください。

SQL Server では、さまざまなメンテナンス アクティビティに対して SQL Server から外部プログラムへのインターフェイスを提供するシステム プロシージャがサポートされています。 これらの拡張プロシージャでは、xp_ プレフィックスを使用します。 拡張プロシージャの完全な一覧については、「 拡張ストアド プロシージャ (Transact-SQL)」を参照してください。

拡張 User-Defined
拡張プロシージャを使用すると、C などのプログラミング言語で外部ルーチンを作成できます。これらのプロシージャは、SQL Server のインスタンスが動的に読み込んで実行できる DLL です。

関連タスク

関連コンテンツ

CLR ストアド プロシージャ