SQLストアドプロシージャを作成します。 ms SQL サーバー環境のストアドプロシージャ。ストアドプロシージャの作成

ストアドプロシージャ

この章の主題は、ビジネスロジックを実装するために InterBase データベースアプリケーションの開発者に提供される最も強力なツールの 1 つです。ストアドプロシージャ (英語、ストイックプロシージャ) を使用すると、アプリケーションロジックの重要な部分をデータベースレベルで実装できるため、アプリケーション全体のパフォーマンスを向上させ、データ処理を集中化し、割り当てられたタスクを完了するために必要なコードの量を削減します。かなり複雑なデータベースアプリケーションのほとんどは、ストアドプロシージャを使用せずには実行できません。
ストアドプロシージャを使用することによるこれらのよく知られた利点 (ほとんどのリレーショナル DBMS に共通) に加えて、InterBase ストアドプロシージャはほぼ完全なデータセットとして機能し、返された結果を通常の SQL クエリで使用できるようになります。
多くの場合、初心者の開発者は、ストアドプロシージャを単にデータベース内で何かを実行する特定の SQL クエリのセットとして想像します。また、ストアドプロシージャを使用する作業は、同じ機能をクライアントアプリケーションに実装するよりもはるかに難しいという意見があります。レベルランゲージ
では、InterBase のストアドプロシージャとは何でしょうか?
ストアドプロシージャ (SP) はデータベースメタデータの一部であり、InterBase の内部表現にコンパイルされるサブルーチンであり、特別な言語で記述され、そのコンパイラは InreiBase サーバーのコアに組み込まれています。
ストアドプロシージャは、クライアントアプリケーション、トリガー、および他のストアドプロシージャから呼び出すことができます。ストアドプロシージャはサーバープロセス内で実行され、データベース内のデータを操作できるだけでなく、ストアドプロシージャを呼び出したクライアント (つまり、トリガー、HP、アプリケーション) に実行結果を返すことができます。
HP に固有の強力な機能の基礎は手続き型プログラミング言語です。これには、INSERT、UPDATE、SELECT などの通常の SQL の変更ステートメントと、分岐やループ (IF、WHILE) を編成するためのツールの両方が含まれます。エラー処理ツールと例外的な状況ストアドプロシージャの言語を使用すると、データを操作するための複雑なアルゴリズムを実装できます。また、リレーショナルデータの操作に重点を置いているため、HP は従来の言語の同様のプロシージャよりもはるかにコンパクトです。
多くの機能と制限を除き、同じプログラミング言語がトリガーに使用されることに注意してください。トリガーで使用される言語のサブセットと HP 言語の違いについては、「トリガー」の章 (パート 1) で詳しく説明します。

単純なストアドプロシージャの例

最初のストアドプロシージャを作成し、それを例として使用してストアドプロシージャの作成プロセスを学習します。しかし、その前に、ストアドプロシージャの使用方法について少し言っておきますが、実際のところ、HP が不明瞭で不便なツールであるという評判は、ストアドプロシージャの開発とデバッグ用の非常に貧弱な標準ツールのせいであるということです。 InterBase のドキュメントでは、HP テキストを含む SQL スクリプトファイルを使用してプロシージャを作成し、isql インタープリタへの入力として提供され、BLR でプロシージャのテキストをコンパイルする段階で、この SQL スクリプト内の HP If を作成および変更することを推奨しています (約BLR (「InterBase データベース構造」の章 (パート 4) を参照))、エラーが発生した場合、isql は、このエラーが SQL スクリプトファイルのどの行で発生したかに関するメッセージを表示します。間違いを修正して、もう一度やり直してください。現代の意味でのデバッグ、つまり変数の中間値を表示する機能を備えた実行トレースについてはまったく話されていません。開発者の目から見て、このアプローチはストアドプロシージャの魅力の向上に貢献していないことは明らかです。
ただし、HP 開発に対する標準的なミニマリストアプローチに加えて、<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
ストアドプロシージャの構文は次のように説明されます。

作成プロシージャ名
[ (パラメータデータ型 [, パラメータデータ型 ...]) ]
)]
として
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var データ型;

=
始める
< compound_statement>
[< compound_statement> ...]
終わり
< compound_statement> = (声明;）

非常にボリュームがあり、面倒に見えるかもしれませんが、実際はすべて非常に単純です。構文を徐々にマスターするために、徐々に複雑な例を見てみましょう。
ここでは、2 つの数値を入力として受け取り、それらを加算して結果を返す、非常に単純なストアドプロシージャの例を示します。

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
Second_arg 倍精度)
戻り値 (結果の倍精度)
として
始める
結果=最初の引数+二番目の引数;
つるす;
終わり

ご覧のとおり、すべては単純です。CREATE PROCEDURE コマンドの後に、新しく作成されたプロシージャの名前 (データベース内で一意である必要があります) が表示されます。この場合は SP_Add、次に HP 入力パラメータである first_arg と Second_arg が表示されます。括弧内にカンマで区切ってリストし、そのタイプを示します。
入力パラメータのリストは、CREATE PROCEDURE ステートメントのオプションの部分です。プロシージャが、プロシージャ本体内のテーブルへのクエリを通じて、その作業のためのすべてのデータを受け取る場合があります。

ストアドプロシージャは、任意のスカラーデータ型を使用します。 InreiBase は、配列およびユーザー定義型 (ドメイン) の使用を許可しません。

次に RETURNS キーワードが続き、その後に返されるパラメータが括弧内にリストされ、そのタイプ (この場合は 1 つだけ) が示されます。
プロシージャがパラメータを返さない場合、RETURNS という単語と返されるパラメータのリストが欠落しています。
RETURNSQ の後にキーワード AS が指定されます。 AS というキーワードが入る前に タイトル、それとその後 - 手帳手順。
ストアドプロシージャの本体は、内部 (ローカル) 変数 (存在する場合は、以下で詳しく説明します) の説明をセミコロン (;) で区切ったリストと、演算子括弧で囲まれたステートメントのブロックです。始まり、終わり。この場合、HP の本体は非常に単純です。2 つの入力引数を追加し、その結果を出力引数に割り当ててから、SUSPEND コマンドを呼び出します。このコマンドの動作の本質については後で説明しますが、ここではストアドプロシージャの呼び出し元に戻りパラメータを転送する必要があることだけを説明します。

ストアドプロシージャの区切り文字

プロシージャ内のステートメントはセミコロン (;) で終わることに注意してください。ご存知のとおり、セミコロンは SQL の標準コマンド区切り文字です。これは、コマンドテキストが完全に入力され、処理を開始する必要があることを SQL インタープリタに伝える信号です。 SQL インタープリターが HP の中央にセミコロンを見つけた場合、コマンドが完全に入力されたとみなして、ストアドプロシージャの一部を実行しようとすることがわかりませんか? この仮定にはメリットがないわけではありません。実際、上記の例を記述するファイルを作成し、データベースから接続コマンドを追加し、isql インタープリターを使用してこの SQL スクリプトを実行しようとすると、インタープリターの意見では予期しない終了が原因でエラーが返されます。ストアドプロシージャ作成コマンドの。特殊な InterBase 開発者ツールを使用せずに SQL スクリプトファイルを使用してストアドプロシージャを作成する場合は、各 HP 作成コマンド (トリガーにも同じことが当てはまります) の前と、テキスト HP の後にスクリプトコマンドの区切り文字をセミコロン以外の別の文字に変更する必要があります。それを元に戻すために。 SQL 句の区切り文字を変更する isql コマンドは次のようになります。

期間を設定する

ストアドプロシージャを作成する一般的なケースは次のようになります。

用語を設定^;
CREATE PROCEDURE some_procedure
... . .
終わり
^
期間を設定 ;^

ストアドプロシージャの呼び出し

ストアドプロシージャに戻りましょう。これで作成が完了したので、何らかの方法で呼び出してパラメータを渡し、結果を取得する必要があります。これは非常に簡単に実行できます。次のような SQL クエリを記述するだけです。

選択する *
FROM Sp_add(181.35, 23.09)

このクエリは、数値 181.35 と 23.09 の合計、つまり 204.44 を含む Result フィールドを 1 つだけ含む 1 行を返します。
したがって、私たちのプロシージャは、クライアントプログラムと他の HP またはトリガーの両方で実行される通常の SQL クエリで使用できます。このプロシージャの使用は、ストアドプロシージャの最後に SUSPEND コマンドを使用することによって可能になります。
実際、InterBase (およびそのすべてのクローン) には、選択可能なプロシージャと実行可能なプロシージャという 2 種類のストアドプロシージャが存在します。これら 2 種類の HP の動作の違いは、サンプリングプロシージャは通常、データのセットのように見える、行ごとにグループ化された多数の出力パラメータセットを返しますが、実行可能プロシージャはパラメータをまったく返さないか、パラメータのみを返すかのどちらかであることです。出力パラメータの 1 セット。「Returns」にリストされています。パラメータは 1 行です。選択プロシージャは SELECT クエリで呼び出され、実行可能プロシージャは EXECUTE PROCEDURE コマンドを使用して呼び出されます。
どちらのタイプのストアドプロシージャも同じ作成構文を持ち、形式的には違いがないため、任意の実行可能プロシージャを SELECT クエリで呼び出すことができ、任意の選択プロシージャを EXECUTE PROCEDURE を使用して呼び出すことができます。問題は、さまざまな種類の呼び出しの下で HP がどのように動作するかということです。言い換えれば、違いは特定の種類の呼び出しに対するプロシージャの設計にあります。つまり、選択プロシージャは SELECT クエリから呼び出されるように特別に作成され、実行可能プロシージャは EXECUTE PROCEDURE を使用して呼び出されるように特別に作成されます。これら 2 種類の HP のデザインの違いを見てみましょう。
サンプリング手順がどのように機能するかを理解するには、理論をもう少し深く掘り下げる必要があります。 SELECT ID, NAME FROM Table_example のような通常の SQL クエリを想像してみましょう。実行の結果、2 つの列 (ID と NAME) と特定の行数 (Table_example テーブルの行数と同じ) で構成されるテーブルが得られます。このクエリの結果として返されるテーブルは SQL データセットとも呼ばれます。このクエリの実行中にデータセットがどのように形成されるかを考えてみましょう。クエリを受信したサーバーは、どのテーブルを参照しているかを判断し、これらのテーブルのレコードのどのサブセットをクエリ結果に含める必要があるかを判断します。次に、サーバーはクエリ結果を満たす各レコードを読み取り、そこから必要なフィールド (この場合は ID と NAME) を選択し、クライアントに送信します。その後、選択したレコードごとにこのプロセスが再度繰り返されます。
親愛なる読者が、ストアドプロシージャを含め、すべての SQL データセットが行ごとに生成されることを理解するには、このすべての余談が必要です。フェッチプロシージャと実行可能プロシージャの主な違いは、前者は多くの行を返すように設計されているのに対し、後者は 1 行だけを返すように設計されていることです。これが、これらが異なる方法で使用される理由です。選択プロシージャは SELECT コマンドを使用して呼び出され、プロシージャが返すことができるすべてのレコードを放棄することが「要求」されます。実行可能プロシージャは、EXECUTE PROCEDURE を使用して呼び出されます。これにより、HP から 1 行だけが「取り出され」、残りは (たとえ存在していても) 無視されます。
わかりやすくするために、サンプリング手順の例を見てみましょう。 > 念のため、SELECT ID, NAME FROM Table_Example クエリとまったく同じように機能するストアドプロシージャを作成しましょう。つまり、テーブル全体から ID フィールドと NAME フィールドを単に選択するだけです。以下にその例を示します。

作成プロシージャ Simple_Select_SP
戻り値（
procID 整数、
procNAME VARCHAR(80))
として
始める
のために
table_example から ID、名前を選択
INTO:procID、:procNAME
する
始める
つるす;
終わり
終わり

Simple_Select_SP と呼ばれるこのプロシージャのステップを見てみましょう。ご覧のとおり、入力パラメータはなく、ID と NAME という 2 つの出力パラメータがあります。もちろん、最も興味深いのは手順の本体にあります。ここでは FOR SELECT 構造が使用されています。

のために
table_example から ID、名前を選択
INTO:procID、:procNAME
する
始める

/*変数 procID と procName を使って何かを行う*/

終わり

このコード部分は次のことを意味します。Table_example テーブルから選択された各行について、選択した値を procID 変数と procName 変数に入れ、これらの変数を使用して何らかの処理を行います。
あなたは驚いた顔をして、「変数? 他にどんな変数があるの? 9」と尋ねるかもしれません。ストアドプロシージャで変数を使用できるというのは、この章の一種の驚きです。 HP 言語では、プロシージャ内で独自のローカル変数を宣言し、入力パラメータと出力パラメータを変数として使用できます。
ストアドプロシージャでローカル変数を宣言するには、AS キーワードの後、最初の単語 BEGIN の前にその説明を配置する必要があります。ローカル変数の説明は次のようになります。

変数の宣言 ;

たとえば、整数のローカル変数 Mylnt を宣言するには、AS と BEGIN の間に次の宣言を挿入します。

変数 Mylnt INTEGER を宣言します。

この例の変数はコロンで始まります。これは、FOR SELECT SQL コマンド内でアクセスされるため、SELECT で使用されるテーブル内のフィールドと変数を区別するために、後者の前にコロンを付ける必要があるために行われます。結局のところ、変数はテーブル内のフィールドとまったく同じ名前を持つことができます。
ただし、変数名の前のコロンは SQL クエリ内でのみ使用してください。テキストの外では、変数はコロンなしで参照されます。次に例を示します。

procName="何らかの名前";

手順の本文に戻りましょう。 FOR SELECT 句は、テーブル形式 (データのセット) ではなく、一度に 1 行ずつデータを返します。返された各フィールドは、独自の変数 (ID => procID、NAME => procName) に配置する必要があります。 DO 部分では、これらの変数は、SUSPEND コマンドを使用してプロシージャを呼び出したクライアントに送信されます。
したがって、FOR SELECT...DO コマンドは、コマンドの SELECT 部分で選択されたレコードをループします。 DO 部分によって形成されるループの本体では、次に生成されるレコードが SUSPEND コマンドを使用してクライアントに転送されます。
そのため、選択手順は 1 つ以上の行を返すように設計されており、その行に対して、結果として得られる変数パラメーターを埋めるループが HP 本体内に編成されます。このループ本体の最後には常に SUSPEND コマンドがあり、次のデータ行をクライアントに返します。

ループと分岐ステートメント

選択のレコードを介してループを構成する FOR SELECT...DO コマンドに加えて、条件のチェックに基づいてループを構成できる別のタイプのループ WHILE...DO があります。以下は、WHILE..DO ループを使用する HP の例です。このプロシージャは、0 ～ 99 の整数の 2 乗を返します。

手順クワッドの作成
戻り値 (クワドラット整数)
として
変数 I 整数を宣言します。
始める
I = 1;
WHILE(i<100) DO
始める
QUADRAT= I*I;
I=I+1;
つるす;
終わり
終わり

SELECT FROM QUAD クエリを実行した結果、1 ～ 99 の整数の 2 乗を含む 1 つの QUADRAT 列を含むテーブルを受け取ります。
SQL サンプルとクラシックループの結果を反復処理することに加えて、ストアドプロシージャ言語では IF...THEN..ELSE 演算子を使用し、条件の実行に応じて分岐を整理できます。その構文は似ていますPascal や C などの高級プログラミング言語のほとんどの分岐演算子に適用されます。
次のことを行うストアドプロシージャのより複雑な例を見てみましょう。

テーブル Table_example の平均価格を計算します (「テーブルの主キーとジェネレータ」の章を参照)
次に、テーブル内の各エントリに対して次のチェックを行います。既存の価格 (PRICE) が平均価格より高い場合は、平均価格に指定された固定パーセンテージを加えた価格を設定します。
既存の価格が平均価格以下の場合は、以前の価格に以前の価格と平均価格の差の半分を加えた価格に設定します。
テーブル内の変更されたすべての行を返します。

まず、HP の名前と入力パラメータと出力パラメータを定義しましょう。これらはすべてストアドプロシージャのヘッダーに書き込まれます。

プロシージャの作成IncreasePrices (
パーセント 2 増加 DOUBLE PRECISION)
戻り値 (ID INTEGER、NAME VARCHAR(SO)、new_price DOUBLE)
精度として

このプロシージャは IncreasePrices と呼ばれ、DOUBLE PRECISION タイプの入力パラメータ Peiceni21nciease が 1 つと、出力パラメータ ID、NAME、new_pnce の 3 つがあります。最初の 2 つの出力パラメーターは、これから作業するテーブル Table_example のフィールドと同じ名前を持っていることに注意してください。これは、ストアドプロシージャ言語の規則によって許可されています。
ここで、平均値を格納するために使用されるローカル変数を宣言する必要があります。宣言は次のようになります。

変数 avg_price 倍精度を宣言します。

次に、ストアドプロシージャの本体に進みます。HP の本体を開きます。キーワード BEGIN。
まず、アルゴリズムの最初のステップである平均価格の計算を実行する必要があります。これを行うには、次のタイプのクエリを使用します。

平均を選択(価格_l)
FROM テーブルの例
INTO:avg_price,-

このクエリは AVG 集計関数を使用し、選択したクエリ行の PRICE_1 フィールドの平均を返します。この場合、Table_example テーブル全体の PRICE_1 の平均です。リクエストによって返された値は、avg_price 変数に格納されます。 avg_pnce 変数の前には、リクエストで使用されるフィールドと区別するためにコロンが付いていることに注意してください。
このクエリの特徴は、常に 1 つのレコードを正確に返すことです。このようなクエリはシングルトンクエリと呼ばれ、ストアドプロシージャではそのような選択のみを使用できます。クエリが複数の行を返す場合は、返された各行を処理するループを編成する FOR SELECT...DO 構造としてフォーマットする必要があります。
ということで、平均価格を出してみました。次に、テーブル全体を調べ、各エントリの価格値を平均価格と比較し、適切なアクションを実行する必要があります。
最初から、Table_example テーブルからの各レコードの検索を整理します。

のために
ID、名前、PRICE_1 を選択してください
FROM テーブルの例
INTO:ID、:NAME、:new_price
する
始める
/*_ここで各エントリについて説明します*/
終わり

この構築が実行されると、Table_example テーブルから行ごとにデータが抽出され、各行のフィールド値が変数 ID、NAME、new_pnce に割り当てられます。もちろん、これらの変数が出力パラメータとして宣言されていることを覚えておいてください。しかし、選択されたデータが結果として返されることを心配する必要はありません。出力パラメータに何かが割り当てられているという事実は、クライアントが HP を呼び出すことを意味するものではありません。これらの値をすぐに受け取ります。パラメーターは SUSPEND コマンドが実行される場合にのみ渡され、その前に出力パラメーターを通常の変数として使用できます。この例では、new_price パラメーターを使用してそれを行っています。
したがって、BEGIN...END ループの本体内で各行の値を処理できます。ご存知のとおり、既存の価格が平均とどのように比較されるかを把握し、適切な措置を講じる必要があります。この比較手順は IF ステートメントを使用して実装します。

IF (new_price > avg_price) THEN /*既存の価格が平均価格より高い場合*/
始める
/*その後、平均価格に固定パーセンテージを加えた新しい価格を設定します */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE テーブルの例
SET PRICE_1 = :new_price
WHERE ID = :ID;
終わり
それ以外
始める
/* 既存の価格が平均価格以下の場合は、以前の価格に、以前の価格と平均価格の差の半分を加えた価格を設定します */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE テーブルの例
SET PRICE_1 = :new_price
WHERE ID = .ID;
終わり

ご覧のとおり、結果はかなり大きな IF 構造になり、/**/ 記号で囲まれたコメントがなければ理解するのが困難です。
計算された差額に従って価格を変更するには、UPDATE ステートメントを使用します。これにより、1 つ以上の既存のレコードを変更できます。どのレコードの価格を変更する必要があるかを明確に示すために、WHERE 条件で主キーフィールドを使用し、現在のレコードの ID 値を格納する変数の値 ID=:ID と比較します。 ID 変数の前にコロンが付いていることに注意してください。
IF...THEN...ELSE 構造を実行した後、ID、NAME、および new_price 変数には、プロシージャを呼び出したクライアントに返さなければならないデータが含まれています。これを行うには、IF の後に、HP の呼び出し元にデータを送信する SUSPEND コマンドを挿入する必要があります。転送中、プロシージャは一時停止され、HP から新しいレコードが必要になると、再び続行されます。これは、FOR SELECT...DO がクエリ内のすべてのレコードを反復しなくなるまで続きます。
ストアドプロシージャを一時停止するだけの SUSPEND コマンドに加えて、文字列を渡した後にストアドプロシージャを終了する EXIT コマンドがあることに注意してください。ただし、EXIT コマンドは主に条件に達したときにループを中断するために必要なため、使用されることはほとんどありません。
ただし、プロシージャが SELECT ステートメントで呼び出され、EXIT で完了した場合、取得された最後の行は返されません。つまり、手順を中断してもこの文字列を取得する必要がある場合は、シーケンスを使用する必要があります。

つるす;
出口;

EXIT の主な目的は、EXECUTE PROCEDURE を呼び出してシングルトンデータセット、返されたパラメータを受け取ることです。この場合、出力パラメータの値は設定されますが、そこから SQL データセットは生成されず、プロシージャの実行は終了します。
ストアドプロシージャのロジックを一目で理解できるように、ストアドプロシージャのテキスト全体を書き留めてみましょう。

プロシージャの作成IncreasePrices (
パーセント 2 倍精度を増加)
戻り値 (ID 整数、名前 VARCHAR(80)、
new_price DOUBLE PRECISION) AS
変数 avg_price 倍精度を宣言します。
始める
平均を選択(価格_l)
FROM テーブルの例
INTO:平均価格;
のために
ID、名前、PRICE_1 を選択してください
FROM テーブルの例
INTO:ID、:NAME、:new_price
する
始める
/*ここで各レコードを処理します*/
IF (new_pnce > avg_price) THEN /*既存の価格が平均価格より高い場合*/
始める
/*平均価格に固定パーセンテージを加えた値に等しい新しい価格を設定します */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE テーブルの例
SET PRICE_1 = :new_price
WHERE ID = :ID;
終わり
それ以外
始める
/* 既存の価格が平均価格以下の場合は、以前の価格に以前の価格と平均価格の差の半分を加えたものに等しい価格を設定します */
新しい価格 = (新しい価格 + ((平均価格 - 新しい価格)/2));
UPDATE テーブルの例
SET PRICE_1 = :new_price
WHERE ID = :ID;
終わり
つるす;
終わり
終わり

このストアドプロシージャの例は、基本的なストアドプロシージャ言語の構造とトリガーの使用方法を示しています。次に、ストアドプロシージャを使用して一般的な問題を解決する方法を見ていきます。

再帰ストアドプロシージャ

InterBase ストアドプロシージャは再帰的に使用できます。これは、ストアドプロシージャがそれ自体を呼び出すことができることを意味します。ストアドプロシージャのネストは最大 1000 レベルまで許可されますが、HP のネストが最大に達する前にサーバー上の空きリソースが使い果たされる可能性があることに注意する必要があります。
ストアドプロシージャの一般的な用途の 1 つは、データベースに保存されているツリー構造を処理することです。木は、製品構成、倉庫、人事、その他の一般的な用途でよく使用されます。
特定のネストレベルから開始して、特定の種類のすべての製品を選択するストアドプロシージャの例を見てみましょう。
問題を次のように定式化してみましょう。次のタイプの階層構造を持つ商品のディレクトリがあります。

品
- 家電製品
- 冷蔵庫
- 3 チャンバー
- ダブルチャンバー
- シングルチャンバー
- 洗濯機
- 垂直
- 正面
- クラシック
- 狭い
- コンピューターテクノロジー
....

製品カテゴリディレクトリのこの構造には、さまざまな深さの分岐を含めることができます。そして時間の経過とともに増加します。私たちのタスクは、任意のノードから開始して、「完全名を展開」してディレクトリからすべての有限要素を確実に選択することです。たとえば、「洗濯機」ノードを選択した場合、次のカテゴリを取得する必要があります。

洗濯機 - 縦型
洗濯機 - フロントクラシック
洗濯機 - フロントナロー

製品ディレクトリ情報を保存するためのテーブル構造を定義しましょう。簡略化されたスキームを使用してツリーを 1 つのテーブルに編成します。

CREATE TABLE グッズツリー
(ID_GOOD INTEGER NOT NULL、
ID_PARENT_GOOD INTEGER、
GOOD_NAME VARCHAR(80)、
制約 pkGooci 主キー (ID_GOOD));

GoodsTree という 1 つのテーブルを作成します。このテーブルには、ID_GOOD - カテゴリのスマート識別子、ID_PARENT_GOOD - このカテゴリの親会社の識別子、および GOOD_NAME - カテゴリの名前の 3 つのフィールドだけがあります。このテーブルのデータの整合性を確保するために、このテーブルに外部キー制約を課します。

ALTER TABLE グッズツリー
制約を追加 FK_goodstree
外部キー (ID_PARENT_GOOD)
参考文献 GOODSTPEE (ID__GOOD)

テーブルはそれ自体を参照し、この外部キーがそれを追跡します。これにより、テーブルには存在しない親への参照が含まれないようになり、子を持つ製品カテゴリを削除しようとすることも防止されます。
次のデータをテーブルに入力してみましょう。

ID_GOOD

1
2
3
4
5
6
7
8
9
10
11
12

ID_PARENT_GOOD

0
1
1
2
2
4
4
4
5
5
10
10

いい名前

品
家電製品
コンピュータとコンポーネント
冷蔵庫
洗濯機
3 チャンバー
ダブルチャンバー
シングルチャンバー
垂直
正面
狭い
クラシック

データを保存する場所ができたので、すべての「最終的な」製品カテゴリを「展開された」形式で出力するストアドプロシージャの作成を開始できます。たとえば、カテゴリ「Three-Chamber」の場合は、完全なカテゴリです。名称は「家電冷蔵庫『3室』」となります。
ツリー構造を処理するストアドプロシージャには、独自の用語があります。ツリーの各要素はノードと呼ばれます。相互に参照するノード間の関係は親子関係と呼ばれます。ツリーの最後にあり、子のないノードは「リーフ」と呼ばれます。
このストアドプロシージャの場合、入力パラメータはカテゴリ識別子となり、そこからドリルダウンを開始する必要があります。ストアドプロシージャは次のようになります。

CREATE プロシージャ GETFULLNAME (ID_GOOD2SHOW INTEGER)
戻り値 (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
として
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
始める
/*0ID_GOOD=ID_GOOD2SHOW を使用して、製品の直接の子孫に従って外側の FOR SELECT ループを編成します */
FOR SELECT gtl.id_good、gtl.good_name
グッズツリー gtl より
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD、:full_goods_name
する
始める
/"EXISTS 関数を使用して確認します。この関数は、括弧内のクエリが少なくとも 1 行を返す場合に TRUE を返します。ID_PARENT_GOOD = ID_CHILD_GOOD で見つかったノードに子がない場合、それはツリーの「リーフ」であり、結果に含まれます。 */
IF (存在しない(
グッズツリーから * を選択
WHERE GoodsTree.id_parent_good=:id_child_good))
それから
始める
/* ツリーの「葉」を結果に渡します */
つるす;
終わり
それ以外
/* 子を持つノードの場合*/
始める
/*親ノードの名前を一時変数に保存します */
CURR_CHILD_NAME=完全な商品名;
/* このプロシージャを再帰的に実行します */
のために
SELECT ID_CHILD_GOOD、完全な商品名
GETFULLNAME から (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD、:full_goods_name
始めてください
/*文字列連結操作を使用して、親ノードの名前を、見つかった子の名前に追加します || */
full_goods_name=CURR_CHILD_NAME| " " | 完全な商品名、-
つるす; /* 製品の完全な名前を返します*/
終わり
終わり
終わり
終わり

入力パラメータ ID_GOOD2SHOW= 1 を指定してこのプロシージャを実行すると、次の結果が得られます。

ご覧のとおり、再帰ストアドプロシージャを使用して、カテゴリツリー全体を調べ、分岐の先端にある「リーフ」カテゴリの完全な名前を表示しました。

結論

これで、ストアドプロシージャ言語の主な機能についての考察が終わりました。明らかに、ストアドプロシージャの開発を 1 章だけで完全にマスターすることは不可能ですが、ここではストアドプロシージャに関連する基本概念を紹介し、説明することを試みました。ここで説明した HP 設計の設計と手法は、ほとんどのデータベースアプリケーションに適用できます。
ストアドプロシージャの開発に関連する重要な問題の一部については、次の章「InterBase ストアドプロシージャ言語の高度な機能」で説明します。この章では、例外処理、ストアドプロシージャ内のエラー状況の解決、および配列の操作について説明します。

このトピックでは、Transact-SQL CREATE PROCEDURE ステートメントを使用して SQL Server Management Studio で Transact-SQL ストアドプロシージャを作成する方法について説明します。

始める前に、次の手順を完了してください。権限

以下を使用してプロシージャを作成します。 SQL Server Management Studio、Transact-SQL

このステートメントを実行するには、データベースに対する CREATE PROCEDURE 権限と、プロシージャが作成されるスキーマに対する ALTER 権限が必要です。

次のいずれかの方法を使用できます。

SQL Server 管理スタジオ

SQL Server Management Studio の使用

オブジェクトエクスプローラーでプロシージャを作成する

で オブジェクトエクスプローラーデータベースエンジンのインスタンスに接続し、デプロイします。

ノードを順番に展開します データベース、データベース アドベンチャーワークス2012とノード プログラミング.

要素を右クリック ストアドプロシージャそして選択します ストアドプロシージャの作成.

メニューにある リクエストアイテムを選択 テンプレートパラメータの値を指定する.

ダイアログボックスで テンプレートパラメータの値の設定表示されるパラメータに次の値を入力します。

パラメータ	意味
著者	あなたの名前
作成日	今日の日付
説明	従業員に関するデータを返します。
プロシージャ名
@Param1	@苗字
@Datatype_For_Param1	ンバーチャー(50)
Param1 のデフォルト値	ヌル
@Param2	@ファーストネーム
@Datatype_For_Param2	ンバーチャー(50)
Param2 のデフォルト値	ヌル

ボタンをクリックしてください わかりました.
で クエリエディタ SELECT ステートメントを次のステートメントに置き換えます。

構文をチェックするには、次を選択します 解析中メニューにある リクエスト。エラーメッセージが返された場合は、上記の手順と比較し、必要に応じて修正してください。
プロシージャを作成するには、メニューで リクエストアイテムを選択 実行する。プロシージャはデータベース内のオブジェクトとして作成されます。
オブジェクトエクスプローラーで手順を確認するには、要素を右クリックします ストアドプロシージャそして選択します アップデート.
プロシージャを実行するには、オブジェクトエクスプローラーでストアドプロシージャ名を右クリックします。 HumanResources.uspGetEmployeesTestそして選択します ストアドプロシージャの実行.
窓の中で 手続きの実行@LastName パラメーターの値として Margheim を入力し、@FirstName パラメーターの値として Diane を入力します。

Transact-SQL の使用

クエリエディターでのプロシージャの作成

で オブジェクトエクスプローラーデータベースエンジンのインスタンスに接続します。

メニューにある ファイルアイテムを選択 リクエストを作成する.

次の例をクエリウィンドウにコピーし、ボタンをクリックします。 実行する。この例では、前と同じストアドプロシージャを作成しますが、プロシージャ名が異なります。

AdventureWorks2012 を使用します。作成に行く PROCEDURE HumanResources .uspGetEmployeesTest2 @LastName nvarchar (50) , @FirstName nvarchar (50) AS SET NOCOUNT ON ; HumanResources.vEmployeeDepartmentHistory から名、姓、部門を選択します。 WHERE 名 = @FirstName AND LastName = @LastName AND EndDate IS NULL; 行く

手順を完了するには、作成中のクエリウィンドウに次の例をコピーし、ボタンをクリックします。 実行する。パラメータ値を設定するためのさまざまな方法が示されています。

仕事の目標– データベースサーバー上でストアドプロシージャを作成して使用する方法を学びます。

1. すべての例を実行し、SQL Server Management Studio ユーティリティでの実行結果を分析します。現在のデータベースに作成されたプロシージャが存在するかどうかを確認します。

2. 実験室での作業中にすべてのサンプルとタスクを完了する。

3. オプションに従って個々のタスクを完了します。

作業を行うための説明

ストアドプロシージャプログラミングを習得するには、というサンプルデータベースを使用します。 DB_書籍、実験室ワークNo.1で作成しました。例とタスクを完了するときは、データベース、テーブル、その他のプロジェクトオブジェクトの名前の対応に注意してください。

ストアドプロシージャは、1 つ以上の SQL ステートメントまたは関数で構成され、コンパイルされた形式でデータベースに保存される一連のコマンドです。

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

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

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

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

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

ストアドプロシージャを作成するには、アクセス権の計画という問題を解決する必要があります。ストアドプロシージャを作成するときは、ストアドプロシージャがデータベースオブジェクトに対して、それを作成したユーザーと同じアクセス権を持つことになることに注意してください。ストアドプロシージャのパラメータを定義します。ストアドプロシージャは入力パラメータと出力パラメータを持つことができます。ストアドプロシージャコードの開発。プロシージャコードには、他のストアドプロシージャの呼び出しを含む、一連の SQL コマンドを含めることができます。

MS SQL Server 表記で新規ストアドプロシージャを作成または既存ストアドプロシージャを変更するための演算子の構文は次のとおりです。

( CREATE | ALTER ) PROC[ EDURE] プロシージャ名 [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION ) ] [ レプリケーション用] AS sql_statement [ ... n]

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

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

入力データと出力データを渡すには、作成するストアドプロシージャ内のパラメータ名が @ 文字で始まる必要があります。単一のストアドプロシージャで複数のパラメーターをカンマで区切って指定できます。プロシージャの本体では、このプロシージャのパラメータの名前と名前が一致するローカル変数を使用しないでください。ユーザー定義のものを含む任意の SQL データ型は、ストアドプロシージャパラメーターのデータ型を決定するのに適しています。ただし、CURSOR データ型はストアドプロシージャの出力パラメータとしてのみ使用できます。 OUTPUT キーワードを指定します。

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

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

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

FOR REPLICATION パラメーターは、データをレプリケートし、作成されたストアドプロシージャをパブリケーション内のアーティクルとして含める場合に必要です。 ENCRYPTION キーワードは、ストアドプロシージャコードを暗号化するようにサーバーに指示します。これにより、ストアドプロシージャを実装する独自のアルゴリズムの使用に対する保護が提供されます。 AS キーワードは、ストアドプロシージャ本体自体の先頭に配置されます。プロシージャ本体では、ほぼすべての SQL コマンドを使用し、トランザクションを宣言し、ロックを設定し、他のストアドプロシージャを呼び出すことができます。 RETURN コマンドを使用してストアドプロシージャを終了できます。

ストアドプロシージャの削除

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

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

ストアドプロシージャを実行するには、次のコマンドを使用します。 [ [ EXEC [ UTE] プロシージャ名 [ ;number] [ [ @parameter_name= ] ( value | @variable_name) [ OUTPUT ] | [ デフォルト ] ] [ ,... n]

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

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

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

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

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

ストアドプロシージャでの RETURN の使用

指定した条件に従って任意の時点で手順を終了でき、また手順の結果を数値として伝えることができるため、手順の品質と正確さを判断できます。 パラメータなしでプロシージャを作成する例:

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

演習 1.

EXEC Count_Books

結果を確認してください。

入力パラメータを使用してプロシージャを作成する例:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

タスク 2。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。コマンドを使用して実行します

EXEC 書籍数_ページ数 100

結果を確認してください。

入力パラメータを使用してプロシージャを作成する例:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

タスク3. SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。コマンドを使用して実行します

EXEC Count_Books_Title 100 、 "P%"

結果を確認してください。

入力パラメータと出力パラメータを使用してプロシージャを作成する例:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

タスク4。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。コマンドセットを使用して実行します。

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q 出力選択 @q

結果を確認してください。

入力パラメータと RETURN を使用したプロシージャの作成例:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "プーシキン A.S." リターン 1 それ以外の場合はリターン 2

タスク5。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。次のコマンドを使用して実行します。

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "リターンステータス" = @return_status

Purchases テーブルのキーフィールドの値を 2 倍にするパラメータなしのプロシージャを作成する例:

CREATE PROC update_proc AS UPDATE 購入 SET Code_purchase = Code_purchase* 2

タスク6。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。コマンドを使用して実行します

EXEC update_proc

特定の作成者に関するすべての情報を取得するための入力パラメータを使用したプロシージャの例:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

タスク7。

EXEC select_author "プーシキン A.S." または select_author @k= "プーシキン A.S." または EXEC select_author @k= "プーシキン A.S."

入力パラメータとデフォルト値を使用して、Purchases テーブルのキーフィールドの値を指定された回数 (デフォルトでは 2 回) 増やすプロシージャを作成する例:

CREATE PROC update_proc @p INT = 2 AS UPDATE 購入 SET Code_purchase = Code_purchase * @p

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

タスク8。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。次のコマンドを使用して実行します。

EXEC update_proc 4 または EXEC update_proc @p = 4 または EXEC update_proc -- デフォルト値が使用されます。

入力パラメータと出力パラメータを使用したプロシージャの作成例。指定された期間中に完了した注文の数を確認するプロシージャを作成します。

CREATE PROC count_purchases @d1 SMALLDATETIME、@d2 SMALLDATETIME、@c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

タスク9。 SQL サーバー Management Studio ユーティリティを使用して、DB_Books データベースのストアドプロシージャセクションにこのプロシージャを作成します。次のコマンドを使用して実行します。

DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2

実験室作業のタスクのオプションその 4

一般規定。 SQL Server Management Studio ユーティリティで、コード用の新しいページ ([クエリの作成] ボタン) を作成します。 Use ステートメントを使用して、作成した DB_Books データベースをプログラムでアクティブにします。 Create プロシージャステートメントを使用してストアドプロシージャを作成し、プロシージャの名前を自分で定義します。各プロシージャは、2 番目のラボで実行された 1 つの SQL クエリを実行します。さらに、検索に使用されるフィールドの値を送信できるように、クエリの SQL コードを変更する必要があります。

たとえば、実験室作業 No. 2 の最初のタスクとリクエスト:

/*サプライヤーディレクトリ (納品テーブル) から、会社名 (フィールド Name_company) が「OJSC MIR」である会社名、電話番号、および INN (フィールド Name_company、Phone および INN) を選択します。

SELECT Name_company、電話番号、INN FROM Deliveries WHERE Name_company = "OJSC MIR"

*/ –本作では以下のプロシージャを作成します。

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company、電話番号、INN FROM Deliveries WHERE Name_company = @comp

–手順を開始するには、次のコマンドを使用します。

EXEC select_name_company "JSC MIR"

タスクリスト

SQL Server Management Studio で新しいプログラムを作成します。 Use ステートメントを使用して、実験作業 1 で作成した個別のデータベースをプログラムでアクティブにします。 Create プロシージャステートメントを使用してストアドプロシージャを作成し、プロシージャの名前を自分で定義します。各プロシージャは 1 つの SQL クエリを実行します。SQL クエリは、オプションに応じて個別のタスクの形式で表示されます。

オプション1

1. 少なくとも 1 人の子供がいる従業員のリストを表示します。

2. 指定期間中にプレゼントを受け取った子供の一覧を表示します。

3. 未成年の子供を持つ親のリストを表示します。

4. 指定した数値以上のギフトに関する情報を日付順に表示します。

オプション 2

1. 指定したタイプのデバイスのリストを表示します。

2. 指定された技術者が修理したデバイスの数と修理の総費用を表示します。

3. デバイス所有者とそのリクエスト数のリストをリクエスト数の降順で表示します。

4. 指定したランク以上の職人、または指定した日付よりも入社日が古い職人の情報を表示します。

オプション 3

2. 指定した数量以上の花を販売した販売コードのリストを表示します。

3. 指定された販売コードに従って、販売日、金額、販売者、花を表示します。

4. 指定した数以上の高さまたは開花している花の花と品種のリストを表示します。

オプション 4

1. 使用適応が指定されている医薬品のリストを表示します。

2. 同一名の医薬品が指定数量以上売れた納期の一覧を表示します。

3. 納品日、数量、仕入先からの管理者の氏名、薬品名を指定番号以上の受付コードで表示します。

オプション 5

2. 指定された理由により廃止された機器のリストを表示します。

3. 受領日、機器の名前、責任者のフルネーム、および指定された期間内に償却された機器の償却日を表示します。

4. 指定したタイプまたは受領日が特定の値より古い機器のリストを表示します

オプション 6

1. 指定した数値を超える重量の食器のリストを表示します。

2. 指定した単語の一部が名前に含まれる製品のリストを表示します。

3. 指定した初期値から一定の最終値までの製品の容量、料理名、料理コード付きの製品名を表示します。

4. 糖質量が一定値以上、カロリー量が一定値以上の料理の作る順番と料理名を表示します。

オプション 7

1. 指定した役職の従業員のリストを表示します。

3. 指定した期間に登録された文書の登録日、文書の種類、登録者の氏名、組織名を表示します。

4. 特定の文書タイプまたは指定された値よりも古い登録日を持つ登録文書のリストを表示します。

オプション 8

1. 解雇事由を指定した従業員の一覧を表示します。

3. 指定期間内に登録された書類については、登録日、解雇事由、従業員の氏名を表示します。

オプション 9

1. 指定した種類の休暇を取得した従業員の一覧を表示します。

2. 指定した期間内の登録日を持つ文書の一覧を表示します。

3. 指定期間内に登録された書類について、登録日、休暇の種類、従業員の氏名を表示します。

4. 指定した範囲の文書コードを持つ登録文書の一覧を表示します。

オプション 10

1. 指定した役職の従業員のリストを表示します。

2. 指定した単語の一部がコンテンツに含まれる文書のリストを表示します。

3. 指定した期間内に登録された文書の登録日、文書の種類、送信者の氏名、組織名を表示します。

4. 指定した文書タイプまたは文書コードが一定値未満の登録文書の一覧を表示します。

オプション 11

1. 指定したポジションに割り当てられている従業員のリストを表示します。

2. 指定した期間内の登録日を持つ文書の一覧を表示します。

3. 指定した期間内に登録された文書の登録日、役職、従業員の氏名を表示します。

4. 指定した範囲の文書コードを持つ登録文書の一覧を表示します。

オプション 12

3. レンタルした人とそのリクエスト数をリクエスト数の多い順に一覧表示します。

オプション 13

1. 指定した種類の機器の一覧を表示します。 2. 特定の従業員によって償却された設備のリストを表示します。

3. 廃止された機器の量を機器の種類ごとにグループ化して表示します。

4. 入社日が特定の日付より古い従業員に関する情報を表示します。

オプション 14

1. 指定した葉の種類を持つ花のリストを印刷します。

2. 一定金額以上の花が売れたレシートコードの一覧を表示します。

3. 受入日、金額、仕入先名、色を特定の仕入先コードごとに表示します。

4. 一定以上の高さの花や開花中の花の品種と一覧を表示します。

オプション 15

1. 指定期間内にチェックインしたお客様の一覧を表示します。

2. 顧客ごとの客室の支払総額を表示します。

3. 指定期間中に登録されたお客様の到着日、部屋タイプ、氏名を表示します。

4. 特定のタイプのルームに登録されているクライアントのリストを表示します。

オプション 16

1. 指定した種類の機器の一覧を表示します。

2. 特定のクライアントがレンタルした機器のリストを表示します。

3. レンタルした人とそのリクエスト数をリクエスト数の多い順に一覧表示します。

4. クライアントに関する情報をアドレス別に並べて表示します。

オプション 17

1. 購入価格が一定値以上、または保証期間が指定数以上の貴重品のリストを表示します。

2. 指定した単語を名称に含む有形資産の所在の一覧を表示します。

3. 指定した範囲のコードが付いた値の合計を表示します。

4. 指定範囲内の雇用日を含む財務責任者のリストを表示します。

オプション 18

1. 特定の技術者が行った修理のリストを表示します。

2. 指定した単語がタイトルに含まれる作品に含まれる作業ステージの一覧を表示します。

3. 指定範囲のコードを持つ工事の修繕工事段階の費用の合計を表示します。

4. 指定した範囲の採用日を持つ職長のリストを表示します。

オプション 19

1. 特定の適応のある医薬品のリストを表示します。

2. 一定数以上の医薬品が販売された受付番号の一覧を表示します。

3.レシートに発売日、金額、レジ担当者名、薬剤を指定の番号で表示します。

4. パッケージ内の数量が指定された数を超えるか、または薬剤コードが特定の値未満である薬剤の測定単位と薬剤のリストを表示します。

オプション 20

1. 指定した役職の従業員のリストを表示します。

2. 指定した単語の一部がコンテンツに含まれる文書のリストを表示します。

3. 指定した期間内に登録された文書について、登録日、文書の種類、執行者の氏名、執行の事実を表示します。

4. 指定した文書種類または文書コードで一定範囲の登録文書を一覧表示します。

SQL ストアドプロシージャは、さまざまなオブジェクトの形式で保存できる実行可能なプログラムモジュールです。つまり、SQL ステートメントを含むオブジェクトです。これらのストアドプロシージャをアプリケーションクライアントで実行すると、良好なパフォーマンスが得られます。さらに、そのようなオブジェクトは他のスクリプトや他のセクションから呼び出されることがよくあります。

導入

多くの人は、これらがさまざまなプロシージャ (MS SQL を除く) に似ていると信じています。おそらくこれは真実でしょう。これらは同様のパラメータを持ち、同様の値を生成できます。さらに場合によっては接触することもあります。たとえば、これらは DDL および DML データベース、およびユーザー関数 (コード名 UDF) と結合されます。

実際、SQL ストアドプロシージャには、同様のプロセスとは異なる幅広い利点があります。セキュリティ、プログラミングの柔軟性、生産性 - これらすべてがデータベースを使用するユーザーをますます惹きつけています。プロシージャの人気がピークに達したのは、Microsoft から「SQL Server Management Studio」と呼ばれるプログラムがリリースされた 2005 年から 2010 年でした。そのおかげで、データベースの操作がはるかに簡単、より実用的、便利になりました。年々、これはプログラマーの間で人気を集めてきました。現在では、データベースと「通信」するユーザーにとっては Excel と同等の非常に馴染みのあるプログラムです。

プロシージャが呼び出されると、不要なプロセスやユーザーの介入なしに、サーバー自体によって即座に処理されます。この後、削除、実行、または変更を実行できます。 DDL オペレーターはこれらすべてを担当し、オブジェクトを処理するための最も複雑なアクションを単独で実行します。さらに、これらすべては非常に迅速に行われ、サーバーは実際にはロードされません。この速度とパフォーマンスにより、大量の情報をユーザーからサーバーに、またはその逆に非常に迅速に転送できます。

情報を操作するためにこのテクノロジーを実装するには、いくつかのプログラミング言語があります。これらには、たとえば、Oracle の PL/SQL、InterBase および Firebird システムの PSQL、さらに従来の Microsoft Transact-SQL が含まれます。これらはすべてストアドプロシージャの作成と実行用に設計されており、大規模なデータベースプロセッサが独自のアルゴリズムを使用できるようになります。これは、そのような情報を管理する者が第三者による不正アクセスからすべてのオブジェクトを保護し、それに応じて特定のデータの作成、変更、または削除を保護できるようにするためにも必要です。

生産性

これらのデータベースオブジェクトは、さまざまな方法でプログラムできます。これにより、ユーザーは最も適切な方法の種類を選択できるようになり、労力と時間を節約できます。さらに、手順自体が処理されるため、サーバーとユーザー間の通信に費やす膨大な時間が回避されます。また、モジュールはいつでも再プログラムして希望の方向に変更することができます。 SQL ストアドプロシージャの起動速度は特に注目に値します。このプロセスは、同様の他のプロセスよりも高速に実行されるため、便利で汎用的です。

安全性

このタイプの情報処理は、セキュリティの向上が保証されるという点で同様のプロセスとは異なります。これは、他のユーザーによるプロシージャへのアクセスを完全に排除できるという事実によって保証されます。これにより、管理者は情報の傍受やデータベースへの不正アクセスを恐れることなく、独立して操作を実行できるようになります。

データ転送

SQL ストアドプロシージャとクライアントアプリケーションの間の関係は、パラメータと戻り値の使用です。後者はストアドプロシージャにデータを渡す必要はありませんが、この情報は (主にユーザーの要求に応じて) SQL 用に処理されます。ストアドプロシージャは作業を完了すると、SQL ストアドプロシージャを呼び出すか返すために使用できるさまざまなメソッドを使用して、ストアドプロシージャを呼び出したアプリケーションにデータパケットを (ただし、オプションで再度) 送り返します。次に例を示します。

出力タイプパラメータを使用してデータを転送します。

return 演算子を使用してデータを渡す。

select 演算子を使用してデータを渡します。

では、このプロセスが内部からどのように見えるかを見てみましょう。

1. SQL で EXEC ストアドプロシージャを作成する

MS SQL (Managment Studio) でプロシージャを作成できます。プロシージャが作成されると、データベースのプログラマブルノードにリストされ、作成プロシージャがオペレータによって実行されます。 SQL ストアドプロシージャを実行するには、オブジェクト自体の名前を含む EXEC プロセスを使用します。

プロシージャを作成すると、最初にその名前が表示され、次にそれに割り当てられた 1 つ以上のパラメータが表示されます。パラメータはオプションである場合があります。パラメータ、つまりプロシージャの本体を記述した後、いくつかの必要な操作を実行する必要があります。

重要なのは、ボディにはローカル変数を配置でき、これらの変数はプロシージャに関してもローカルであるということです。つまり、Microsoft SQL Server プロシージャの本体内でのみ表示できます。この場合のストアドプロシージャはローカルとみなされます。

したがって、プロシージャを作成するには、プロシージャの名前と、プロシージャの本体として少なくとも 1 つのパラメータが必要です。この場合の優れたオプションは、分類子にスキーマ名を使用してプロシージャを作成して実行することです。

プロシージャの本体は、テーブルの作成、テーブルの 1 つ以上の行の挿入、データベースのタイプと性質の確立など、あらゆる種類のものにすることができます。ただし、プロシージャ本体は、その中での特定の操作の実行を制限します。重要な制限の一部を以下に示します。

本体では他のストアドプロシージャを作成しないでください。

身体はその物体に対して誤った印象を与えてはなりません。

身体が引き金を作ってはなりません。

2. プロシージャ本体に変数を設定する

変数をプロシージャ本体に対してローカルに作成すると、変数はプロシージャ本体内にのみ常駐するようになります。ストアドプロシージャ本体の先頭に変数を作成することをお勧めします。ただし、特定のオブジェクトの本体内の任意の場所に変数を設定することもできます。

場合によっては、複数の変数が 1 行に設定されており、各変数パラメーターがカンマで区切られていることに気づくかもしれません。また、変数には @ という接頭辞が付いていることに注意してください。プロシージャの本体では、変数を任意の場所に設定できます。たとえば、@NAME1 変数はプロシージャ本体の終わり近くで宣言される場合があります。宣言された変数に値を割り当てるには、一連の個人データが使用されます。複数の変数が同じ行で宣言されている状況とは異なり、この状況では 1 セットの個人データのみが使用されます。

ユーザーはよく「プロシージャ本体の 1 つのステートメントで複数の値を割り当てるにはどうすればよいですか?」という質問をします。良い。興味深い質問ですが、思っているよりもはるかに簡単です。回答: 「Select Var = value」などのペアを使用します。これらのペアはカンマで区切って使用できます。

さまざまな例で、簡単なストアドプロシージャを作成して実行する様子が示されています。ただし、プロシージャは、それを呼び出すプロセスがそれに近い値を持つようなパラメータを受け入れることができます (ただし、常にではありません)。それらが一致すると、対応するプロセスが体内で始まります。たとえば、呼び出し元から都市と地域を受け入れ、対応する都市と地域に属する著者の数に関するデータを返すプロシージャを作成するとします。このプロシージャは、Pubs などのデータベースの作成者テーブルをクエリして、この作成者のカウントを実行します。これらのデータベースを取得するために、たとえば、Google は SQL2005 ページから SQL スクリプトをダウンロードします。

前の例では、プロシージャは 2 つのパラメータを受け取ります。これらのパラメータは、英語では慣例的に @State および @City と呼ばれます。データ型はアプリケーションで定義された型と一致します。プロシージャの本体には内部変数 @TotalAuthors があり、この変数は作成者の数を表示するために使用されます。次にクエリ選択セクションがあり、すべてを計算します。最後に、print ステートメントを使用して、計算された値が出力ウィンドウに表示されます。

SQLでストアドプロシージャを実行する方法

手続きには2つの方法があります。最初の方法は、パラメータを渡すことによって、プロシージャ名の後にカンマ区切りのリストがどのように実行されるかを示します。 (前の例のように) 2 つの値があるとします。これらの値は、@State および @City プロシージャパラメーター変数を使用して収集されます。パラメーターを渡すこの方法では、順序が重要です。この方法は、順序引数の受け渡しと呼ばれます。 2 番目の方法では、パラメーターはすでに直接割り当てられており、この場合、順序は重要ではありません。この 2 番目のメソッドは、名前付き引数の受け渡しとして知られています。

この手順は、通常の手順とは若干異なる場合があります。すべては前の例と同じですが、ここでのみパラメータがシフトされます。つまり、@City パラメーターが最初に保存され、@State がデフォルト値の次に保存されます。通常、デフォルトのパラメータは個別に強調表示されます。 SQL ストアドプロシージャは単なるパラメータとして渡されます。この場合、指定されたパラメータ「UT」がデフォルト値「CA」を置き換えます。 2 回目の実行では、@City パラメーターに 1 つの引数値のみが渡され、@State パラメーターはデフォルト値の「CA」を受け取ります。経験豊富なプログラマは、デフォルトではすべての変数をパラメータリストの末尾の方に配置することをお勧めします。そうしないと実行できず、名前付き引数を渡す作業が必要になりますが、これは長くて複雑です。

4. SQL Server ストアドプロシージャ: 戻りメソッド

呼び出されたストアドプロシージャでデータを送信するには、3 つの重要な方法があります。それらは以下にリストされています。

ストアドプロシージャの値を返します。

ストアドプロシージャパラメータ出力。

ストアドプロシージャの 1 つを選択します。

4.1 SQLストアドプロシージャからの戻り値

この手法では、プロシージャはローカル変数に値を代入し、それを返します。プロシージャは定数値を直接返すこともできます。次の例では、著者の総数を返すプロシージャを作成しました。この手順を前の手順と比較すると、印刷値が逆転していることがわかります。

次に、プロシージャを実行してその戻り値を出力する方法を見てみましょう。プロシージャを実行するには、変数の設定と印刷が必要です。これは、このプロセス全体の後に実行されます。 print ステートメントの代わりに、Select @RetValue や OutputValue などの Select ステートメントを使用できることに注意してください。

4.2 SQL ストアドプロシージャパラメータの出力

応答値を使用して単一の変数を返すことができます。これは前の例で見たものです。 Output パラメーターを使用すると、プロシージャで 1 つ以上の変数値を呼び出し元に送信できます。出力パラメータは、プロシージャを作成するときにこのキーワード「Output」によって正確に指定されます。パラメータが出力パラメータとして指定された場合、プロシージャオブジェクトはそれに値を割り当てる必要があります。 SQL ストアドプロシージャ (以下にその例を示します) は、この場合、概要情報を返します。

この例では、@TotalAuthors と @TotalNoContract という 2 つの出力名があります。それらはパラメータのリストに示されています。これらの変数は、プロシージャ本体内の値を割り当てます。出力パラメータを使用すると、呼び出し元はプロシージャの本体内に設定された値を確認できます。

また、前のシナリオでは、MS SQL Server ストアドプロシージャが出力パラメータに設定した値を確認するために 2 つの変数が宣言されています。次に、「CA」パラメータの通常の値を指定することによって手順が実行されます。次のパラメータは出力パラメータであるため、宣言された変数は指定された順序で渡されます。変数を渡す場合、ここで出力キーワードも設定されることに注意してください。プロシージャが正常に完了すると、出力パラメータによって返された値がメッセージボックスに表示されます。

4.3 SQL ストアドプロシージャの 1 つを選択する

この手法は、呼び出し元のストアドプロシージャに値のセットをデータテーブル (RecordSet) として返すために使用されます。この例では、@AuthID パラメーターを含む SQL ストアドプロシージャは、その @AuthId パラメーターを使用して返されたレコードをフィルター処理することによって、Authors テーブルをクエリします。 Select ステートメントは、ストアドプロシージャの呼び出し元に何を返すかを決定します。ストアドプロシージャが実行されると、AuthId が返されます。ここでのこのプロシージャは、常に 1 つのレコードのみを返すか、まったくレコードを返しません。ただし、ストアドプロシージャには、複数のレコードを返すことに対する制限はありません。複数の合計を提供することにより、計算変数を含む選択したパラメーターを使用してデータが返される例を見ることは珍しくありません。

ついに

ストアドプロシージャは、クライアントアプリケーションのおかげで、返したり渡したり、必要な変数を設定したりする、かなり本格的なプログラムモジュールです。ストアドプロシージャ自体がサーバー上で実行されるため、サーバーとクライアントアプリケーションの間での大量のデータ交換 (一部の計算用) を回避できます。これにより、SQL サーバーの負荷が軽減され、当然、サーバーの所有者にとってもメリットが得られます。サブタイプの 1 つは T SQL ストアドプロシージャですが、優れたデータベースを作成する人にはこれらの学習が必要です。ストアドプロシージャを学習する際に役立つ微妙なニュアンスも多数ありますが、これはプロを含めてプログラミングに参加しようとしている人にとってはさらに必要です。

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

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

ストアドプロシージャ相互接続された 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]

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

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

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

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

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

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

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

シール