SqlServer游标的建立与使用

时间  2021-04-09 标签 html 数据库 缓存 服务器 微信 函数 post 学习 fetch spa

前言

你们都对SqlServer视图、存储过程、触发器的建立与使用有必定的了解了，咱们来看下什么是游标，怎么使用，何时用。html

SqlServer视图的建立与使用数据库

SqlServer存储过程的建立与使用缓存

SqlServer触发器的建立与使用服务器

 

什么是游标

一、游标的概念

 

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力微信

它使用户可逐行访问由SQL Server返回的结果集。使用游标(cursor)的一个主要的缘由就是把集合操做转换成单个记录处理方式。函数

用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果每每是一个含有多个记录的集合。post

游标机制容许用户在SQL server内逐行地访问这些记录，按照用户本身的意愿来显示和处理这些记录。学习

咱们能够把游标看成一个指针，它能够指定结果中的任何位置，而后容许用户对指定位置的数据进行处理。fetch

二、游标的组成

游标包含两个部分：一个是游标结果集、一个是游标位置。spa

游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。

三、游标的分类

游标共有3类：API服务器游标、Transaction-SQL游标和API客户端游标。

其中前两种游标都是运行在服务器上的，因此又叫作服务器游标。

API服务器游标

API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法能够实现以下功能：

（１）打开一个链接。

（２）设置定义游标特征的特性或属性，API自动将游标映射到每一个结果集。

（３）执行一个或多个Transaction-SQL语句。

（４）使用API函数或方法提取结果集中的行。

API服务器游标包含如下四种：静态游标、动态游标、只进游标、键集驱动游标（Primary key）

静态游标的完整结果集将打开游标时创建的结果集存储在临时表中，（静态游标始终是只读的）。静态游标具备如下特色：老是按照打开游标时的原样显示结果集；不反映数据库中做的任何修改，也不反映对结果集行的列值所做的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其余用户更新，新的数据值不会显示在静态游标中；可是静态游标会显示打开游标之后从数据库中删除的行。

动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的全部更改。结果集中的行数据值、顺序和成员每次提取时都会改变。

只进游标不支持滚动，它只支持游标从头至尾顺序提取数据行。注意：只进游标也反映对结果集所作的全部更改。

键集驱动游标同时具备静态游标和动态游标的特色。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工做表中，对非键集列的数据值的更改在用户游标滚动的时候能够看见，在游标打开之后对数据库中插入的行是不可见的，除非关闭从新打开游标。

 

Transaction-SQL游标

该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。

在存储过程或触发器中使用Transaction-SQL游标的过程为：

（１）声明Transaction-SQL变量包含游标返回的数据。为每一个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换获得的数据类型。

（２）使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还能够利用Declare Cursor定义游标的只读、只进等特性。　

（３）使用Open语句执行Select语句填充游标。

（４）使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其余Transaction-SQL语句能够引用那些变量来访问提取的数据值。Transaction-SQL游标不支持提取行块。

（５）使用Close语句结束游标的使用。注意：关闭游标之后，该游标仍是存在，可使用Open命令打开继续使用，只有调用Deallocate语句才会彻底释放。

 

客户端游标

该游标将使用默认结果集把整个结果集高速缓存在客户端上，全部的游标操做都在客户端的高速缓存中进行。

注意：客户端游标只支持只进和静态游标。不支持其余游标。

 

怎么使用游标

游标的生命周期包含有五个阶段，也是它使用的顺序：

  1. 声明游标（建立）；
  2. 打开游标；
  3. 读取游标数据；
  4. 关闭游标；
  5. 释放游标。

 

一、声明游标

声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。

在SQL Server中建立游标对象使用关键之declare（变量声明）以及curosr（游标）。

声明游标的参数

（１）Local与Global:Local表示游标的做用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕之后游标自动释放。Global表示的是该游标做用域是整个会话层。由链接执行的任何存储过程、批处理等均可以引用该游标名称，仅在断开链接时隐性释放。

（２）Forward_only与Scroll:前者表示为只进游标，后者表示为能够随意定位。默认为前者。

（３）Static、Keyset与Dynamic: 第一个表示定义一个游标，其数据存放到一个临时表内，对游标的全部请求都从临时表中应答，所以，对该游标进行提取操做时返回的数据不反映对基表所做的修改，而且该游标不容许修改。Keyset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内全部数据的更改。

（４）Read_only 、Scroll_Locks与Optimistic：第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中而后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取之后，若是这些数据被更新了，则经过游标定位进行的更新与删除操做将不会成功。

标准游标：

declare
MyCursor cursor
for select Name,ClassId from a_Students

只读游标

declare
MyCursor cursor
for SELECT s.Name,sc.ClassName FROM a_Students s
INNER JOIN a_StudentClass sc ON s.ClassId=sc.ClassId
For Read Only

可更新游标

declare
MyCursor cursor
for SELECT s.Name,sc.ClassName FROM a_Students s
INNER JOIN a_StudentClass sc ON s.ClassId=sc.ClassId
For UpDate

--声明（建立）游标对象（标准游标）
declare 
MyCursor cursor 
for SELECT s.Name,sc.ClassName FROM a_Students s
INNER JOIN a_StudentClass sc ON s.ClassId=sc.ClassId;

 

二、打开游标对象

打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程当中就是按照Select语句进行填充数据，打开游标之后游标位置在第一行。

打开游标

全局游标：Open Global MyCursor 局部游标: Open MyCursor

游标对象建立以后，必须打开以后才能进行使用，使用关键字open。

--打开游标
open MyCursor;

三、读取游标数据

在打开游标之后，使用Fetch语句从Transaction-SQL服务器游标中检索特定的一行。使用Fetch操做，可使游标移动到下一个记录，并将游标返回的每一个列得数据分别赋值给声明的本地变量。

Fetch [Next | Prior | First | Last | Absolute　n　| Relative　n　] From MyCursor

Into @GoodsID,@GoodsName

其中:Next表示返回结果集中当前行的下一行记录，若是第一次读取则返回第一行。默认的读取选项为Next

Prior表示返回结果集中当前行的前一行记录，若是第一次读取则没有行返回，而且把游标置于第一行以前。

First表示返回结果集中的第一行，而且将其做为当前行。

Last表示返回结果集中的最后一行，而且将其做为当前行。

Absolute　n　若是n为正数，则返回从游标头开始的第n行，而且返回行变成新的当前行。若是n为负，则返回从游标末尾开始的第n行，而且返回行为新的当前行，若是n为0，则返回当前行。

Relative　n　若是n为正数，则返回从当前行开始的第n行，若是n为负,则返回从当前行以前的第n行，若是为0，则返回当前行。

 

--读取游标数据
--声明两个变量接收从游标中取出的值
declare @Name varchar(50),@ClassName varchar(50);    
    --移动游标取值
    fetch next from MyCursor into @Name,@ClassName;
    print(@Name);
    print(@ClassName);

四、关闭游标

游标使用完毕以后须要关闭，以及资源的释放，关键字close，deallocate。

--关闭游标
CLOSE MyCursor

 

五、释放游标

游标使用完毕以后须要关闭，以及资源的释放，关键字close，deallocate。

--释放游标
DEALLOCATE MyCursor

 

六、修改游标数据

--修改当前游标数据
--a_Students表名，Name字段名，MyCursor游标名
UpDate a_Students Set Name = '孙悟空111' Where CURRENT Of MyCursor;

七、删除游标数据

--删除当前游标数据
--a_Students表名
Delete From a_Students Where Current Of MyCursor

 

完整实例

使用游标查询数据，只获取取字段姓名(Name)，和字段班级(ClassName)

--声明（建立）游标对象（标准游标）
declare 
MyCursor cursor 
for SELECT  s.Name,sc.ClassName FROM a_Students s
INNER JOIN a_StudentClass sc ON s.ClassId=sc.ClassId;

--声明两个变量接收从游标中取出的值
declare @Name varchar(50),@ClassName varchar(50);    
begin
    --打开游标
    open MyCursor;

    --移动游标取值
    fetch next from MyCursor into @Name,@ClassName;
    --这里对游标的状态进行判断，若是为0，证实游标中有值
    while @@FETCH_STATUS = 0
        BEGIN
            print(@Name);
            print(@ClassName);
            --让游标继续日后移动
            fetch next from MyCursor into @Name,@ClassName
        end

--关闭游标
CLOSE MyCursor

--释放游标
DEALLOCATE MyCursor

end

 

 

何时使用游标

当你要处理的结果集比较庞大，而你要对某一行或几行进行操做的时候，要考虑使用游标。

特别是对结果集中第几行进行行操做的时候，通常能够考虑使用游标。

但也不是惟一的方法，能够利用别的方法来替代，通常比较复杂的存储过程里面会出现游标的影子。

  1. 现存系统有一些游标，咱们查询必须经过游标来实现
  2. 做为一个备用方式，当咱们穷尽了while循环,子查询，临时表，表变量,自建函数或其余方式扔来没法实现某些查询的时候，使用游标实现.

 

游标使用的建议

游标用于按顺序遍历结果集。

但通常状况下，应尽可能避免使用游标。

缘由：

  1.  游标违背了关系模型，即按集合来考虑问题的思想；
  2. 游标逐行对纪录进行操做，会带来额外的开销，使用游标的解决方案一般比使用集合的解决方案要慢得多；
  3. 使用游标的解决方案，须要用不少代码来描述对游标的操做，所以代码更长，可读性更差，也更难以维护。

若是要使用，必定记住要记住：

  1. 用完以后必定要关闭和释放，尽可能不要在大量数据上定义游标；
  2. 尽可能不要使用游标上更新数据；
  3. 尽可能不要使用insensitive, static和keyset这些参数定义游标；
  4. 若是能够，尽可能使用FAST_FORWARD关键字定义游标；
  5. 若是只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数。

 

咱们就介绍到这里吧，各位拜了个拜。

 

欢迎关注订阅微信公众号【熊泽有话说】，更多好玩易学知识等你来取
做者：熊泽-学习中的苦与乐
公众号：熊泽有话说
出处： https://www.cnblogs.com/xiongze520/p/14633171.html
创做不易，任何人或团体、机构所有转载或者部分转载、摘录，请在文章明显位置注明做者和原文连接。  
联系我们 沪ICP备2021010478号-7