数据库原理——T-SQL、游标

T-SQL语言简介

• T-SQL简介及基本语法

• 变量
  1，局部变量：变量名前加 $@$
  2，全局变量：变量名前加 $@@$ 如：
  

• 变量的声明及赋值：
  1，变量声明语法：
  declare @varName dataType [, @varName dateType] ...
  2，单个变量赋值语法：
  set @varName = <expr>
  3，多个变量赋值语法：
  select @varName = <expr|columnName> [, @varName = <expr|columnName>] ...

• T-SQL控制流程语句：
  

• 程序实例：
  【例1】在ScoreDB数据库中，查询Score表中最高成绩，如果最高成绩大于95分，则显示“Pretty Good”

use ScoreDB
go
declare @maxScore numeric        --定义局部变量@maxScore 类型为numeric
select @maxScore =       --给变量赋值
	(select max(score) From Score)  
if @maxScore > 95			
	print 'Pretty Good'

【例2】声明两个局部变量@sno 和 @ score，用于接收select语句查询结果，并显示其结果

declare @sno char(7), @score numeric    -- 定义变量

select @sno = b.studentNo, @score = score    -- 接受查询结果
from Score a, Student b
where a.studentNo = b.studentNo
			and ...

if @@ROWCOUNT = 0     -- @@ROWCOUNT全局变量 表示上述查询影响的行数
	print 'No answer'
else
	select @sno,@score      -- 显示@sno 和 @score

【例3】列示成绩表Score中的所有记录，要求根据学期号的不同取值 分别 显示 开课时间 为’xx上半年‘，’xx下半年‘ 和 ‘xx小学期’；根据score 不同取值 分别 显示 等级 为优良，合格，不合格。
如：’152‘ 为 16年第二学期

select studentNo 学号, courseNo 课程号，
	CASE right（termNo，1）  -- 取termNo 右边的一个字符
		when '1' then left(termNo,2)+'年下半年'   -- 如果为 1 ， 则值为：termNo左边两个字符 + ’年下半年‘
		when '2' then str((convert(tinyint, left(termNo,2))+1,2)+ '年上半年'  -- 如果为 2，则表明为第二学期，第二学期在另一年的上半年，所以将termNo左两位转为数值+1 再转为字符串
		else str((convert(tinyint, left(termNo,2))+1,2) + '小学期'
	end 开课时间，      -- 定义列名 为 开课时间
	
	case
		when score >= 80 then '优良'
		when score >= 60 then '合格'
		else '不及格'
	end 等级   -- 定义列名 为 等级

from Score

游标

• 对 select 语句查询的 结果集 进行 逐行处理，需要游标（cursor）
• 游标 是系统为用户提供的一个 数据缓冲区， 用于存放SQL语言的 执行结果（元组集合）
• 用户可以使用SQL提供的语句从游标中逐一获取元组记录，并赋给主变量，交给主语言进一步处理

游标的定义与使用

• 可对游标 $当前位置$ 进行更新、查询、删除
• 需要经历五个步骤：
• 定义游标：
  
• 打开游标：
  
  （打开游标表示：系统按照 游标的定义 （定义游标时查询语句不执行）从数据库中将数据检索出来，放在内存的游标集中，并为该游标集指定一个游标（游标名即为我们定义的名字），该游标指向游标集中的第一个元组之前，fetch一次之后，游标指向第一个元组）（所以每次打开游标都会执行一次查询，并占用内存，所以需要关闭）
• 获取游标中的值：
  
• 关闭、释放游标：
  

游标使用例子

【例1】创建一个游标，逐行显示 选修了《计算机原理》课程 的学生 姓名、成绩、和选课学期，最后显示该课程的平均分。

• 选取操作

declare mycur cursor for
	select studentName, score, termNo
	from Student a, Course b, Score c
	where a.studentNo = b.studentNo 
		and b.courseNo = c.courseNo
		and courseName = '计算机原理'
	order by studentName   -- 可能有同学 重修情况

• 要获得平均分，必须首先计算选课人数 和 总分，即，声明一个计数变量@countScore 和一个 累加变量@sumScore，初始值为0

declare @countScore int, @sumScore int
set @countScore = 0
set @sumScore = 0

• 声明三个变量 @sName @score @termNo，接收游标集中当前游标的值

declare @sName varchar(10), @score int, @termNo char(3)

• 因为Fetch 命令一次仅从游标集中提取一条记录，并将游标移到下一条记录上。所以必须通过循环来重复提取。（$@@FETCH\_STATUS=0$ 表 示 正确提取）

-- 逐行处理记录，统计总分和总人数
open mycur
fetch mycur into @sName, @score, @termNo

while (@@FETCH_STATUS = 0)
	begin
	print convert(char(10), @sName) + convert(char(10), @score) + convert(char(10), @termNo)
	set @sumScore = @sumScore + @score
	set @countScore = @countScore + 1
	fetch mycur into @sName, @score, @termNo
	end
-- 如果人数为0， 则显示0.00
if @countScore > 0
	print @sumScore / @countScore
else
	print 0.00

• 处理完全部游标记录后，必须关闭和释放游标

close mycur
deallocate mycur

---

当前游标集的修改与删除

• $对游标当前行的修改和删除，都会转化为对基本表的更新$
• 删除游标集中的当前行
  (删除一行后，游标定位于被删除行的下一行）
  DELETE FROM <tableName> WHERE CURRENT OF <cursorName>
• 修改游标集中的当前行

update <tableName>
set <columnName> = <expr> [, <columnName> = <expr2>...]
where current of <cursorName>

【例1】将选修了《高等数学》课程且成绩不及格的学生选课记录显示出来，并从数据库中删除该选课记录。
（思路：因为需要显示出来，所以可以选择使用游标，显示后删除，可以直接对游标集进行删除操作）

• 定义游标：

-- 定义游标
declare mycur cursor for
	select studentName, score
	from Student a, Course b, Score c
	where a.studentNo = c.studentNo
			and b.courseNo = c.courseNo
			and courseName = '高等数学'
			and score < 60
-- 定义变量接收值
declare @sName varchar(10), @score int

-- 打开游标
open mycur
-- 此时游标指向游标集中第一个元组之前
-- fetch 获取值
fetch mycur into @sName, @score
-- 此时 游标指向游标集中第一个元组
while(@@FETCH_STATUS = 0)
	BEGIN
		-- 显示变量 @sName 和 变量@score的值
		SELECT @sName, @score
		-- 删除当前游标所指的选课记录
		delete from Score where current of mycur
		-- 此时游标指向下一条元组之前，同open游标时情况相同，等待fetch
		fetch mycur into @sName,  @score
		-- 此时游标指向被删除的元组的下一条记录
	END
close mycur
deallocate mycur

• 注意游标指向的变化，关于open，fetch，delete对于游标指向的问题有各种说法，上述过程逻辑合理。