cursordemo7.sql

T-SQL示例大全

/* 文件名称: CursorDemo7.sql */

-- 声明变量
DECLARE @Name nvarchar(10),
                   @Gender nvarchar(2),
                   @Marital nvarchar(2),
                   @Tele nvarchar(11)

-- 声明数据指针
DECLARE Foxman_Cursor CURSOR 
     LOCAL
     FAST_FORWARD
     FOR
     SELECT 姓名,
                   员工性别 = CASE 员工性别
                                             WHEN 0 THEN '女'
                                             WHEN 1 THEN '男'
                                        END,
                   婚姻状况 = CASE 婚姻状况
                                             WHEN 0 THEN '未婚'
                                             WHEN 1 THEN '已婚'
                                        END,
                   电话号码
     FROM 北风贸易.dbo.飞狐工作室
     WHERE 家庭地址 LIKE '%北京%'

-- 开启数据指针
OPEN Foxman_Cursor

-- 提取数据指针
FETCH NEXT FROM Foxman_Cursor
     INTO @Name,@Gender,@Marital,@Tele

WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT '姓名: ' + @Name
     PRINT '性别: ' + @Gender
     PRINT '婚姻:' + @Marital
     PRINT '电话:' + @Tele
     PRINT REPLICATE('-',30)
    FETCH NEXT FROM Foxman_Cursor
        INTO @Name,@Gender,@Marital,@Tele
END

-- 关闭数据指针
CLOSE Foxman_Cursor

-- 删除数据指针
DEALLOCATE Foxman_Cursor