埋め込み SQL のカーソル。 ms SQL サーバー データベースでのカーソルの作成と使用 SQL でのカーソルの使用
たくさんのコメントをいただきました。 その中の 1 つで、読者から、ストアド プロシージャの重要な要素の 1 つであるカーソルにもっと注意を払うようにとの質問がありました。
カーソルはストアド プロシージャの一部であるため、この記事では HP について詳しく説明します。 特に、HP からデータセットを抽出する方法。
カーソルとは何ですか?
MySQL ではカーソルを単独で使用することはできません。 これはストアド プロシージャの重要なコンポーネントです。 私はカーソルを C/C++ の「ポインター」、または PHP の foreach ステートメントのイテレーターと比較します。
カーソルを使用すると、データ セットを反復処理し、特定のタスクに従って各レコードを処理できます。
このレコード処理操作は PHP 層でも行うことができ、処理された概要/統計結果を単純に返すことができるため、PHP 層に渡されるデータ量が大幅に削減されます (これにより、クライアント側での select - foreach 処理が不要になります)。 。
カーソルはストアド プロシージャで実装されるため、HP に固有のすべての利点 (および欠点) (アクセス制御、プリコンパイル、デバッグの難しさなど) が備わっています。
カーソルに関する公式ドキュメントはここで見つけることができます。 カーソルの宣言、オープン、クローズ、取得に関連する 4 つのコマンドについて説明します。 前述したように、他のストアド プロシージャ ステートメントについてもいくつか取り上げます。 始めましょう。
実用例
私の個人的なウェブサイトには、私のお気に入りの NBA チーム、レイカーズの試合結果が掲載されたページがあります。
このページのテーブル構造は非常に単純です。
図 1. レイカーズの試合結果表の構造
私は 2008 年からこの表に記入しています。 2013-14 シーズンのレイカーズの最新の試合結果の一部は以下のとおりです。
米。 2. 2013-2014シーズン レイカーズの試合結果表（一部）
(私は MySQL データベースを管理するための GUI ツールとして MySQL Workbench を使用しています。好みの別のツールを使用することもできます)。
そうですね、レイカーズのバスケットボール選手たちは最近あまり良いプレーをしていないことを認めざるを得ません。 1月15日時点で6連敗。 私はこれらを定義しました」 6連敗" 現在の日付から開始して (さらに以前の試合まで)、winlose 値が "L" (負け) である連続する試合の数を手動でカウントします。
これは決して不可能な作業ではありませんが、条件がより複雑になり、データ テーブルが非常に大きくなると、時間がかかり、エラーの可能性も高くなります。
単一の SQL ステートメントで同じことを実行できますか? 私は SQL の専門家ではないので、望ましい結果を達成する方法を理解できませんでした (" 6連敗") を単一の SQL ステートメントを通じて実行します。 教祖の意見は私にとって非常に貴重ですので、以下のコメントに残してください。
PHP経由でこれを行うことはできますか? はい、確かに。 そのシーズンのゲーム データ (具体的には winlos 列) を取得し、レコードを反復処理して現在の連敗の長さを計算できます。
しかし、そのためには、その年のすべてのデータをカバーする必要があり、データのほとんどは役に立たないことになります (どのチームも 20 試合以上連続で記録を残す可能性はほとんどありません)レギュラーシーズンは82試合）。
ただし、シリーズを決定するために PHP でいくつのレコードを取得する必要があるかは正確にはわかりません。 したがって、不要なデータを不必要に抽出せずに済ますことはできません。 そして最後に、このテーブルから知りたいのが現在の連続した勝ち数/負け数だけである場合、なぜすべてのデータ行を抽出する必要があるのでしょうか?
別の方法でこれを行うことはできますか? はい、可能です。 たとえば、連続した勝敗数の現在値を保存するために特別に設計されたバックアップ テーブルを作成できます。
新しいレコードを追加するたびに、このテーブルが自動的に更新されます。 しかし、これは非常に面倒で、間違いが発生しやすくなります。
では、どうすればこれをより良くできるでしょうか?
ストアド プロシージャでのカーソルの使用
この記事のタイトルから推測できるかもしれませんが、この問題を解決するための (私の意見では) 最良の代替案は、ストアド プロシージャでカーソルを使用することです。
MySQL Workbench で最初の HP を作成しましょう。
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1)) BEGIN cancel current_win char(1); current_streak int を宣言します。 current_status char(1) を宣言します。 year=cur_year および winlose のレイカーズから winlose を選択するための cur カーソルを宣言します<>"" ID の説明で並べ替えます。 current_streak=0 を設定します。 オープンカー; cur を current_win にフェッチします。 現在のストリーク = 現在のストリーク +1 を設定します。 start_loop: cur を current_status にループフェッチします。 現在のステータスの場合<>current_win は start_loop を離れます。 それ以外の場合は、current_streak=current_streak+1 を設定します。 次の場合は終了します。 ループを終了します。 クローズカー; current_streak を最長のストリークに選択します。 current_win を「status」に選択します。 終わり
この HP には 1 つの受信パラメータと 2 つの送信パラメータがあります。 これは HP 署名を定義します。
HP の本文では、一連の結果 (勝敗、current_win)、現在のシリーズ、および特定の試合の現在の勝敗ステータスに対するいくつかのローカル変数も宣言しました。
この行はカーソルの宣言です。 cur という名前のカーソルと、このカーソルに関連付けられたデータのセットを宣言しました。これは、それらの一致の勝敗ステータスです (winlose 列の値は "W" または "L" のいずれかですが、空ではありません)。 ID で降順に並べられた特定の年 (最後にプレイされたゲームほど ID が高くなります)。
目には見えませんが、このデータセットには一連の値「L」と「W」が含まれると想像できます。 図 2 に示すデータに基づくと、「LLLLLLWLL...」 (6 つの値「L」、1 つの「W」など) となるはずです。
連続した勝敗数を計算するには、最後 (および指定されたデータセットの最初) の一致から開始します。 カーソルが開かれると、カーソルは常に対応するデータ セットの最初のレコードから始まります。
最初のデータがロードされた後、カーソルは次のレコードに移動します。 したがって、カーソルの動作は、FIFO (先入れ先出し) システムを使用して一連のデータを反復処理するキューに似ています。 これこそまさに私たちが必要としているものです。
現在の勝敗ステータスとセット内の連続する同一要素の数を受け取った後、データセットの残りの部分をループ処理し続けます。 ループの各反復では、ループを終了するか、すべてのレコードが反復されるまで、カーソルは次のレコードに「ジャンプ」します。
次のレコードのステータスが現在の連続した勝ち/負けのセットと同じである場合、それは連勝が続いていることを意味し、連続した勝ち (または負け) の数をさらに 1 増やして、データのループを続けます。
ステータスが異なる場合は、連続記録が途切れたことを意味し、サイクルを停止できます。 最後に、カーソルを閉じて、元のデータを残します。 この後、結果が表示されます。
この HP の動作をテストするには、短い PHP スクリプトを作成します。
exec("call streak(2013, @longeststreak, @status)"); $res=$cn->query("select @longeststreak, @status")->fetchAll(); var_dump($res); // 出力の生のビューを取得するには、ここに出力をダンプします $win=$res["@status"]="L"?"Loss":"Win"; $streak=$res["@longeststreak"]; echo "レイカーズは現在 $streak 連続 $win.n";
処理結果は次の図のようになります。
ストアド プロシージャからのデータ セットの導出
この記事の途中で何度か、別の HP への連続した呼び出しの処理結果からデータ セットを構成するデータ セットを HP から取得する方法に関する会話が行われました。
ユーザーは、以前に作成した HP を使用して、年間の継続的な勝ち負けだけではなく、より多くの情報を取得したいと考えているかもしれません。 たとえば、異なる年の一連の勝敗を表示する表を作成できます。
| 年 | 勝ち負け | ストリーク |
| 2013 | L | 6 |
| 2012 | L | 4 |
| 2011 | L | 2 |
(原則として、より有益な情報は、特定のシーズンでの最も長い連勝または連敗の期間です。この問題を解決するには、記載されている HP を簡単に拡張できますので、このタスクは興味のある読者に任せます。現在の記事のフレームワークでは、現在の一連の勝敗の処理を続けます)。
MySQL ストアド プロシージャは、select ... from ... ステートメントとは異なり、スカラー値 (整数、文字列など) のみを返すことができます (結果はデータ セットに変換されます)。 問題は、結果を取得したいテーブルが、ストアド プロシージャの処理結果からコンパイルされた既存のデータベース構造に存在しないことです。
この問題を解決するには、一時テーブル、または可能であればバックアップ テーブルが必要です。 一時テーブルを使用して目前の問題を解決する方法を見てみましょう。
まず、2 番目の HP を作成します。そのコードを以下に示します。
DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`() begin destroy cur_year, max_year, min_year int; レイカーズから max(year)、min(year) を max_year、min_year に選択します。 一時テーブルが存在する場合は削除 yearly_streak; 一時テーブルを作成します yearly_streak (season int, streak int, win char(1)); cur_year=max_year を設定します。 year_loop: cur_year の場合にループします。 上記のコードに関する重要な注意点がいくつかあります。 結果を取得するために、別の小さな PHP スクリプトを作成します。そのコードを以下に示します。 query("call yearly_streak")->fetchAll(); foreach ($res as $r) ( echo sprintf("年 %d では、最長の W/L ストリークは %d %sn です", $r["シーズン"], $r["ストリーク"], $r[ "勝つ"]）; 表示される結果は次のようになります。 )ENGINE=InnoDB )ENGINE=InnoDB 各銀行を順番に受信し、それに対していくつかのアクションを実行する必要があるとします。次のクエリがこれに役立ちます。 * このソース コードはソース コード ハイライターで強調表示されています。 エラー: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) メッセージ: データがありません - フェッチ、選択、または処理された行はゼロです SQLSTATE: 02000 は、カーソルの終端に到達したとき、または選択または更新が空の文字列を返したときに発生します。 次の行では、カーソル DECLARE カーソル名 CURSOR FOR select_statement; を宣言しています。 複雑ではないようです。 しかし、SQLSTATE "02000" には多くの落とし穴があります。 * このソース コードはソース コード ハイライターで強調表示されています。 * このソース コードはソース コード ハイライターで強調表示されています。 ここで、顧客ごとに異なる銀行の口座の合計金額を削除する必要があるとします。 ClientSummCursor を開きます。 * このソース コードはソース コード ハイライターで強調表示されています。 ClientSummCursor カーソル内のデータが BankCursor 内のデータより早く終了し、SQLSTATE: 02000 がトリガーされ、done 変数が 1 に設定され、while ループが予想よりも早く終了した場合にも、同じ状況が発生する可能性があります。 これは次のようにすることで回避できます * このソース コードはソース コード ハイライターで強調表示されています。 ここまで読んでくれた皆さんに感謝します、これが誰かの役に立つことを願っています。 カーソルは、SELECT ステートメントによって返された結果セットの行を個別に処理できるようにするオブジェクトです。 次に、Transact-SQL 言語でサポートされるカーソルを見ていきます。 これらは、データベース サーバー側のオブジェクトとして存在するサーバー側カーソルです。 クライアント データベース アプリケーションの作成に使用されるクライアント カーソルもあります。 文献によると、ほとんどの場合、カーソルを使用したデータ セットの行ごとの処理は、複数の行を処理するために SQL ツールで実行される同様のアクションよりも大幅に時間がかかります。 したがって、行のセットを使用した操作によって必要なアクションを記述することが明らかに効果的でない、または不可能である場合にのみカーソルを使用することをお勧めします。 カーソルの操作には通常、次の手順が含まれます。 カーソルは DECLARE ステートメントを使用して宣言されます。その形式を次に示します。 SQL Server では、この演算子は ISO SQL 標準の構文 (標準のバージョンはドキュメントで指定されていません) と、一連の Transact-SQL CURSOR 言語拡張機能を使用した構文の両方をサポートしていることに注意してください。 FOR 選択ステートメント 拡張 Transact-SQL 構文: DECLARE カーソル名 CURSOR FOR 選択ステートメント ]][;] GLOBAL キーワードを指定することは、宣言されたカーソルが、サーバーへの現在の接続内で実行されるジョブ バッチ、トリガー、またはストアド プロシージャで使用できることを意味します。 カーソルは、接続が切断された場合にのみ暗黙的に解放されます。 「ローカル」カーソルは、デフォルトで作成されたか明示的に LOCAL を指定して作成されたかに関係なく、それが作成されたジョブ バッチ、ストアド プロシージャ、またはトリガーでのみ使用できます。 このカーソルは、バッチ、ストアド プロシージャ、またはトリガーの実行が完了すると暗黙的に解放されます。 例外は、カーソルがストアド プロシージャの OUTPUT パラメータを介して渡される場合です。 その後、カーソルを参照しているすべての変数が解放されるか、カーソルがスコープ外に出ると、カーソルは解放されます。 FORWARD_ONLY は、カーソルに沿って前方へのみ「移動」できることを意味します (FETCH NEXT コマンドのみが使用可能です。以下を参照)。 カーソル内の各エントリは最大 1 回処理できます。 STATIC、KEYSET、または DYNAMIC キーワードを指定せずに FORWARD ONLY を指定した場合、カーソルは DYNAMIC カーソルとして動作します (以下を参照)。 FORWARD_ONLY も SCROLL も指定されておらず、STATIC、KEYSET、または DYNAMIC キーワードも指定されていない場合、デフォルトは FORWARD_ONLY になります。 SCROLL は、カーソルの周りを任意の方向に「移動」できることを意味します (FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE は FETCH ステートメントで使用できます)。 SCROLL オプションは、FAST_FORWARD オプションと一緒に指定できません。 STATIC、KEYSET、および DYNAMIC カーソルのデフォルト値は SCROLL です。 STATIC は、カーソルが更新できないことを意味します。 このようなカーソルの結果として得られるデータ セットはデータベースから取得され、一時オブジェクト tempdb のデータベースに保存されます。 カーソルの基礎となるテーブルへの変更は、これ以降カーソルには反映されません。 KEYSET – このタイプのカーソルの場合、選択されたレコードを識別する一連のキー値が一時テーブルに保存されます。 カーソルを移動すると、非キー属性の値が対応するテーブルから取得されるため、カーソルを移動すると、非キー列への変更が表示されます。 カーソル内の行が、FETCH オペレーターによってフェッチされるまでにテーブルからすでに削除されている場合、サービス変数 @@ FETCH_STATUS は値 -2 を返します。 カーソルを開いた後にテーブルに追加された行は、カーソルには表示されません。 カーソルを生成するクエリに一意のインデックスを持たないテーブルが少なくとも 1 つ含まれる場合、KEYSET 型のカーソルは STATIC 型に変換されます。 DYNAMIC は、新しく挿入された行を含む、結果セットの行で行われたすべてのデータ変更を表示する、最もリソースを消費するカーソル タイプです。 各選択のデータ値、順序、行メンバーシップは異なる場合があります。 FETCH ABSOLUTE は動的カーソルでは使用できません。 FAST_FORWARD は最も高速なカーソル タイプで、ある行から別の行に「前方」にのみ移動できます。 これはデフォルトのカーソル タイプです (オプションのキーワードが省略された場合)。 これは、FORWARD_ONLY および READ_ONLY パラメータを使用して宣言されたカーソルと同等です。 READ_ONLY – 「読み取り専用」カーソルを定義します。このようなカーソルを介してデータベースを変更することはできません。 SCROLL_LOCKS は、SQL Server がカーソルに読み込まれる行をロックし、その種類のカーソルを通じて行を更新または削除できることを保証することを意味します。 OPTIMISTIC キーワードを使用して宣言されたカーソルは行ロックを必要とせず、データを変更できます。 データがカーソルに読み取られた後にベース テーブルへの変更が発生した場合、カーソルを介してそのデータを変更しようとするとエラーが発生します。 TYPE_WARNING は、カーソルが要求されたタイプから別のタイプに暗黙的に変換されるとき (たとえば、テーブルに一意のインデックスがない場合の上記の KEYSET から STATIC カーソルへの変換)、クライアントに警告が送信されることを指定します。 Select_statement – カーソルの結果セットを形成する SELECT ステートメント。 FOR UPDATE ステートメントは、更新するカーソル内の列を指定します。 OF 列名 [, . 。 。 n] の場合、リストされた列のみが変更可能になります。 列のリストがない場合、READ_ONLY パラメーターを使用したカーソル宣言の場合を除き、すべての列に対して更新が可能です。 カーソルを開いて埋めるには、次のコマンドを使用します。 OPEN (( カーソル名) I @cursor_variable) 開くとき、カーソルは名前 (cursor_name) または CURSOR 型の変数 (@cursor_variable) によって指定できます。 GLOBAL パラメータは、cursor_name がグローバル カーソルであることを指定します。 カーソルのデータ セット内を移動し、データを変数値として取得するには、FETCH ステートメントを使用します。 フェッチ [ (( カーソル名] I @cursor_variable] カーソルに沿った移動方向を決定するコマンドを表に示します。 10.10. 前述したように、カーソルのタイプによっては、特定のカーソルの一部のコマンドが適用できない場合があります。 カーソルを開いたばかりの場合、FETCH NEXT を最初に実行すると、カーソルの最初のレコードにジャンプすることに注意することが重要です。 表10.10 カーソル データセットの移動 @@FETCH_STATUS グローバル変数を使用すると、FETCH ステートメントの最後の実行結果を確認できます。 O – アクションは正常に完了しました。 CLOSE ステートメントは、開いているカーソルを閉じ、データ セットの保存に使用されていたメモリを解放します。 データを取得して閉じたカーソルを移動することは不可能なので、カーソルを再度開く必要があります。 CLOSE (( カーソル名)|@cursor_variable ) DEALLOCATE ステートメントは、カーソルとその名前または変数の間の関連付けを削除します。 これがカーソルを参照する最後の名前または変数である場合、カーソル自体が削除され、カーソルが使用しているリソースはすべて解放されます。 DEALLOCATE (( カーソル名] | @cursor_variable) カーソルを使用する簡単な例を考えてみましょう。ここでは、2000 年以前に出版された書籍の著者とタイトルがテーブルから選択され、その後データが SELECT ステートメントのループに出力されます。毎回 1 つのレコードに独自のタイトルが付けられます。追加の説明はコード内のコメントで示されます。 /*変数を宣言します*/ DECLARE @auth varchar(50)、@title varchar(50) ここで >= 2000 /*カーソルを開いて「実行」し、別の SELECT ステートメントを使用して作成者とタイトルを表示します*/ FETCH NEXT FROM カーソル INTO @auth、@title SSFETCH_STATUS = 0 の間 FETCH NEXT FROM カーソル INTO @auth、タイトル /*カーソルを閉じて放します*/ DEALLOCATE カーソルl 上で述べたように、カーソル名の代わりに CURSOR 型の変数を使用できます。 以下は、これらの変数を使用した同様のコードです。 DECLARE South varchar(50)、Stitle varchar(50) /*カーソル型の変数を宣言します*/ スカールカーソルの宣言 DECLARE カーソルl CURSOR FAST_FORWARD dbo.Bookl から著者、タイトルを選択 ここで >= 2000 /*カーソル型の変数に値を代入します*/ SET スクロール = カーソルl SSFETCH_STATUS = 0 の間 Scurl INTO South、Stitle から次を取得 私の T-SQL コードでは、常にセットベースの操作を使用します。 この種の操作は SQL Server が処理するように設計されており、シリアル処理よりも高速であるはずだと言われました。 カーソルが存在することは知っていますが、その使用方法がわかりません。 カーソルの例をいくつか挙げていただけますか? カーソルをいつ使用するかについてアドバイスをいただけますか? Microsoft がこれらを 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 カーソルと代替カーソルの詳細については、こちらをご覧ください。 上記の例では、カーソルを介してバックアップが発行されています。カーソルベースのロジックを利用する他のヒントを確認してください。 以下の分析は、カーソルベースのロジックが有益である場合とそうでない場合があるさまざまなシナリオについての洞察として役立つことを目的としています。 適用対象: SQL Server (2008 年以降)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse Transact-SQL サーバー カーソルの属性 (ビュー プロパティや、カーソルが操作する結果セットの構築に使用されるクエリなど) を定義します。 DECLARE CURSOR ステートメントは、ISO 標準構文と Transact-SQL 言語拡張セットを使用する構文の両方をサポートします。 ISO 構文 DECLARE カーソル名 [INSENSITIVE ] [SCROLL ] CURSOR FOR select_statement [FOR ( READ ONLY | UPDATE [ OF 列名 [ ,...n ] ] ) ] [;] Transact-SQL 拡張構文 DECLARE カーソル名 CURSOR [ LOCAL | グローバル ] [ FORWARD_ONLY | スクロール ] [ 静的 | キーセット | ダイナミック | 早送り ] [ 読み取り専用 | スクロールロック | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;] カーソル名 鈍感 スクロール 選択ステートメント 選択ステートメント要求されたタイプのカーソルと競合します。 読み取り専用 アップデート ] カーソル名 地元 グローバル FORWARD_ONLY 静的 キーセット カーソル所有者によって行われた、または他のユーザーによってコミットされたベース テーブルの非キー値への変更は、カーソル所有者が表示すると表示されます。 他のユーザーが行った変更は反映されません (Transact-SQL サーバー カーソルを使用して変更を行うことはできません)。 行が削除された場合、行をフェッチしようとすると @@FETCH_STATUS -2 が返されます。 カーソルの境界を越えてキー値を更新することは、古い行を削除してから新しい行を挿入することに似ています。 新しい値を含む行は表示されず、古い値を含む行をフェッチしようとすると、@@FETCH_STATUS -2 が返されます。 WHERE CURRENT OF 句を使用してカーソルを通じて更新が行われた場合、更新はすぐに表示されます。 動的 早送り 読み取り専用 SCROLL_LOCKS 楽観的 TYPE_WARNING 選択ステートメント SQL Server は、次の句が存在する場合、カーソルを暗黙的に別の型に変換します。 選択ステートメント要求されたタイプのカーソルと競合します。 詳細については、「暗黙的なカーソル変換」を参照してください。 更新用] DECLARE CURSOR ステートメントは、ビュー プロパティや、カーソルが操作する結果セットの構築に使用されるクエリなど、Transact-SQL サーバー カーソルの属性を定義します。 OPEN ステートメントは結果セットにデータを入力し、FETCH ステートメントはそこから行を返します。 CLOSE ステートメントは、カーソルに関連付けられている現在の結果セットをクリアします。 DEALLOCATE ステートメントは、カーソルによって使用されているリソースを解放します。 DECLARE CURSOR ステートメントの最初の形式では、ISO 構文を使用してカーソル パラメーターを指定します。 DECLARE CURSOR ステートメントの 2 番目の形式では、Transact-SQL 言語の拡張機能を使用します。これにより、ODBC や ADO などのデータベース API のカーソル関数で使用されるものと同じ型を使用してカーソルを定義できます。 これら 2 つの形式を混合しないでください。 SCROLL を指定するか、CURSOR キーワードの前のキーワードを省略した場合、CURSOR とキーワードの間にキーワードを使用することはできません。 選択ステートメントキーワード。 CURSOR の間にキーワードを指定する場合と、CURSOR の間にキーワードを指定する場合 選択ステートメントキーワードを使用する場合、CURSOR キーワードの前に SCROLL または INSENSITIVE を指定することはできません。 DECLARE CURSOR ステートメントに Transact-SQL 構文を使用し、READ_ONLY、OPTIMISTIC、または SCROLL_LOCKS オプションを指定しない場合は、次のデフォルト値が想定されます。 SELECT ステートメントが更新をサポートしていない場合 (または権限が不十分である場合、または更新をサポートしていないリモート テーブルにアクセスしている場合など)、カーソルは READ_ONLY に設定されます。 STATIC カーソルと FAST_FORWARD カーソルはデフォルトで READ_ONLY になります。 DYNAMIC カーソルと KEYSET カーソルのデフォルトは OPTIMISTIC です。 カーソルは、他の Transact-SQL ステートメントによってのみ参照できます。 データベース API 関数はカーソルを参照できません。 たとえば、カーソルが宣言されると、OLE DB、ODBC、または ADO の関数およびメソッドはその名前を参照できなくなります。 対応する API 関数およびメソッドを使用してカーソル行を選択することはできません。 この目的には、Transact-SQL FETCH ステートメントを使用する必要があります。 次のストアド プロシージャを使用して、カーソルの宣言後にカーソルのプロパティを定義できます。 変数は部品として使用できます 選択ステートメント、カーソルが宣言されています。 カーソル変数の値は宣言後に変更されません。 デフォルトでは、DECLARE CURSOR 権限は、カーソルによって使用されるビュー、テーブル、列に対する SELECT 権限を持つすべてのユーザーに付与されます。 クラスター化列ストア インデックスを含むテーブルではカーソルやトリガーを使用できません。 この制限は、非クラスター化インデックスには適用されません。 非クラスター化列ストア インデックスを含むテーブルでは、カーソルとトリガーを使用できます。 このカーソルを開いたときに作成される結果セットには、テーブルのすべての行と列が含まれます。 このカーソルは更新でき、すべての更新と削除はこのカーソルの選択内容で表されます。 SCROLL パラメータが指定されていないため、FETCH``NEXT はフェッチのみになります。 DECLARE Vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN Vend_cursor FETCH NEXT FROM Vend_cursor; 次の例では、ネストされたカーソルを使用して複雑なレポートを表示します。 内部カーソルはプロバイダーごとに宣言されます。 ノーカウントをオンに設定します。
DECLARE @vendor_id int 、 @vendor_name nvarchar ( 50 )、 @message varchar ( 80 )、 @product nvarchar ( 50 );プリント」 -------- ベンダー製品レポート --------";
DECLAREvendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN Vendor_cursor FETCH NEXT FROM Vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = 「----- ベンダーからの製品:」+ @vendor_name PRINT @message -- 内部カーソルベースを宣言します。
-- 外側のカーソルからのvendor_id上。 DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv、Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- 外側カーソルからの変数値 OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 プリント "<
カーソルはコンテキスト メモリ領域へのリンクです。 SQL プログラミング言語 (Oracle、Microsoft SQL Server) の一部の実装では、クエリの実行時に取得された結果セットとそれに関連付けられた現在のレコード ポインター。 カーソルは、代替データ ストレージを表す仮想テーブルであると言えます。 この場合、カーソルを使用すると、通常の配列のデータであるかのように、そのデータにアクセスできます。
カーソルはストアド プロシージャで使用されます。 理論は十分なので、例を見てみましょう。
私たちはデータベースを持っています（データベースは少し良くありません。これは私の研究室の作品の1つですが、データベースの先生はこの構造を主張しました） /*銀行情報*/
CREATE TABLE `bank` (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" 、
主キー (`BankId`)
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`)
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin" `bank` を選択してください。* `bank` から必要な_RECORD_NUMBER OF THE_RECORD_WEED,1
。 したがって、LIMIT WE NEED_RECORD NUMBER, 1 を使用して、ループ内のバンク テーブルから各レコードを順番に抽出し、WE NEED_RECORD NUMBER の値を 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;
/* カーソルを開く */
BankCursor を開きます。
/*データを取得*/
完了中 = 0 DO
私たちは必要な行動をとります
途中で終了します。
/*カーソルを閉じる*/
BankCursor を閉じます。
終わり ;
カーソルを開く カーソル名を開きます。
次に、カーソルの終わりに到達するまで (WHILE 完了 = 0 DO)、データを抽出して処理します。
ストアド プロシージャを終了する前にカーソルを閉じる必要があります。 カーソル名を閉じます。 完了中 = 0 DO
BankCursor を vBankId、vBankName、vAddress、vPhone にフェッチします。
Bankdistribution から (ContributeAmount) INTO vContributeAmountSUM を選択します (BankId = vBankId 制限 1)。
私たちはいくつかのアクションを行います
途中で終了します。
構文の観点からは、すべて問題なく、正しいです。 しかし、論理的な観点から言えば、そうではありません。 預金者が一部の銀行に口座を開設していない可能性があるため、Select (ContributeAmount) INTO vContributeAmountSUM FROM 銀行分布の場合、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`;
完了中 = 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 を開きます。
完了中 = 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
私たちはいくつかのアクションを実行します。
終了の場合;
/* while が終了する前に、done 変数の値を復元します */
完了 = old_status を設定します。
途中で終了します。解決
SQL Server カーソルを作成する方法
SQL Server カーソルの例
SQL Server カーソル コンポーネント
推奨書籍
追加の SQL Server カーソルの例
SQL Server カーソル分析
次のステップ
カーソル名
tempdb; したがって、基礎となるテーブルへの変更は、このカーソルの選択によって返されるデータには反映されず、このカーソルは変更できません。 ISO 構文を使用する場合、INSENSITIVE オプションが指定されていない限り、ベース テーブルに対してコミットされた更新と削除は後続の選択に表示されます。
すべてのサンプリング オプション (FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE) が使用可能であることを示します。 ISO DECLARE CURSOR ステートメントで SCROLL オプションが指定されていない場合、NEXT フェッチ オプションのみがサポートされます。 SCROLL オプションは、FAST_FORWARD オプションと一緒に指定できません。
カーソルの結果セットを指定する標準の SELECT ステートメント。 FOR BROWSE キーワードと INTO キーワードは使用できません。 選択ステートメントカーソル宣言。
列名 [,
.. .n] が指定されている場合、リストされている列のみが変更できます。 列のリストを指定せずに UPDATE ステートメントを使用すると、すべての列に対して更新が可能になります。
特定のサーバー カーソルの Transact-SQL 名。 カーソル名識別子の規則に従う必要があります。
カーソルが、カーソルが作成されたパッケージ、ストアド プロシージャ、またはトリガーに対してローカルであることを示します。 カーソル名はこの領域内でのみ有効です。 カーソルは、パッケージのローカル変数、ストアド プロシージャ、トリガー、またはストアド プロシージャの出力パラメータによって参照できます。 OUTPUT パラメーターは、ローカル カーソルを呼び出し元のパッケージ、ストアド プロシージャ、またはトリガーに渡すために使用されます。その後、ストアド プロシージャの完了後にカーソルにアクセスするために、パラメーターをカーソル変数に割り当てることができます。 カーソルが OUTPUT パラメータに渡されない限り、バッチ、ストアド プロシージャ、またはトリガーの実行が完了すると、カーソルは暗黙的に解放されます。 カーソルが OUTPUT パラメータに渡された場合、カーソルを参照しているすべての変数が解放されるか、スコープが終了すると、カーソルは解放されます。
カーソルが接続に対してグローバルであることを示します。 カーソル名は、接続上で実行されるストアド プロシージャまたはパッケージで使用できます。 カーソルは、接続が切断された場合にのみ暗黙的に解放されます。
カーソルを最初の行から最後の行までのみ表示できるように指定します。 FETCH NEXT フェッチ オプションのみがサポートされています。 FORWARD_ONLY が STATIC、KEYSET、または DYNAMIC キーワードなしで指定された場合、カーソルは DYNAMIC カーソルとして動作します。 FORWARD_ONLY 引数も SCROLL 引数も指定されていない場合、STATIC、KEYSET、または DYNAMIC キーワードが存在しない限り、デフォルトは FORWARD_ONLY 引数です。 STATIC、KEYSET、および DYNAMIC カーソルのデフォルト値は SCROLL です。 ODBC や ADO などのデータベース API とは異なり、FORWARD_ONLY モードは、STATIC、KEYSET、DYNAMIC の Transact-SQL カーソルでサポートされています。
カーソルで使用するデータの一時コピーを作成するカーソルを定義します。 カーソルへのすべてのクエリは、指定された一時テーブルにアクセスします。 tempdb; したがって、基礎となるテーブルへの変更は、このカーソルの選択によって返されるデータには反映されず、このカーソルは変更できません。
カーソルを開いたときに、カーソル内の行のメンバーシップまたは順序が変更されないことを示します。 行を一意に識別するキーのセットがテーブルに組み込まれています。 tempdb呼ばれた キー.
このカーソルの表示中に結果セット内の行に対して行われたすべてのデータ変更を表示するカーソルを定義します。 各選択のデータ値、順序、行メンバーシップは異なる場合があります。 ABSOLUTE 選択オプションは動的カーソルではサポートされていません。
パフォーマンスの最適化が有効になっている FORWARD_ONLY、READ_ONLY カーソルを示します。 FAST_FORWARD オプションは、SCROLL オプションまたは FOR_UPDATE オプションと一緒に指定できません。
このカーソルによる変更を禁止します。 WHERE CURRENT OF 句は、UPDATE または DELETE ステートメント内のカーソルを参照できません。 このオプションは、デフォルトのカーソル更新機能よりも優先されます。
カーソルを使用して実行される位置指定更新または削除が成功することが保証されていることを示します。 SQL Server は、カーソルに読み込まれる行をロックして、それらの行が後続の変更に使用できるようにします。 SCROLL_LOCKS オプションは、FAST_FORWARD または STATIC オプションと一緒に指定できません。
行がカーソルに読み込まれてから更新されている場合、カーソルを使用して行われる位置指定更新または削除が失敗することを指定します。 SQL Server は、カーソルに読み込まれる行をロックしません。 代わりに比較が使用されます タイムスタンプテーブルに列値またはチェックサムがない場合は、 タイムスタンプ列を参照して、行がカーソルに読み込まれてから変更されたかどうかを判断します。 行が変更されている場合、位置指定された更新または削除の試みは失敗します。 OPTIMISTIC オプションは、FAST_FORWARD オプションと一緒に指定できません。
カーソルが要求された型から別の型に暗黙的に変換された場合に、クライアントに警告が送信されるように指定します。
カーソルの結果セットを指定する標準の SELECT ステートメント。 キーワード COMPUTE、COMPUTE BY、FOR BROWSE、および INTO は使用できません。 選択ステートメントカーソル宣言。
更新するカーソル内の列を定義します。 OFの場合 列名 [,
... n] が指定されている場合、リストされている列のみが変更できます。 UPDATE ステートメントが列リストなしで使用される場合、READ_ONLY 同時実行オプションが指定されていない限り、すべての列に対して更新が可能です。A. 単純なカーソルと構文の使用
B. ネストされたカーソルを使用したレポートの表示
