SQLサーバーのカーソルプロシージャ。 DECLARE CURSOR コマンドは一般的な規則です。メモリの割り当て解除、割り当て解除

明示カーソルは、プログラムの宣言セクションで明示的に定義された SELECT ステートメントです。明示カーソルが宣言されると、名前が付けられます。 INSERT、UPDATE、MERGE、および DELETE コマンドの場合、明示カーソルは定義できません。

SELECT コマンドを明示カーソルとして定義すると、プログラマは Oracle データベースから情報を取得する基本的な手順を制御できるようになります。カーソルを開くタイミング (OPEN)、カーソルから行をフェッチするタイミング (FETCH)、フェッチする行の数、および CLOSE コマンドでカーソルを閉じるタイミングを決定します。カーソルの現在の状態に関する情報は、その属性を通じて取得できます。制御の粒度が細かいため、明示的カーソルはプログラマにとって貴重なツールになります。

例を考えてみましょう。

1 FUNCTION angely_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR angely_cur 5 IS 6 友達から場所を選択 7 WHERE NAME = UPPER (NAME_IN); 8 8 嫉妬_rec 嫉妬_cur%ROWTYPE; 9 検索番号; 10 開始 11 オープン嫉妬_cur; 13 12 嫉妬_cur を嫉妬_rec にフェッチします。 15 13 IFjerey_cur%FOUND 14 THEN 15 IFjeraty_rec.location = "プエルトリコ" 16 THEN retval:= 10; 17 ELSIF 嫉妬_rec.location = "シカゴ" 18 THEN retval:= 1; 19 エンディフ; 20 終了 IF; 24 21 閉じる 26 22 RETURN 戻り値。 23 例外 24 その他の場合 25 嫉妬_cur%ISOPEN の場合 26 嫉妬_cur; を閉じる 27 終了 IF; 28エンド;

次のいくつかのセクションでは、これらの各操作について詳しく説明します。これらの文中の「カーソル」という用語は、テキストで明示的に明記されていない限り、明示的なカーソルを指します。

明示的なカーソルの宣言

明示カーソルを使用できるようにするには、PL/SQL ブロックまたはパッケージの宣言セクションで明示カーソルを宣言する必要があります。

CURSOR カーソル名 [ ([ パラメータ [, パラメータ...]) ] [ RETURN refEurn_spec ] IS SELECT_command ];

ここで、カーソル名は宣言されているカーソルの名前です。 special_te?um - オプションの RETURN セクション。 KOMaHdaSELECT - 任意の有効な SQL SELECT コマンド。パラメータをカーソルに渡すこともできます (以下の「カーソルパラメータ」セクションを参照)。最後に、SELECT...FOR UPDATE コマンドの後に、更新する列のリストを指定できます (以下も参照)。カーソルが宣言された後、OPEN コマンドでカーソルが開かれ、そこからの行のフェッチが FETCH コマンドで行われます。

明示的なカーソル宣言の例をいくつか示します。

パラメータのないカーソル。このカーソルの行の結果セットは、テーブル内のすべての行から選択された企業 ID のセットです。

CURSOR company_cur IS SELECT company_id FROM company;

パラメータ付きのカーソル。このカーソルの行の結果セットには、渡されたパラメーターの値と一致する会社名を含む 1 つの行が含まれます。

カーソル name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;

RETURN 句のあるカーソル。このカーソルの結果セットには、部門 ID 10 の従業員テーブル内のすべてのデータが含まれます。

CURSOR emp_cur RETURN 従業員%ROWTYPE IS SELECT * FROM 従業員 WHERE 部門 ID = 10;

カーソル名

明示的なカーソル名は最大 30 文字で、他の PL/SQL 識別子と同じルールに従う必要があります。カーソルの名前は変数ではなく、クエリへのポインタの識別子です。カーソル名には値が割り当てられていないため、式で使用できません。カーソルは、OPEN、CLOSE、および FETCH コマンドでのみ使用され、カーソル属性を修飾するために使用されます。

パッケージ内のカーソル宣言

明示的カーソルは、PL/SQL ブロックの宣言セクションで宣言されます。カーソルはパッケージレベルで宣言できますが、特定のパッケージプロシージャや関数では宣言できません。パッケージ内で 2 つのカーソルを宣言する例:

パッケージ book_info はカーソル title_cur は書籍からタイトルを選択します。 CURSOR Books_cur (title_filter_in IN Books.title%TYPE) RETURN Books%ROWTYPE IS SELECT * FROM Books WHERE title LIKE title_filter_in; 終わり;

最初の title_cur カーソルは本のタイトルのみを返します。 2 番目の books_cur は、書籍タイトルがカーソルパラメータとして指定されたパターンと一致する書籍テーブル内のすべての行を返します (たとえば、「文字列 'PL/SQL' を含むすべての書籍」)。 2 番目のカーソルは、FETCH コマンドによって返されるデータ構造を宣言する RETURN 句を使用していることに注意してください。

RETURN セクションでは、次のデータ構造のいずれかを指定できます。

%ROWTYPE 属性を使用して、データテーブルの行に基づいて定義されるレコード。
%rowtype 属性も使用して、以前に宣言された別のカーソルに基づいて定義されたエントリ。
プログラマによって定義されたレコード。

カーソルの選択リスト内の式の数は、レコード table_name%ROWTYPE、Kypcop%ROWTYPE、またはレコードタイプ内の列の数と一致する必要があります。要素のデータ型にも互換性がある必要があります。たとえば、選択リストの 2 番目の要素が NUMBER 型である場合、 RETURN セクションのエントリの 2 番目の列を VARCHAR2 または BOOLEAN 型にすることはできません。

RETURN セクションとその利点を詳しく説明する前に、まずパッケージ内でカーソルを宣言する目的を理解しましょう。明示カーソルを使用するプログラムのプロシージャ、関数、または無名ブロック内で明示カーソルを宣言してはどうでしょうか?

答えはシンプルで説得力があります。パッケージ内でカーソルを定義すると、アプリケーション内の別の場所で同じコードを繰り返すことなく、パッケージ内で定義されたクエリを再利用できます。クエリを 1 か所に実装すると、コードの改良と保守が容易になります。処理されるリクエストの数を減らすことで、ある程度の時間の節約が実現します。

REF CURSOR に基づいてカーソル変数を返す関数の作成を検討する価値もあります。呼び出し元のプログラムは、カーソル変数を介して行をフェッチします。詳細については、「カーソル変数」と「REF CURSOR」を参照してください。

再利用可能なパッケージでカーソルを宣言するときに留意すべき重要な点が 1 つあります。 (特定の関数やプロシージャ内ではなく) 「パッケージレベル」で宣言されたカーソルを含むすべてのデータ構造は、セッションを通じてその値を保持します。これは、明示的に閉じるかセッションが終了するまで、バッチカーソルは開いたままになることを意味します。ローカルブロックで宣言されたカーソルは、それらのブロックが終了すると自動的に閉じられます。

次に RETURN セクションを見てみましょう。パッケージ内のカーソル宣言には、カーソルのタイトルを本文から分離できるという興味深い機能が 1 つあります。このようなヘッダー (関数ヘッダーに似ています) には、カーソルの名前、そのパラメーター、返されるデータのタイプなど、プログラマが作業する必要がある情報が含まれています。カーソルの本体は SELECT ステートメントです。この手法は、book_info パッケージの新しいバージョンのbooks_cur カーソル宣言で実証されています。

パッケージ book_info は CURSOR Books_cur (title_filter_in IN Books.title%TYPE) RETURN Books%ROWTYPE; 終わり; パッケージ本体 book_info IS CURSOR Books_cur (title_filter_in IN Books.title%TYPE) RETURN Books%ROWTYPE IS SELECT * FROM Books WHERE title LIKE title_filter_in; 終わり;

キーワード IS までのすべての文字が仕様を形成し、IS の後にカーソルの本体が続きます。カーソル宣言を分割すると、2 つの目的があります。

情報を隠す。パケット内のカーソルは「ブラックボックス」です。これは、プログラマにとって、SELECT コマンドを作成したり、確認したりする必要がないため便利です。このカーソルがどのレコードをどのような順序で返し、どのような列が含まれているかを知るだけで十分です。パッケージプログラマは、他の既製の要素と同様にカーソルを使用します。
最小限の再コンパイル。パッケージ本体のクエリ定義を非表示にすると、パッケージ仕様のカーソルヘッダーを変更せずに SELECT ステートメントを変更できます。これにより、パッケージ仕様を再コンパイルせずにコードを改善、修正、および再コンパイルできるため、パッケージに依存するプログラムは無効としてマークされず、再コンパイルする必要がなくなります。

明示的なカーソルを開く

カーソルの使用は、宣言セクションでのカーソルの定義から始まります。次に、宣言されたカーソルを開く必要があります。 OPEN ステートメントの構文は非常に単純です。

OPEN カーソル名 [ (引数 [, 引数...]) ];

ここで、cursorname は以前に宣言されたカーソルの名前であり、argument はパラメータのリストを使用して宣言されている場合にカーソルに渡される値です。

Oracle は、カーソルを開くときに FOR 構文もサポートします。これは、カーソル変数 (「カーソル変数」と「REF CURSOR」を参照) とインライン動的 SQL の両方に使用されます。

カーソルをオープンすると、PL/SQLはカーソルに含まれる問合せを実行します。さらに、アクティブなデータセット、つまり WHERE 基準と結合条件に一致するクエリに参加しているすべてのテーブルの行を識別します。 OPEN コマンドはデータをフェッチしません。これが FETCH コマンドの仕事です。

最初のデータフェッチがいつ行われるかに関係なく、Oracle のデータ整合性モデルにより、すべてのフェッチ操作でカーソルがオープンされた時点の状態でデータが返されることが保証されます。つまり、カーソルが開いてから閉じるまで、カーソルからデータをフェッチする際、この間に実行される挿入、更新、および削除の操作は完全に無視されます。

さらに、SELECT ステートメントに FOR UPDATE 句が含まれている場合、カーソルを開くときにカーソルによって識別されるすべての行がロックされます。

すでにオープンされているカーソルを開こうとすると、PL/SQL によって次のエラーメッセージが生成されます。

ORA-06511: PL/SQL: カーソルはすでにオープンしています

したがって、カーソルを開く前に、属性の値によってカーソルの状態を確認する必要があります。 %isopen:

company_cur%ISOPEN でない場合は、company_cur を開きます。終了 IF;

明示的なカーソル属性については、以下のセクションで説明します。

プログラムがカーソルを使用して FOR ループを実行する場合、そのカーソルを明示的に開く (データのフェッチ、クローズ) 必要はありません。 PL/SQL カーネルはこれを自動的に実行します。

明示的カーソルからのデータのフェッチ

SELECT コマンドは、仮想テーブル (SELECT 列リストで定義された列を持つ WHERE 句で定義された行のセット) を作成します。したがって、カーソルはPL/SQLプログラム内のこの表を表します。 PL/SQL プログラムにおけるカーソルの主な目的は、処理のために行をフェッチすることです。カーソル行のフェッチは、FETCH コマンドを使用して行われます。

FETCH カーソル名 INTO レコードまたは変数リスト;

ここで、cursorname はレコードの選択元のカーソルの名前で、レコードまたは変数 list はアクティブなレコードセットの次の行がコピーされる PL/SQL データ構造です。データは、PL/SQLレコード(%ROWTYPE属性またはTYPE宣言で宣言)または変数(PL/SQL変数またはバインド変数 - Oracle Forms要素など)に配置できます。

明示的なカーソルの例

次の例は、データを取得するさまざまな方法を示しています。

カーソルからPL/SQLレコードにデータをフェッチします。

DECLARE CURSOR company_cur は SELECT ...; company_rec company_cur%ROWTYPE; company_cur を開いて開始します。 company_cur INTO company_rec を取得します。

カーソルから変数にデータをフェッチします。

new_balance_cur INTO new_balance_dollars をフェッチします。

カーソルからPL/SQL表の行、変数およびOracle Formsバインド変数にデータをフェッチします。

emp_name_cur INTO emp_name(1)、hiredate、:dept.min_salary; をフェッチします。

カーソルからフェッチされたデータは、常に、%ROWTYPE 属性を持つ同じカーソルに基づいて宣言されたレコードに配置される必要があります。変数のリストへのフェッチを避けてください。 Fetch-to-Record により、コードがよりコンパクトかつ柔軟になり、FETCH コマンドを変更せずにフェッチリストを変更できるようになります。

最後の行を処理した後にフェッチする

カーソルを開くと、すべての行がなくなるまで行を 1 つずつ選択します。ただし、その後も FETCH コマンドを実行できます。

奇妙なことに、この場合、PL/SQL は例外をスローしません。彼は何もしないだけだ。他に選択するものがないため、FETCH コマンドの INTO セクションの変数の値は変更されません。つまり、FETCH コマンドはこれらの変数を NULL に設定しません。

明示的なカーソル列の別名

カーソル宣言内の SELECT コマンドは、返される列のリストを定義します。このリストには、テーブル列名に加えて、計算列または仮想列と呼ばれる式を含めることができます。

列の別名は、列または式の SELECT コマンドで指定される代替名です。 SQL*Plus で適切な別名を指定すると、任意の問合せの結果を人間が判読できる形式で表示できます。このような状況では、エイリアスはオプションです。一方、明示カーソルを使用する場合、次の場合には計算列の別名が必要になります。

カーソルから、同じカーソルに基づいて %ROWTYPE 属性で宣言されたレコードにデータをフェッチするとき。
プログラムに計算列への参照が含まれている場合。

次のクエリについて考えてみましょう。 SELECT コマンドは、2001 年に商品を注文したすべての会社の名前と注文の合計金額を選択します (現在のデータベースインスタンスのデフォルトの形式マスクが DD-MON-YYYY であると仮定します)。

SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

SQL*Plus でこのコマンドを実行すると、次の出力が生成されます。

会社名	合計 (INV_AMT)
アクメターボ株式会社	1000
ワシントンヘアーカンパニー	25.20

ご覧のとおり、SUM (INV_AMT) 列ヘッダーはレポートにはあまり適していませんが、単純なデータの表示には問題ありません。ここで、明示カーソルを使用して同じ問合せをPL/SQLプログラムで実行し、列の別名を追加してみましょう。

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM company C, invoice I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; comp_cur を開いて開始します。 comp_cur を comp_rec にフェッチします。終わり;

エイリアスがないと、comp_rec レコード構造内の列を参照できません。別名を指定すると、他のクエリ列と同じように計算列を操作できます。

IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE (" 与信限度額 $5000 を超えました " || TO_CHAR (comp_rec.total_sales - 5000, "$9999")); 終了 IF;

%ROWTYPE 属性で宣言されたレコードに行をフェッチする場合、レコードの構造はカーソル自体の構造によって決定されるため、計算列にはその名前によってのみアクセスできます。

明示的なカーソルを閉じる

私たちは子供の頃に、自分の後片付けをするように教えられ、この習慣が（すべてではありませんが）生涯にわたって残っています。このルールはプログラミングにおいても、特にカーソル制御に関しては非常に重要な役割を果たしていることがわかりました。カーソルが必要なくなったら、忘れずにカーソルを閉じてください。

CLOSE コマンドの構文は次のとおりです。

CLOSE カーソル名;

以下に、明示カーソルを閉じるための重要なヒントと考慮事項をいくつか示します。

カーソルがプロシージャ内で宣言されて開かれている場合は、操作が完了したらカーソルを閉じることを忘れないでください。そうしないと、コードでメモリリークが発生します。理論的には、カーソル (他のデータ構造と同様) は、範囲外になると自動的に閉じられ、破棄される必要があります。通常、プロシージャ、関数または無名ブロックを終了するとき、PL/SQLは実際にその中で開いているカーソルをすべて閉じます。ただし、このプロセスにはコストがかかるため、効率上の理由から、PL/SQLはオープン・カーソルの検出とクローズを遅らせることがあります。 REF CURSOR は、定義上、暗黙的に閉じることができません。唯一確実なことは、「最も外側」のPL/SQLブロックが終了し、制御がSQLまたは別の呼出し元に戻されると、PL/SQLはそのブロックまたはネストしたブロックによってオープンされたカーソルを暗黙的にクローズするということです（REF CURSORを除く）。。 Oracle Technology Networkの記事「PL/SQL静的SQLでのカーソルの再利用」では、PL/SQLがカーソルをいつどのようにクローズするかについて詳細に分析しています。ネストされた匿名ブロックは、PL/SQL が暗黙的にカーソルをクローズしない状況の例です。このトピックに関する興味深い情報については、Jonathan Gennick の記事「Does PL/SQL Implicitly Close Cursors?」を参照してください。
カーソルがパッケージレベルでパッケージ内で宣言され、何らかのブロックまたはプログラムで開かれた場合、カーソルは明示的に閉じるまで、またはセッションが終了するまで開いたままになります。したがって、バッチレベルカーソルの操作が終了したら、CLOSE コマンドを使用して直ちにカーソルを閉じる必要があります (ちなみに、例外セクションでも同じことを行う必要があります)。

my_package.my_cursor を開いて開始します。 ... カーソルの操作 CLOSE my_package.my_cursor; 例外は、他の人が mypackage.my_cursor%ISOPEN であれば my_package.my_cursor を閉じる場合です。終了 IF; 終わり;

カーソルは、以前に開いていた場合にのみ閉じることができます。それ以外の場合は、INVALID_CURS0R 例外がスローされます。カーソルの状態は、%ISOPEN 属性を使用してチェックされます。

company_cur%ISOPEN THEN CLOSE company_cur; 終了 IF;

プログラム内にオープンカーソルが多すぎると、その数が OPEN_CURSORS データベースパラメータの値を超える可能性があります。エラーメッセージを受け取った場合、最初に行うことは、パッケージ内で宣言されたカーソルが不要になったときに確実に閉じられるようにすることです。

明示的なカーソル属性

Oracle は、明示カーソルの状態に関する情報を取得するために 4 つの属性 (%FOUND、%NOTFOUND、%ISOPEN、%ROWCOUNTM) をサポートしています。属性参照の構文は次のとおりです:cursor%attribute

ここで、cursor は宣言されたカーソルの名前です。

明示的なカーソル属性によって返される値を表 1 に示します。 1.

表1。明示的なカーソル属性

カーソル属性でさまざまな操作を実行する前後の値を表に示します。 2.

明示的なカーソル属性を使用する場合は、次の点に注意してください。

カーソルがオープンされる前またはクローズされた後に、%FOUND、%NOTFOUND、または %ROWCOUNT 属性にアクセスしようとすると、Oracle は INVALID CURSOR (ORA-01001) 例外をスローします。
FETCH コマンドの最初の実行後に結果行セットが空の場合、カーソル属性は次の値を返します: %FOUND = FALSE 、 %NOTFOUND = TRUE 、および %ROWCOUNT = 0。
BULK COLLECT を使用する場合、%ROWCOUNT 属性は、指定されたコレクションに取得された行数を返します。

表 2.カーソルの属性値

手術	％見つかった	％見つかりません	%ISOPEN	%ROWCOUNT
OPEN前	例外 ORA-01001	例外 ORA-01001	間違い	例外 ORA-01001
OPEN後	ヌル	ヌル	真実	0
最初の FETCH の前	ヌル	ヌル	真実	0
最初のサンプルの後フェッチ	真実	間違い	真実	1
その後の前にフェッチ	真実	間違い	真実	1
後続の FETCH 後	真実	間違い	真実	データに依存する
最後のFETCHの前	真実	間違い	真実	データに依存する
最後のFETCHの後	真実	間違い	真実	データに依存する
閉店前	間違い	真実	真実	データに依存する
閉店後	例外	例外	間違い	例外

これらすべての属性の使用方法を次の例で示します。

以前のブログでは、プロシージャと関数のパラメータの使用例を繰り返し示してきました。パラメーターは、プログラムモジュールとの間で情報をやり取りする手段です。正しく使用すると、モジュールがより便利で柔軟になります。

PL/SQL を使用すると、パラメータをカーソルに渡すことができます。これらは、プログラムモジュールのパラメータと同じ機能に加えて、いくつかの追加機能も実行します。

カーソルの再利用の拡張。データ選択条件を定義する WHERE 句の値をハードコーディングする代わりに、パラメータを使用して、カーソルが開かれるたびに新しい値を WHERE 句に渡すことができます。
カーソルのスコープの問題の解決。ハードコーディングされた値の代わりにパラメーターがクエリで使用される場合、カーソル行の結果セットは特定のプログラムまたはブロック変数に関連付けられません。プログラムにネストされたブロックがある場合は、最上位でカーソルを定義し、変数が宣言されたネストされたブロック内でカーソルを使用できます。

カーソルパラメータの数に制限はありません。 OPEN を呼び出すときは、カーソルをすべてのパラメータに設定する必要があります (デフォルト値を持つパラメータを除く)。

カーソルにパラメータが必要になるのはどのような場合ですか? ここでの一般的なルールはプロシージャや関数の場合と同じです。カーソルが別の場所で使用され、WHERE 句の別の値で使用されることが予想される場合は、そのカーソル用のパラメータを定義する必要があります。パラメータありとパラメータなしのカーソルを比較してみましょう。パラメータのないカーソルの例:

CURSOR ジョーク_キュアは、ジョークから名前、カテゴリ、最終使用日を選択します。

カーソルの結果セットには、ジョークテーブル内のすべてのエントリが含まれます。行のサブセットのみが必要な場合は、クエリに WHERE 句が含まれます。

CURSOR Jordan_cur IS SELECT 名前、カテゴリ、last_used_date FROM ジョーク WHERE category = "HUSBAND";

このタスクを実行するためにパラメータは使用しませんでしたし、それらは必要ありません。この場合、カーソルは特定のカテゴリに属するすべての行を返します。しかし、このカーソルがアクセスされるたびにカテゴリが変わるとしたらどうなるでしょうか?

パラメータ付きカーソル

もちろん、カテゴリごとに個別のカーソルを定義することはありません。これは、データ駆動型アプリケーションの開発原則と完全に矛盾します。必要なカーソルは 1 つだけですが、カテゴリを変更できるカーソルは必要な情報を返します。この問題に対する最善の (ただし唯一ではない) 解決策は、パラメータ化されたカーソルを定義することです。

PROCEDURE Explain_joke (main_category_in IN Joke_category.category_id%TYPE) IS /* || || で構成されるパラメーターリストを持つカーソル単一の文字列パラメータから。 */ CURSOR ジョーク_キュア (カテゴリー_in IN VARCHAR2) は SELECT 名前、カテゴリー、最終使用日 FROM ジョーク WHERE カテゴリー = UPPER (カテゴリー_in); ジョーク_レクジョーク_cur%ROWTYPE; BEGIN /* カーソルが開かれるときに引数がカーソルに渡されるようになりました */ OPEN Joke_cur (main_category_in); FETCH ジョーク_cur INTO ジョーク_rec;

カーソル名と IS キーワードの間にオプションのリストが存在します。 WHERE 句内のハードコーディングされた HUSBAND 値は、UPPER パラメータ (category_in) への参照に置き換えられました。カーソルを開くときに、値を HUSBAND 、夫、または HuSbAnD に設定できます。カーソルは引き続き機能します。カーソルがジョークテーブル行を返すカテゴリの名前は、OPEN ステートメント (括弧内) でリテラル、定数、または式として指定されます。カーソルを開いた瞬間に SELECT コマンドが解析され、パラメータが値に関連付けられます。その後、行の結果セットが定義され、カーソルをフェッチできる状態になります。

パラメータを使用してカーソルを開く

新しいカーソルはどのカテゴリでも開くことができます。

OPEN ジョーク_cur(ジョーク_pkg.カテゴリー); OPEN ジョーク_cur("夫"); OPEN ジョーク_cur("政治家"); OPENjoke_cur(Jokes_pkg.relation || "-IN-LAW");

カーソルパラメーターは WHERE 句で最も一般的に使用されますが、SELECT ステートメントの他の場所でも参照できます。

DECLARE CURSOR ジョーク_cur(カテゴリ_in IN ARCHAR2) IS SELECT 名前、カテゴリ_in、last_used_date FROM ジョーク WHERE category = UPPER(category_in);

テーブルからカテゴリを読み取る代わりに、単純に category_in パラメータを選択リストに代入します。 WHERE 句が選択カテゴリをパラメータ値に制限しているため、結果は変わりません。

カーソルパラメータのスコープ

カーソルパラメータの有効範囲は、そのカーソルに限定されます。カーソルパラメータは、カーソルに関連付けられた SELECT コマンドの外部から参照することはできません。以下の PL/SQL スニペットは、program_name がブロック内のローカル変数ではないため、コンパイルされません。これは、カーソル内でのみ定義される正式なカーソルパラメータです。

DECLARE CURSOR feariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM Tales_from_the_crypt WHERE prog_name = Program_name; BEGIN プログラム名:= "呼吸するミイラ"; /* 無効な参照 */ OPEN sceariness_cur (program_name); .... 閉じる怖さ_cur; 終わり;

カーソルパラメータモード

カーソルパラメーターの構文は、プロシージャや関数の構文とよく似ていますが、カーソルパラメーターが IN パラメーターのみである点が異なります。カーソルパラメータを OUT または IN OUT に設定することはできません。これらのモードでは、プロシージャから値を渡したり返したりすることができますが、これはカーソルにとっては意味がありません。カーソルから情報を取得する方法は 1 つだけです。レコードをフェッチし、INTO 句の列のリストから値をコピーします。

デフォルトのパラメータ値

カーソルパラメータにはデフォルト値を割り当てることができます。デフォルトのパラメータ値を使用したカーソルの例:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT 従業員 ID、従業員名 FROM 従業員 WHERE 従業員 ID = emp_id_in;

emp_id_in パラメータにはデフォルト値があるため、FETCH コマンドではその値を省略できます。この場合、カーソルはコード 0 の従業員に関する情報を返します。

私の T-SQL コードでは、常にセットベースの操作を使用します。この種の操作は SQL Server が処理するように設計されており、シリアル処理よりも高速であるはずだと言われました。カーソルが存在することは知っていますが、その使用方法がわかりません。カーソルの例をいくつか挙げていただけますか? カーソルをいつ使用するかについてアドバイスをいただけますか? Microsoft がこれらを SQL Server に含めたのには、効率的に使用できる場所が必要なためだと思います。

解決

あるサークルではカーソルはまったく使用されませんが、他のグループではカーソルは最後の手段であり、他のグループでは定期的に使用されています。これらの陣営のそれぞれで、カーソルを使用するのにはさまざまな理由があります。あなたのスタンドオンカーソルに関係なく、おそらく彼らはカーソルを使用することに関係なく、したがって、カーソルベースの処理が適切かどうかを判断するには、コーディング手法を理解し、次に当面の問題を理解する必要があります。以下をせよ：

カーソルの例を見てください
カーソルのコンポーネントを分解する
追加のカーソルの例を提供する
カーソルの使用の長所と短所を分析する

SQL Server カーソルを作成する方法

SQL Server カーソルの作成は一貫したプロセスであるため、手順を一度学習すると、データをループするさまざまなロジックセットを使用してその手順を簡単に複製できます。手順を見ていきましょう。

まず、ロジックで必要な変数を宣言します。
次に、ロジック全体で使用する特定の名前でカーソルを宣言します。この直後にカーソルが開きます。
3 番目に、カーソルからレコードをフェッチしてデータ処理を開始します。
4 番目は、各ロジックセットに固有のデータプロセスです。これには、挿入、更新、削除などが考えられます。フェッチされたデータの各行に対して。これは、各行に対して実行されるこのプロセス中の最も重要なロジックのセットです。
5 番目に、手順 3 で行ったようにカーソルから次のレコードをフェッチし、選択したデータを処理して手順 4 を再度繰り返します。
6 番目に、すべてのデータが処理されたら、カーソルを閉じます。
最後の重要な手順として、カーソルの割り当てを解除して、SQL Server が保持しているすべての内部リソースを解放する必要があります。

ここからは、SQL Server カーソルをいつ使用するか、どのように使用するかを知るために、以下の例を確認してください。

SQL Server カーソルの例

以下は、バックアップがシリアル方式で発行されるすべての SQL Server データベースをバックアップするための単純なスクリプトのヒントからのカーソルの例です。

DECLARE @name VARCHAR(50) -- データベース名 DECLARE @path VARCHAR(256) -- バックアップファイルのパス DECLARE @fileName VARCHAR(256) -- バックアップのファイル名 DECLARE @fileDate VARCHAR(20) -- ファイル名 SET に使用されます@path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model) ","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @ name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

SQL Server カーソルコンポーネント

上記の例に基づくと、カーソルには次のコンポーネントが含まれます。

DECLARE ステートメント - コードブロックで使用される変数を宣言します。
SET\SELECT ステートメント - 変数を特定の値に初期化します。
DECLARE CURSOR ステートメント - 評価される値をカーソルに入力します
- 注 - DECLARE CURSOR FOR ステートメントには、SELECT ステートメントの変数と同じ数の変数があります。これは、1 つまたは複数の変数および関連する列である可能性があります。
OPEN ステートメント - カーソルを開いてデータ処理を開始します。
FETCH NEXT ステートメント - カーソルから変数に特定の値を代入します。
- 注 - このロジックは、WHILE ステートメントの前の初期設定に使用され、プロセス内の各ループ中に WHILE ステートメントの一部として再度使用されます。
WHILE ステートメント - データ処理を開始および継続するための条件
BEGIN...END ステートメント - コードブロックの開始と終了
- 注 - データ処理に基づいて、複数の BEGIN...END ステートメントを使用できます。
データ処理 - この例では、このロジックはデータベースを特定のパスとファイル名にバックアップすることですが、これはほぼ任意の DML または管理ロジックである可能性があります。
CLOSE ステートメント - 現在のデータと関連するロックを解放しますが、カーソルを再度開くことは許可します。
DEALLOCATE ステートメント - カーソルを破棄します

推奨書籍

SQL Server カーソルと代替カーソルの詳細については、こちらをご覧ください。

追加の SQL Server カーソルの例

上の例では、カーソルを介してバックアップが発行されています。カーソルベースのロジックを利用するその他のヒントを確認してください。

SQL Server で外部キー制約を無効化、有効化、削除、再作成するコマンドを作成するスクリプト

SQL Server カーソル分析

以下の分析は、カーソルベースのロジックが有益である場合とそうでない場合があるさまざまなシナリオについての洞察として役立つことを目的としています。

オンライントランザクション処理 (OLTP) - ほとんどの OLTP 環境では、短いトランザクションには SET ベースのロジックが最も合理的です。私たちのチームは、すべての処理にカーソルを使用するサードパーティアプリケーションに遭遇し、問題が発生しましたが、これはまれな出来事です。通常、SET ベースのロジックは十分に実現可能であり、カーソルが必要になることはほとんどありません。
レポート - レポートの設計と基礎となる設計に基づいて、通常、カーソルは必要ありません。ただし、私たちのチームは、基になるデータベースに参照整合性が存在せず、レポート値を正しく計算するにはカーソルを使用する必要があるというレポート要件に遭遇しました。下流プロセスのためにデータを集約する必要があるとき、私たちも同じ経験をしました。カーソルベースのアプローチはすぐに開発され、ニーズを満たす許容可能な方法で実行されました。
シリアル化された処理 - シリアル化された方法でプロセスを完了する必要がある場合、カーソルは実行可能なオプションです。
管理タスク - 多くの管理タスクはシリアル方式で実行する必要があり、これはカーソルベースのロジックにうまく適合しますが、そのニーズを満たすために他のシステムベースのオブジェクトが存在します。このような状況の中には、プロセスを完了するためにカーソルが使用される場合があります。
大規模なデータセット - 大規模なデータセットでは、次の 1 つ以上が発生する可能性があります。
- カーソルベースのロジックは、処理のニーズを満たすように拡張できない場合があります。
- 最小限のメモリ量を備えたサーバー上で大規模なセットベースの操作を行うと、データがページングされたり、SQL Server を独占したりする可能性があり、時間がかかり、競合やメモリの問題が発生する可能性があります。したがって、カーソルベースのアプローチがニーズを満たす可能性があります。
- 一部のツールは本質的に内部でデータをファイルにキャッシュするため、メモリ内でデータを処理する場合と実際に処理しない場合があります。
- データがステージング SQL Server データベースで処理できる場合、運用環境への影響は、最終データが処理されるときのみです。ステージングサーバー上のすべてのリソースを ETL プロセスに使用して、最終データをインポートできます。
- SSIS は、データセットのバッチ処理をサポートしています。これにより、大規模なデータセットをより管理しやすいサイズに分割し、カーソルを使用した行ごとのアプローチよりもパフォーマンスを向上させるという全体的なニーズを解決できます。
- カーソルまたは SSIS ロジックのコーディング方法によっては、エラーが発生した時点から再起動できる場合があります。
- GO コマンドでバッチを繰り返す
次のステップ
- データ処理に関する決定を迫られた場合は、SQL Server のカーソルの使用状況を判断してください。これらは、アプリケーションや運用プロセスに存在する場合もあれば、存在しない場合もあります。タスクを完了するには多くの方法があるため、カーソルの使用が合理的な代替手段となる場合もあれば、そうでない場合もあります。あなたが裁判官になってください。
- 別のコーディング手法で問題が発生し、何かを迅速に完了する必要がある場合は、カーソルを使用することが有力な代替手段となる可能性があります。データの処理には時間がかかる場合がありますが、コーディング時間は大幅に短縮される可能性があります。 1 回限りのプロセスまたは夜間の処理がある場合は、これでうまくいく可能性があります。
- ご使用の環境でカーソルが回避されている場合は、必ず別の実行可能な代替手段を選択してください。このプロセスによって他の問題が発生しないことを確認してください。たとえば、カーソルが使用され、数百万行が処理される場合、キャッシュからすべてのデータがフラッシュされ、さらなる競合が発生する可能性がありますか? それとも、大規模なデータセットの場合、データはディスクにページングされるか、一時ディレクトリに書き込まれるのでしょうか?
- カーソルベースのアプローチと他の代替方法を評価するときは、必要な時間、競合、およびリソースの観点から技術を公正に比較してください。これらの要素があなたを適切なテクニックに導いてくれることを願っています。

DECLARE CURSOR コマンドを使用すると、テーブルからレコードを行ごとに取得して操作できます。これにより、SQL が行う従来のデータセット処理の代わりに、行ごとの処理が可能になります。

ごく最初の近似では、カーソルを操作する場合、次の手順が使用されます。

カーソルは DECLARE コマンドで作成されます。カーソルは OPEN コマンドで開きます。

カーソル操作は FETCH コマンドを使用して実行されます。カーソルは CLOSE コマンドで閉じます。

DECLARE CURSOR コマンドは SELECT ステートメントを指定します。 SELECT ステートメントによって返される各行は、個別に取得して処理できます。次の Oracle の例では、カーソルは他のいくつかの変数とともに宣言ブロックで宣言されています。その後、後続の BEGIN…END ブロックで、カーソルが開き、カーソル上で選択が行われ、カーソルが閉じます。

CURSOR title_price_cursor IS SELECT タイトル、タイトルからの価格

価格が NULL でない場合。 title_price_val title_price_cursor ROWTYPE; 新しい価格 NUMBER(10.2);

タイトル価格カーソルを開く;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 new_title_price 値に挿入

(title_price_val.title, new_price) CLOSE title_price_cursor; 終わり;

この例では PL/SQL を使用しているため、コードの大部分は本書では説明されません。ただし、カーソル宣言は DECLARE ブロック内で明確に表示されます。 PL/SQL実行可能ブロックでは、カーソルはOPEN文で初期化され、値はFETCH文で取得され、最後にカーソルはCLOSE文で閉じられます。

SELECT ステートメントはカーソルの中核であるため、DECLARE CURSOR ステートメントに含める前に徹底的にテストすることをお勧めします。 SELECT ステートメントは、基になるテーブルまたはビューに対して操作できます。したがって、読み取り専用カーソルは更新不可能なビューでも機能します。 SELECT ステートメントには、ソーステーブルを更新しない限り、ORDER BY、GROUP BY、HAVING などの句を含めることができます。カーソルが FOR UPDATE として定義されている場合は、そのような句を SELECT ステートメントから削除することをお勧めします。

ローカルカーソルは、ストアドプロシージャの出力パラメータとしてよく使用されます。したがって、ストアドプロシージャでは、カーソルを定義して設定し、それを呼び出したバッチジョブまたはストアドプロシージャに渡すことができます。

次の簡単な DB2 の例では、admin_group "XO1" 内の部門番号、部門名、マネージャー番号を検索するカーソルを宣言します。

DECLARE dept_cursor CURSOR

FOR SELECT 部門名、部門名、マネージャー名

WHERE 管理グループ = "X01"

ORDER BY d "ept_name ASC、dept_nbr DESC、mgr_nbr DESC;

Microsoft SQL Server の次の例では、publishers テーブルでカーソルを宣言して開きます。カーソルは、SELECT ステートメントに一致する最初のレコードをパブリッシャーテーブルから選択し、それを別のテーブルに挿入します。次に、すべてのレコードが処理されるまで、次のレコードに進み、さらに次のレコードに進みます。最後に、カーソルが閉じられ、メモリが解放されます (DEALLOCATE コマンドは Microsoft SQL Server でのみ使用されます)。

DECLARE @publisher_name VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM Publishers WHERE country "USA"

pub_cursor INTO Publisher_name から次をフェッチします

WHILE @s>FETCH_STATUS=0

INSERT INTO external_publishers VALUES("j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

この例では、カーソルがレコードセット内をどのように移動するかを確認できます。 (実際には、このタスクを実行するより良い方法、つまり INSERT、SELECT ステートメントがあるため、この例はこのアイデアを示すことのみを目的としています。)

カーソル - コンテキストメモリ領域へのリンク。 SQL プログラミング言語 (Oracle、Microsoft SQL Server) の一部の実装では、クエリの実行時に取得された結果セットと、それに関連付けられた現在のレコードポインター。カーソルは代替データストアである仮想テーブルであると言えます。同時に、カーソルを使用すると、通常の配列であるかのようにデータにアクセスできます。
カーソルはストアドプロシージャで使用されます。理論は十分なので、例を見てみましょう。
私たちはデータベースを持っています (データベースは少し良くありません。これは私の研究室の 1 つですが、データベースの先生はそのような構造を主張しました)

/* 銀行詳細 */
CREATE TABLE `bank` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、

主キー (`BankId`)
)ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*入金データ */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL 、
`Persent` INTEGER (11) DEFAULT NULL 、
`ContributeAmount` DECIMAL (10,0) NOT NULL 、
`ClientId` INTEGER (11) NOT NULL 、
主キー (`BankId`、`ClientId`)、
KEY `BankId` (`BankId`)、
KEY `ClientId` (`ClientId`)、
CONSTRAINT `bankdistribution_fk` 外部キー (`BankId`) REFERENCES `bank` (`BankId`)、
制約 `bankdistribution_fk1` 外部キー (`ClientId`) 参照 `client` (`ClientId`)
)ENGINE=InnoDB
/*貢献者に関するデータ*/
CREATE TABLE `クライアント` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT、
`CreditCardId` BIGINT(10) NOT NULL 、
`姓` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
`名前` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
`アドレス` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
`SafeId` INTEGER (5) NOT NULL 、
主キー (`ClientId`、`CreditCardId`)、
KEY `ClientId` (`ClientId`)
)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"

各銀行を順番に受け取り、それに対していくつかのアクションを実行する必要があるとします。このようなリクエストはこれに役立つ可能性があります。

「銀行」を選択してください。* 「銀行」から LIMIT RECORD_NUMBER_WE NEED,1

。したがって、LIMIT NUMBER_NECESSARY_US_RECORD,1 を使用して、ループ内でバンクテーブルから各レコードを順番に抽出し、値 NUMBER_NECESSARY_US_RECORD を 1 ずつ増やしながら、必要なアクションを実行します。次に、同じことを行いますが、カーソルを使用します。

始める
/* データをフェッチする変数 */
vBankId を整数で宣言します。
vBankName VARCHAR(50); を宣言します。
vAddress VARCHAR(50) を宣言します。
vPhone VARCHAR (50) を宣言します。
/* 変数ハドラー - a*/
完了した整数のデフォルト 0 を宣言します。
/*カーソル宣言*/
BankCursor カーソルを Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` として宣言します。ここで 1;
/*HANDLER の割り当て (以下で説明します) */
SQLSTATE "02000" SET の CONTINUE ハンドラーを宣言します。done=1;
/* カーソルを開く */
OpenBankCursor;
/* データを抽出します */
完了中 = 0 DO

私たちはやるべきことをやります
途中で終了します。
/* カーソルを閉じる */
BankCursor を閉じます。
終わり ;
* このソースコードは、ソースコードハイライターで強調表示されています。

エラー: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

メッセージ: データがありません - フェッチ、選択、または処理された行はゼロです

SQLSTATE: 02000 は、カーソルの終端に到達したとき、または選択または更新が空の文字列を返したときに発生します。

次の行では、カーソル DECLARE カーソル名 CURSOR FOR select_statement; を宣言しました。
カーソルを開くカーソル名を開く;
さらに、カーソルの終わりに到達するまで (WHILE 完了 = 0 DO)、データを抽出して処理します。
ストアドプロシージャを終了する前にカーソルを閉じます。カーソル名を閉じます。

何も複雑なことはないようです。しかし、SQLSTATE "02000" には多くの落とし穴があります。

完了中 = 0 DO
BankCursor を vBankId、vBankName、vAddress、vPhone にフェッチします。

Bankdistribution から (ContributeAmount) INTO vContributeAmountSUM を選択します (BankId = vBankId 制限 1)。
何らかの行動を起こす
途中で終了します。
* このソースコードは、ソースコードハイライターで強調表示されています。

すべてが適切であり、構文の観点からは正しいです。しかし、論理的にはノーです。寄付者がどの銀行にも口座を開設していない可能性があります。その場合、Select (ContributeAmount) INTO vContributeAmountSUM FROM Bankdistribution where BankId = vBankId 制限 1; の場合、 SQLSTATE: 02000 がトリガーされ、done 変数が 1 に設定され、while ループは予想よりも早く終了します。これは次のようにすることで回避できます

完了中 = 0 DO
BankCursor を vBankId、vBankName、vAddress、vPhone にフェッチします。
/* 銀行への預金額を抽出します */

if (vContributeAmountSUM > 0) then
/* 銀行への預金額を抽出します */

終了の場合;
何らかの行動を起こす
途中で終了します。
* このソースコードは、ソースコードハイライターで強調表示されています。

最初のリクエストでは、コントリビューションがあるかどうかを確認し (コントリビュートがない場合は、vContributeAmountSUM == 0)、コントリビューションがある場合にのみデータを抽出します。

ここで、顧客ごとに異なる銀行の口座の合計金額を回収する必要があるとします。
ClientSummCursor 合計を選択するカーソルを宣言

Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` の内部結合クライアント (client.ClientId = Bankdistribution.`ClientId`) の ClientSummCursor カーソルを宣言します。ここで 1 は `bankdistribution` によってグループ化されます。 `クライアントID`;
ClientSummCursor を開きます。
完了中 = 0 DO
BankCursor を vBankId、vBankName、vAddress、vPhone にフェッチします。
/* 銀行への預金額を抽出します */
BankId = vBankId 制限 1 の場合、Bankdistribution から Count(ContributeAmount) INTO vContributeAmountSUM を選択します。
/* この銀行に本当に預金があるかどうかを確認します */
if (vContributeAmountSUM > 0) then
/* 銀行への預金額を抽出します */
BankId = vBankId 制限 1 の場合、bankdistribution から ContributeAmount INTO vContributeAmountSUM を選択します。
終了の場合;

私たちは何らかの行動を起こします。
途中で終了します。
* このソースコードは、ソースコードハイライターで強調表示されています。

ClientSummCursor のデータが BankCursor のデータより早く終了し、SQLSTATE: 02000 がトリガーされ、done 変数が 1 に設定され、while ループが予想よりも早く終了した場合にも、同じ状況が発生する可能性があります。これは次のようにすることで回避できます

ClientSummCursor を開きます。
完了中 = 0 DO
BankCursor を vBankId、vBankName、vAddress、vPhone にフェッチします。
/* 銀行への預金額を抽出します */
BankId = vBankId 制限 1 の場合、Bankdistribution から Count(ContributeAmount) INTO vContributeAmountSUM を選択します。
/* この銀行に本当に預金があるかどうかを確認します */
if (vContributeAmountSUM > 0) then
/* 銀行への預金額を抽出します */
BankId = vBankId 制限 1 の場合、bankdistribution から ContributeAmount INTO vContributeAmountSUM を選択します。
終了の場合;
/* 2 番目のカーソルからデータをフェッチする前に、sqlstate の状態を覚えておいてください */
SET old_status = 完了;
/* 必要なデータを抽出します */
ClientSummCursor INTO vSum,vClientId を取得します。
/* sqlstate 0200 が失敗した場合は、データがフェッチされたかどうかを確認します */
if (完了 = 0) then
私たちは何らかの行動を起こします。
終了の場合;
/* 時間が終了する前に、変数の値を復元します */
完了 = old_status を設定します。
途中で終了します。
* このソースコードは、ソースコードハイライターで強調表示されています。

ここまで読んでくれた皆さんに感謝します。これが誰かの役に立つことを願っています。

今日は、パラメータを受け入れるすでに作成されたプロシージャを一括で実行する方法など、多くの興味深いことを検討します。静的パラメータだけでなく、たとえば通常の関数のようにテーブルに基づいて変化するパラメータも使用できます。これは単に役に立ちます。 カーソルとループ次に、これらすべてを実装する方法を見ていきます。

ご理解のとおり、カーソルとループは特定のタスクに適用できるものと考えます。そして、どのようなタスクなのか、今からお話します。

計算やその計算に基づく挿入など、通常の SQL 関数では実行できないことを実行するプロシージャがあります。そして、次のように実行します。

EXEC test_PROCEDURE par1、par2

言い換えれば、指定されたパラメータのみを使用して実行しますが、このプロシージャを 100 回、200 回、あるいはそれ以上の回数実行する必要がある場合、これはあまり便利ではないことに同意するでしょう。長い間。次のように、選択クエリの通常の関数のようにプロシージャを実行できれば、はるかに簡単になります。

test_table から my_fun(id) を選択します

つまり、この関数は test_table テーブルの各レコードに対して機能しますが、ご存知のとおり、このプロシージャはこのように使用できません。しかし、計画の実装に役立つ方法があり、より正確には 2 つの方法があります。1 つ目はカーソルとループを使用する方法で、2 つ目はカーソルを使用せずにループのみを使用する方法です。どちらのオプションも、追加のプロシージャを作成し、将来実行することを意味します。

注記！ Management Studio を使用して、すべての例を MSSql 2008 DBMS に記述します。また、以下にリストされているすべてのアクションには、SQL、つまり Transact-SQL プログラミングに関する必要な知識が必要です。まずは次の記事を読むことをお勧めします。

それでは始めましょう。手順を書く前に、例の初期データについて考えてみましょう。

test_table というテーブルがあるとします。

CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

プロシージャが実行するいくつかの計算に基づいて、データを挿入する必要があります。 my_proc_test、この場合はデータを挿入するだけですが、実際には多くの計算を実行できる独自のプロシージャを使用できます。そのため、この例ではこのプロシージャは重要ではなく、単なる例です。さて、作成してみましょう:

プロシージャを作成します。 (@number numeric, @pole1 varchar(50), @pole2 varchar(50)) AS BEGIN INSERT INTO dbo.test_table (number,pole1,pole2) VALUES (@number, @pole1, @pole2) END GO

3 つのパラメータを受け取り、テーブルに挿入するだけです。

このプロシージャを、テーブルまたはビュー (VIEWS) 内の行の数だけ実行する必要があるとします。つまり、ソースの各行に対して一括で実行する必要があります。

そして、たとえば、このようなソースを作成してみましょう。単純なテーブルができます。 test_table_vrem先ほど述べたように、一時テーブルやビューなどのソースを使用できます。

CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

テストデータを入力してみましょう。

ここで、プロシージャを行ごとに実行する必要があります。異なる設定で 3 回。ご理解のとおり、これらのフィールドの値はパラメータです。つまり、手順を手動で開始すると、次のようになります。

exec my_proc_test 1、'pole1_str1'、'pole2_str1'

適切なパラメーターを使用して、さらに 3 回繰り返します。

しかし、それはしたくないので、メインプロシージャを必要なだけ実行する追加のプロシージャをもう 1 つ作成します。

最初のオプション。

プロシージャ内でのカーソルとループの使用

早速本題に入り、手順を書いてみましょう( my_proc_test_all）、いつものようにコードにコメントを付けました。

プロシージャを作成します。 AS -- 変数を宣言 DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) -- カーソルを宣言 DECLARE my_cur CURSOR FOR SELECT 番号、pole1、pole2 FROM test_table_vrem -- カーソルをオープン OPEN my_cur -- 最初にデータを読み取ります文字列を変数に FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 --カーソル内にデータがある場合はループに入り、カーソル内の行がなくなるまでスピンします WHILE @@FETCH_STATUS = 0 BEGIN -- ループの各反復で、必要なパラメータを指定して main プロシージャを実行します。 exec dbo.my_proc_test @number, @pole1, @pole2 -- カーソルの次の行を読み取ります。 FETCH NEXT FROM my_cur INTO @number, @pole1 , @pole2 END -- カーソルを閉じる CLOSE my_cur DEALLOCATE my_cur GO

あとは、それを呼び出して結果を確認するだけです。

前 SELECT * FROM test_table --call EXEC dbo.my_proc_test_all --after SELECT * FROM test_table

ご覧のとおり、すべてが正常に機能しました。つまり、my_proc_test プロシージャは 3 回すべて機能し、追加のプロシージャを起動したのは 1 回だけでした。

2 番目のオプション。

プロシージャ内でループのみを使用する

ここで一時テーブル内の行の番号付けが必要であることをすぐに言わなければなりません。各行には番号を付ける必要があります。たとえば、1、2、3 など、一時テーブルのフィールドは数値です。

手順を書く my_proc_test_all_v2

プロシージャを作成します。 AS --変数の宣言 DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) DECLARE @cnt int DECLARE @i int --一時テーブルの行数を取得 SELECT @cnt=count(*) FROM test_table_vrem - - 識別子の初期値を設定します SET @i=1 WHILE @cnt >= @i BEGIN -- パラメータに値を割り当てます SELECT @number=number, @pole1=pole1, @pole2=pole2 FROM test_table_vrem WHERE number = @I --ループの反復ごとに、必要なパラメータを指定してメインプロシージャを実行します。 EXEC dbo.my_proc_test @number, @pole1, @pole2 --increase step set @i= @i+1 END GO

そして結果をチェックしますが、my_proc_test_all プロシージャを使用してテーブルを入力したばかりなので、まずテーブルをクリアします。

テーブルをクリア DELETE test_table -- SELECT * FROM test_table の実行前 -- EXEC プロシージャ dbo.my_proc_test_all_v2 の呼び出し -- SELECT * FROM test_table の実行後

予想どおり、結果は同じですが、カーソルは使用されません。どのオプションを使用するかはあなた次第です。原則として番号付けが必要ないため、最初のオプションが適しています。しかし、ご存知のとおり、カーソル内の行数が多い場合、カーソルは長時間動作します。 2 番目のオプションは機能するので良いです。行数が多いが番号付けが必要な場合は、その方が速いように思えます。個人的にはカーソルを使用するオプションが好きですが、一般的には、考え出すのはあなた次第です。もっと便利なものとして、タスクを実装する方法の基本を示しただけです。幸運を！

SQLサーバーのカーソルプロシージャ。 DECLARE CURSOR コマンドは一般的な規則です。 メモリの割り当て解除、割り当て解除