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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

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

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

  • 呼び出し元のプログラムにステータス値を返し、成功、失敗、および失敗の原因を示す。

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

プロシージャを使用する利点の一部を次に示します。

サーバー/クライアント ネットワーク トラフィックの低減

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

セキュリティの強化

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

CREATE PROCEDURE CREATE PROCEDURE ステートメントに EXECUTE AS 句を指定し、別のユーザーのアクセス許可を借用したり、ユーザーまたはアプリケーションが基のオブジェクトおよびコマンドへの直接のアクセス許可を持たなくても特定のデータベース アクティビティを実行できるようにすることができます。 たとえば、TRUNCATE TABLE などの操作には、許可できるアクセス許可がありません。 TRUNCATE TABLE を実行するには、指定されたテーブルの ALTER アクセス許可がユーザーに許可されている必要があります。 ところが場合によっては、テーブルの ALTER アクセス許可をユーザーに許可することは望ましくありません。これは、実質的にはテーブルの切り捨て以外の操作も実行できる権限をそのユーザーに許可することになるためです。 TRUNCATE TABLE ステートメントをモジュール内に組み込み、テーブルを変更する権限が許可されているユーザーとしてそのモジュールを実行するように指定すると、テーブルの切り捨てを行うためのアクセス許可を、そのモジュールの EXECUTE アクセス許可が許可されたユーザーに拡張できます。

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

プロシージャ パラメーターを使用することで、SQL インジェクション攻撃から保護することができます。 パラメーター入力は実行可能コードとしてではなく、リテラル値として処理されるため、攻撃者はプロシージャ内の Transact-SQL ステートメントにコマンドを挿入してセキュリティを損なうことが難しくなります。

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

コードの再利用

繰り返し実行するデータベース操作のコードは、プロシージャにカプセル化するのに最適です。 これにより同じコードを再作成する必要がなくなり、コードの矛盾を低減して、必要なアクセス許可を持つユーザーまたはアプリケーションがコードにアクセスして実行できるようになります。

メンテナンスの簡素化

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

パフォーマンスの向上

既定では、初回実行時にプロシージャがコンパイルされ、以降の実行時に再利用される実行プランが作成されます。 クエリ プロセッサは新しいプランを作成する必要がないため、通常のプロシージャの実行はそれほど時間がかかりません。

プロシージャにより参照されるテーブルまたはデータに大幅な変更があると、事前にコンパイルされているプランによりプロシージャの実行が遅くなる場合があります。 この場合には、プロシージャを再コンパイルするか、新しい実行プランを強制することにより、パフォーマンスを向上できます。

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

ユーザー定義

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

一時

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

System

システム プロシージャはデータベース エンジンに含まれています。 物理的には内部の非表示 Resource データベースに格納されますが、論理的には各システム データベースとユーザー定義データベースの sys スキーマに表示されます。 さらに、 msdb データベースには、警告とジョブのスケジュール設定に使用される dbo スキーマ内にシステム ストアド プロシージャも含まれます。 システム プロシージャ名には sp_ というプレフィックスが付くため、ユーザー定義プロシージャ名を付けるときにこのプレフィックスを使用しないようにすることをお勧めします。 すべてのシステム プロシージャの一覧については、「システム ストアド プロシージャ (Transact-SQL)」を参照してください。

SQL Server では、SQL Server から外部プログラムへの、さまざまなメンテナンス作業に使用するためのインターフェイスになるシステム プロシージャがサポートされます。 そのような拡張プロシージャには xp_ プレフィックスが付きます。 すべての拡張プロシージャの一覧については、「汎用拡張ストアド プロシージャ (Transact-SQL)」を参照してください。

拡張ユーザー定義

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

Note

拡張ストアド プロシージャは、今後のバージョンの SQL Server では削除される予定です。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションはできるだけ早く修正してください。 代わりに CLR プロシージャを作成してください。 この方法では、拡張プロシージャの記述に代わる、より堅牢でセキュリティ保護された手段が提供されます。

タスクの説明 記事
ストアド プロシージャの作成方法を説明する ストアド プロシージャの作成
ストアド プロシージャの変更方法を説明する ストアド プロシージャの変更
ストアド プロシージャの削除方法を説明する ストアド プロシージャの削除
ストアド プロシージャの実行方法を説明する ストアド プロシージャの実行
ストアド プロシージャの権限の許可方法を説明する ストアド プロシージャへのアクセス許可の付与
ストアド プロシージャからアプリケーションにデータを返す方法を説明する ストアド プロシージャからデータを返す
ストアド プロシージャの再コンパイル方法を説明する ストアド プロシージャの再コンパイル
ストアド プロシージャ名の変更方法を説明する ストアド プロシージャの名前の変更
ストアド プロシージャの定義の表示方法を説明する ストアド プロシージャの定義の表示
ストアド プロシージャの依存関係の表示方法を説明する ストアド プロシージャの依存関係の表示
ストアド プロシージャでパラメーターを使用する方法について説明します。 パラメーター

関連するコンテンツ