wordpress 电商网站,口碑好的邯郸网站建设,网站备案 密码找回,查网站跳出率一#xff1a;认识游标 游标是SQL Server的一种数据访问机制#xff0c;它允许用户访问单独的数据行。用户可以对每一行进行单独的处理#xff0c;从而降低系统开销和潜在的阻隔情况#xff0c;用户也可以使用这些数据生成的SQL代码并立即执行或输出。
1.游标的概念 游标是…
一认识游标 游标是SQL Server的一种数据访问机制它允许用户访问单独的数据行。用户可以对每一行进行单独的处理从而降低系统开销和潜在的阻隔情况用户也可以使用这些数据生成的SQL代码并立即执行或输出。
1.游标的概念 游标是一种处理数据的方法主要用于存储过程触发器和 T_SQL脚本中它们使结果集的内容可用于其它T_SQL语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语言中的指针它可以指向结果集中的任意位置当要对结果集进行逐条单独处理时必须声明一个指向该结果集中的游标变量。 SQL Server 中的数据操作结果都是面向集合的并没有一种描述表中单一记录的表达形式除非使用WHERE子句限定查询结果使用游标可以提供这种功能并且游标的使用和操作过程更加灵活、高效。
2.游标的优点 SELECT 语句返回的是一个结果集但有时候应用程序并不总是能对整个结果集进行有效地处理游标便提供了这样一种机制它能从包括多条记录的结果集中每次提取一条记录游标总是与一跳SQL选择语句相关联由结果集和指向特定记录的游标位置组成。使用游标具有一下优点
(1).允许程序对由SELECT查询语句返回的行集中的每一次执行相同或不同的操作而不是对整个集合执行同一个操作。
(2).提供对基于游标位置中的行进行删除和更新的能力。
(3).游标作为数据库管理系统和应用程序设计之间的桥梁将两种处理方式连接起来。
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支持的4种API服务器游标的类型是
(i).只进游标 只进游标不支持滚动它只支持游标从头到尾顺序提取。行只在从数据库中提取出来后才能检索。对所有又当前用户发出或又其它用户提交、并影响结果集中的行的INSERTUPDATE和DELETE语句其效果在这些行从游标中提取是可见的。 由于游标无法向后滚动则在提取行后对数据库中的行进行的大多数更改通过游标均不可见。当值用于确定所修改的结果集(例如更新聚集索引涵盖的列)中行的位置时修改后的值通过游标可见。
(ii).静态游标 SQL Server静态游标始终是只读的。其完整结果集在打开游标时建立在tempdb中静态游标总是按照打开游标时的原样显示结果集。 游标不反映在数据库中所做的任何影响结果集成员身份的更改也不反映对组合成结果集的行的列值所做的更改静态游标不会显示打开游标以后在数据库中新插入的行即使这些行符合游标SELECT语句的搜索条件。如果组成结果集的行被其它用户更新则新的数据值不会显示在静态游标中。静态游标会显示打开游标以后从数据中删除的行。静态游标中不反UPDATE、INSERT或者DELETE操作除非关闭游标然后重新打开甚至不反映使用打开游标的同一连接所做的修改。
(iii).由键驱动的游标 该游标中各行的成员身份和顺序是固定的。由键集驱动的游标由一组唯一标识符(键)控制这组键成为键集。键是根据以唯一方式标识结果集各行的一组列生成的键集是打开游标时来自符合SELECT语句要求的所有行中的一组键值。由键集驱动的游标对应的键集是打开游标时在tempdb中生成的。
(IV).动态游标 动态游标与静态游标相对。当滚动游标时动态游标反映结果集中所做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。如果使用API函数如SQLSePos或T_SQL WHERE CURRENT OF 子句通过游标进行更新它们将立即可见。在游标外部所做的更新直到提交时才可见除非将游标的事物隔离级别设为未提交读。 二游标的基本操作
1.声明游标 游标主要包括游标结果集和游标位置两部分游标结果集是定义游标的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:是所定义的T_SQL 服务器游标的名称。
LOCAL对于在其中创建批处理、存储过程或触发器来说该游标的作用域是局部的。
GLOBAL指定该游标的作用域是全局的
FORWARD_ONLY:指定游标只能从第一行滚动到最后一行。FETCH NEXT是唯一支持的提取选项如果在指定FORWARD_ONLY时不指定STATICKEYSET和DYNAMIC关键字则游标作为DYNAMIC游标进行操作如果FORWARD_ONLY和SCROLL均为指定则除非指定STATICKEYSET和DYNAMIC关键字否则默认为FORWARD_ONLY。STATICKEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同STATICKEYSET和DYNAMIC T_SQL游标支持FORWARD_ONLY。
STATIC定义一个游标以创建将又该游标使用的数据临时复本对游标的所有请求都从tempdb中的这以临时表中不得到应答因此在对该游标进行提取操作时返回的数据中不反映对基表所做的修改并且该游标不允许修改。
KEYSET:指定当游标打开时游标重的行的成员身份和顺序已经固定。对行进行唯一标识的键值内置在tempdb内一个称为keyset的表中。
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不锁定行它改用timestamp列值比较结果来确定行读入游标后是否发生了修改如果表不包含timestamp列它改用校验和值进行确定如果以修改该行则尝试进行的定位更新或删除将失败如果还指定了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表中的记录输入如下
ETCH
[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | nvar }| RELATIVE { n | nvar }]
FROM
]
{ { [GLOBAL ] cursor_name } | cursor_variable_name}
[ INTO variable_name [ ,...n ] ] 4.关闭游标 SQL Server 在打开游标之后服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集同时游标的使用也会根据具体情况对某些数据进行封锁。所以在不使用游标的时候可以将其关闭以释放游标所占用的服务器资源关闭游标使用CLOSE语句。语法格式如下
CLOSE [ GLOBAL ] cursor_name | cursor_variable_name
【例】关闭名称为cursor_fruit的游标 1 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 varCursorcursor_fruit --为游标变量赋值FETCH NEXT FROM varCursor --从游标变量中读取值WHILE FETCH_STATUS0 --判断FETCH语句是否执行成功BEGINFETCH NEXT FROM varCursor --读取游标变量中的数据ENDCLOSE 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.用游标修改数据
【例】声明整型变量sid101,然后声明一个对fruits表进行操作的游标打开该游标使用FETCH NEXT方法来获取游标中的每一行的数据如果获取到的记录的s_id的字段值与sid值相同将s_idsid的记录中的f_price修改为12.2最后关闭释放游标输入如下 5.用游标删除数据 使用游标删除数据时既可以删除游标结果集中的数据也可以删除基本表中的数据
【例】使用游标删除fruits表中s_id102的记录如下 以上例子的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_STATUS0
BEGINFETCH 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 varCursorcursor_fruit --为游标变量赋值
FETCH NEXT FROM varCursor --从游标变量中读取值
WHILE FETCH_STATUS0 --判断FETCH语句是否执行成功
BEGINFETCH 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_id101;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO fruitName,fruitPrice
PRINT 编号为101的供应商提供的水果种类和价格为
WHILE FETCH_STATUS0
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_STATUS0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;
--4.用游标修改数据
--【例】声明整型变量sid101,然后声明一个对fruits表进行操作的游标打开该游标
--使用FETCH NEXT方法来获取游标中的每一行的数据
--如果获取到的记录的s_id的字段值与sid值相同将s_idsid的记录中的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_STATUS0
BEGINIF sidid
BEGIN UPDATE fruits SET f_price11.1 WHERE s_idid
END
FETCH NEXT FROM cursor_fruit INTO sid
END
CLOSE cursor_fruit
DEALLOCATE cursor_fruit;
SELECT * FROM fruits where s_id101;
--5.使用游标删除数据
--【例】使用游标删除fruits表中s_id102的记录如下
DECLARE sid1 INT,id1 int102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO sid1
WHILE FETCH_STATUS0
BEGINIF sid1id1
BEGIN DELETE FROM fruits where s_idid1
END
FETCH NEXT FROM cursor_delete INTO sid1
END
CLOSE cursor_delete
DEALLOCATE cursor_delete;
SELECT * FROM fruits where s_id102;
