文档章节

SQL中游标(二)

-wangming-
 -wangming-
发布于 2015/04/14 21:22
字数 3736
阅读 105
收藏 7
点赞 0
评论 0

游标是SQL数据库中不可或缺的部分,可以旋转储存在系统永久表中的数据行的副本,下面就将为您详解游标的使用,以及语法,供您参考学习。

MS-SQL的游标是一种临时的数据库对象,既对可用来旋转储存在系统永久表中的数据行的副本,也可以指向储存在系统永久表中的数据行的指针。 
 

游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。 
 

1.如何使用游标 
1)    定义游标语句 Declare <游标名> Cursor For 
2)    创建游标语句 Open <游标名> 
3)    提取游标列值、移动记录指针 Fetch <列名列表> From <游标名> [Into <变量列表>] 
4)    使用@@Fetch_Status利用While循环处理游标中的行 
5)    删除游标并释放语句 Close <游标名>/Deallocate <游标名> 
6)    游标应用实例 
--定义游标 
Declare cur_Depart Cursor 
For Select cDeptID,cDeptName From Department into @DeptID,@DeptName 
--创建游标 
Open cur_Depart 
--移动或提取列值 
Fetch From cur_Depart into @DeptID,@DeptName 
--利用循环处理游标中的列值 
While @@Fetch_Status=0 
Begin 
    Print @DeptID,@DeptName 
    Fetch From cur_Depart into @DeptID,@DeptName 
End 
--关闭/释放游标 
Close cur_Depart 
Deallocate cur_Depart 
简单的过程: 
定义游标 
DECLARE CustomerCursor CURSOR FOR 
SELECT acct_no,name,balance 
FROM customer 
WHERE province="北京"; 
打开游标 
OPEN CustomerCursor; 
提取数据--设置循环 
lb_continue=True

ll_total=0 
DO WHILE lb_continue 
FETCH CustomerCursor 
INTO:ls_acct_no, :ls_name, :ll_balance; 
If sqlca.sqlcode=0 Then 
ll_total+=ll_balance 
Else 
lb_continue=False 
End If 
LOOP 
关闭游标 
CLOSE CustomerCursor; 
 

 

2.语句的详细及注意

1) 定义游标语句 
Declare <游标名> [Insensitive] [Scroll] Cursor                     
    For  [FOR {Read Only | Update [ OF <列名列表>]}] u     Insensitive DBMS创建查询结果集数据的临时副本(而不是使用直接引用数据库表中的真实数据行中的列)。游标是Read Only,也就是说不能修改其内容或底层表的内容; u     Scroll 指定游标支持通过使用任意Fetch 选项(First Last Prior Next Relative Absolute)选取它的任意行作为当前行。如果此项省略,则游标将只支持向下移动单行(即只支持游标的Fetch Next); u     Select语句 定义游标结果集的标准 SELECT 语句。在游标声明的 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO; u     Read Only 防止使用游标的用户通过更新数据或删除行改变游标的内容; u     Update 创建可更新游标且列出值能被更新的游标列。如果子句中列入了任意列,则只有被列入的列才能被更新。如果Declare Cursor语句中只指定的UPDATE(没有列名列表),则游标将允许更新它的任何或所有列。 Declare cur_Depart Cursor    For Select * From Department For Update OF cDeptID,cDeptName 2) 提取游标列值、移动记录指针语句 Fetch [Next | Prior | First | Last | {Absolute <行号>} | {Relative <行号>}]    From <游标名> [Into <变量列表……>]                         每次执行Fetch语句时,DBMS移到游标中的下一行并把游标中的列值获取到Into中列出的变量中。因此Fetch语句的Into子句中列出的变量必须与游标定义中Select 语句中的列表的类型与个数相对应; 仅当定义游标时使用Scroll参数时,才能使用Fetch语句的行定位参数(First、Last、Prior、Next、Relative、Absolute);如果Fetch语句中不包括参数Next | Prior | First | Last,DBMS将执行默认的Fetch Next; u     Next 向下、向后移动一行(记录); u     Prior 向上、向前移动一行(记录); u     First 移动至结果集的第一行(记录); u     Last 移动至结果集的最后一行(记录); u     Absolute n 移动到结果集中的第n行。如果n是正值,DBMS从结果集的首部向后或向下移动至第n行;如果n是负数,则DBMS从结果集的底部向前或向上移动n行;         Fetch Absolute 2 From cur_Depart Into @DeptID,@DeptName u     Relative n   从指针的当前位置移动n行。如果n是正值,DBMS将行指针向后或向下移动至第n行;如果n是负数,则DBMS将行指针向前或向上移动n行; 

Fetch Relative 2 From cur_Depart Into @DeptID,@DeptName 
3) 基于游标的定位DELETE/UPDATE语句 
如果游标是可更新的(也就是说,在定义游标语句中不包括Read Only参数),就可以用游标从游标数据的源表中DELETE/UPDATE行,即DELETE/UPDATE基于游标指针的当前位置的操作; 
举例: 
--删除当前行的记录 
Declare cur_Depart Cursor 
    For Select cDeptID,cDeptName From Department into @DeptID,@DeptName 
Open cur_Depart 
Fetch From cur_Depart into @DeptID,@DeptName 
Delete From Department Where CURRENT OF cur_Depart 
--更新当前行的内容 
Declare cur_Depart Cursor 
   For Select cDeptID,cDeptName From Department into @DeptID,@DeptName 
Open cur_Depart 
Fetch From cur_Depart into @DeptID,@DeptName 
   Update Department Set cDeptID=’2007’ + @DeptID Where CURRENT OF cur_Depart

 

3.游标使用技巧及注意 
1) 利用Order By改变游标中行的顺序。此处应该注意的是,只有在查询的中Select 子句中出现的列才能作为Order by子句列,这一点与普通的Select语句不同; 
2) 当语句中使用了Order By子句后,将不能用游标来执行定位DELETE/UPDATE语句;如何解决这个问题,首先在原表上创建索引,在创建游标时指定使用此索引来实现;例如: 
Declare cur_Depart Cursor 
For Select cDeptID,cDeptName From Department With INDEX(idx_ID) 
For Update Of cDeptID,cDeptName 
通过在From子句中增加With Index来实现利用索引对表的排序; 
3) 在游标中可以包含计算好的值作为列; 
4) 利用@@Cursor_Rows确定游标中的行数 

4.使用系统过程管理游标 
在建立一个游标之后,便可利用系统过程对游标进行管理管理,游标的系统过程主要有以下几个:sp_cursor_list、sp_describe_cursor、 sp_describe_cursor_tables 、sp_describe_cursor_columns。 
1) sp_cursor_list   显示在当前作用域内的游标及其属性。其命令格式为: 
">sp_cursor_list [ @cursor_return = ] cursor_variable_name OUTPUT, 
[ @cursor_scope = ] cursor_scope 
参数: 
·         [@cursor_return =] cursor_variable_name OUTPUT:声明的游标变量的名称。cursor_variable_name 的数据类型为 cursor,没有默认值。游标是可滚动的、动态的只读游标。 
·         [@cursor_scope =] cursor_scope:指定要报告的游标级别。cursor_scope 的数据类型为 int,没有默认值,可以是下列值中的一个。

值描述 
1 报告所有本地游标。 
2 报告所有全局游标。 
3 报告本地游标和全局游标。

提示:由于sp_cursor_list是一个含有游标类型变量@cursor_return,且有OUTPUT保留字的系统过程,游标变量@cursor_return中的结果集与pub_cur游标中的结果集是不同的。 
2) sp_describe_cursor 报告服务器游标的特性。 
sp_describe_cursor [ @cursor_return = ] output_cursor_variable OUTPUT 
    { [ , [ @cursor_source = ] N''local'' 
        , [ @cursor_identity = ] N''local_cursor_name'' ] 
            | [ , [ @cursor_source = ] N''global'' 
        , [ @cursor_identity = ] N''global_cursor_name'' ] 
            | [ , [ @cursor_source = ] N''variable'' 
        , [ @cursor_identity = ] N''input_cursor_variable'' ] 
    } 
参数: 
·         [@cursor_return =] output_cursor_variable OUTPUT:声明游标变量的名称,该变量接收游标输出。output_cursor_variable 的数据类型为 cursor,没有默认值。调用 sp_describe_cursor 时,不能与任何游标相关联。返回的游标是可滚动的动态只读游标。 
·         [@cursor_source =] { N''local'' | N''global'' | N''variable'' }:指定是使用本地游标的名称、全局游标的名称、还是游标变量的名称来指定当前正在对其进行报告的游标。参数是 nvarchar(30)。 

·         [@cursor_identity =] N''local_cursor_name'']:由具有 LOCAL 关键字或默认设置为 LOCAL 的 DECLARE CURSOR 语句创建的游标的名称。local_cursor_name 的数据类型为 nvarchar(128)。 
·         [@cursor_identity =] N''global_cursor_name'']:由具有 GLOBAL 关键字或默认设置为 GLOBAL 的 DECLARE CURSOR 语句创建的游标的名称。也可以是由 ODBC 应用程序打开然后通过调用 SQLSetCursorName 对游标命名的 API 服务器游标的名称。global_cursor_name 的数据类型为 nvarchar(128)。 
·         [@cursor_identity =] N''input_cursor_variable'']:与开放游标相关联的游标变量的名称。input_cursor_variable 的数据类型为 nvarchar(128)。 
提示: sp_descride_cursor_tables和sp_describe_cursor_columms的命令格式与sp_describe_cursor的命令格式一样。

 

5.游标种类 
MS SQL SERVER 支持三种类型的游 
标:Transact_SQL 游标,API 服务器游标和客户游标。 
1) Transact_SQL 游标Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上,由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。 Transact_SQL 游标不支持提取数据块或多行数据。 
2) API 游标 API 游标支持在OLE DB, ODBC 以及DB_library 中使用游标函数,主要用在服务器上。每一次客户端应用程序调用API 游标函数,MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库(DLL) 都会将这些客户请求传送给服务器以对API游标进行处理。 
3) 客户游标 客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中,有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理,所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下,服务器游标能支持绝大多数的游标操作。 
由于API 游标和Transact-SQL 游标使用在服务器端,所以被称为服务器游标,也被称为后台游标,而客户端游标被称为前台游标。在本章中我们主要讲述服务器(后台)游标。 
select count(id) from info 
select * from info 
--清除所有记录 
truncate table info 
declare @i int 
set @i=1 
while @i<1000000 
begin 
insert into info values(''Justin''+str(@i),''深圳''+str(@i)) 
set @i=@i+1 
end

6.游标和游标的优点 
在数据库中,游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集(可以是零条、一条或由相关的选择语句检索出的多条记录)和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时,必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序,那么游标就像您打开文件所得到的文件句柄一样,只要文件打开成功,该文件句柄就可代表该文件。对于游标而言,其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集,从而把表中数据以平面文件的形式呈现给程序。 
我们知道关系数据库管理系统实质是面向集合的,在MS SQL中并没有一种描述表中单一记录的表达形式,除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。 SERVER 
由此可见,游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作;它还提供对基于游标位置而对表中数据进行删除或更新的能力;而且,正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来,使两个数据处理方式能够进行沟通。
以[master].[dbo].[spt_values] 这个表为例子
===

declare @name nvarchar(35) 
declare @number int

declare my_cursor cursor for         --定义游标cursor1 
select TOP 5 [name],[number] from [spt_values]               --使用游标的对象(跟据需要填入select文)

open my_cursor                       --打开游标 
fetch next from my_cursor into @name,@number --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中

while(@@fetch_status=0)          --判断是否成功获取数据 
begin

--update [spt_values] set [name]=@name+'1' 
--where [number]=@number+1           --进行相应处理(跟据需要填入SQL文)
print @name
print @number
print '===='
fetch next from my_cursor into @name,@number --将游标向下移1行

end

close my_cursor                   --关闭游标 
deallocate my_cursor

=====================以下是基础
blog.csdn.net/lejuo/archive/2008/11/12/3279340.aspx
可百度 SQL游标语法及举例 进行更深入学习

游标的定义:
每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序; 
1.DECLARE 游标 
2.OPEN 游标 
3.从一个游标中FETCH 信息 
4.CLOSE 或DEALLOCATE 游标

通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容:

 

游标名字 
数据来源(表和列) 
选取条件 
属性(仅读或可修改) 
其语法格式如下: 
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR 
FOR select_statement 
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}] 
其中: 
cursor_name 
指游标的名字。 
INSENSITIVE 
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内(建立在tempdb 数据库下)。对该游标的读取操作皆由临时表来应答。因此,对基本表的修改并不影响游标提取的数据,即游标不会随着基本表内容的改变而改变,同时也无法通过 
游标来更新基本表。如果不使用该保留字,那么对基本表的更新、删除都会反映到游标中。

另外应该指出,当遇到以下情况发生时,游标将自动设定INSENSITIVE 选项。 
在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句; 
使用OUTER JOIN; 
所选取的任意表没有索引; 
将实数值当作选取的列。 
SCROLL 
表 明所有的提取操作(如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE)都可用。如果不使用该保留字,那么只能进行NEXT 提取操作。由此可见,SCROLL 极大地增加了提取数据的灵活性,可以随意读取结果集中的任一行数据记录,而不必关闭再 
重开游标。 
select_statement 
是定义结果集的SELECT 语句。应该注意的是,在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。 
READ ONLY 
表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE 语句的WHERE CURRENT OF 子句中,不允许对该游标进行引用。 
UPDATE [OF column_name[,…n]] 
定义在游标中可被修改的列,如果不指出要更新的列,那么所有的列都将被更新。当游标被成功创。

© 著作权归作者所有

共有 人打赏支持
-wangming-
粉丝 33
博文 78
码字总数 89622
作品 0
上海
后端工程师
PL/SQL Step By Step(二)

游标是PL/SQL非常重要的一部分,也是很多人为什么使用PL/SQL的原因。游标能够让开发者对数据库查询进行记录级别的控制。也就是说,可以对查询返回的数据一次一行进行步进,并且在两行之间停顿...

wawlian
2012/03/10
0
0
mysql和Oracle 游标的使用

使用游标根据一个表中的数据,循环创建不同的表 mysql中的 DELIMITER $$ DROP PROCEDURE IF EXISTS zy.jkjkzlpoliticallocationpro $$ CREATE PROCEDURE zy.jkjkzlpoliticallocationpro() BE......

qq5805bc784f826
06/28
0
0
MySQL数据库高级(九)——游标

MySQL数据库高级(九)——游标 一、游标简介 1、游标简介 游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果。游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制...

642960662
04/12
0
0
SQL Server中查看哪些游标未释放

SQL SERVER提供了一个动态管理函数sys.dmexeccursors,返回有关在数据库中打开的游标的信息。 一、语法 dmexeccursors (session_id | 0 ) 二、参数说明 session_id | 0:会话的 ID。 1、如果...

学习也休闲
2016/03/16
31
0
SQL Server游标的使用【转】

SQL Server游标的使用【转】 作者:mosstan_jun 来源:博客园 发布时间:2011-11-26 00:33 阅读:348 次原文链接[收藏] 游标是邪恶的! 在关系数据库中,我们对于查询的思考是面向集合的。而...

潇风
2012/03/30
0
0
Oracle --存储过程基本语法

一、存储过程的理解 create or replace procedure 存储过程名 as begin null end 行1:create or replace procedure 是一个SQL语句,通知oracle数据库去创建一个叫做skeleton储存过程,如果存...

求是科技
2015/04/09
0
0
python中的SQLite数据库

python中的SQLite数据库 Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件,也就是说没有独立的维护进程,所有的维护都来自于程序本身。它使用一个文件存储整个数据库,操作十分方便...

幸运券发放
05/03
0
0
ORACLE中DBMS_SQL的用法

对于一般的select操作,如果使用动态的sql语句则需要进行以下几个步骤: open cursor---> parse---> define column---> excute---> fetch rows---> close cursor; 而对于dml操作(insert,updat......

紫地瓜
2012/12/06
0
0
oracle pl/sql(二)

2014-04-14 1.标量变量:用于存放单行单列的数据 2.复合数据类型 1)PL/SQL记录:处理单行多列数据 2)PL/SQL集合:用于处理多行单列数据 AA:索引表(Associative arrays, also known as index...

蓝蝶飞扬
2014/04/15
0
0
温故而知新之Oracle 父子游标

当两个sql文本相同,而其他环境不同,这两个游标不能共享执行计划。它们都有各自的执行计划存在缓存库中。这两个游标就是子游标,oracle还会建立一个父游标,父游标没有执行计划,只是文本相...

lych528
06/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Kafka设计解析(一)- Kafka背景及架构介绍

原创文章,转载请务必将下面这段话置于文章开头处。(已授权InfoQ中文站发布) 本文转发自技术世界,原文链接 http://www.jasongj.com/2015/03/10/KafkaColumn1 摘要   Kafka是由LinkedI...

mskk
8分钟前
0
0
使用Service Mesh整合您的微服务架构

在微服务架构的世界中,它正在达到这样的程度,即管理系统的复杂性对于利用它带来的好处变得至关重要。 目前,如何实现这些微服务不再是一个问题,因为有很多可用的框架(Spring Boot,Vert....

xiaomin0322
12分钟前
0
0
看看 LinkedList Java 9

终于迎来了 LinkedList 类,实现的接口就有点多了 Serializable, Cloneable, Iterable<E>, Collection<E>, Deque<E>, List<E>, Queue<E>。LinkedList是一个实现了List接口和Deque接口的双端链......

woshixin
30分钟前
0
0
算法 - 冒泡排序 C++

大家好,我是ChungZH。今天我给大家讲一下最基础的排序算法:冒泡排序(BubbleSort)。 冒泡排序算法的原理如下: 比较相邻的元素。如果第一个比第二个大(可以相反),就交换他们两个。 对每...

ChungZH
33分钟前
0
0
jquery ajax request payload和fromData请求方式

请求头的不同 fromData var data = { name : 'yiifaa'};// 提交数据$.ajax('app/', { method:'POST', // 将数据编码为表单模式 contentType:'application/x-ww...

lsy999
35分钟前
0
0
阿里P7架构师,带你点亮程序员蜕变之路

前言: Java是现阶段中国互联网公司中,覆盖度最广的研发语言。 掌握了Java技术体系,不管在成熟的大公司,快速发展的公司,还是创业阶段的公司,都能有立足之地。 有不少朋友问,成为Java架...

Java大蜗牛
37分钟前
1
0
Ecstore 在没有后台管理界面(维护)的情况如何更新表的字段

window 系统: 切换到:app\base 目录下: C:\Users\qimh>d: D:\>cd D:\WWW\huaqh\app\base 执行:D:\WWW\huaqh\app\base>cmd update linux 系统: 1># cd /alidata/www.novoeshop.com/app/......

qimh
41分钟前
0
0
设计模式-策略模式

策略模式 解释 对工厂模式的再次封装,使用参数控制上下文信息(将工厂返回的实例赋值给context field) 不会返回bean实例,只是设置对应的条件 调用context的方法(调用field的方法) 用户只...

郭里奥
44分钟前
0
0
python使用有序字典

python自带的collections包中有很多有用的数据结构可供使用,其中有个叫OrderedDict类,它可以在使用的时候记录元素插入顺序,在遍历使用的时候就可以按照原顺序遍历。 a = {"a":1,"b"...

芝麻糖人
今天
0
0
RestTemplate HttpMessageConverter

RestTemplate 微信接口 text/plain HttpMessageConverter

微小宝
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部