sample70.sql

T-SQL示例大全

/* 文件名称: Sample70.sql */
USE 北风贸易

DECLARE @Name varchar(10),
                  @Sex bit,
                  @Married bit,
                  @Phone char(10),
                  @ChineseSex char(2),
                  @ChineseMarried char(4)

DECLARE Studio_Cursor CURSOR FOR
  SELECT 姓名, 员工性别, 婚姻状况, 电话号码 FROM 飞狐工作室

OPEN Studio_Cursor

FETCH NEXT FROM Studio_Cursor
  INTO @Name, @Sex, @Married, @Phone 

-- 检查 @@FETCH_STATUS 的值以便决定是否
-- 要继续执行循环以便提取更多的数据记录
WHILE @@FETCH_STATUS = 0

  BEGIN

    PRINT '员工姓名: ' + @Name

    IF (@Sex = 1)

       SET @ChineseSex = '男'
    
    ELSE

       SET @ChineseSex = '女'

    IF (@Married = 1)

       SET @ChineseMarried = '已婚'
    
    ELSE

       SET @ChineseMarried = '未婚'
   
    PRINT '性   别: ' + @ChineseSex
    PRINT '婚姻状况: ' + @ChineseMarried
    PRINT '电话号码: ' + @Phone
    PRINT REPLICATE('-',30)

    FETCH NEXT FROM Studio_Cursor
      INTO @Name, @Sex, @Married, @Phone

  END

CLOSE Studio_Cursor

DEALLOCATE Studio_Cursor