SQL Server：流程控制语言详解
文章目录
- 一、批处理、脚本和变量
- 局部变量和全局变量
- 1、局部变量
- 2、全局变量
- 二、顺序、分支和循环结构语句
- 1、程序注释语句
- 2、BEGIN┅END语句块
- 3、IF┅ELSE语句
- 4、CASE语句
- 5、WHILE语句
- 6、BREAK和CONTINUE语句
- BREAK语句
- CONTINUE语句
- 三、程序返回、屏幕显示等语句
- 1、RETURN语句
- 2、PRINT和RAISERROR语句
- 3、WAITFOR语句
- 四、游标概念及使用
- 1、 游标的概念
- 2、游标的使用
一、批处理、脚本和变量
局部变量和全局变量
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系统提供并赋值的变量
【注】
- 用户不能建立全局变量
- 也不能用SET语句来修改全局变量的值
- 但可以将全局变量的值赋给局部变量，以便保存和处理
例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 teaching
GO
IF EXISTS (select * from student_course where cno='10101' )
BEGIN
PRINT '存在选修10101号课程的选课记录！'
select cno, avg(grade) from student_course
where cno='10101' group by cno
END
ELSE
PRINT '不存在选修10101号课程的选课记录！'
GO
IF(select avg(grade)from student_course where
cno='10101')>80
BEGIN
PRINT '选修10101号课程学生的平均成绩大于80分！'
Select s.sno,sname from student s,student_course sc
Where s.sno=sc.sno and cno='10101' and grade>=85
END
ELSE
PRINT '选修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，若是的话则执行后面的 结果表达式
CASE
WHEN <逻辑表达式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 int
SET @s=0
SET @x=1
WHILE @x<=100
BEGIN
SET @s=@s+@x
SET @x=@x+1
END
PRINT '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 int
SET @s=0
SET @x=1
WHILE @x<=100
BEGIN
SET @s=@s+@x
IF @s>2000
BREAK
SET @x=@x+1
END
PRINT '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 int
SET @s=0
SET @x=1
WHILE @x<=100
BEGIN
SET @s=@s+@x
SET @x=@x+1
IF @x<=50
CONTINUE
ELSE
BREAK
END
SET @x=@x-1
PRINT '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 student
where dept = '计算机系' order by sno
FOR READ ONLY
go
2）打开游标
在使用游标之前，必须先打开游标
OPEN <游标名>
3）关闭游标
不使用游标时应关闭游标，以通知服务器释放游标所占用的资源
CLOSE <游标名>
4）释放游标
游标结构本身也会占用一定的计算机资源，所以在使用完游标后应该回收被游标占用的资源和空间，彻底将游标释放
DEALLOCATE <游标名>
例13：说明游标的定义、打开、关闭和释放的过程。
use teaching
go
DECLARE student_course_cursor CURSOR
FOR select * from student_course
where 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 CURSOR
FOR select sno , sname from student
where spec= '计算机' order by sno
-- 打开游标
OPEN student_cursor1
-- 执行第一次取操作数并对局部变量赋值
FETCH NEXT FROM student_cursor1
INTO @sno , @sname
/* 检查上一次操作的执行状态，若@@FETCH_STATUS为0，则表示成功，可以打印并继续取数，否则停止取数 */
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '学号：'+@sno+'姓名：'+@sname
FETCH NEXT FROM student_cursor1
INTO @sno , @sname
END
-- 关闭游标
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 CURSOR
FOR select sno , sname FROM student
where 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 <游标名>
注：在使用游标进行数据的更新或删除之前，用户必须事先获得相应数据库对象的更新或删除的权力，这是进行这类操作的必要前提。
