SQL Server の基本: カーソル
- 高洛峰オリジナル
- 2016-12-14 11:45:031258ブラウズ
クエリ ステートメントが複数のレコードを返す場合、データ量が非常に大きい場合は、カーソルを使用してクエリ結果セット内のレコードを 1 つずつ読み取る必要があります。アプリケーションは、必要に応じてデータをスクロールまたは参照できます。この記事では、カーソルの概念や分類、基本的な操作について紹介します。
1: カーソルについて
カーソルは、ユーザーが個々のデータ行にアクセスできるようにする SQL Server のデータ アクセス メカニズムです。ユーザーは各行を個別に処理できるため、システムのオーバーヘッドや潜在的なブロック状況が軽減され、これらのデータによって生成された SQL コードをすぐに実行または出力することもできます。
1. カーソルの概念
カーソルはデータを処理する方法であり、主にストアド プロシージャ、トリガー、および T_SQL スクリプトで使用され、結果セットの内容を他の T_SQL ステートメントで使用できるようにします。結果セットの表示または処理中に、データを前後に移動する機能。 C 言語のポインタと同様に、結果セット内の任意の位置を指すことができ、結果セットを 1 つずつ個別に処理する場合は、結果セットを指すカーソル変数を宣言する必要があります。
SQL Server のデータ操作の結果はすべてセット指向であり、クエリ結果を制限するために WHERE 句を使用しない限り、テーブル内の単一のレコードを記述する式形式はありません。カーソルを使用するとこの機能が提供されます。 、カーソルの使用により、操作プロセスがより柔軟かつ効率的になります。
2. カーソルの利点
SELECT ステートメントは結果セットを返しますが、アプリケーションが常に結果セット全体を効果的に処理できるとは限りません。これには、複数のレコードから一度に 1 つのレコードをフェッチする場合が含まれます。結果セットの場合、カーソルは常にワンホップ SQL 選択ステートメントに関連付けられ、結果セットと特定のレコードを指すカーソル位置で構成されます。カーソルを使用すると、次のような利点があります:
(1). プログラムは、セット全体に対して同じ操作を実行するのではなく、SELECT クエリ ステートメントによって返される行セットに対して毎回同じ操作または異なる操作を実行できます。
(2)。カーソル位置に基づいて行を削除および更新する機能を提供します。
(3). カーソルは、データベース管理システムとアプリケーション設計の間の橋渡しとして機能し、2 つの処理方法を接続します。
3. カーソルの分類
SQL Server は 3 つのカーソル実装をサポートしています:
(1)。Transact_SQL カーソル
は DECLARE CURSOR 構文に基づいており、主に T_SQL スクリプト、ストアド プロシージャ、トリガーに使用されます。 T_SQL カーソルはサーバー上に実装され、クライアントからサーバーに送信される T_SQL ステートメントによって管理されます。バッチ、ストアド プロシージャ、またはトリガーに含めることもできます。
(2). アプリケーション プログラミング インターフェイス (API) サーバー カーソル
は、OLE DB および ODBC の API カーソル関数をサポートしており、API サーバー カーソルはサーバーに実装されています。クライアント アプリケーションが API カーソル関数を呼び出すたびに、SQL Server Native Client OLE DB プロバイダーまたは ODBC ドライバーは、API サーバー カーソルを操作するための要求をサーバーに送信します。
(3). クライアント側カーソル
は、SQL Server Native Client ODBC ドライバーと ADO API を実装する DLL によって内部的に実装されます。クライアント側カーソルは、クライアント側ですべての結果セット行をキャッシュすることによって実装されます。クライアント アプリケーションが API カーソル関数を呼び出すたびに、SQL Server Native Client ODBC ドライバーまたは ADO DLL は、クライアント上のキャッシュされた結果セット内の行に対してカーソル操作を実行します。
T_SQL カーソルとサーバー カーソルは両方ともサーバー上に実装されるため、これらを総称してサーバー カーソルと呼びます。
ODBC と ADO は Microsoft SQL Server でサポートされる 4 つのカーソル タイプを定義しているため、T_SQL カーソルには 4 つのカーソル タイプを指定できます。
SQL Server でサポートされる API サーバー カーソルの 4 種類は次のとおりです:
(i). 前方専用カーソル
前方専用カーソルはスクロールをサポートせず、カーソルの最初から最後までの順次抽出のみをサポートします。行はデータベースから抽出された後にのみ取得できます。現在のユーザーによって発行された、または他のユーザーによって送信された、結果セット内の行に影響を与えるすべての INSERT、UPDATE、および DELETE ステートメントについては、それらの行がカーソルから抽出されるときにその効果が表示されます。
カーソルは後方にスクロールできないため、行がフェッチされた後にデータベース内の行に加えられた変更のほとんどは、カーソルを通しては表示されません。変更された結果セット内の行の位置を決定するために値が使用される場合 (クラスター化インデックスでカバーされる列の更新など)、変更された値はカーソルを通して表示されます。
(ii). 静的カーソル
SQL Server の静的カーソルは常に読み取り専用です。カーソルが開かれると、完全な結果セットが tempdb に構築されます。静的カーソルでは、常にカーソルを開いたときの結果セットがそのまま表示されます。
カーソルには、結果セットのメンバーシップに影響を与えるデータベースでの変更は反映されません。また、静的カーソルを開いた後、結果セットに結合された行の列値に加えられた変更もデータベースに表示されません。カーソルの SELECT ステートメントの検索条件に一致する場合でも、行が挿入されます。結果セットを構成する行が他のユーザーによって更新された場合、新しいデータ値は静的カーソルに表示されません。静的カーソルには、カーソルを開いた後にデータから削除された行が表示されます。 UPDATE、INSERT、または DELETE 操作は (カーソルが閉じて再度開かれない限り) 静的カーソルには反映されません。また、カーソルを開いたときと同じ接続を使用して行われた変更も反映されません。
(iii).キー駆動カーソル
このカーソル内の行のメンバーシップと順序は固定されています。キーセット駆動カーソルは、キーセットと呼ばれる一意の識別子 (キー) のセットによって制御されます。キーは、結果セットの各行を一意に識別する列のセットから生成されます。キー セットは、カーソルを開いたときに SELECT ステートメントの要件を満たすすべての行からのキー値のセットです。キーセット駆動カーソルに対応するキーセットは、カーソルが開かれるときに tempdb に生成されます。
(IV).動的カーソル
動的カーソルは静的カーソルの反対です。動的カーソルは、カーソルがスクロールされるときに結果セットに加えられたすべての変更を反映します。結果セット内の行データの値、順序、メンバーはフェッチのたびに変化します。すべてのユーザーによって作成されたすべての UPDATE、INSERT、および DELETE ステートメントは、カーソルを通して表示されます。 SQLSePos や T_SQL WHERE CURRENT OF 句などの API 関数を使用してカーソル経由で更新すると、すぐに表示されます。カーソルの外部で行われた更新は、カーソルのトランザクション分離レベルが非コミットを読み取るように設定されていない限り、コミットされるまで表示されません。
2: カーソルの基本操作
1. カーソルを宣言する
カーソルには主に、カーソル結果セットとカーソル位置の 2 つの部分が含まれます。カーソル結果セットは、カーソル結果セットを定義する SELECT ステートメントによって返される行のセットです。カーソル、およびカーソル位置はこの結果セットを指します。行へのポインター。
カーソルを使用する前に、SQL Server で DECLARE CURSOR ステートメントを使用します。カーソルの宣言には、カーソルのスクロール動作と、カーソルによって操作される結果セットのユーザー生成クエリの定義が含まれます。構文形式は次のとおりです。
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [,...n] ] ]
cursor_name: Yes 定義された T_SQL サーバー カーソルの名前。
LOCAL: このカーソルのスコープは、その中に作成されたバッチ、ストアド プロシージャ、またはトリガーに対してローカルです。
GLOBAL: カーソルのスコープがグローバルであることを指定します。
FORWARD_ONLY: カーソルが最初の行から最後の行までのみスクロールできることを指定します。 FORWARD_ONLY が指定されているときに STATIC、KEYSET、および DYNAMIC キーワードが指定されていない場合、カーソルは DYNAMIC カーソルとして動作します。 FORWARD_ONLY と SCROLL の両方が指定されている場合を除き、カーソルは DYNAMIC カーソルとして動作します。 STATIC、KEYSET、および DYNAMIC キーワードが指定されている場合、それ以外の場合はデフォルトの FORWARD_ONLY になります。 STATIC、KEYSET、および DYNAMIC カーソルのデフォルトは SCROLL です。 ODBC や ADO などのデータベース API とは異なり、STATIC、KEYSET、DYNAMIC T_SQL カーソルは FORWARD_ONLY をサポートします。
STATIC: カーソルで使用されるデータの一時コピーを作成するカーソルを定義します。したがって、カーソルに対するフェッチ操作を実行するときに、カーソルへのすべてのリクエストが応答されません。ベース テーブルは返されたデータに反映されず、カーソルは変更を許可しません。
KEYSET: カーソルを開いたときに、カーソルの下にある行のメンバーシップと順序が固定されることを指定します。行を一意に識別するキーは、keyset と呼ばれる tempdb 内のテーブルに組み込まれます。
DYNAMIC: カーソルがスクロールされるときに、結果セット内の行に加えられたすべてのデータ変更を反映するカーソルを定義します。データ値、順序、行のメンバーシップはフェッチごとに変更され、ABSOLUTE フェッチ オプションは動的カーソルではサポートされません。
FAST_FORWARD: パフォーマンスの最適化を有効にした FORWARD_ONLY カーソルと READ_ONLY カーソルを指定します。 SCROLLまたはFOR_UPDATEを指定した場合、FAST_FORWARDは指定できません。
SCROLL_LOCKS: カーソルによる位置の更新または削除が成功する必要があることを指定します。 SQL Server は、カーソルに読み込まれる行をロックして、後で変更できるようにします。FAST_FORWARD または STATIC も指定されている場合は、SCROLL_LOCKS を指定できません。
OPTIMISTIC: カーソルが読み込まれてから行が更新されている場合、カーソルを介した位置指定更新または位置指定削除が成功しないことを指定します。行がカーソルに読み込まれるとき、SQL Server は行をロックせず、テーブルにタイムスタンプ列が含まれていない場合、その行がカーソルに読み込まれてから変更されたかどうかを判断します。の場合、代わりにチェックサム値を使用して判断します。行が変更されると、位置指定更新または削除の試行は失敗します。また、FAST_FORWARD も指定されている場合は OPTIMISTIC を指定できません。
TYPE_WARNING: カーソルが要求されたタイプから別のタイプに暗黙的に変換されるときに、警告メッセージがクライアントに送信されるように指定します。
select_statement: カーソルの結果セットを定義する標準の SELECT ステートメントです。
【例】cursor_fruitという名前のカーソルを宣言します
USE sample_db; GO DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;
2. カーソルをオープンします
カーソルをオープンするための構文は次のとおりです:
OPEN [ GLOBAL ] cursor_name | cursor_variable_name;
GLOBAL:cursor_nameを指定します。グローバルカーソルとして。
cursor_name: 宣言されたカーソルの名前。グローバル カーソルとローカル カーソルの両方が名前としてcursor_nameを使用する場合、GLOBALが指定されている場合はcursor_nameがグローバル カーソルを指し、それ以外の場合はcursor_nameがローカル カーソルを指します。
cursor_variable_name: カーソル変数の名前。
【例】上記の例で宣言したcursor_fruitという名前のカーソルを開きます
USE sample_db; GO OPEN cursor_fruit;
3. カーソル内のデータを読み込みます
カーソルを開いたら、FETCHコマンドでカーソル内のデータを読み込むことができます。カーソル内のデータ行。 FETCH の構文は次のとおりです:
ETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]NEXT:紧跟当前行返回结果行，并且当前行递增为返回行，如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。
PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行，如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
FIRST：返回游标中的第一行并将其作为当前行。
LAST:返回游标中的最后一行并将其作为当前行。
ABSOLUTE { n | @nvar }：如果n或@nvar为正，则返回从游标头开始向后n行的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾开始向前的n行的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.
RELATIVE { n | @nvar }：如果n或@nvar为正，则返回从当前行开始向后的第n行。如果n或@nvar为负，则返回从当前行开始向前的第n行。如果n或@nvar为0，则返回当前行,对游标第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE,则不返回行，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.
GLOBAL：指定cursor_name是全局游标。
cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。
@cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。
INTO @variable_name [ ,…n ]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所支持的隐士转换。变量的数目必须与游标选择列表中的列数一致。
【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下：
USE sample_db; GO FETCH NEXT FROM cursor_fruit WHILE @@FETCH_STATUS=0 BEGIN FETCH NEXT FROM cursor_fruit END;
4.关闭游标
SQL Server 在打开游标之后，服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以在不使用游标的时候，可以将其关闭，以释放游标所占用的服务器资源，关闭游标使用CLOSE语句。语法格式如下：
CLOSE [ GLOBAL ] cursor_name | cursor_variable_name
【例】关闭名称为cursor_fruit的游标
CLOSE cursor_fruit;
5.释放游标
游标操作的结果集空间虽然被释放了，但是游标本身也会占用一定的计算集资源，所以使用完游标之后，为了收回被游标占用的资源，应该将游标释放。释放游标使用DEALLOCATE语句，语法格式如下：
DEALLOCATE [GLOBAL] cursor_name | @ccursor_variable_name
@ccursor_variable_name：游标变量的名称，@ccursor_variable_name必须为cursor类型。
DEALLOCATE @ccursor_variable_name 语句只删除对游标变量名称的引用，直到批处理、存储过程或触发器结束时变量离开作用域，才释放变量。
【例】使用DEALLOCATE语句释放名称为cursor_fruit的变量，输入如下：
DEALLOCATE cursor_fruit;
三:游标的运用
1.使用游标变量
声明变量用DECLARE，为变量赋值可以用set或SELECT语句，对于游标变量的声明和赋值，其操作基本相同。在具体使用时，首先要创建一个游标，将其打开后，将游标的值赋给游标变量，并通过FETCH语句从游标变量中读取值，最后关闭释放游标。
【例】声明名称为@varCursor的游标变量，输入如下：
DECLARE @varCursor Cursor --声明游标变量 DECLARE cursor_fruit CURSOR FOR --创建游标 SELECT f_name,f_price FROM fruits; OPEN cursor_fruit --打开游标 SET @varCursor=cursor_fruit --为游标变量赋值 FETCH NEXT FROM @varCursor --从游标变量中读取值 WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功 BEGIN FETCH NEXT FROM @varCursor --读取游标变量中的数据 END CLOSE @varCursor --关闭游标 DEALLOCATE @varCursor; --释放游标
2.用游标为变量赋值
在游标的操作过程中，可以使用FETCH语句将数据值存入变量，这些保持表中列值的变量可以在后面的程序中使用。
【例】创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。
3.用ORDER BY 子句改变游标中的执行顺序
游标是一个查询结果集，那么能不能对结果进行排序呢？答案是否定的。与基本的SELECT语句中的排序方法相同，ORDER BY子句添加到查询中可以对游标查询的结果排序。
注意：只有出现在游标中的SELECT语句中的列才能作为ORDER BY 子句的排序列，而对与非游标的SELECT语句中，表中任何列都可以作为ORDER BY 的排序列，即使该列没有出现在SELECT语句的查询结果列中。
【例】声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下：
4.用游标修改数据
【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，使用FETCH NEXT方法来获取游标中的每一行的数据，如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：
5.用游标删除数据
使用游标删除数据时，既可以删除游标结果集中的数据，也可以删除基本表中的数据
【例】使用游标删除fruits表中s_id=102的记录，如下
以上例子的sql脚本：
USE sample_db; create TABLE fruits( f_id int IDENTITY(1,1) PRIMARY KEY,--水果id s_id int not null, --供应商id f_name varchar(255) not null,--水果名称 f_price decimal(8,2) not null --水果价格 ); insert into fruits (s_id,f_name,f_price) values (101,'apple',5.8), (102,'blackberry',6.8), (105,'orange',4.5), (102,'banana',3.5), (103,'lemon',8.0), (104,'grape',7.6), (101,'melon',10.5); --1.声明名称为cursor_fruit的游标 USE sample_db; GO DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits; --2.打开游标 OPEN cursor_fruit; --3.读取游标中的数据 --【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下： USE sample_db; GO FETCH NEXT FROM cursor_fruit WHILE @@FETCH_STATUS=0 BEGIN FETCH NEXT FROM cursor_fruit END; --4.关闭关闭名称为cursor_fruit的游标 CLOSE cursor_fruit --5.释放游标 DEALLOCATE cursor_fruit; --游标的运用 --1.使用游标变量 --声明名称为@varCursor的游标变量 DECLARE @varCursor Cursor --声明游标变量 DECLARE cursor_fruit CURSOR FOR --创建游标 SELECT f_name,f_price FROM fruits; OPEN cursor_fruit --打开游标 SET @varCursor=cursor_fruit --为游标变量赋值 FETCH NEXT FROM @varCursor --从游标变量中读取值 WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行成功 BEGIN FETCH NEXT FROM @varCursor --读取游标变量中的数据 END CLOSE @varCursor --关闭游标 DEALLOCATE @varCursor; --释放游标 --2.用游标为变量赋值 --创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。 DECLARE @fruitName varchar(50),@fruitPrice DECIMAL(8,2) DECLARE cursor_variable CURSOR FOR SELECT f_name,f_price FROM fruits WHERE s_id=101; OPEN cursor_variable FETCH NEXT FROM cursor_variable INTO @fruitName,@fruitPrice PRINT '编号为101的供应商提供的水果种类和价格为：' WHILE @@FETCH_STATUS=0 BEGIN PRINT @fruitName+' '+STR(@fruitPrice,8,2) FETCH NEXT FROM cursor_variable INTO @fruitName,@fruitPrice END CLOSE cursor_variable DEALLOCATE cursor_variable; --3.用ORDER BY子句改变游标中的执行顺序 --声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下： DECLARE cursor_order CURSOR FOR SELECT f_id,f_name,f_price FROM fruits ORDER BY f_price DESC OPEN cursor_order FETCH NEXT FROM cursor_order WHILE @@FETCH_STATUS=0 FETCH NEXT FROM cursor_order CLOSE cursor_order DEALLOCATE cursor_order; --4.用游标修改数据 --【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标， --使用FETCH NEXT方法来获取游标中的每一行的数据， --如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下： DECLARE @sid INT,@id INT =101 DECLARE cursor_fruit CURSOR FOR SELECT s_id FROM fruits; OPEN cursor_fruit FETCH NEXT FROM cursor_fruit INTO @sid WHILE @@FETCH_STATUS=0 BEGIN IF @sid=@id BEGIN UPDATE fruits SET f_price=11.1 WHERE s_id=@id END FETCH NEXT FROM cursor_fruit INTO @sid END CLOSE cursor_fruit DEALLOCATE cursor_fruit; SELECT * FROM fruits where s_id=101; --5.使用游标删除数据 --【例】使用游标删除fruits表中s_id=102的记录，如下 DECLARE @sid1 INT,@id1 int=102 DECLARE cursor_delete CURSOR FOR SELECT s_id FROM fruits; OPEN cursor_delete FETCH NEXT FROM cursor_delete INTO @sid1 WHILE @@FETCH_STATUS=0 BEGIN IF @sid1=@id1 BEGIN DELETE FROM fruits where s_id=@id1 END FETCH NEXT FROM cursor_delete INTO @sid1 END CLOSE cursor_delete DEALLOCATE cursor_delete; SELECT * FROM fruits where s_id=102;