文章目录
7.4 T-SQL语言简介
7.4.1 变量
1、变量分类
局部变量
:变量名前加1个@符号全局变量
:变量名前加2个@@符号。如:- @@ERROR:当事务成功时为0,否则为最近一次的错误号
- @@ROWCOUNT:返回受上一语句影响的行数
- @@FETCH_STATUS:返回最近的FETCH语句执行后的游标状态
2、变量的声明与赋值
- 声明变量的语法
DECLARE <@variableName> <datatype> [, <@variableName> <datatype> … ]
declare @var char(7),@score numeric
- 单个变量赋值的语法:
SET <@variableName> = <expr>
- 变量列表赋值(或显示表达式的值)的语法:
SELECT <@variableName> [= <expr | columnName>]
[, <@variableName> [= <expr | columnName>] … ]
7.4.2 运算符
名称 | 符号 |
---|---|
算术运算符 | +,-,*,/,%(取余) |
比较运算符 | >,>=,<,<=,=,<>,!= |
逻辑运算符 | AND,OR,NOT |
位运算符 | &(按位与) |(按位或) ~(按位非) ^(按位异或) |
字符串连接运算符 | + |
赋值语句 | SELECT:一次可赋值多个变量,或显示多个表达式的值 SET:一次仅能给一个变量赋值 |
7.4.3 函数
数学函数、字符串函数、日期和时间函数、聚合函数和系统函数等
1、数学函数
- 绝对值函数abs
- 随机数函数rand
- 四舍五入函数round
- 上取整函数ceiling
- 下取整函数floor
- 指数函数exp
- 平方根函数sqrt等
2、字符串函数
3、日期和时间函数
4、系统函数
convert(data_type [(length)], expr [, style])
data_type
:系统所提供的数据类型length
:字符数据类型的可选参数,用于控制字符串的长度expr
:任何有效的SQL Serve表达式style
:日期格式样式(详见表7-4)。- 例如,经常将datetime数据或数值数据表达式expr转换为字符数据类型data_type,然后可用于字符串的连接输出
7.4.4 流程控制语句
关键字 | 功能描述 |
---|---|
begin...end | 定义语句块 |
break | 退出当前层的while循环 |
case when [else] end | 多分支语句 |
continue | 重新开始当前层的while循环 |
goto label | 将程序流程转向到标号label处继续执行 |
if [else] | 分支(选择)语句 |
return | 无条件退出 |
waitfor | 为语句的执行设置延迟 |
while | 循环语句 |
7.4.5 程序实例
[例7.39] 声明两个局部变量@sno和@score,用于接受
SELECT语句查询返回的结果,并显示其结果。
declare @son char(7),@score numeric
select @sno=a.studentNo,@score=score
from Score a,Student b
where a.studentNo=b.studentNo
and courseNo='005' and studentName='刘方晨'
if @@rowcount=0
print 'Warning: No rows were selected'
else
select @sno,@score
[例7.45] 在学生表Student中,如果有蒙古族学生,则显示:存在蒙古族的学生。
if exists(select *from Student Where nation='蒙古族')
print '存在蒙古族的学生'
[例7.46] 列示成绩表Score中的所有选课记录,要求根据学期号termNo的不同取值分别显示开课时间为xx年下半年、
xx年上半年、xx年暑期小学期,根据成绩score的不同取值分别显示等级为优良(80分及以上)、合格和不及格(小于60
分)。如‘152’显示为“16年上半年”。
select studentNo 学号,course 课程号
case right(termNo,1)
when '1' then left(termNo,2)+'年下半年'
when '2' then str(convert(tinyint,left(termNo,2))+1,2)+'年下半年'
else str(convert(tinyint,left(term,2))+1,2)+'年暑期小学期'
end 开课时间
case
when score>=80 then '优良'
when score>=60 then '合格'
else '不及格'
end 等级
from Score
7.5 游标
对SELECT语句的结果集进行逐行处理,需使用游标。
- 游标(cursor)是系统为用户开设的一个数据缓冲区,用于存放SQL语句的执行结果(元组集合)。
- 每个游标都有一个名字,用户可以用SQL提供的语句从游标中逐一获取元组(记录),并赋给主变量,交由主语言进一步处理。
可对游标的当前位置进行更新、查询和删除,使用游标需要经历5个步骤:
定义游标
:DECLARE打开游标
:OPEN逐行提取游标集中的行
:FETCH关闭游标
:CLOSE释放游标
:DEALLOCATE
7.5.1 游标的定义与使用
1、定义游标
语法为:
DECLARE <cursorName> CURSOR
FOR <SQL-Statements>
[ FOR { READ ONLY | UPDATE [OF <columnName_list>] } ]
在使用游标之前,必须先定义游标。其中:
<cursorName>
:所定义游标的名称;<SQL-Statements>
:游标要实现的功能程序,即SQL子查询;<columnName_list>
:属性列名列表;[ FOR { READ ONLY | UPDATE [OF <columnName_list>] } ]
:READ ONL
Y表示当前游标集中的元组仅可查询,不能修改;UPDATE [OF <columnName_list>]
表示可对当前游标集中的元组进行更新操作。- 如果有
OF <columnName_list>
,表示仅可以对游标集中指定的属性列进行修改操作;缺省为UPDATE
2、打开游标
语法为:
OPEN <curserName>
游标定义后,如果要使用游标,必须要先打开游标。
打开游标操作表示:
- 系统按照游标的定义从数据库中将数据检索出来,放在内存的游标集中(如果内存不够,会放在临时数据库中)
- 为游标集指定一个游标(相当于一个指针),该游标指向游标集中的第1个元组
3、获取当前游标值
获取当前游标值:即获取当前游标所指向元组的值,语法是
FETCH <curserName> INTO <@variableName_list>
- 执行一次该SQL语句,系统将当前游标所指向的元组属性值放到变量中,然后游标自动下移一个元组。
- 当前游标所指向元组的每个属性值必须分别用一个变量来接收,即变量个数、数据类型必须与定义游标中的SELECT子句所定义的属性(或表达式)个数、数据类型相一致。
- 当游标移至尾部,不可再读取游标,必须关闭游标,然后重新打开游标。
- 通过检查全局变量@@FETCH_STATUS来判断是否已读完游标集中所有行(元组)。
@@FETCH_STATUS的值有:
0
:FETCH 语句成功,表示已经从游标集中获取了元组值
-1
:FETCH 语句失败或此行不在结果集中
-2
:被提取的行不存在
4、关闭游标
游标不使用了,必须关闭,其语法为:
CLOSE <curserName>
5、释放游标(集)
关闭游标并没有释放游标所占用的内存和外存空间,必须释放游标,其语法为:
DEALLOCATE <curserName>
6、程序案例
[例7.48] 创建一个游标,逐行显示选修了《计算机原理》课程的学生姓名、相应成绩和该课程的平均分。
/* 声明变量及赋初值 */
DECLARE @sName varchar(20), @score tinyint, @termNo char(3)
DECLARE @countScore smallint, @sumScore int
SET @countScore=0
SET @sumScore=0
-- 定义游标
DECLARE myCur CURSOR FOR
SELECT studentName, score, termNo
FROM Student a, Course b, Score c
WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo
AND courseName='计算机原理‘
ORDER BY studentName
OPEN myCur -- 打开游标,游标指向游标集(查询结果集)的第1个元组
PRINT convert(char(10), '学生姓名')+convert(char(10), '课程成绩')+convert(char(10), '选课学期')
PRINT replicate(‘-’, 30) -- 输出表头信息
--获取当前游标的值(即第1个元组值)放到变量@sName、@score和@termNo中
FETCH myCur INTO @sName, @score, @termNo --获取第1个元组值, 游标下移
WHILE ( @@FETCH_STATUS = 0 ) -- 循环处理游标集中的每一个元组
BEGIN
-- 显示变量@sName、@score和@termNo中的值
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
PRINT replicate(‘-’, 30) -- 输出表格底线
PRINT ‘课程平均分’ -- 输出选修《计算机原理》课程的所有学生的平均分
IF @countScore>0
PRINT @sumScore/@countScore
ELSE -- 选修人数为0,即没有学生选修《计算机原理》课程
PRINT 0.00
CLOSE myCur -- 关闭游标
DEALLOCATE myCur -- 释放游标
7.5.2 当前游标集的修改与删除
1、删除游标集
删除游标集中的当前元组(即游标所指向的元组)
DELETE FROM <tableName>
WHERE CURRENT OF <curserName>
从游标集中删除当前元组后,游标定位于被删除元组的下一行,但还需要用FETCH语句提取该行的值。
2、修改游标集
修改游标集中的当前元组(即游标所指向的元组)
UPDATE <tableName>
SET <columnName> = <expr> [, <columnName> = <expr>... ]
WHERE CURRENT OF <curserName>