sql中游标的使用

存储过程中游标的使用

1、需要游标的数据操作

    当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组

    活动集：select语句返回的元组的集合

    当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。

2、游标分类

    滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。

    非滚动游标：只能在活动集中顺序地取下一个元组。

    更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。

3、定义与使用游标的语句

    declare ：

        declare 游标名[scroll]  cursor for select语句[for update [of列表名]]

定义一个游标，使之对应一个select语句

       for update任选项，表示该游标可用于对当前行的修改与删除

    open

       打开一个游标，执行游标对应的查询，结果集合为该游标的活动集

       open 游标名

    fetch

       在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中

       fetch [next | prior | first | last |current | relative n | absolute m] 游标名into  [变量表]

    close

       关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句

       close 游标名

    deallocate

       删除游标，以后不能再对该游标执行open语句

       deallocate 游标名

    @@FETCH_STATUS

        返回被FETCH 语句执行的最后游标的状态.

       0 fetch语句成功

        -1 fetch语句失败

        -2 被提取的行不存在

4、游标实例

     例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？

       declare c1 cursor for selectsno,sname,ssex from student where sdept='ec'

       declare @sno char(10),@snamechar(10),@ssex char(2)

       Open c1

       Fetch c1 into @sno,@sname,@ssex

       While @@fetch_status==0

       Begin

       if @ssex='女'

           begin  set @ssex='female' end

       else

           begin set @ssex='male' end

       Select @sno,@sname ,@ssex

           Fetch c1 into @sno,@sname,@ssex

       end

例：
    ALTER PROC [dbo].[dnt_UserRecoveryByUserName]
    @username    NVARCHAR(50)
AS
BEGIN
    DECLARE    @uid INT;
    DECLARE    @tid INT;
    DECLARE    @replies INT;
    DECLARE    @temp varchar(50);
    SET @uid        = (SELECTTOP(1) uid FROM dnt_users WHERE username = '@username');
    SET @tid        = 0;
    SET @replies    = 0;
    UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
    UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 ANDuid = @uid;
    UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 ANDposterid = @uid;
    -- 定义一游标
    DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHEREposterid = @uid FOR READ only -- FOR UPDATE
    OPEN Ctemp  
        FETCH next FROM Ctemp INTO @tid;
        WHILE (@@fetch_status = 0)
        BEGIN
            SET @replies = (SELECTCOUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0);
            UPDATEdnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
            FETCH next FROM CtempINTO @tid;
        END
    CLOSE Ctemp;
    DEALLOCATE Ctemp;
END