**************************************************************************************************
FETCH FIRST:第一
FETCH NEXT:下一
FETCH PRIOR:前一
FETCH LAST:最后
FETCH ABSOLUTE N :N>0 : 从第一行数第N
N<0 : 倒数第N
N=0 : 不提取
FETHC RELATIVE N :N>0: 提取上次提取后第N条
N<0: 提取上次提取前第N条
N=0: 提取上次提取行
---------------------------
游标的第一次打开:逻辑位置对应于第一条记录之前
FETCH FIRST:第一条
FETCH NEXT :第一条
FETCH PRIOR:不提取
FETCH LAST:最后一条
FETCH ABSOLUTE N :N>0:从1数第N
N<0:倒数第N
N=0:提提取
FETCH RELATIVE N :N>0:提取游标中第N
N<=0:不提取
--Transact-SQL 游标限于一次只能提取一行。API 服务器游标则支持每次提取时提取一批行。支持一次提取多行的游标称为块状游标。
---------------------------
游标种类
只进:只能从第一条往最后一条:只允许FETCH NEXT操作。
可滚动性:可以在游标中任何地方随机提取任意行。允许所有的提取操作(但动态游标不支持绝对提取)。
可滚动游标对支持联机应用程序特别有用。可将游标映射为应用程序中的表格或列表框。随着用户向 上、向下和在整个表格中滚动,应用程序使用滚动提取从游标中检索用户想要查看的行。
用 API 提取行
实际使用的语句、函数或方法的 API 都有不同的名称来提取行:
Transact-SQL 游标使用 FETCH FIRST、FETCH LAST、FETCH NEXT、FETCH PRIOR、FETCH ABSOLUTE(n) 和 FETCH RELATIVE(n) 语句。
OLE DB 使用如下方法:IRowset::GetNextRows、IRowsetLocate::GetRowsAt、IRowsetLocate::GetRowsAtBookmark 和 IRowsetScroll::GetRowsAtRatio。
ODBC 使用 SQLFetch 函数,它与用于一行的 FETCH NEXT 或 SQLFetchScroll 相同。SQLFetchScroll 支持块状游标和所有的提取选项(第一、最后、下一个、前一个、绝对、相对)。
ADO 使用 Move、MoveFirst、MoveLast、MoveNext 和 MovePrevious Recordset 方法获取游标的位置。然后使用 GetRows 记录集方法在那个位置检索一行或多行。也可直接调用 GetRows,将其中的 Start 参数设为想要提取的行数。
DB-Library 使用 dbcursorfetch 和 dbcursorfetchex 函数。
/***************************************************************************************************
declare cursor1 cursor for
select * from sysobjects where xtype='u'
open cursor1
FETCH NEXT FROM CURSOR1 --into等其他操作
while @@FETCH_STATUS = 0
begin
FETCH NEXT FROM CURSOR1
end
CLOSE CURSOR1
DEALLOCATE CURSOR1
------------------------------------------ THE RESULT
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-------------------------------------------------------------------------------------------------------------------------------- -----------
PassWordLog 578101100 U 1 6 1610621217 48 0 0 2005-07-07 11:09:17.810 0 48 0 U 1 83 0 2005-07-07 11:09:17.810 0 0 0 0 0 2561 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
AllUser_Other 866102126 U 1 16 1610612768 0 0 0 2005-09-26 16:18:16.780 0 0 0 U 1 67 0 2005-09-26 16:18:16.780 0 0 0 0 0 1 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
dtproperties 981578535 U 1 7 -536862427 32 0 0 2004-11-18 02:24:09.280 0 32 0 U 1 8275 0 2004-11-18 02:24:09.280 0 0 0 0 0 2563 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
AllUser 1042102753 U 1 17 1610623779 432 0 0 2005-10-17 15:56:09.077 0 432 0 U 1 115 0 2005-10-17 15:56:09.077 0 0 0 0 0 3593 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
alluser_del_051109 1550628567 U 1 17 1610612768 0 0 0 2005-11-09 12:25:19.060 0 0 0 U 1 67 0 2005-11-09 12:25:19.060 0 0 0 0 0 1 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
alluser_bak051109 1566628624 U 1 17 1610612768 0 0 0 2005-11-09 13:20:58.560 0 0 0 U 1 67 0 2005-11-09 13:20:58.560 0 0 0 0 0 1 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
Commend 1710629137 U 1 2 1610612736 0 0 0 2005-11-17 12:57:27.873 0 0 0 U 1 67 0 2005-11-17 12:57:27.873 0 0 0 0 0 0 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
Confra 1993058136 U 1 2 1610615042 64 0 0 2004-11-18 02:23:33.653 0 64 0 U 1 99 0 2004-11-18 02:23:33.653 0 0 0 0 0 520 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
ExpireIP 2009058193 U 1 8 1619407139 128 0 0 2004-11-18 02:23:33.780 0 128 0 U 1 115 0 2004-11-18 02:23:33.780 0 0 965578478 965578478 0 2689 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
ExpireMachine 2025058250 U 1 8 1619013923 80 0 0 2004-11-18 02:23:33.920 0 80 0 U 1 115 0 2004-11-18 02:23:33.920 0 0 0 0 0 2689 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
Grade 2041058307 U 1 2 1610612994 48 0 0 2004-11-18 02:23:34.047 0 48 0 U 1 99 0 2004-11-18 02:23:34.047 0 0 0 0 0 512 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
ReserveWord 2057058364 U 1 4 1619013923 96 0 0 2004-11-18 02:23:34.170 0 96 0 U 1 115 0 2004-11-18 02:23:34.170 0 0 0 0 0 2689 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
SafeGuard_backup 2073058421 U 1 18 1610621217 32 0 0 2004-11-18 02:23:34.297 0 32 0 U 1 83 0 2004-11-18 02:23:34.297 0 0 0 0 0 2561 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
Member 2089058478 U 1 3 1610614018 64 0 0 2004-11-18 02:23:34.437 0 64 0 U 1 99 0 2004-11-18 02:23:34.437 0 0 0 0 0 516 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
SafeGuard 2105058535 U 1 17 1610622210 416 0 0 2004-11-18 02:23:34.547 0 416 0 U 1 99 0 2004-11-18 02:23:34.547 0 0 0 0 0 2564 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
UserRelation 2121058592 U 1 3 1610614017 128 0 0 2004-11-18 02:23:34.670 0 128 0 U 1 83 0 2004-11-18 02:23:34.670 0 0 0 0 0 516 0
(所影响的行数为 1 行)
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
----------------------------------------------------------------------------------------------
(所影响的行数为 0 行)