対象者:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticsアナリティクスプラットフォームシステム(PDW)Microsoft FabricにおけるSQLデータベース
SQL Server のストアド プロシージャは、1 つ以上の Transact-SQL ステートメント、または Microsoft .NET Framework 共通言語ランタイム (CLR) メソッドへの参照のグループです。 プロシージャは、以下ができるために、他のプログラミング言語の構造に似ています。
入力パラメーターを受け取り、呼び出し元のプログラムに出力パラメーターの形式で複数の値を返す。
データベース内での操作を実行するプログラミング ステートメントを含む。 これらのステートメントには、他のプロシージャの呼び出しが含まれます。
呼び出し元のプログラムにステータス値を返し、成功、失敗、および失敗の原因を示す。
ストアド プロシージャを使用する利点
プロシージャを使用する利点の一部を次に示します。
サーバー/クライアント ネットワーク トラフィックの低減
プロシージャ内のコマンドは単一バッチのコードとして実行されます。 この方法では、プロシージャを実行する呼び出しのみがネットワーク経由で送信されるため、サーバーとクライアントの間のネットワーク トラフィックを大幅に削減できます。 プロシージャによって提供されるコードのカプセル化が存在しない場合は、ネットワークでコードの各行を送信する必要があります。
セキュリティの強化
ユーザーとクライアント プログラムが基になるデータベース オブジェクトの直接アクセス許可を持っていない場合でも、プロシージャによって、複数のユーザーとクライアント プログラムがそれらの基になるデータベース オブジェクトに対して操作を実行できます。 プロシージャにより、実行するプロセスとアクティビティが制御され、基になるデータベース オブジェクトが保護されます。 この方法では、個々のオブジェクト レベルでアクセス許可を付与する必要がなくなり、セキュリティ層が簡略化されます。
CREATE PROCEDURE ステートメントに CREATE PROCEDURE 句を指定し、別のユーザーのアクセス許可を借用したり、ユーザーまたはアプリケーションが基のオブジェクトおよびコマンドへの直接のアクセス許可を持たなくても特定のデータベース アクティビティを実行できるようにすることができます。 たとえば、TRUNCATE TABLE などの操作には、許可できるアクセス許可がありません。
TRUNCATE TABLE を実行するには、指定されたテーブルの ALTER アクセス許可がユーザーに許可されている必要があります。 ところが場合によっては、テーブルの ALTER アクセス許可をユーザーに許可することは望ましくありません。これは、実質的にはテーブルの切り捨て以外の操作も実行できる権限をそのユーザーに許可することになるためです。
TRUNCATE TABLE ステートメントをモジュール内に組み込み、テーブルを変更する権限が許可されているユーザーとしてそのモジュールを実行するように指定すると、テーブルの切り捨てを行うためのアクセス許可を、そのモジュールの EXECUTE アクセス許可が許可されたユーザーに拡張できます。
アプリケーションがネットワークを介してプロシージャを呼び出すと、プロシージャの実行の呼び出しのみが表示されます。 したがって、悪意のあるユーザーがテーブルやデータベース オブジェクト名を表示したり、独自の Transact-SQL ステートメントを埋め込んだり、重要なデータを検索したりすることができません。
プロシージャ パラメーターを使用することで、SQL インジェクション攻撃から保護することができます。 パラメーター入力は実行可能コードとしてではなく、リテラル値として処理されるため、攻撃者はプロシージャ内の Transact-SQL ステートメントにコマンドを挿入してセキュリティを損なうことが難しくなります。
プロシージャを暗号化して、ソース コードを難読化することができます。 詳細については、「 SQL Server の暗号化」を参照してください。
コードの再利用
繰り返し実行されるデータベース操作のコードは、プロシージャのカプセル化に最適な候補です。 この方法により、同じコードの不必要な書き換えが不要になり、コードの不整合が減少し、必要なアクセス許可を持つユーザーまたはアプリケーションがコードにアクセスして実行できるようになります。
メンテナンスの簡素化
クライアント アプリケーションがプロシージャを呼び出し、データベース操作をデータ層に保持する場合は、基になるデータベースの変更に対してプロシージャを更新するだけで済みます。 アプリケーション層は分離されたままであり、データベース レイアウト、リレーションシップ、またはプロセスに対する変更について知る必要はありません。
改良型パフォーマンス
既定では、プロシージャは初回実行時にコンパイルし、後続の実行に再利用する実行プランを作成します。 クエリ プロセッサは新しいプランを作成する必要がないため、通常のプロシージャの実行はそれほど時間がかかりません。
プロシージャにより参照されるテーブルまたはデータに大幅な変更があると、事前にコンパイルされているプランによりプロシージャの実行が遅くなる場合があります。 この場合には、プロシージャを再コンパイルするか、新しい実行プランを強制することにより、パフォーマンスを向上できます。
ストアド プロシージャの種類
User-defined
ユーザー定義プロシージャは、ユーザー定義データベース、または Resource データベースを除くすべてのシステム データベースに作成できます。 このプロシージャは、Transact-SQL で開発することも、.NET Framework 共通ランタイム言語 (CLR) メソッドへの参照として開発することもできます。
Temporary
一時プロシージャは、ユーザー定義プロシージャの 1 形式です。 一時プロシージャは永続的なプロシージャに似ていますが、tempdb に格納されている点が異なります。 一時プロシージャには、ローカル一時プロシージャとグローバル一時プロシージャの 2 種類があります。 この 2 種類の一時テーブルでは、名前、表示設定、および可用性が異なります。 ローカルの一時プロシージャには、名前の最初の文字として単一の番号記号 (#) があります。 現在のユーザー接続にのみ表示され、接続が閉じられると削除されます。 グローバル一時プロシージャには、名前の最初の 2 文字として 2 つの数字記号 (##) があります。 作成後、すべてのユーザーに表示され、プロシージャによって、最後のセッション終了時に削除されます。
System
システム プロシージャはデータベース エンジンに含まれています。 これらは物理的に内部の非表示の Resource データベースに格納され、システム定義およびユーザー定義のすべてのデータベースの sys スキーマに論理的に表示されます。 さらに、 msdb データベースには、警告とジョブのスケジュール設定に使用される dbo スキーマ内にシステム ストアド プロシージャも含まれます。 システム プロシージャはプレフィックス sp_で始まるので、ユーザー定義プロシージャに名前を付ける場合は、このプレフィックスを使用しないでください。 システム プロシージャの完全な一覧については、「 システム ストアド プロシージャ」を参照してください。
SQL Server では、SQL Server から外部プログラムへの、さまざまなメンテナンス作業に使用するためのインターフェイスになるシステム プロシージャがサポートされます。 そのような拡張プロシージャには xp_ プレフィックスが付きます。 拡張プロシージャの完全な一覧については、「 一般的な拡張ストアド プロシージャ」を参照してください。
拡張ユーザー定義
拡張プロシージャを使用すると、C などのプログラミング言語で外部ルーチンを作成できます。これらのプロシージャは DLL なので、SQL Server のインスタンスで動的に読み込んで実行できます。
Note
拡張ストアド プロシージャは、今後のバージョンの SQL Server では削除される予定です。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションはできるだけ早く修正してください。 代わりに CLR プロシージャを作成してください。 この方法では、拡張プロシージャの記述に代わる、より堅牢でセキュリティ保護された手段が提供されます。
関連タスク
| タスクの説明 | Article |
|---|---|
| ストアド プロシージャの作成方法を説明する | ストアド プロシージャの作成 |
| ストアド プロシージャの変更方法を説明する | ストアド プロシージャの変更 |
| ストアド プロシージャの削除方法を説明する | ストアド プロシージャの削除 |
| ストアド プロシージャの実行方法を説明する | ストアド プロシージャの実行 |
| ストアド プロシージャの権限の許可方法を説明する | ストアド プロシージャへのアクセス許可の付与 |
| ストアド プロシージャからアプリケーションにデータを返す方法を説明する | ストアド プロシージャからデータを返す |
| ストアド プロシージャの再コンパイル方法を説明する | ストアド プロシージャの再コンパイル |
| ストアド プロシージャ名の変更方法を説明する | ストアド プロシージャの名前の変更 |
| ストアド プロシージャの定義の表示方法を説明する | ストアド プロシージャの定義の表示 |
| ストアド プロシージャの依存関係の表示方法を説明する | ストアド プロシージャの依存関係の表示 |
| ストアド プロシージャでパラメーターを使用する方法について説明します。 | Parameters |