任务8.2 流控语句

（一）顺序语句

1.SET语句

功能：•局部变量赋值•设定用户执行T-SQL命令时的处理选项

SET 选项 ON：选项开关打开。

SET 选项 OFF：选项开关关闭。

SET 选项值：设定选项的具体值。

例如：

--阻止返回可显示受T-SQL 语句影响的行数的消息

SET NOCOUNT {ON|OFF}

--将日期格式设置为年月日格式

SET DATEFORMAT ymd--Changed language setting

SET LANGUAGE us_english  to us_English

--將語言設定變更為繁體中文

SET LANGUAGE 繁體中文

用SET给局部变量赋值

v在给变量赋值时，最好使用SET语句，其语法格式如下。

vSET ＠变量名 = 表达式v

【例】统计学生名册表的记录数并赋值给变量。

vUSE 学生管理v

GO

vDECLARE ＠row int

vSET ＠row = (SELECT COUNT(*) FROM 学生名册)

vSELECT ＠row AS 统计数vGO

2.SELECT输出语句

语法：vSELECT {表达式  [AS 别名]} [,…n]

【例】给变量赋值并输出。

①DECLARE @score1 int, @score2 int

SELECT  @score1 = 8, @score2 = 18

SELECT @score1 , @score2 

GO

3.PRINT输出语句

语法：PRINT 表达式

【例】给变量赋值并输出。

① DECLARE @sname char(10), @age int

SET  @sname ='张三‘SET  @age = 19

PRINT  @sname  

PRINT  @age  

GO

② DECLARE ＠score3 int

SET ＠score3 = 98

PRINT ＠score3

GO

例：DECLARE @sname char(10), @age int

SET  @sname ='张三'

SET  @age = 19

PRINT  '姓名: '+@sname   --注意：PRINT  ‘年龄:’+@age  出错！

PRINT  '年龄:'+STR(@age,3,1)

 GO

【例  】显示[学生表]中的学号为201905101的学生姓名。

USE 教务数据库

GO

DECLARE @姓名 char(8)

BEGIN

SELECT @姓名= (SELECT 姓名 FROM 学生表

WHERE 学生表.学号 ='201905101')

SELECT @姓名 AS 学生姓名

END

【例】使用SELECT语句从学生表中找出学号为“201905101”的数据行，再将其姓名赋给变量＠name,计算其年龄赋给变量＠age 。

USE   教务数据库

GO

DECLARE @name char(8),@AGE int

SELECT @name = 姓名,@AGE= YEAR(GETDATE())-YEAR(出生日期)

FROM 学生表

WHERE 学号= '201905101'

SELECT @name  姓名,@age   年龄

GO

(2)利用UPDATE为局部变量赋值

—

【例 】将学号为“201905101” 的学生，所选课号为“206001”的课程成绩赋给变量。

USE 教务数据库

GO

DECLARE @score int

UPDATE 成绩表

SET @score =成绩+5

WHERE 课程号 ='206001'  AND 学号 ='201905101'

SELECT @score AS 成绩

GO

注意：这里的修改，并没有真正修改表内的数据。

(3)用SET给局部变量赋值

在给变量赋值时，最好使用SET语句，其语法格式如下。

SET @变量名 = 表达式

【例】统计学生表的记录数并赋值给变量。

USE 教务数据库

GO

DECLARE @row int

SET @row = (SELECT COUNT(*) FROM 学生表)

SELECT @row AS 统计数

GO

（二）条件语句

1.IF语句

语法格式如下：

IF 逻辑表达式 <T-SQL语句|语句块> 

      /* 逻辑表达式为真时执行 */

[ELSE   

<T-SQL语句|语句块>]      /* 逻辑表达式为假时执行 */

功能： IF…ELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。

说明：“逻辑表达式”可以是各种表达式的组合，但表达式的值必须是逻辑值真或假。ELSE子句任选。

语句块语法：

BEGIN   

T-SQL语句/* 语句块 */

END

说明：BEGIN...END语句块允许嵌套。

【例 】判断学生名册表中男生入学成绩的平均值是否大于600分。

—

USE 学生管理

GO

IF (SELECT AVG(入学成绩) FROM 学生名册

WHERE 性别=’男’)>600

PRINT ’男生入学成绩的平均值大于600分’

ELSE

PRINT ’男生入学成绩的平均值小于600分’

  例：用IF…ELSE语句查询图书中有没有英语书，如果图书中有英语书，统计其数量，否则显示没有英语书。

代码：

USE Library

GO/*如果图书中有英语书，统计其数量，否则显示没有英语书*/

IF EXISTS(SELECT * FROM Book

    W HERE Bname LIKE '%英语%')    

SELECT  COUNT(*)  AS  英语图书数量   

FROM Book 

  WHERE Bname  LIKE '%英语%'

ELSE

PRINT '数据库中没有英语书’

例：用IF...ELSE语句的嵌套结构进行图书查询。

查询图书中有没有英语方面的图书，有则统计其册数，否则查询一下有没有SQL Server 2008方面的图书，如果有则统计其册数。

代码：

USE Library

GO

IF EXISTS(SELECT * FROM Book 

WHERE Bname LIKE '%英语%')   

SELECT COUNT(*) AS 英语图书数量   FROM Book  

 WHERE Bname LIKE '%英语%'

   ELSE   IF EXISTS(SELECT * FROM Book 

     WHERE Bname LIKE '%SQL Server 2008%')     

     SELECT COUNT(*) AS SQLServer2008图书数量    

     FROM Book      WHERE Bname LIKE '%SQL Server 2008%'   ELSE   

    PRINT '英语和SQL Server 2008两种书都没有！‘

v执行结果：（略）

例：BEGIN…END语句在图书查询中的应用。

代码：

DECLARE @ebook int,@cbook int

IF EXISTS(SELECT * FROM Book 

WHERE Bname like '%英语%')   

BEGIN

   SELECT @ebook=COUNT(*) FROM Book 

    WHERE Bname LIKE '%英语%'

  PRINT '英语书数量'+RTRIM(CAST(@ebook AS char(4)))+'册'   END

ELSE   

  PRINT '英语书没有！’

（三） WHILE循环语句

语法：

WHILE <逻辑表达式>

BEGIN

 <T-SQL语句或语句块>   

 [BREAK]     /*退出此循环语句的执行*/ 

   [CONTINUE]           /*结束一次循环体的执行*/

END

功能：WHILE命令在设定的条件为真时会重复执行命令行或程序块，除非遇到逻辑表达式为假或遇到BREAK语句才跳出循环。

CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行命令。

BREAK命令则让程序无条件跳出循环，结束WHILE命令的执行。

例：一个简单的循环程序。

代码：

DECLARE @x int

SET @x=0

WHILE @x<3            --当@x>=3时终止循环  

 BEGIN    

 SET @x=@x+1  

   PRINT 'x='+CONVERT(char(1),@x)   --类型转换函数CONVERT  

END

执行结果：

x=1

x=2

x=3

例：编程计算：1+2+3+......+n,如：n=100. —

DECLARE @n int,@k int @s int

Set @n=100

SELECT @s=0,@k=1

WHILE @k<=@n

 BEGIN,

  SELECT @s=@s+@k,@k=@K+1

 END

Select  @s  as ‘1+2+3+…+n=’,@n as n

例：综合利用示例，变量，赋值，循环，函数，输出

DECLARE @x int,@y float

SET @x=0

WHILE @x<10            --当@x>=10时终止循环   

BEGIN     

SET @x=@x+1     

SET @y=RAND(@x)     --随机函数RAND(X) ，取整函数FLOOR(X),CEILING(X)

SELECT @X AS 'X',@Y  AS  'RAND(x)', FLOOR(100000*@y) AS  'FLOOR(100000*RAND(x))',CEILING(100000*@y) AS '(10000000*RAND(x))'      

END

例：以下程序计算1－100之间所有能被3整除的数的个数及总和。

DECLARE @S SMALLINT,@I SMALLINT,@NUMS SMALLINT 

SET @S=0

SET @I=1

SET @NUMS=0

WHILE (@I<=100)    

 BEGIN    

    IF (@I%3=0)     

      BEGIN  

         SET @S=@S+@I

            SET @NUMS=@NUMS+1 

       END 

   SET @I=@I+1     

END

SELECT @NUMS  AS '个数',@S  AS '总和'

【例 】计算1到10，10个数阶乘之和。

DECLARE @A int,@B int,@C int,@D int

SELECT @A=0,@B=1

WHILE @B<=10

 BEGIN

  SELECT @C=1,@D=1

  WHILE @C<=@B

   BEGIN

    SET @D=@D*@C

    SET @C=@C+1

   END

   SET @A=@A+@D

   SET @B=@B+1

  END

SELECT @A,@B’

【例】给选修了C1课程的同学每人每次加1分，直到该课程平均分大于或等于80分为止，并输出所加分数及该课程最后的平均分。

【练一练】给属于部门02的商品每次提价10%，直到该部门商品均价大于或等于10为止，并输出提价后该部门的商品均价及提价次数。

（四）CASE语句

语法格式如下：

第一种：

CASE 表达式

WHEN 值1 THEN 结果1

WHEN 值2 THEN 结果2

……

WHEN 值n THEN 结果n

ELSE 

结果n+1

END

第二种：

CASE WHEN 表达式1 THEN 结果1

WHEN表达式2 THEN 结果2

……

WHEN表达式你 THEN 结果n

ELSE 结果n+1

END

【例  】显示每个选课学生的学号所选课程的成绩，按优、良、中、及格、不及格形式显示。

SELECT 学号,成绩 = CASE

WHEN 成绩>=9O THEN ’优’

WHEN 成绩>=8O AND 成绩<9O THEN ’良’

WHEN 成绩>=7O AND 成绩<8O THEN ’中’

WHEN 成绩>=6O AND 成绩<7O THEN ’及格’

WHEN 成绩<=6O  THEN ’不及格’

END,班级

FROM 选课表

【例】根据S（学生表）的SDEPT列的值输出该生对应的学院。

“计算机”和“信息”对应“信息学院”，“数学”对应“理学院”，

其他属于“人文学院”。

【练一练】使用CASE语句输出Employee（职员表）中的职员所属的部门。要求：“01”输出“家电部”，“02” 输出“食品部”，“03” 输出“日用品部”。

【例】输出SC（选课表）中每位同学的成绩等第。

要求：90分以上输出“优秀”

80～89分输出“良好”

70～79分输出“中等”

60～69分输出“合格”

60分以下为“不合格”

如果成绩为NULL，输出“未公布”。

例：显示各读者可借书的数量。

代码：

SELECT Rname AS 读者名,rt.Typename AS 读者类型,

限借阅量=   

CASE r.TypeID  --根据TypeID的值得到CASE表达式的返回值

   WHEN 1 THEN '可以借6本书!'     

   WHEN 2 THEN '可以借4本书!'     

    WHEN 3 THEN '可以借3本书!'  

    ELSE '无规定'   END

FROM Reader r,ReaderType rt  

     WHERE r.TypeID=rt.TypeID

【练一练】根据商品库存数量输出商品库存情况。

要求如下：

库存数量>=50,显示库存数量太多

库存数量为10～49,显示库存数量合适

库存数量<10, 显示库存数量过低。

v

--例：从[成绩表 ]中查询所有同学选课成绩情况，

凡成绩为空者输出“未考”、

小于60分输出“不及格”、

06分至70分输出“及格”、

70分至90分输出“良好”、

大于或等于90分时输出“优秀”。

v

例：对教务数据库的成绩表，

SELECT 学号,课程号，成绩 = CASE

WHEN 成绩>=9O THEN ’优’

WHEN 成绩>=8O AND 成绩<9O THEN ’良’

WHEN 成绩>=7O AND 成绩<8O THEN ’中’

WHEN 成绩>=6O AND 成绩<7O THEN ’及格’

WHEN 成绩<=6O  THEN ’不及格’

END，任课教师号

FROM  成绩表

v

--【示例】统计每个学生平均成绩并划分等级。执行结果如图（截图）。

SELECT学号, STR(AVG(成绩),5,2) AS '平均成绩',

    CASE

       WHEN AVG(成绩)>=90 THEN 'A'

       WHEN AVG(成绩 )>=80 THEN 'B'

       WHEN AVG(成绩)>=70 THEN 'C'

       WHEN AVG(成绩)>=60 THEN 'D'

       WHEN AVG(成绩)<60 THEN 'E'

    ENDAS '等级'  

FROM  成绩表  GROUP BY 学号

--【示例】统计学号为“201905101”的学生的选课数目，如果不少于三门课就显示“你选了××门课。很好，你完成了任务！“否则显示“你选了××门课。选课太少，加油！”。（其中××表示选课数目）。执行结果见图。

USE 教务数据库

DECLARE @cn smallint, @text varchar(100)

SET @cn=(SELECT count(学号) 

FROM 成绩表

          WHERE 学号='201905101')

IF @cn>=3

 BEGIN 

  SET @text='你选了'+CAST(@cn AS char(2))

 /* CAST函数将@cn的值转换为长度为2的字符数据*/

  SET @text=@text+'门课。很好，你完成了任务！'

 END

ELSE 

 BEGIN            

  SET @text='你选了'+CAST(@cn AS char(2)) 

  SET @text=@text+'门课。选课太少，加油！'

 END

SELECT @text AS 选课提示