sample72.sql

T-SQL示例大全

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

DECLARE @Name varchar(10),
                  @Salary money,
                  @Comment varchar(20)

DECLARE Studio_Cursor CURSOR FOR
  SELECT 姓名, 目前薪资 FROM 飞狐工作室

OPEN Studio_Cursor

FETCH NEXT FROM Studio_Cursor
  INTO @Name, @Salary

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

  BEGIN

    PRINT '员工姓名: ' + @Name
    PRINT '目前薪资: ' + CONVERT(varchar(10),@Salary,1)

    IF (@Salary >= 60000)
        SET @Comment = '哇塞!!超越巅峰耶..'
    ELSE
        IF (@Salary >= 50000)
           SET @Comment = '领先群雄耶...'
        ELSE
           IF (@Salary >= 40000)
              SET @Comment = '还过得去啦!!'
           ELSE
              IF (@Salary >= 20000)
                 SET @Comment = '剃刀边缘!!'
              ELSE
                 SET @Comment = '准备卷铺盖吧!!'

    PRINT '薪资评比: ' + @Comment
    PRINT REPLICATE('-',30)

    FETCH NEXT FROM Studio_Cursor
      INTO @Name, @Salary

  END

CLOSE Studio_Cursor

DEALLOCATE Studio_Cursor