T-SQL のストアドプロシージャ - 作成、変更、削除。 Microsoft SQL Server でストアドプロシージャを作成する SQL ストアドプロシージャを作成する

ストアドプロシージャ SQL 言語とプロシージャ拡張を使用して作成された特別なタイプの Transact-SQL ステートメントパッケージです。パッケージとストアドプロシージャの主な違いは、後者はデータベースオブジェクトとして保存されることです。つまり、反復可能なタスクのパフォーマンスと一貫性を向上させるために、ストアドプロシージャはサーバー側に保存されます。

データベースエンジンはストアドプロシージャとシステムプロシージャをサポートしています。ストアドプロシージャは、他のすべてのデータベースオブジェクトと同じ方法で作成されます。 DDL言語を使用します。 システム手順これらはデータベースエンジンによって提供され、システムカタログ内の情報にアクセスして変更するために使用できます。

ストアドプロシージャを作成するときに、オプションのパラメーターのリストを定義できます。こうすることで、プロシージャは呼び出されるたびに適切な引数を受け入れるようになります。ストアドプロシージャは、ユーザー定義の情報を含む値を返すか、エラーが発生した場合には適切なエラーメッセージを返すことができます。

ストアドプロシージャは、データベースにオブジェクトとして保存される前にプリコンパイルされます。プロシージャのプリコンパイルされた形式はデータベースに保存され、呼び出されるたびに使用されます。ストアドプロシージャのこの特性により、(ほとんどの場合) プロシージャのコンパイルの繰り返しが不要になり、それに応じてパフォーマンスが向上するという重要な利点が得られます。ストアドプロシージャのこの特性は、データベースシステムとアプリケーション間で交換されるデータの量にもプラスの影響を与えます。特に、サイズが数千バイトのストアドプロシージャを呼び出す場合、必要な量は 50 バイト未満になる場合があります。複数のユーザーがストアドプロシージャを使用して反復的なタスクを実行すると、これらの節約の累積効果は非常に大きくなる可能性があります。

ストアドプロシージャは次の目的にも使用できます。

データベーステーブルを使用してアクションのログを作成します。

ストアドプロシージャを使用すると、ユーザーにさまざまなアクセス権限を付与する GRANT および REVOKE ステートメントを使用して提供されるセキュリティをはるかに超えるレベルのセキュリティ制御が提供されます。次のセクションで説明するように、ストアドプロシージャを実行する権限は、ストアドプロシージャに含まれるオブジェクトを変更する権限から独立しているため、これが可能になります。

テーブルの書き込み操作や読み取り操作のログを作成するストアドプロシージャは、データベースセキュリティの追加オプションを提供します。このような手順を使用すると、データベース管理者は、ユーザーまたはアプリケーションプログラムによってデータベースに加えられた変更を監視できます。

ストアドプロシージャの作成と実行

ストアドプロシージャはステートメントを使用して作成されます プロシージャの作成、次の構文があります。

CREATE PROC proc_name [((@param1) type1 [ VARYING] [=default1] )] (, ...) AS バッチ | EXTERNAL NAME method_name 構文規則

schema_name パラメーターは、作成されたストアドプロシージャの所有者によって割り当てられるスキーマの名前を指定します。 proc_name パラメーターは、ストアドプロシージャの名前を指定します。 @param1 パラメータは、type1 パラメータによってデータ型が決定されるプロシージャパラメータ (仮引数) です。ローカル変数がパッケージ内でローカルであるのと同様に、プロシージャパラメータはプロシージャ内でローカルです。プロシージャのパラメータは、呼び出し元によってプロシージャ内で使用するためにプロシージャに渡される値です。 default1 パラメータは、対応するプロシージャパラメータのデフォルト値を指定します。 (デフォルト値は NULL にすることもできます。)

出力オプションプロシージャパラメータが戻りパラメータであり、ストアドプロシージャから呼び出し元のプロシージャまたはシステムに値を返すために使用できることを示します。

前述したように、プロシージャのプリコンパイルされた形式はデータベースに保存され、呼び出されるたびに使用されます。何らかの理由で、ストアドプロシージャを呼び出すたびにコンパイルする必要がある場合は、プロシージャを宣言するときに、次を使用します。 RECOMPILE オプションあり。 WITH RECOMPILE オプションを使用すると、ストアドプロシージャの最も重要な利点の 1 つである、1 回のコンパイルによるパフォーマンスの向上が無効になります。したがって、WITH RECOMPILE オプションは、ストアドプロシージャで使用されるデータベースオブジェクトが頻繁に変更される場合にのみ使用してください。

EXECUTE AS 句ストアドプロシージャが呼び出された後に実行されるセキュリティコンテキストを定義します。このコンテキストを設定すると、データベースエンジンはユーザーアカウントの選択を制御して、ストアドプロシージャによって参照されるオブジェクトへのアクセス許可を確認できます。

デフォルトでは、sysadmin 固定サーバーロールと db_owner または db_ddladmin 固定データベースロールのメンバーのみが CREATE PROCEDURE ステートメントを使用できます。ただし、これらのロールのメンバーは、次のステートメントを使用してこの権限を他のユーザーに割り当てることができます。 付与作成手順.

以下の例は、Project テーブルを操作する単純なストアドプロシージャを作成する方法を示しています。

SampleDb を使用します。 GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE プロジェクト SET Budget = Budget + Budget * @percent/100;

前述したように、2 つのパケットを分離するには、次を使用します。 GOの指示。 CREATE PROCEDURE ステートメントは、同じバッチ内の他の Transact-SQL ステートメントと組み合わせることはできません。 IncreaseBudget ストアドプロシージャは、@percent パラメーターで決定される特定の割合ですべてのプロジェクトの予算を増やします。プロシージャは、プロシージャの実行時にこの引数が存在しない場合に使用されるデフォルトのパーセンテージ値 (5) も定義します。

ストアドプロシージャは、存在しないテーブルにアクセスできます。このプロパティを使用すると、最初に適切なテーブルを作成したり、宛先サーバーに接続したりせずに、プロシージャコードをデバッグできます。

常に現在のデータベースに保存されるプライマリストアドプロシージャとは異なり、常に一時システムデータベース tempdb に保存される一時ストアドプロシージャを作成することができます。一時ストアドプロシージャを作成する理由の 1 つは、データベースへの接続時に特定のステートメントグループが繰り返し実行されるのを避けるためである可能性があります。ローカルまたはグローバルの一時プロシージャを作成できます。これを行うには、ローカルプロシージャの名前を単一の # 文字 (#proc_name) で指定し、グローバルプロシージャの名前を 2 つの文字 (##proc_name) で指定します。

ローカル一時ストアドプロシージャは、それを作成したユーザーのみが、それが作成されたデータベースに接続している間のみ実行できます。グローバル一時プロシージャはすべてのユーザーが実行できますが、実行できるのは、それが実行された最後の接続 (通常はプロシージャの作成者の接続) が終了するまでです。

ストアドプロシージャのライフサイクルは、作成と実行の 2 つの段階で構成されます。各プロシージャは一度作成され、何度も実行されます。ストアドプロシージャは次を使用して実行されます。 命令の実行プロシージャの所有者であるか、そのプロシージャにアクセスする EXECUTE 権限を持つユーザー。 EXECUTE ステートメントの構文は次のとおりです。

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. 構文規則

return_status パラメータを除いて、EXECUTE ステートメントのすべてのパラメータは、CREATE PROCEDURE ステートメントの同じパラメータと同じ論理的意味を持ちます。 return_status パラメータは、プロシージャの戻りステータスを格納する整変数を指定します。値は、定数 (値) またはローカル変数 (@variable) を使用してパラメータに割り当てることができます。名前付きパラメータの値の順序は重要ではありませんが、名前なしパラメータの値は、CREATE PROCEDURE ステートメントで定義されている順序で指定する必要があります。

DEFAULT句プロシージャ定義で指定されたプロシージャパラメータのデフォルト値を提供します。プロシージャがデフォルト値が定義されていないパラメータの値を予期しており、そのパラメータが欠落しているか DEFAULT キーワードが指定されている場合、エラーが発生します。

EXECUTE ステートメントがバッチの最初のステートメントである場合、EXECUTE キーワードは省略できます。ただし、この単語をすべてのパケットに含めた方が安全です。 EXECUTE ステートメントの使用方法を次の例に示します。

SampleDb を使用します。 IncreaseBudget 10 を実行します。

この例の EXECUTE ステートメントは、IncreaseBudget ストアドプロシージャを実行し、すべてのプロジェクトの予算を 10% 増加します。

次の例は、Employee テーブルと Works_on テーブルのデータを処理するストアドプロシージャを作成する方法を示しています。

ModifyEmpId サンプルプロシージャは、参照整合性 (この場合は Employee テーブルと Works_on テーブルの間) を維持するプロセスの一部としてストアドプロシージャを使用する方法を示しています。同様のストアドプロシージャをトリガー定義内で使用でき、実際に参照整合性が提供されます。

次の例は、ストアドプロシージャでの OUTPUT 句の使用を示しています。

このストアドプロシージャは、次の命令を使用して実行できます。

@quantityDeleteEmployee INT を宣言します。 DeleteEmployee @empId=18316、@counter=@quantityDeleteEmployee 出力を実行します。 PRINT N"削除された従業員: " + Convert(nvarchar(30), @quantityDeleteEmployee);

このプロシージャは、従業員番号 @empId の従業員が取り組んでいるプロジェクトの数をカウントし、結果の値を ©counter パラメータに割り当てます。特定の従業員番号のすべての行が Employee テーブルと Works_on テーブルから削除された後、計算された値が @quantityDeleteEmployee 変数に割り当てられます。

パラメータ値は、OUTPUT オプションが指定されている場合にのみ呼び出し元プロシージャに返されます。上の例では、DeleteEmployee プロシージャは呼び出し元プロシージャに @counter パラメータを渡すため、ストアドプロシージャはシステムに値を返します。したがって、 @counter パラメータは、プロシージャを宣言するときの OUTPUT オプションとプロシージャを呼び出すときの EXECUTE ステートメントの両方で指定する必要があります。

EXECUTE ステートメントの WITH RESULTS SETS 句

SQL Server 2012 では、EXECUTE ステートメントに次のように入力します。 WITH RESULTS SETS 句を使用すると、特定の条件が満たされたときに、ストアドプロシージャの結果セットの形式を変更できます。

次の 2 つの例は、この文を説明するのに役立ちます。最初の例は、WITH RESULTS SETS 句を省略した場合の結果がどのようになるかを示す導入例です。

EmployeesInDept プロシージャは、特定の部門で働くすべての従業員の従業員番号と姓を表示する単純なプロシージャです。部門番号はプロシージャのパラメータであり、プロシージャを呼び出すときに指定する必要があります。このプロシージャを実行すると、見出しがデータベーステーブル内の対応する列の名前と一致する 2 つの列を持つテーブルが作成されます。 ID と姓。結果列のヘッダー (およびそのデータ型) を変更するために、SQL Server 2012 は新しい WITH RESULTS SETS 句を使用します。この文の応用を以下の例に示します。

SampleDb を使用します。 EmployeesInDept "d1" を結果セットとともに実行します (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

この方法で呼び出されたストアドプロシージャを実行した結果は次のようになります。

ご覧のとおり、EXECUTE ステートメントで WITH RESULT SETS 句を使用してストアドプロシージャを実行すると、プロシージャによって生成された結果セット内の列の名前とデータ型を変更できます。したがって、この新しい機能により、ストアドプロシージャを実行し、その結果を新しいテーブルに配置する際の柔軟性が向上します。

ストアドプロシージャの構造の変更

データベースエンジンは次の命令もサポートしています。 手順の変更ストアドプロシージャの構造を変更します。 ALTER PROCEDURE ステートメントは通常、プロシージャ内の Transact-SQL ステートメントを変更するために使用されます。 ALTER PROCEDURE ステートメントのすべてのパラメーターは、CREATE PROCEDURE ステートメントの同じパラメーターと同じ意味を持ちます。このステートメントを使用する主な目的は、既存のストアドプロシージャ権限をオーバーライドしないようにすることです。

データベースエンジンがサポートするのは、 CURSORデータ型。このデータ型は、ストアドプロシージャでカーソルを宣言するために使用されます。 カーソルは、クエリの結果 (通常は一連の行) を保存し、ユーザーがその結果を行ごとに表示できるようにするために使用されるプログラミング構造です。

1 つまたはストアドプロシージャのグループを削除するには、次を使用します。 DROP PROCEDURE命令。ストアドプロシージャを削除できるのは、db_owner および sysadmin 固定ロールの所有者またはメンバーのみです。

ストアドプロシージャと共通言語ランタイム

SQL Server は共通言語ランタイム (CLR) をサポートしています。これにより、C# と Visual Basic を使用してさまざまなデータベースオブジェクト (ストアドプロシージャ、ユーザー定義関数、トリガー、ユーザー定義の集計、カスタムデータ型) を開発できます。 CLR を使用すると、共通のランタイムシステムを使用してこれらのオブジェクトを実行することもできます。

共通言語ランタイムは、オプションを使用して有効または無効にします。 clr_enabledシステム手順 sp_configure、命令によって実行するために起動されます。 再構成。次の例は、sp_configure システムプロシージャを使用して CLR を有効にする方法を示しています。

SampleDb を使用します。 EXEC sp_configure "clr_enabled",1 再構成

CLR を使用してプロシージャを作成、コンパイル、保存するには、次の一連の手順を示されている順序で完了する必要があります。

C# または Visual Basic でストアドプロシージャを作成し、適切なコンパイラを使用してコンパイルします。

使用説明書 アセンブリの作成、対応する実行可能ファイルを作成します。

EXECUTE文を使用してプロシージャを実行します。

以下の図は、前に概説した手順のグラフィカルな図を示しています。以下に、このプロセスについてさらに詳しく説明します。

まず、Visual Studioなどの開発環境で必要なプログラムを作成します。 C# または Visual Basic コンパイラを使用して、完成したプログラムをオブジェクトコードにコンパイルします。このコードはダイナミックリンクライブラリ (.dll) ファイルに保存され、中間実行可能コードを作成する CREATE ASSEMBLY ステートメントのソースとして機能します。次に、CREATE PROCEDURE ステートメントを発行して、実行中のコードをデータベースオブジェクトとして保存します。最後に、使い慣れた EXECUTE ステートメントを使用してプロシージャを実行します。

以下の例は、C# のストアドプロシージャのソースコードを示しています。

System.Data.SqlClient の使用; Microsoft.SqlServer.Server を使用します。 public Partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "従業員数" " + "従業員から"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

このプロシージャは、Employee テーブルの行数をカウントするクエリを実装します。プログラムの先頭でディレクティブを使用して、プログラムの実行に必要な名前空間を指定します。これらのディレクティブを使用すると、対応する名前空間を明示的に指定せずに、ソースコードでクラス名を指定できます。次に、StoredProcedures クラスが定義されます。 SqlProcedure 属性これにより、このクラスがストアドプロシージャであることがコンパイラに通知されます。 CountEmployees() メソッドはクラスコード内で定義されます。データベースシステムへの接続は、クラスのインスタンスを通じて確立されます。 SQL接続。接続を開くには、このインスタンスの Open() メソッドが使用されます。あ CreateCommand() メソッドクラスのインスタンスにアクセスできるようにします SQLコマンド、必要な SQL コマンドが渡されます。

次のコードスニペットでは次のようになります。

Cmd.CommandText = "select count(*) as "従業員数" " + "from Employee";

SELECT ステートメントを使用して、Employee テーブル内の行数をカウントし、結果を表示します。コマンドテキストは、cmd 変数の CommandText プロパティを CreateCommand() メソッドによって返されたインスタンスに設定することによって指定されます。次に呼ばれるのは ExecuteScalar() メソッド SqlCommand インスタンス。このメソッドは、整数データ型に変換されて rows 変数に割り当てられるスカラー値を返します。

これで、Visual Studio を使用してこのコードをコンパイルできるようになりました。このクラスを CLRStoredProcedures というプロジェクトに追加したので、Visual Studio は *.dll 拡張子を持つ同じ名前のアセンブリをコンパイルします。以下の例は、ストアドプロシージャ作成の次のステップである実行可能コードの作成を示しています。この例のコードを実行する前に、コンパイルされた DLL ファイルの場所 (通常はプロジェクトの Debug フォルダーにあります) を知っておく必要があります。

SampleDb を使用します。「D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll」からアセンブリ CLRStoredProcedures を PERMISSION_SET = SAFE で作成してください

CREATE ASSEMBLY ステートメントは、マネージコードを入力として受け取り、CLR ストアドプロシージャ、ユーザー定義関数、トリガーを作成できる対応するオブジェクトを作成します。この命令の構文は次のとおりです。

CREATE ASSEMBLY アセンブリ名 [ AUTHORIZATION 所有者名 ] FROM (dll_file) 構文規則

Assembly_name パラメーターはアセンブリの名前を指定します。オプションの AUTHORIZATION 句は、このアセンブリの所有者としてのロール名を指定します。 FROM 句は、ロードするアセンブリが配置されているパスを指定します。

WITH PERMISSION_SET 句は CREATE ASSEMBLY ステートメントの非常に重要な句であり、常に指定する必要があります。これは、アセンブリコードに付与される一連の権限を定義します。 SAFE 権限セットは最も制限が厳しいです。これらの権限を持つアセンブリコードは、ファイルなどの外部システムリソースにアクセスできません。 EXTERNAL_ACCESS 権利セットを使用すると、アセンブリコードが特定の外部システムリソースにアクセスできるようになりますが、UNSAFE 権利セットを使用すると、データベースシステムの内部と外部の両方のリソースに無制限にアクセスできます。

アセンブリコード情報を保存するには、ユーザーは CREATE ASSEMBLY ステートメントを発行できる必要があります。アセンブリの所有者は、命令を実行するユーザー (またはロール) です。 CREATE SCHEMA ステートメントの AUTHORIZATION 句を使用すると、別のユーザーをアセンブリの所有者にすることができます。

データベースエンジンは、ALTER ASSEMBLY ステートメントと DROP ASSEMBLY ステートメントもサポートしています。 ALTER ASSEMBLY ステートメントアセンブリを最新バージョンに更新するために使用されます。この命令は、対応するアセンブリに関連付けられたファイルも追加または削除します。 ドロップアセンブリ命令指定されたアセンブリとそのすべての関連ファイルを現在のデータベースから削除します。

以下の例は、以前に実装したマネージコードに基づいてストアドプロシージャを作成する方法を示しています。

SampleDb を使用します。 GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

この例の CREATE PROCEDURE 命令は、次のものが含まれているという点で、前の例の同じ命令とは異なります。 EXTERNAL NAME パラメータ。このオプションは、コードが共通言語ランタイムによって生成されることを指定します。この文の名前は 3 つの部分で構成されます。

アセンブリ名.クラス名.メソッド名

Assembly_name - アセンブリの名前を示します。

class_name - 一般クラスの名前を示します。

Method_name - オプションの部分。クラス内で定義されているメソッドの名前を指定します。

CountEmployees プロシージャの実行を次の例に示します。

SampleDb を使用します。 DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- 7 を返す

PRINT ステートメントは、Employee テーブルの現在の行数を返します。

SQL Server を使用する場合、ユーザーは特定のアクションを実装する独自のプロシージャを作成できます。ストアドプロシージャは本格的なデータベースオブジェクトであるため、それぞれが特定のデータベースに保存されます。ストアドプロシージャへの直接呼び出しは、プロシージャが配置されているデータベースのコンテキストで実行される場合にのみ可能です。

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

SQL Server にはいくつかの種類のストアドプロシージャがあります。

システムストアドプロシージャは、さまざまな管理アクションを実行するように設計されています。ほとんどすべてのサーバー管理活動は、彼らの助けを借りて実行されます。システムストアドプロシージャは、システムテーブルの操作を提供するインターフェイスであると言えます。最終的には、ユーザーデータベースとシステムデータベースの両方のシステムテーブルからデータを変更、追加、削除、取得することになります。システムストアドプロシージャには sp_ というプレフィックスが付けられ、システムデータベースに保存され、他のデータベースのコンテキストで呼び出すことができます。

カスタムストアドプロシージャは特定のアクションを実装します。ストアドプロシージャは、本格的なデータベースオブジェクトです。その結果、各ストアドプロシージャは特定のデータベースに配置され、そこで実行されます。

一時ストアドプロシージャは短期間のみ存在し、その後はサーバーによって自動的に破棄されます。それらはローカルとグローバルに分けられます。ローカルの一時ストアドプロシージャは、それが作成された接続からのみ呼び出すことができます。このようなプロシージャを作成する場合は、単一の # 文字で始まる名前を付ける必要があります。すべての一時オブジェクトと同様に、このタイプのストアドプロシージャは、ユーザーが切断されるか、サーバーが再起動または停止されると自動的に削除されます。グローバル一時ストアドプロシージャは、同じプロシージャを持つサーバーからのあらゆる接続で使用できます。これを定義するには、## の文字で始まる名前を付けるだけです。これらのプロシージャは、サーバーが再起動または停止されるとき、またはプロシージャが作成されたコンテキスト内の接続が閉じられるときに削除されます。

トリガー

トリガーストアドプロシージャの一種です。これらは、データ操作言語 (DML) 演算子がテーブルで実行されるときに実行されます。トリガーは、データの整合性をチェックしたり、トランザクションをロールバックしたりするために使用されます。

引き金はコンパイルされた SQL プロシージャであり、その実行はリレーショナルデータベース内での特定のイベントの発生によって条件付けされます。トリガーの使用は、ほとんどの場合、データベースユーザーにとって非常に便利です。それでも、それらを使用すると、I/O 操作に追加のリソースコストがかかることがよくあります。ストアドプロシージャまたはアプリケーションプログラムを使用して同じ結果 (はるかに少ないオーバーヘッドで) を達成できる場合、トリガーの使用は現実的ではありません。

トリガーは、データベース内のデータの整合性を維持するために使用される特別な SQL サーバーツールです。整合性の制約、ルール、およびデフォルトは、常に必要なレベルの機能を達成できるとは限りません。多くの場合、信頼性と現実性を確保するために、複雑なデータ検証アルゴリズムを実装する必要があります。さらに、必要に応じて関連データを変更できるように、テーブル値の変更を監視する必要がある場合があります。トリガーは、ルールや基準値などに従ってすべての操作が完了した後に有効になるフィルターの一種と考えることができます。

引き金は、トリガーが関連付けられているテーブル内のデータを変更しようとすると、サーバーによって自動的に起動される特殊なタイプのストアドプロシージャです。毎 引き金特定のテーブルに関連付けられています。行われるすべてのデータ変更は 1 つのトランザクションとみなされます。エラーまたはデータ整合性違反が検出された場合、トランザクションはロールバックされます。したがって、変更は禁止されています。トリガーによってすでに行われた変更も元に戻されます。

作成します 引き金データベース所有者のみ。この制限により、テーブルの構造や他のオブジェクトをテーブルに接続する方法などが誤って変更されるのを防ぐことができます。

引き金これは非常に有用な治療法であると同時に、危険な治療法でもあります。したがって、その操作のロジックが間違っていると、データベース全体を簡単に破壊してしまう可能性があるため、トリガーは非常に慎重にデバッグする必要があります。

通常のサブルーチンとは異なり、 引き金トリガーイベントが発生するたびに暗黙的に実行され、引数はありません。これをアクティブにすることを、トリガーの発動と呼ぶこともあります。トリガーを使用すると、次の目標が達成されます。

入力されたデータの正確性を検証し、テーブルに設定された整合性制約を使用して維持するのが不可能ではないにしても困難な複雑なデータ整合性制約を適用します。

特定の方法で実装されたテーブルを更新するときに、特定のアクションを実行するよう警告する警告を発行します。

行われた変更および変更を実行した人物に関する情報を記録することによる監査情報の蓄積。

レプリケーションのサポート。

CREATE TRIGGER コマンドの基本的な形式を以下に示します。

<Определение_триггера>::=

CREATE TRIGGER トリガー名

前 | 後<триггерное_событие>

の上<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

トリガーイベントは、テーブル内の行の挿入、削除、更新で構成されます。後者の場合、トリガーイベントに対して特定のテーブル列名を指定できます。トリガーのタイミングは、BEFORE キーワード ( 引き金関連付けられたイベントが実行される前に実行されるか、AFTER (イベントが実行された後) に実行されます。

トリガーによって実行されるアクションは、イベントの対象となる行ごと (FOR EACH ROW)、またはイベントごとに 1 回だけ (FOR EACH STATEMENT) 指定されます。

トリガーが正しく書かれていないと、デッドロックなどの深刻な問題が発生する可能性があります。トリガーは多くのリソースを長期間ブロックする可能性があるため、アクセスの競合を最小限に抑えるために特別な注意を払う必要があります。

引き金は現在のデータベース内でのみ作成できますが、リモートサーバー上にあるデータベースを含め、トリガー内の他のデータベースにアクセスすることは可能です。

ストアドプロシージャプロシージャが配置されているデータベースのコンテキストで実行される場合にのみ可能です。

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

SQL Serverにはいくつかの種類がありますストアドプロシージャ.

システムストアドプロシージャさまざまな管理アクションを実行するように設計されています。ほとんどすべてのサーバー管理活動は、彼らの助けを借りて実行されます。システム的に言えることは、ストアドプロシージャはシステムテーブルの操作を提供するインターフェイスであり、最終的にはユーザーデータベースとシステムデータベースの両方のシステムテーブルからのデータの変更、追加、削除、取得を行います。システムストアドプロシージャ sp_ プレフィックスが付いており、システムデータベースに保存され、他のデータベースのコンテキストで呼び出すことができます。
カスタムストアドプロシージャ特定のアクションを実行します。ストアドプロシージャ– 本格的なデータベースオブジェクト。その結果、それぞれのストアドプロシージャ実行される特定のデータベースに配置されます。
一時的ストアドプロシージャ一時的にのみ存在し、その後サーバーによって自動的に破棄されます。それらはローカルとグローバルに分けられます。ローカル一時的ストアドプロシージャ作成された接続からのみ呼び出すことができます。このようなプロシージャを作成するときは、単一の # 文字で始まる名前を付ける必要があります。すべての一時的なオブジェクトと同様に、ストアドプロシージャこのタイプのファイルは、ユーザーが切断されるか、サーバーが再起動または停止されると自動的に削除されます。グローバルテンポラリストアドプロシージャ同じ手順を持つサーバーからのすべての接続で利用できます。これを定義するには、文字 ## で始まる名前を付けるだけです。これらのプロシージャは、サーバーが再起動または停止されるとき、またはプロシージャが作成されたコンテキスト内の接続が閉じられるときに削除されます。

ストアドプロシージャの作成、変更、削除

創造ストアドプロシージャ次の問題を解決する必要があります。

作成されたタイプの決定ストアドプロシージャ: 一時的またはカスタム。さらに、独自のシステムを作成することもできますストアドプロシージャ、sp_ という接頭辞を付けた名前を付けて、システムデータベースに配置します。この手順は、任意のローカルサーバーデータベースのコンテキストで使用できます。
アクセス権の計画。作成中ストアドプロシージャデータベースオブジェクトに対して、それを作成したユーザーと同じアクセス権を持つことを考慮する必要があります。
意味ストアドプロシージャのパラメータ。ほとんどのプログラミング言語に含まれるプロシージャと同様に、ストアドプロシージャ入力パラメータと出力パラメータがある場合があります。
コード開発ストアドプロシージャ。プロシージャコードには、他の SQL コマンドの呼び出しを含む、一連の SQL コマンドを含めることができます。ストアドプロシージャ.

新しいものを作成し、既存のものを変更するストアドプロシージャ次のコマンドを使用して実行します。

<определение_процедуры>::= (CREATE | ALTER ) プロシージャ名 [;番号] [(@パラメータ名データ型 ) [=デフォルト] ][,...n] AS sql_operator [...n]

このコマンドのパラメータを見てみましょう。

接頭辞 sp_ 、 # 、 ## を使用すると、作成されたプロシージャをシステムまたは一時的なプロシージャとして定義できます。コマンド構文からわかるように、作成されたプロシージャを所有する所有者の名前や、プロシージャを配置するデータベースの名前を指定することはできません。したがって、作成したものを配置するには、ストアドプロシージャ特定のデータベースでは、そのデータベースのコンテキストで CREATE PROCEDURE コマンドを発行する必要があります。本体から回転する場合ストアドプロシージャ短縮名は、同じデータベースのオブジェクトに対して、つまりデータベース名を指定せずに使用できます。他のデータベースにあるオブジェクトにアクセスする必要がある場合は、データベース名の指定が必須です。

名前の数字は識別番号ですストアドプロシージャ、これによりプロシージャのグループ内で一意に識別されます。管理を容易にするために、プロシージャは論理的に同じタイプになります。ストアドプロシージャ同じ名前で異なる識別番号を与えることでグループ化できます。

作成した入出力データを転送するにはストアドプロシージャパラメータを使用できますが、その名前はローカル変数の名前と同様に @ 記号で始まる必要があります。 1つストアドプロシージャ複数のパラメータをカンマで区切って指定できます。プロシージャの本体では、このプロシージャのパラメータの名前と名前が一致するローカル変数を使用しないでください。

対応するデータ型を判断するには、ストアドプロシージャのパラメータ、ユーザー定義のものを含むあらゆる SQL データ型が適しています。ただし、CURSOR データ型は次のようにのみ使用できます。出力パラメータストアドプロシージャ、つまり OUTPUT キーワードを指定します。

OUTPUT キーワードの存在は、対応するパラメータがデータを返すことを意図していることを意味します。ストアドプロシージャ。ただし、これは、パラメーターが値を渡すのに適していないという意味ではありません。ストアドプロシージャ。 OUTPUT キーワードを指定すると、サーバーに終了が指示されます。ストアドプロシージャパラメータの現在の値を、プロシージャの呼び出し時にパラメータの値として指定したローカル変数に割り当てます。 OUTPUT キーワードを指定する場合、プロシージャを呼び出すときの対応するパラメーターの値は、ローカル変数を使用してのみ設定できることに注意してください。通常のパラメータに許可されている式や定数の使用は許可されません。

VARYING キーワードは以下と組み合わせて使用されます。

ストアドプロシージャストアドプロシージャ) は、名前付きデータベースプログラムオブジェクトです。 SQL Server にはいくつかの種類のストアドプロシージャがあります。

システムストアドプロシージャシステムストアドプロシージャ) は DBMS 開発者によって提供され、システムディレクトリでアクションを実行したり、システム情報を取得したりするために使用されます。通常、それらの名前は接頭辞「sp_」で始まります。すべての種類のストアドプロシージャを実行するには、EXECUTE コマンド (短縮して EXEC と呼ぶこともできます) を使用します。たとえば、sp_helplogins ストアドプロシージャをパラメータなしで実行すると、アカウント名に関する 2 つのレポートが生成されます。 （英語）ログイン) と各データベース内の対応するユーザー （英語）ユーザー）。

EXEC sp_helplogins;

システムストアドプロシージャを使用して実行されるアクションを理解するには、表を参照してください。 10.6 にいくつかの例を示します。 SQL Server には、合計で 1,000 を超えるシステムストアドプロシージャが存在します。

表10.6

SQL Server システムストアドプロシージャの例

ユーザーは、ユーザーデータベースおよび一時オブジェクトのデータベースにストアドプロシージャを作成できます。後者の場合、ストアドプロシージャは次のようになります。 一時的な。一時テーブルの場合と同様、一時ストアドプロシージャの名前は、ローカルの一時ストアドプロシージャの場合は接頭辞 "#" で始まり、グローバルの場合は "##" で始まる必要があります。ローカル一時プロシージャは、それが作成された接続内でのみ使用できます。グローバル一時プロシージャは、他の接続内でも使用できます。

SQL Server のプログラム可能なオブジェクトは、Transact-SQL ツールまたはアセンブリを使用して作成できます。 （英語） Microsoft.Net Framework の CRL (Common Language Runtime) 環境のアセンブリ)。このチュートリアルでは、最初の方法のみを説明します。

ストアドプロシージャを作成するには、CREATE PROCEDURE (PROC と短縮することもできます) ステートメントを使用します。その形式は次のとおりです。

CREATE (PROC I PROCEDURE) proc_name [ ; 番号 ]

[(gパラメータのデータ型)

[「デフォルト] |

[と [ ,...n ] ]

【複製用】

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

ストアドプロシージャ (またはトリガー、関数、ビュー) が ENCRYPTION オプションを使用して作成された場合、そのコードはテキストが判読できなくなるような方法で変換されます。同時に、で述べたように、使用されているアルゴリズムは SQL Server の以前のバージョンから転送されたものであり、信頼できる保護アルゴリズムとは見なされません。逆変換を迅速に実行できるユーティリティがあります。

RECOMPILE オプションは、プロシージャが呼び出されるたびにシステムがテキストを再コンパイルすることを指定します。通常の場合、最初の実行時にコンパイルされたプロシージャはキャッシュに保存されるため、パフォーマンスが向上します。

EXECUTE AS は、プロシージャが実行されるセキュリティコンテキストを指定します。次に、値の 1 つ f CALLER | 自己 | オーナー | 「ユーザー名」)。 CALLER がデフォルトであり、このモジュールを呼び出すユーザーのセキュリティコンテキストでコードが実行されることを意味します。したがって、ユーザーはプログラム可能なオブジェクト自体に対する権限だけでなく、そのオブジェクトの影響を受ける他のデータベースオブジェクトに対する権限も持っている必要があります。 EXECUTE AS SELF は、プログラム可能なオブジェクトを作成または変更するユーザーのコンテキストを使用することを意味します。 OWNER は、プロシージャの現在の所有者のコンテキストでコードが実行されることを指定します。所有者が指定されていない場合は、そのスキーマが属するスキーマの所有者とみなされます。 EXECUTE AS "user_name" を使用すると、ユーザー名を (一重引用符で囲んで) 明示的に指定できます。

プロシージャにはパラメータを指定できます。これらは、プロシージャに値を渡すために使用されるローカル変数です。パラメータがキーワード OUTPUT (または略して OUT) を使用して宣言されている場合、それは出力値です。完了後にプロシージャ内でパラメータに指定された値は、プロシージャを呼び出したプログラムで使用できます。 READONLY キーワードは、ストアドプロシージャ内でパラメータの値を変更できないことを意味します。

パラメータにはデフォルト値を割り当てることができ、プロシージャの呼び出し時にパラメータ値が明示的に指定されなかった場合に使用されます。例を見てみましょう:

CREATE PROC surma (@a int, @b int=0,

©結果 int 出力) AS

SET @結果=0a+0b

3 つのパラメーターを持つプロシージャを作成しました。@b パラメーターのデフォルト値は =0 で、@result パラメーターは出力パラメーターであり、呼び出し側プログラムに値を返します。実行されるアクションは非常に単純です。出力パラメーターは 2 つの入力パラメーターの合計値を受け取ります。

SQL Server Management Studio で作業している場合、作成されたストアドプロシージャはプログラマブルデータベースオブジェクトセクションにあります。 （英語）プログラマビリティ) はストアドプロシージャのサブセクションにあります (図 10.2)。

プロシージャを呼び出すときは、変数と定数の両方を入力パラメータとして使用できます。 2 つの例を見てみましょう。最初の方法では、プロシージャの入力パラメータが定数として明示的に指定され、呼び出し内の出力パラメータに OUTPUT キーワードが指定されます。 2 番目のオプションは、最初の入力パラメータとして変数の値を使用し、DEFAULT キーワードを使用して 2 番目のパラメータにデフォルト値を使用することを指定します。

米。 10.2.

@с int を宣言します。

EXEC summa 10.5,@c OUTPUT;

0c を印刷します。 – 15 個が表示されます

Gi int = 5 を宣言します。

– 呼び出すときはデフォルト値を使用します

EXEC 合計 Gi,DEFAULT , 0c OUTPUT;

0c を印刷します。 – 5が表示されます

次に、プロシージャが終了する戻りコードの分析の例を考えてみましょう。 Bookl テーブル内の書籍が指定された範囲の年に出版された数を計算する必要があるとします。さらに、最初の年が最終年より大きい場合、プロシージャは「1」を返し、カウントされません。それ以外の場合は、本の数をカウントして 0 を返します。

CREATE PROC dbo.rownum (0Firs Year int, GLast Year int, 0result int OUTPUT) AS

IF 0初年>0昨年 RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

0Firs Year と 0Last Year の間の場所);

このプロシージャを呼び出す変形例を考えてみましょう。このプロシージャでは、リターンコードが整数変数 0ret に格納され、その後その値が分析されます (この場合は 1 になります)。 PRINT ステートメントで使用される CAST 関数は、整数変数 Gres の値を文字列型に変換するために使用されます。

DECLARE 0ret int、Gres int

EXEC Gret = rownum 2004、2002、Gres OUT;

IF 0ret=l PRINT "開始年が終了年より大きいです"

PRINT "本の数" + CAST(Gres as varchar(20))

ストアドプロシージャは、テーブルからデータを読み取るだけでなく、データを変更したり、テーブルやその他の多数のデータベースオブジェクトを作成したりすることもできます。

ただし、ストアドプロシージャからスキーマ、関数、トリガー、プロシージャ、ビューを作成することはできません。

次の例は、これらの機能と、一時オブジェクトのスコープに関連する問題の両方を示しています。次のストアドプロシージャは、一時テーブル #TaL2 の存在を確認します。このテーブルが存在しない場合は作成されます。この後、2 つの列の値がテーブル #TaL2 に入力され、SELECT ステートメントを使用してテーブルの内容が表示されます。

CREATE PROC My_Procl (@id int, @name varchar(30))

OBJECT_ID("tempdb.dbo.#Tab21) が NULL の場合

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo。 #タブ2 –№1

ストアドプロシージャを初めて呼び出す前に、そこで使用される一時テーブル #TaL2 を作成します。 EXEC 演算子に注意してください。前の例では、パラメータは「位置によって」プロシージャに渡されましたが、この場合、パラメータを渡すための別の形式が使用されます。「名前によって」、パラメータの名前とその値が明示的に示されています。

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

上の例では、SELECT ステートメントは 2 回処理されます。1 回目はプロシージャ内で、2 回目は呼び出し元のコードフラグメント (コメント「No. 2」でマーク) から処理されます。

プロシージャの 2 回目の呼び出しの前に、一時テーブル #TaL2 を削除します。次に、同じ名前の一時テーブルがストアドプロシージャから作成されます。

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="イワン", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

この場合、プロシージャ内にある SELECT ステートメント (コメント「Xa 1」付き) のみがデータを表示します。ストアドプロシージャで作成された一時テーブルは、プロシージャが返された時点ですでに tempdb データベースから削除されているため、SELECT "No. 2" を実行するとエラーが発生します。

DROP PROCEDURE ステートメントを使用してストアドプロシージャを削除できます。その形式を以下に示します。 1 つのステートメントで複数のストアドプロシージャを削除し、それらをカンマで区切ってリストできます。

DROP (PROC I PROCEDURE) (プロシージャ) [

たとえば、以前に作成した Summa プロシージャを削除してみましょう。

DROP PROC 合計。

ALTER PROCEDURE ステートメントを使用して、既存のプロシージャを変更 (実際には再定義) できます (許可されています)。

略称PROC）。 ALTER キーワードを除き、ステートメントの形式は CREATE PROCEDURE の形式と基本的に同じです。たとえば、dbo プロシージャを変更してみましょう。 rownum を、所有者のセキュリティコンテキストで実行するように設定します。

ALTER PROC dbo.rownum (SFirsyear int,

SLast Year int、Sresult int OUTPUT)

所有者として実行を使用 – インストール可能なオプション

IF 0Firs Year>0Last Year RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

SFirs Year と SLast Year の間);

場合によっては、コマンドを動的に生成し、データベースサーバー上で実行することが必要になる場合があります。この問題は、EXEC 演算子を使用して解決することもできます。以下の例では、年属性が変数で指定された値と等しい場合に、Bookl テーブルからレコードを取得します。

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

動的に生成された命令の実行により、「SQL インジェクション」などのコンピューター攻撃を実行するための前提条件が作成されます。 （英語） SQL インジェクション)。攻撃の本質は、攻撃者が動的に生成されたクエリに独自の SQL コードを挿入することです。これは通常、置換されるパラメータがユーザー入力の結果から取得される場合に発生します。

前の例を少し変更してみましょう。

DECLARE 0у varchar(100);

SET 0у="2ООО"; – ユーザーからこれを受け取りました

SET ステートメントで割り当てられた文字列値をユーザーから (Web アプリケーション経由などの方法を問わず) 受け取ったと仮定すると、この例はコードの「通常の」動作を示しています。

DECLARE 0у varchar(100);

SET 0у="2000; dbo.Book2 から削除"; - 注射

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

このような場合、可能であれば、SQL インジェクションの障壁の 1 つであるパラメーターのタイプを制御できるシステムストアドプロシージャ sp_executcsql を使用することをお勧めします。形式を詳しく考慮せずに、前に示したものと同様の例を見てみましょう。

実行 sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

これにより、クエリで使用されるパラメーターの種類が明示的に指定され、SQL Server が実行中にそれを制御します。引用符の前の文字「N」は、プロシージャで必要とされる Unicode リテラル定数であることを示します。パラメータには定数値だけでなく、別の変数の値も割り当てることができます。

ストアドプロシージャの概念が定義されています。パラメーターを使用したストアドプロシージャの作成、変更、および使用の例を示します。入力パラメータと出力パラメータの定義が示されています。ストアドプロシージャの作成と呼び出しの例が示されています。

ストアドプロシージャの概念

ストアドプロシージャ相互接続された SQL ステートメントのグループであり、これを使用するとプログラマの作業がより簡単かつ柔軟になります。ストアドプロシージャ多くの場合、一連の個別の SQL ステートメントよりもはるかに単純です。ストアドプロシージャは、1 つ以上の SQL ステートメントまたは関数で構成され、コンパイルされた形式でデータベースに保存される一連のコマンドです。データベース内での実行ストアドプロシージャ個々の SQL ステートメントの代わりに、ユーザーには次の利点があります。

必要な演算子はすでにデータベースに含まれています。
彼らは全員ステージを通過しました解析する実行可能形式です。前にストアドプロシージャの実行 SQL Server は実行プランを生成し、最適化とコンパイルを実行します。
ストアドプロシージャサポートモジュール式プログラミング大きなタスクを、独立した、より小さく、管理しやすい部分に分割できるためです。
ストアドプロシージャ他の人に影響を与える可能性がありますストアドプロシージャおよび機能。
ストアドプロシージャ他の種類のアプリケーションプログラムから呼び出すことができます。
いつもの、ストアドプロシージャ個々のステートメントのシーケンスよりも高速に実行されます。
ストアドプロシージャ使いやすい: コマンドは数十または数百のコマンドで構成できますが、実行するには目的のコマンドの名前を指定するだけです。ストアドプロシージャ。これにより、クライアントからサーバーに送信されるリクエストのサイズが軽減され、ネットワークの負荷が軽減されます。

プロシージャを実行する場所と同じ場所に保存すると、ネットワーク上で転送されるデータ量が削減され、システム全体のパフォーマンスが向上します。応用ストアドプロシージャソフトウェアシステムのメンテナンスと変更が簡素化されます。通常、ルールとデータ処理アルゴリズムの形式のすべての整合性制約はデータベースサーバーに実装され、セットとしてエンドアプリケーションで利用できます。ストアドプロシージャ、データ処理インターフェイスを表します。データの整合性を確保するため、またセキュリティ目的のため、アプリケーションは通常、データへの直接アクセスを受け取りません。データを扱うすべての作業は、特定のメソッドを呼び出すことによって実行されます。ストアドプロシージャ.

このアプローチにより、データ処理アルゴリズムの変更が非常に簡単になり、すべてのネットワークユーザーがすぐに利用できるようになり、アプリケーション自体に変更を加えずにシステムを拡張できるようになります。ストアドプロシージャデータベースサーバー上で。開発者は、アプリケーションを再コンパイルしたり、そのコピーを作成したり、新しいバージョンで作業するようにユーザーに指示したりする必要はありません。ユーザーは、システムに変更が加えられたことにさえ気づいていない可能性があります。

ストアドプロシージャテーブルや他のデータベースオブジェクトとは独立して存在します。これらはクライアントプログラムによって呼び出されます。ストアドプロシージャまたはトリガー。開発者はアクセス権を管理できます。ストアドプロシージャ、その実行を許可または禁止します。コードを変更するストアドプロシージャ所有者または固定データベースロールのメンバーのみが許可されます。必要に応じて、その所有権をあるユーザーから別のユーザーに譲渡できます。

MS SQL Server環境のストアドプロシージャ

SQL Server を使用する場合、ユーザーは特定のアクションを実装する独自のプロシージャを作成できます。ストアドプロシージャは本格的なデータベースオブジェクトであるため、それぞれが特定のデータベースに保存されます。直接電話ストアドプロシージャプロシージャが配置されているデータベースのコンテキストで実行される場合にのみ可能です。

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

SQL Serverにはいくつかの種類がありますストアドプロシージャ.

システムストアドプロシージャさまざまな管理アクションを実行するように設計されています。ほとんどすべてのサーバー管理活動は、彼らの助けを借りて実行されます。システム的に言えることは、ストアドプロシージャはシステムテーブルの操作を提供するインターフェイスであり、最終的にはユーザーデータベースとシステムデータベースの両方のシステムテーブルからのデータの変更、追加、削除、取得を行います。システムストアドプロシージャ sp_ プレフィックスが付いており、システムデータベースに保存され、他のデータベースのコンテキストで呼び出すことができます。
カスタムストアドプロシージャ特定のアクションを実行します。ストアドプロシージャ– 本格的なデータベースオブジェクト。その結果、それぞれのストアドプロシージャ実行される特定のデータベースに配置されます。
一時的ストアドプロシージャ一時的にのみ存在し、その後サーバーによって自動的に破棄されます。それらはローカルとグローバルに分けられます。ローカル一時的ストアドプロシージャ作成された接続からのみ呼び出すことができます。このようなプロシージャを作成するときは、単一の # 文字で始まる名前を付ける必要があります。すべての一時的なオブジェクトと同様に、ストアドプロシージャこのタイプのファイルは、ユーザーが切断されるか、サーバーが再起動または停止されると自動的に削除されます。グローバルテンポラリストアドプロシージャ同じ手順を持つサーバーからのすべての接続で利用できます。これを定義するには、文字 ## で始まる名前を付けるだけです。これらのプロシージャは、サーバーが再起動または停止されるとき、またはプロシージャが作成されたコンテキスト内の接続が閉じられるときに削除されます。

ストアドプロシージャの作成、変更、削除

創造ストアドプロシージャ次の問題を解決する必要があります。

作成されたタイプの決定ストアドプロシージャ: 一時的またはカスタム。さらに、独自のシステムを作成することもできますストアドプロシージャ、sp_ という接頭辞を付けた名前を付けて、システムデータベースに配置します。この手順は、任意のローカルサーバーデータベースのコンテキストで使用できます。
アクセス権の計画。作成中ストアドプロシージャデータベースオブジェクトに対して、それを作成したユーザーと同じアクセス権を持つことを考慮する必要があります。
意味ストアドプロシージャのパラメータ。ほとんどのプログラミング言語に含まれるプロシージャと同様に、ストアドプロシージャ入力パラメータと出力パラメータがある場合があります。
コード開発ストアドプロシージャ。プロシージャコードには、他の SQL コマンドの呼び出しを含む、一連の SQL コマンドを含めることができます。ストアドプロシージャ.

新しいものを作成し、既存のものを変更するストアドプロシージャ次のコマンドを使用して実行します。

<определение_процедуры>::= (CREATE | ALTER ) PROC プロシージャ名 [;番号] [(@パラメータ名データ型 ) [=デフォルト] ][,...n] AS sql_operator [...n]

このコマンドのパラメータを見てみましょう。

VARYING キーワードは、CURSOR 型の OUTPUT パラメータと組み合わせて使用されます。それは次のことを決定します出力パラメータ結果セットが存在します。

DEFAULT キーワードは、対応する値を表します。デフォルトパラメータ。したがって、プロシージャを呼び出すときに、対応するパラメータの値を明示的に指定する必要はありません。

サーバーはクエリ実行プランとコンパイルされたコードをキャッシュするため、次回プロシージャが呼び出されるときは、既製の値が使用されます。ただし、場合によっては、プロシージャコードを再コンパイルする必要がある場合もあります。 RECOMPILE キーワードを指定すると、システムに実行計画を作成するように指示されます。ストアドプロシージャ彼女が電話するたびに。

FOR REPLICATION パラメータは、データを複製し、作成されたデータを有効にする場合に必要です。ストアドプロシージャ出版用の記事として。

ENCRYPTION キーワードは、コードを暗号化するようにサーバーに指示します。ストアドプロシージャ、作業を実装する独自のアルゴリズムの使用に対する保護を提供できます。ストアドプロシージャ.

AS キーワードは本文自体の先頭に配置されますストアドプロシージャ、つまりこれまたはそのアクションを実装するための一連の SQL コマンド。プロシージャの本体では、ほぼすべての SQL コマンドを使用でき、トランザクションを宣言したり、ロックを設定したり、その他のコマンドを呼び出すことができます。ストアドプロシージャ。から出るストアドプロシージャ RETURNコマンドを使用して実行できます。

ストアドプロシージャの削除次のコマンドによって実行されます。

DROP PROCEDURE (プロシージャ名) [,...n]

ストアドプロシージャの実行

のためにストアドプロシージャを実行する使用されるコマンドは次のとおりです。

[[ EXEC [ UTE] プロシージャ名 [;番号] [[@パラメータ名=](値 | @変数名) |][,...n]

電話の場合ストアドプロシージャパッケージ内の唯一のコマンドではないため、EXECUTE コマンドが存在する必要があります。さらに、このコマンドは、別のプロシージャまたはトリガーの本体からプロシージャを呼び出すために必要です。

プロシージャを呼び出すときの OUTPUT キーワードの使用は、次のときに宣言されたパラメータに対してのみ許可されます。プロシージャの作成 OUTPUT キーワードを使用します。

プロシージャを呼び出すときにパラメータにキーワード DEFAULT が指定されている場合、それが使用されます。デフォルト値。当然のことながら、指定された単語 DEFAULT は、それが定義されているパラメーターに対してのみ許可されます。デフォルト値.

EXECUTE コマンドの構文は、プロシージャを呼び出すときにパラメータ名を省略できることを示しています。ただし、この場合、ユーザーはパラメータの値を、パラメータの値を指定するときにリストされたのと同じ順序で指定する必要があります。プロシージャの作成。パラメータへの割り当てデフォルト値、出品時にスキップすることはできません。定義されているパラメータを省略したい場合デフォルト値を呼び出すときにパラメータ名を明示的に指定するだけで十分です。ストアドプロシージャ。さらに、この方法では、パラメータとその値を任意の順序でリストできます。

プロシージャを呼び出すときは、値を含むパラメータ名、またはパラメータ名のない値のみを指定することに注意してください。これらを組み合わせることはできません。

例12.1。 パラメータのないプロシージャ。イワノフが購入した商品の名前と価格を取得する手順を作成します。

CREATE PROC my_proc1 AS SELECT Product.Name、Product.Price*Transaction.Quantity AS Cost、Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE顧客 .Last name='イワノフ' 例12.1。イワノフが購入した商品の名前と価値を取得する手順。

のために手続きへのアクセス次のコマンドを使用できます。

EXEC my_proc1 または my_proc1

このプロシージャはデータセットを返します。

例12.2。 パラメータのないプロシージャ。第一級品の価格を10％引き下げる手順を作成します。

のために手続きへのアクセス次のコマンドを使用できます。

EXEC my_proc2 または my_proc2

このプロシージャはデータを返しません。

例12.3。 入力パラメータを使用したプロシージャ。特定の顧客が購入した商品の名前と価格を取得するプロシージャを作成します。

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Product.Name、Product.Price*Transaction.Quantity AS Cost、Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode) ON Customer。 CustomerCode =Transaction.ClientCode WHERE Client.LastName=@k 例12.3。特定の顧客が購入した商品の名前と価格を取得する手順。

のために手続きへのアクセス次のコマンドを使用できます。

EXEC my_proc3 "イワノフ" または my_proc3 @k="イワノフ"

例12.4。。指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

のために手続きへのアクセス次のコマンドを使用できます。

EXEC my_proc4 "ワッフル",0.05 または EXEC my_proc4 @t="ワッフル", @p=0.05

例12.5。 入力パラメータを使用したプロシージャおよびデフォルト値。指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

CREATE PROC my_proc5 @t VARCHAR(20)=’Candy`, @p FLOAT=0.1 AS UPDATE Product SET Price=Price*(1-@p) WHERE Type=@t 例12.5。入力パラメータとデフォルト値を使用したプロシージャ。指定されたタイプの製品の価格を、指定された%に従って値下げするプロシージャを作成します。

のために手続きへのアクセス次のコマンドを使用できます。

EXEC my_proc5 "ワッフル",0.05 または EXEC my_proc5 @t="ワッフル", @p=0.05 または EXEC my_proc5 @p=0.05

この場合、キャンディーの価格が減額されます (プロシージャを呼び出すときに型の値は指定されず、デフォルトで使用されます)。

後者の場合、プロシージャを呼び出すときに両方のパラメータ (タイプとパーセンテージの両方) が指定されず、それらの値がデフォルトで取得されます。

例12.6。 入力パラメータと出力パラメータを使用したプロシージャ。特定の月に販売された商品の総原価を決定するプロシージャを作成します。

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Product.Price*Transaction.Quantity) FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Month(Transaction.Date) HAVING Month(トランザクション.日付)=@m 例12.6。入力パラメータと出力パラメータを使用するプロシージャ。特定の月に販売された商品の総原価を決定するプロシージャを作成します。

のために手続きへのアクセス次のコマンドを使用できます。

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

このコマンドブロックを使用すると、1 月に販売された商品の原価を決定できます ( 入力パラメータ月は 1) として指定されます。

特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

まず、従業員が勤務する会社を決定する手順を作成します。

例12.7。使用法ネストされたプロシージャ。特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

次に、関心のある会社が購入した商品の合計数量を計算するプロシージャを作成します。

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Transaction.Quantity) FROM Client INNER JOIN Transaction ON Client.ClientCode= Transaction.ClientCode GROUP BY Client.Firm HAVING Client.Company=@firm 例12.7。特定の従業員が勤務する会社が購入した商品の合計数量を決定する手順を作成します。

このプロシージャは、次のコマンドを使用して呼び出されます。

DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k

シール