SQL Server：流程控制语言详解

一、批处理、脚本和变量

局部变量和全局变量

1、局部变量

局部变量是以@开头的用户定义的变量，用declare语句声明

（1）局部变量的命名

DECLARE  <局部变量名1><数据类型1> [ , <局部变量名2><数据类型2>, …]

（2）局部变量的赋值

SET  <局部变量名>= <表达式>

例1：在同一批中先声明二个变量，并为它们赋值，然后将它们用到SELECT语句的WHERE子句中。

Use  teaching
GO
- - 声明二个局部变量
DECLARE @student_name  varchar(20) , @student_birthday  datetime
-- 对二个局部变量赋值
SET  @student_name = '杨涛'
SET  @student_birthday = ‘2001-01-01'
-- 根据这二个局部变量的值进行查询
SELECT  sno, sname, birthday
FROM  student
WHERE  sname=@student_name  or birthday=@student_birthday
GO

2、全局变量

全局变量是以@@开头的SQL Server系统提供并赋值的变量

【注】

1. 用户不能建立全局变量
2. 也不能用SET语句来修改全局变量的值
3. 但可以将全局变量的值赋给局部变量，以便保存和处理

例2：应用三个全局变量的例子

 /* 第一类全局变量 */
-- @@rowcount表示最近一个语句影响的行数
PRINT  @@rowcount
-- @@error保存最近执行操作的错误状态
PRINT  @@error
/* 第二类全局变量 */
-- @@version表示SQL Server的版本信息
PRINT @@version

例3：将全局变量的值赋给局部变量，请读者利用在线帮助来理解@@MAX_PRECISION的含义

DECLARE  @max_p   tinyint
SET @max_p = @@MAX_PRECISION
PRINT @max_p

二、顺序、分支和循环结构语句

1、程序注释语句

① 注释语句的作用

（1）说明代码的含义；

（2）注释掉程序中暂时不用的语句；

② 注释语句的种类

（1）- -（两个减号）：用于注释单行；

（2）/……/：用于注释多行；

2、BEGIN┅END语句块

BEGIN<T-SQL语句序列>
END

3、IF┅ELSE语句

IF  <逻辑表达式><T-SQL语句序列1>
[ELSE<T-SQL语句序列2>]

例4：请读者仔细阅读下列程序，并理解其含义。

Use  teachingGOIF EXISTS (select *  from student_course  where cno='10101' )BEGIN PRINT  '存在选修10101号课程的选课记录！'select  cno, avg(grade)  from student_course  where cno='10101'    group by cnoENDELSEPRINT  '不存在选修10101号课程的选课记录！'GOIF(select avg(grade)from student_course where cno='10101')>80BEGIN PRINT  '选修10101号课程学生的平均成绩大于80分！'Select s.sno,sname from student s,student_course scWhere  s.sno=sc.sno and cno='10101' and grade>=85ENDELSEPRINT  '选修10101号课程学生的平均成绩小于等于80分！'GO

4、CASE语句

【语句格式1】：根据多个选择来确定执行的内容 —— 类似于C语言中的switch...case

CASE  <条件判断表达式>WHEN <比较表达式1> THEN <结果表达式1>[WHEN <比较表达式2> THEN <结果表达式2> ………WHEN <比较表达式n> THEN <结果表达式n>] [ELSE  <结果表达式q>] 
END

例5：使用CASE语句格式1的例子

Use Teaching
GO
Select  Sno  as  '学号' , sname  as  '姓名' ,CASE  dept	-- 根据属性进行划分WHEN '电子系' THEN '是来自电子系学生’WHEN '计算机系' THEN '是来自计算机系学生'WHEN '信息系' THEN '是来自信息系学生'WHEN '机械系' THEN '是来自机械系学生'ELSE '是来自其它系的学生'END   as  '系名'
From student
Order  by  dept
GO

【语句格式2】：依次判断where后的<逻辑表达式1>是否为TRUE，若是的话则执行后面的 结果表达式

CASEWHEN <逻辑表达式1> THEN <结果表达式1>
[WHEN <逻辑表达式2> THEN <结果表达式2> ………WHEN <逻辑表达式n> THEN <结果表达式n>] [ELSE  <结果表达式q>] 
END

例6：使用CASE语句格式2的例子

Use Teaching
GO
Select sc.sno  as  '学号', sname  as  '姓名' ,sc.cno  as  '课程号', cname  as  '课程名',CASE WHEN  grade>=90  THEN  '优秀'WHEN  grade>=80  THEN  '良好'WHEN  grade>=70  THEN  '中等'WHEN  grade>=60  THEN  '及格' ELSE  '不及格'END  as  '成绩'
from student  s, student_course  sc, course  c
where  s.sno=sc.sno  and  sc.cno=c.cno
order by  s.sno
go

5、WHILE语句

使用WHILE可以在条件成立的时候重复执行一条或多条T-SQL语句

WHILE <逻辑表达式><T-SQL语句序列>  

注：与IF…ELSE语句一样，WHILE语句只能执行一条T-SQL语句，如果希望包含多条T-SQL语句，就应该使用BEGIN…ENG结构

例7：计算s = 1+2+3+…+99+100的和

  DECLARE  @x  int , @s  intSET @s=0SET @x=1WHILE @x<=100BEGINSET @s=@s+@xSET @x=@x+1ENDPRINT  'S='+convert(char(4) , @s )GO
-- 其中convert (char(4) , @s )为转换数据类型的函数

6、BREAK和CONTINUE语句

BREAK语句

BREAK语句用于退出最内层的WHILE循环

WHILE <逻辑表达式><T-SQL语句序列1>BREAK<T-SQL语句序列2>

例8：利用BREAK语句跳出循环的例子

  DECLARE @x int , @s intSET @s=0SET @x=1WHILE  @x<=100BEGINSET @s=@s+@xIF @s>2000BREAKSET @x=@x+1ENDPRINT 'x='+convert (char(3), @x )PRINT 'S='+convert (char(4), @s )GO

CONTINUE语句

CONTINUE语句用于重新开始一次WHILE循环

WHILE <逻辑表达式><T-SQL语句序列1>CONTINUE<T-SQL语句序列2>

例9：使用CONTINUE语句的例子

  DECLARE @x int , @s intSET @s=0SET @x=1WHILE @x<=100BEGINSET @s=@s+@xSET @x=@x+1IF @x<=50CONTINUEELSEBREAKENDSET @x=@x-1PRINT 'x='+convert (char(3) , @x )PRINT 'S='+convert (char(4) , @s )GO

三、程序返回、屏幕显示等语句

1、RETURN语句

RETURN语句可以在过程、批和语句块中的任何位置使用

语法格式如下：

RETURN  [<整数表达式>]

例10：使用RETURN语句返回整数的例子

use teaching
go
create  procedure  checkstate  @param  char(7)
as
if (select  dept  from  student where  sno =@param) = '电子系'RETURN  1
else RETURN  2
go
declare  @return_status  int
exec  @return_status=checkstate @param='0012301'
select  @return_status  as  'Return Status'
go

2、PRINT和RAISERROR语句

（1）PRINT语句

PRINT语句的作用是在屏幕上显示用户消息

PRINT  <字符串>|局部变量|全局变量

（2）RAISERROR语句

RAISERROR语句的作用是将错误信息显示在屏幕上，同时也可以记录在NT日志中

3、WAITFOR语句

WAITFOR语句可以将它之后的语句在一个指定的时间间隔之后执行，或在未来的某一指定时间执行

WAITFOR { DELAY ‘time1’ | TIME ‘time2’}

例11：使用WAITFOR语句的例子

-- 以下代码指示SQL Server等待两秒后查询student表
WAITFOR  DELAY  '00:00:02'
Select  *  from  teaching.dbo.student
GO
/*以下代码指示SQL Server等待到当天上午09:15:10，才执行查询操作*/
Use  teaching
GO
WAITFOR  TIME  '09:15:10'
Select  *  from  student
GO

四、游标概念及使用

1、 游标的概念

游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的工作行，从而可以有选择地按行进行操作

无需借助于高级语言来实现，导致不必要的数据传输，从而延长执行的时间

1）声明游标

DECLARE  <游标名>  [ INSENSITIVE ] [ SCROLL ] CURSOR   FOR  <SELECT语句>[ FOR  { READ ONLY | UPDATE [ OF <列名1> [ , <列名2>… ] ] } ]

有关参数的说明：

① < 游标名 >是为声明的游标所取的名字

② 使用insensitive关键字定义的游标会将提取出来的数据放在一个Tempdb的数据库创建的临时表中，如若不选用insensitive关键字，则用户对基本表所做的任何改动都将在游标中得到体现

③ 使用 SCROOL 关键字定义的游标，包括如下6种取数功能

• FIRST —— 表示取第一行数据
• LAST —— 表示取最后一行数据
• PRIOR —— 表示取前一行数据
• NEXT —— 表示取后一行数据（默认）
• RELATIVE —— 表示按相对位置取数据
• ABSOLUTE —— 表示按绝对位置取数据

④ < SELECT 语句 > 主要用来定义游标所要进行处理的结果集，在声明游标的SELECT语句中不允许使用 compute、compute by、into关键字

⑤ READ ONLY 表示声明只读游标，不允许通过只读游标进行数据的更新

⑥ UPDATE [ OF <列名1> [ , <列名2>… ] ] 表示定义在这个游标里的可更新列

例12：先定义一个可在student表中所有行上进行操作的游标，再定义一个可对游标处理的结果集进行筛选和排序的只读游标

use  teaching
go
-- 定义可在student表中所有行上进行操作的游标
DECLARE  student_ cursor1  CURSOR
FOR   select * from  student
go
/*定义可对游标处理的结果集进行筛选和排序的只读游标 */
DECLARE student_cursor2  CURSOR
FOR  select  sno , sname  from  studentwhere  dept = '计算机系' order by sno
FOR  READ ONLY
go

2）打开游标

在使用游标之前，必须先打开游标

OPEN  <游标名>	

3）关闭游标

不使用游标时应关闭游标，以通知服务器释放游标所占用的资源

CLOSE  <游标名>

4）释放游标

游标结构本身也会占用一定的计算机资源，所以在使用完游标后应该回收被游标占用的资源和空间，彻底将游标释放

DEALLOCATE  <游标名>

例13：说明游标的定义、打开、关闭和释放的过程。

use teaching
go 
DECLARE  student_course_cursor  CURSORFOR  select  *  from  student_coursewhere  cno='10106'
SELECT  @@CURSOR_ROWS  
/* 返回值为0，表示游标还没被打开 */
open  student_course_cursor
fetch  next  from  student_course_cursor  
/* 返回满足条件的第一个记录 */
select  @@CURSOR_ROWS  
/* 返回值为-1，表示游标是动态的 */
close  student_course_cursor
deallocate  student_course_cursor
go

2、游标的使用

1）使用游标取数

打开游标后，就可以利用游标提取数据了

FETCH  [ [ NEXT | PRIOR | FIRST | LAST| ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar }] FROM ] <游标名>[ INTO  <局部变量1> [ , <局部变量2>,…]]
-- 在使用INTO子句对局部变量赋值时，局部变量必须和声明游标时使用的select语句中引用到的数据列在数量、顺序和数据类型上保持一致，否则服务器返回提示错误

例14：使用游标取数的操作与循环语句相结合的例子。

use  teaching 
go
-- 定义局部变量
DECLARE @sno  char(7) , @sname  varchar(20)
-- 声明游标
DECLARE  student_cursor1  CURSORFOR  select sno , sname  from  studentwhere  spec= '计算机' order  by  sno
-- 打开游标
OPEN  student_cursor1
-- 执行第一次取操作数并对局部变量赋值
FETCH   NEXT   FROM   student_cursor1  INTO  @sno , @sname
/* 检查上一次操作的执行状态，若@@FETCH_STATUS为0，则表示成功，可以打印并继续取数，否则停止取数 */
WHILE  @@FETCH_STATUS = 0BEGINPRINT '学号：'+@sno+'姓名：'+@snameFETCH  NEXT  FROM  student_cursor1 INTO  @sno , @snameEND
-- 关闭游标
CLOSE  student_cursor1
-- 释放游标
DEALLOCATE  student_cursor1
GO

例15：定义一个滚动游标，以实现更灵活的数据提取

Use teaching 
GO
-- 首先执行一遍查询语句以提供滚动游标操作成功与否的对比
select  sno, sname  FROM  student
Where  birthday between '1996-01-01' and '1997-12-31'
Order  by  sno
-- 定义滚动游标
DECLARE  student_cursor2  SCROLL  CURSORFOR  select  sno , sname  FROM  studentwhere  birthday between  '1996-01-01'  and  '1997-12-31'order   by   sno
-- 打开游标           
OPEN  student_cursor2
-- 提取数据集中的最后一行
FETCH  LAST  FROM  student_cursor2
-- 提起当前游标所在行的上一行
FETCH  PRIOR  FROM  student_cursor2
-- 提取当前数据集中的第5行
FETCH  ABSOLUTE  5  FROM   student_cursor2
-- 提取当前行的前2行
FETCH  RELATIVE -2  FROM   student_cursor2
-- 关闭游标
CLOSE  student_cursor2
-- 释放游标
DEALLOCATE  student_cursor2
GO

2）利用游标修改数据

要使用游标进行数据的修改，其前提条件是该游标必须被声明为可更新的游标。在进行游标声明时，没有带READONLY关键字的游标都是可更新的游标

UPDATE <表名>
SET <列名1>=<表达式l>[,<列名2>=<表达式2>，…]
WHERE   CURRENT  OF  <游标名>
-- 其中CURRENT OF <游标名>表示当前游标的当前数据行。CURRENT OF子句只能使用在UPDATE和DELETE操作的语句中

使用游标还可以进行数据的删除，其方法与上雷同，下面仅给出它的语法结构，其语句格式如下

DELETE FROM <表名> WHERE  CURRENT  OF <游标名>

注：在使用游标进行数据的更新或删除之前，用户必须事先获得相应数据库对象的更新或删除的权力，这是进行这类操作的必要前提。