您的位置:永利集团登录网址 > 永利集团登录网址 > T-SQL用法三(游标和Fetch)

T-SQL用法三(游标和Fetch)

2019-11-23 07:54

当我们需要批量删除数据库中的表时,对于单个删除一些表是否感到烦躁,厌倦,干脆写个脚本用得了。

一般情况下,我们用SELECT这些查询语句时,都是针对的一行记录而言,
如果要在查询分析器中对多行记录(即记录集)进行读取操作时,则需要使用到游标或WHILE等循环
/
以下内容摘自

/
游标的类型:
  1、静态游标(不检测数据行的变化)
  2、动态游标(反映所有数据行的改变)
  3、仅向前游标(不支持滚动)
  4、键集游标(能反映修改,但不能准确反映插入、删除)

一、简单静态游标示例:

本脚本使用游标循环删除,对于数量比较小,用游标暂不会造成恶劣影响。

游标使用顺序:
   1、定义游标
   2、打开游标
   3、使用游标
   4、关闭游标
   5、释放游标

永利游戏平台 1永利游戏平台 2代码

复制代码 代码如下:DECLARE @tablename VARCHARDECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FORSELECT name FROM sysobjects WHERE name LIKE 'PUB%' AND type='U'OPEN cur_delete_tableFETCH NEXT FROM cur_delete_table INTO @tablenameWHILE @@FETCH_STATUS = 0BEGINSELECT @sql='drop table '+@tablenameEXEC FETCH NEXT FROM cur_delete_table INTO @tablenameENDCLOSE cur_delete_tableDEALLOCATE cur_delete_table

Transact-SQL:
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS] 
  for selet语句   [for  update[of 列名[,列名]]
 注:LOCAL 局部游标     GLOBAL 全局游标
     FORWARD_ONLY 仅向前  SCROLL 滚动
     STATIC 静态  KEYSET 键集 DYNAMIC 动态
     READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

DECLARE product_cursor CURSOR STATIC FOR 
SELECT cName FROM Product 

获取游标的数据
  FETCH [[NEXT | PRIOR | FIRST | LAST |
  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
  From ] 游标名 [into 变量]
  注:
    NEXT  下一行  PRIOR  上一行  FIRST 第一行
    LAST  最后一行  ABSOLUTE n 第n行
    RELATIVE n 当前位置开始的第n行
    into 变量  把当前行的各字段值赋值给变量

OPEN product_cursor
FETCH NEXT FROM product_永利游戏平台 ,cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @message = ' ' + @product
 PRINT @message
 FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

游标状态变量:
    @@fetch_status  游标状态
         0 成功  -1 失败  -2 丢失
    @@cursor_rows 游标中结果集中的行数
        n 行数 -1 游标是动态的  0 空集游标
操作游标的当前行:
   current of 游标名

 

以下例子,在SQL SERVER 2000 测试成功

二、静态游标与动态游标的区别

use pubs
go

静态(STATIC)游标创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb 中的这一临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改

declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)
declare auth_cur cursor for
select au_id, au_lname, au_fname, state
from authors

动态(DYNAMIC) 游标会反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持ABSOLUTE 提取选项。

open auth_cur

FORWARD_ONLY

fetch next from auth_cur into @auid,@aulname,@aufname, @st
while (@@fetch_status=0)
  begin
    print '作者编号: '+@auid
    print '作者姓名: '+@aulname+','+@aufname
    print '所在州: '+@st
    print '--------------------------'
    fetch next from auth_cur into @auid,@aulname,@aufname, @st
  end

指定資料指標只能從第一個資料列捲到最後一個資料列。FETCH NEXT 是唯一支援的提取選項。若指定 FORWARD_ONLY 時未加上 STATIC、KEYSET 或 DYNAMIC 等關鍵字,資料指標便會作為 DYNAMIC 資料指標操作。沒有指定 FORWARD_ONLY 及 SCROLL 時,則預設為 FORWARD_ONLY,除非指定了 STATIC、KEYSET 或 DYNAMIC 關鍵字。

close auth_cur
deallocate auth_cur

如果很在意游标的类型, 应该在定义游标的时候, 加上游标类型定义的关键字, 并加上TYPE_WARNING 关键字, 以便在游标类型不是预期的情况下, 收到警告信息

Transact-SQL 游标主要用于存储过程、触发器和Transact-SQL 脚本中,它们使结果集的内容可用于其他Transact-SQL 语句。
/*另一个例子
来自:
原例子是用于说明如何用WHILE代替游标及其好处,这里只摘出使用游标的例子
*/
  DECLARE @tbTargetPermissions    table(TargetPermissionId uniqueidentifier NOT NULL PRIMARY KEY)
永利游戏平台 3    INSERT INTO @tbTargetPermissions 
永利游戏平台 4        SELECT [TargetPermissionId] 
永利游戏平台 5        FROM [ps_RolePermissions] 
永利游戏平台 6        WHERE [TargetId] = @TargetId AND [RoleId] = @RoleId
永利游戏平台 7    
永利游戏平台 8    DECLARE @TargetPermissionId uniqueidentifier;
永利游戏平台 9
永利游戏平台 10    --定义游标
永利游戏平台 11    DECLARE TargetPermissions_ByRoleId_Cursor CURSOR FOR 
永利游戏平台 12        SELECT [TargetPermissionId] FROM @tbTargetPermissions 
永利游戏平台 13
永利游戏平台 14    --打开游标
永利游戏平台 15    OPEN TargetPermissions_ByRoleId_Cursor
永利游戏平台 16
永利游戏平台 17    --读取游标第一条记录
永利游戏平台 18    FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId
永利游戏平台 19
永利游戏平台 20    --检查@@FETCH_STATUS的值,以便进行循环读取
永利游戏平台 21    WHILE @@FETCH_STATUS = 0
永利游戏平台 22    BEGIN
永利游戏平台 23        EXEC ps_TargetPermissionEntity_Select @TargetPermissionId;
永利游戏平台 24
永利游戏平台 25        FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId;
永利游戏平台 26    END
永利游戏平台 27
永利游戏平台 28    --关闭游标
永利游戏平台 29    CLOSE TargetPermissions_ByRoleId_Cursor
永利游戏平台 30    DEALLOCATE TargetPermissions_ByRoleId_Cursor

永利游戏平台 31永利游戏平台 32代码

<h3>
   心静似高山流水不动,心清若巫峰雾气不沾。
</h3>

--下面的演示针对 STATIC 和DYNAMIC 游标, 显示两者在游标循环期的差异

[code=SQL]

-- 定义演示数据

IF OBJECT_ID('tempdb..#tb') IS NOT NULL

    DROP TABLE #tb

CREATE TABLE #tb(

    id int PRIMARY KEY,

    col sysname)

INSERT #tb(

    id, col)

SELECT 1, 'AA' UNION ALL

SELECT 2, 'BB' UNION ALL

SELECT 3, 'CC' UNION ALL

SELECT 4, 'DD'

-- 游标测试

DECLARE CUR_tb CURSOR LOCAL FORWARD_ONLY READ_ONLY TYPE_WARNING STATIC --DYNAMIC

FOR           

SELECT 

    id, col

FROM #tb

-- 游标打开前删除记录

DELETE TOP (1)

FROM #tb

WHERE id = 4

SELECT 'before cursor open', * FROM #tb

-- 打开游标

OPEN CUR_tb

-- 游标打开后删除记录

DELETE TOP (1)

FROM #tb

WHERE id = 3

SELECT 'after cursor open', * FROM #tb

FETCH CUR_tb

WHILE @@FETCH_STATUS = 0

BEGIN

    -- 游标循环中删除记录

    DELETE TOP (1)

    FROM #tb

    WHERE id = 2

    FETCH CUR_tb

END

CLOSE CUR_tb

DEALLOCATE CUR_tb

 

 

本文由永利集团登录网址发布于永利集团登录网址,转载请注明出处:T-SQL用法三(游标和Fetch)

关键词: