2025年【T-SQL】功能使用实例（变量定义赋值、函数、游标、流程控制）

大家好，我是讯享网，很高兴认识大家。

一、T-SQL：transact SQL，提供了变量定义，赋值操作，流程控制，函数等语句供用户使用

背景知识：
一、变量定义：declare @标识符类型
二、变量赋值：set @变量1 = value1，select @变量1 = value1，@变量2 = value2
三、流程控制：流程空值和程序设计语言中的流程控制类似，流程空值语句是SQL提供给用户用于改变语句执行顺序的控制语句
四、函数：包括系统函数和用户自定义函数
五、游标：可以被视作一个指针，可以指向结果集中的任意一个位置并对其进行处理

名称解释（不区分大小写）

student学生表：
- 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、birthday生日
sc选课信息表：
- 包含属性列：sno学号、cno课程号、grade成绩
dept学院信息表：
- 包含属性列：dno学院编号、dname学院名称、dean学院负责人
course课程信息表：
- 包含属性列：cno课程号、cname课程名称、tname老师名称、credit学分、room教室

下面语句可以直接复制到SQL Server运行

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

运行方法，选中需要执行的语句
讯享网

二、代码

–系统数据类型
declare @Lee int
set @Lee = 32
select @Lee
print @Lee
select @@SERVERNAME --返回SQL服务器名称
select @@LANGUAGE --返回当前使用语言名
select @@VERSION --返回SQL服务器安装日期、版本和处理器类型
print APP_NAME() --返回当前会话应用程序
print USER_NAME() --返回用户数据库用户名
print GETDATE() --返回当前时间
print DATENAME(YYYY ,GETDATE())
– 补:返回年份print DATENAME(YYYY,GETDATE()) 或 select DATENAME(YYYY,GETDATE())

– 日期函数
print DATENAME(YYYY,GETDATE())
print DATEPART(MM,GETDATE())

–定义一个实型变量，并将其值输出
declare @f float
set @f = 456.26
print cast(@f as varchar(12))

–定义一个字符变量，并将其处理后输出
declare @mynumber char(20)
set @mynumber = ‘test’
select ‘compute’ + @mynumber as ‘计算结果’

–根据授课班号自定义变量，查询符合要求的学生成绩
declare @cn char(20),@f float
select @cn = ‘’,@f = 84
select * from sc where cno = @cn and grade >= @f

–BEGIN……END……的使用
begin
declare @myvar float
set @myvar = 1233.23
begin
print(‘变量@myval 的值为’)
print cast(@myvar as char(20))
end
end

–利用 CASE 查看学生的成绩等级
select sno,cno,
(case
when grade >= 80 then ‘A’
when grade >= 70 then ‘B’
when grade >= 60 then ‘C’
else ‘D’
end)
as 等级
from sc

–创建一个视图，统计每个学生的学习情况。若其平均成绩超过 90，则其学习情况为优秀；若其平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view study_view
as(
select sc.sno,sname,cno,
case
when (select avg(grade) from sc where sc.sno = student.sno) > 90 then ‘优秀’
when (select avg(grade) from sc where sc.sno = student.sno) > 80 then ‘良好’
when (select avg(grade) from sc where sc.sno = student.sno) > 70 then ‘中等’
when (select avg(grade) from sc where sc.sno = student.sno) > 60 then ‘合格’
else ‘不合格’
end
from student)

–利用 WHILE 结构，求:1+2+3+…+100 的值
declare @s int,@j int
select @j=0,@s=0
while(@j<=100)
begin
select @s = @s + @j,@j = @j + 1
end
print '1+2+3+4+5+6+…+100 = ’ + cast(@s as char)

–利用 IF…ELSE，查询课程号为’’的学生的总平均成绩，如果大于 90，输出优秀，在 80-90 之间，输出优良，其它输出一般
declare @avg int
select @avg = avg(grade) from sc where sno = ‘’
if @avg > 90
print ‘优秀’
else
begin
if @avg > 80
print ‘优良’
else
print ‘一般’
end

–定义一函数，实现如下功能，对于一给定的学号 studentSNO，查询该值在 student 中是否存在，存在返回 1，不存在返回 0。
create function check_id
(@sno char(8))
returns integer
as
begin
declare @num
if(exists(select * from student where sno = @sno))
set @num = 1
else
set @num = 0
return @num
end

–使用下面的 SQL 语句调用第 9 题的函数。要求：当向表 student 中插入一条记录时，首先调用函数 check_id，检查该记录的学号在表 student 中是否存在，不存在，才可以插入。
declare @num int
select @num = XSGL.check_id(‘’)
if @num = 0
insert into student(sno,sname) values(‘’,‘张文’)
–求学生选课的门数，列出其姓名及选课门数
select sname,(select count() from sc where sc.sno = student.sno)as ‘选课门数’ from student
–根据课程号动态查询学生的选课人数
–可以利用变量以及字符串连接字符‘＋’，动态生成 SQL 语句，达到依据条件，进行数据库动态查询目的。其中，连接字符串中若包含单引号’字符，则必须用两个’’,表示一个单引号’字符。
DECLARE @cno char(8)
DECLARE @sql varchar(8000)
SET @cno=‘’
–动态生成 SQL 语句
SET @sql='SELECT COUNT() FROM sc WHERE CNO=‘’‘+@cno+’‘’’
EXEC(@sql)
SET @cno=‘’
SET @sql=’ SELECT COUNT(*) FROM sc WHERE CNO=‘’‘+@cno+’‘’’
EXEC (@sql)

–用游标结合循环，输出全校各种姓氏及其人数
–定义记录数和循环变量
declare @count int,@i int
–定义姓氏和人数
declare @xs char(6),@rs int
–定义游标
declare @cursor cursor
–为游标赋值
set @cursor = cursor local scroll for
select distinct substring(sname,1,1) as xs,count(*) as 人数 from student
group by substring(sname,1,1)
–变量初始化
select @count=0,@i=0
–打开游标
open @cursor
–@@cursor_rows是系统变量，用于记录游标所指向的结果集的行数
set @count = @@cursor_rows
–打印记录数
print ‘人数’ + cast(@count as char)
–对游标中记录进行操作
if(@count <= 0)
begin
print(‘没有记录’)
end
else
begin
while(@i < @count)
begin
fetch next from @cursor into @xs,@rs
print cast(@i as char) + @xs + cast(@rs as char)
set @i = @i + 1
end
–关闭游标
close @cursor
end

–利用游标结合循环，统计学院各种姓氏的人数
DECLARE @count int
DECLARE @i int
DECLARE @xing char(6)
DECLARE @rs int
DECLARE @sql varchar(8000)
SET @sql=‘’
SET @i=0
DECLARE @name_cursOR CURSOR
SET @name_cursOR =CURSOR LOCAL SCROLL FOR
SELECT DISTINCT SUBSTRING(SNAME,1,1) AS xing,count() AS rs
FROM student
GROUP BY SUBSTRING(SNAME,1,1)
OPEN @name_cursOR
SET @count=@@CURSOR_ROWS
IF @count<= 0
BEGIN
PRINT ‘没有记录’
END
ELSE
BEGIN
WHILE(@i<@count-300)
BEGIN
FETCH @name_cursOR INTO @xing,@rs
–动态生成前 300 个姓氏 SQL 语句
SET @sql=@sql+'(SELECT COUNT() FROM student WHERE DNO=a.DNO
AND SUBSTRING(SNAME,1,1)=‘’‘+@xing+’‘’) AS ‘+@xing+’,’
SET @i=@i+1
END
FETCH @name_cursOR INTO @xing,@rs
–动态生成第 301 个姓氏 SQL 语句
SET @sql=@sql+‘(SELECT count(*) FROM student WHERE DNO=a.DNO
AND SUBSTRING(SNAME,1,1)=’‘’+@xing+‘’') AS '+@xing
CLOSE @name_cursOR
PRINT @sql
SET @sql=‘SELECT DNAME ,’+@sql+‘FROM (SELECT DISTINCT
STUDENT.DNO,DNAME FROM student,DEPT WHERE student.DNO=DEPT.DNO) AS a’
EXEC(@sql)
END

–利用 CASE 实现学生表中学院编号到学院名称的映射
select sno,sname,
(case
when substring(sno,5,1) = ‘1’ then ‘机电学院’
when substring(sno,5,1) = ‘2’ then ‘信息学院’
when substring(sno,5,1) = ‘3’ then ‘工商学院’
end) as 学院
from student

–定义一函数，实现如下功能，对于一给定的学号 studentSNO 和课程号 studentCNO 查询该值在 student 和 course 中是否存在，存在返回 1，不存在返回 0。
create function check
(@sno char(8),@cno char(8))
returns integer
as
begin
declare @num int
if(exists(select * from student where sno = @sno) and exists(select * from course where cno = @cno))
set @num = 1
else
set @num = 0
return @num
end

–根据教师名自定义变量，查询符合要求的教师授课情况
declare @tname char(8)
set @tname = ‘张聪’
select * from course where tname = @tname

–定义一函数，根据学号返回学生的选课门数（参考 INSERT 触发器）
create function choose_lesson
(@sno char(8))
returns integer
as
begin
declare @count int
select @count = count(*) from sc where sno = @sno
return @count
end

–修改学生的成绩，若大于 80 分，增加 5 分，否则，增加 8 分
update sc
set grade = (
case
when(grade > 80) then grade + 5
else grade + 8
end
)

–（1）按课程名称，统计其平均分，列出其课程名称和平均分
declare @cname char(8)
set @cname = ‘线性代数’
select cname,course.cno,avg(grade) from sc,course where cname = @cname group by cname,course.cno
select * from course where cname = ‘线性代数’

–（3）定义一函数，依据学生的姓名，查询其所选课程的门数
create function lesson_count
(@sname char(8))
returns integer
as
begin
declare @count int
select @count = count(*) from sc where sno in (select sno from student where sname = @sname)
return @count
end

–（4）根据学院名称，统计学生人数，列出学院名称和学生人数
declare @dname char(16)
set @dname = ‘机电工程学院’
select dname,count(sno) as ‘人数’
from dept,student
where dname = @dname and dept.dno = student.dno
group by dname

–（5）若存在学号为‘’的学生，则显示其姓名，否则，显示相应提示信息
declare @sno char(8)
set @sno = ‘’
begin
if(exists(select * from student where sno = @sno))
select * from student where sno = @sno
else
print(‘不存在学生’)
end

–（6）查找每个学生超过他选修课程平均成绩的课程相关信息，列出学号，课程号，成绩，选课平均成绩
select sno,cno,grade,avg(grade) 平均成绩 from sc a
where grade >= (
select avg(grade) from sc b
where a.sno = b.sno)
group by sno,cno,grade

–（7）创建一视图，统计每门课程的学习情况。若课程平均成绩超过 90，则其学习情况为优秀；若课程平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。
create view count_view
as
select cno,
(case
when avg(grade) > 90 then ‘优秀’
when avg(grade) > 80 then ‘良好’
when avg(grade) > 60 then ‘合格’
else ‘不合格’
end)
from sc
group by cno

–（8）利用游标结合循环，统计各门课程的各种分数的人数。
select * from sc
order by cno

declare @i int,@count int
declare @cursor cursor
declare @rs int,@chengji float,@cno char(20)
declare @sql char(8000)
select @i=0,@count=0,@sql=‘’
set @cursor = cursor local scroll for
select cno,grade,count(*) as 人数 from sc
group by cno,grade
order by cno
open @cursor
set @count = @@cursor_rows
print @count

if @count <= 0
begin
print ‘没有记录’
end
else
begin
while @i < @count
begin
print cast(@i as char) + @cno + cast(@chengji as char) + cast(@rs as char)
set @i = @i + 1
end
close @cursor
end

三、代码块

--系统数据类型 declare @Lee int set @Lee = 32 select @Lee print @Lee select @@SERVERNAME --返回SQL服务器名称 select @@LANGUAGE --返回当前使用语言名 select @@VERSION --返回SQL服务器安装日期、版本和处理器类型 print APP_NAME() --返回当前会话应用程序 print USER_NAME() --返回用户数据库用户名 print GETDATE() --返回当前时间 print DATENAME(YYYY ,GETDATE()) -- 补:返回年份print DATENAME(YYYY,GETDATE()) 或 select DATENAME(YYYY,GETDATE()) -- 日期函数 print DATENAME(YYYY,GETDATE()) print DATEPART(MM,GETDATE()) --定义一个实型变量，并将其值输出 declare @f float set @f = 456.26 print cast(@f as varchar(12)) --定义一个字符变量，并将其处理后输出 declare @mynumber char(20) set @mynumber = 'test' select 'compute' + @mynumber as '计算结果' --根据授课班号自定义变量，查询符合要求的学生成绩 declare @cn char(20),@f float select @cn = '',@f = 84 select * from sc where cno = @cn and grade >= @f --BEGIN……END……的使用 begin declare @myvar float set @myvar = 1233.23 begin print('变量@myval 的值为') print cast(@myvar as char(20)) end end --利用 CASE 查看学生的成绩等级 select sno,cno, (case when grade >= 80 then 'A' when grade >= 70 then 'B' when grade >= 60 then 'C' else 'D' end) as 等级 from sc --创建一个视图，统计每个学生的学习情况。若其平均成绩超过 90，则其学习情况为优秀；若其平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。 create view study_view as( select sc.sno,sname,cno, case when (select avg(grade) from sc where sc.sno = student.sno) > 90 then '优秀' when (select avg(grade) from sc where sc.sno = student.sno) > 80 then '良好' when (select avg(grade) from sc where sc.sno = student.sno) > 70 then '中等' when (select avg(grade) from sc where sc.sno = student.sno) > 60 then '合格' else '不合格' end from student) --利用 WHILE 结构，求:1+2+3+..+100 的值 declare @s int,@j int select @j=0,@s=0 while(@j<=100) begin select @s = @s + @j,@j = @j + 1 end print '1+2+3+4+5+6+..+100 = ' + cast(@s as char) --利用 IF…ELSE，查询课程号为''的学生的总平均成绩，如果大于 90，输出优秀，在 80-90 之间，输出优良，其它输出一般 declare @avg int select @avg = avg(grade) from sc where sno = '' if @avg > 90 print '优秀' else begin if @avg > 80 print '优良' else print '一般' end --定义一函数，实现如下功能，对于一给定的学号 studentSNO，查询该值在 student 中是否存在，存在返回 1，不存在返回 0。 create function check_id (@sno char(8)) returns integer as begin declare @num if(exists(select * from student where sno = @sno)) set @num = 1 else set @num = 0 return @num end --使用下面的 SQL 语句调用第 9 题的函数。要求：当向表 student 中插入一条记录时，首先调用函数 check_id，检查该记录的学号在表 student 中是否存在，不存在，才可以插入。 declare @num int select @num = XSGL.check_id('') if @num = 0 insert into student(sno,sname) values('','张文') --求学生选课的门数，列出其姓名及选课门数 select sname,(select count(*) from sc where sc.sno = student.sno)as '选课门数' from student --根据课程号动态查询学生的选课人数 --可以利用变量以及字符串连接字符‘＋’，动态生成 SQL 语句，达到依据条件，进行数据库动态查询目的。其中，连接字符串中若包含单引号’字符，则必须用两个’’,表示一个单引号’字符。 DECLARE @cno char(8) DECLARE @sql varchar(8000) SET @cno='' --动态生成 SQL 语句 SET @sql='SELECT COUNT(*) FROM sc WHERE CNO='''+@cno+'''' EXEC(@sql) SET @cno='' SET @sql=' SELECT COUNT(*) FROM sc WHERE CNO='''+@cno+'''' EXEC (@sql) --用游标结合循环，输出全校各种姓氏及其人数 --定义记录数和循环变量 declare @count int,@i int --定义姓氏和人数 declare @xs char(6),@rs int --定义游标 declare @cursor cursor --为游标赋值 set @cursor = cursor local scroll for select distinct substring(sname,1,1) as xs,count(*) as 人数 from student group by substring(sname,1,1) --变量初始化 select @count=0,@i=0 --打开游标 open @cursor --@@cursor_rows是系统变量，用于记录游标所指向的结果集的行数 set @count = @@cursor_rows --打印记录数 print '人数' + cast(@count as char) --对游标中记录进行操作 if(@count <= 0) begin print('没有记录') end else begin while(@i < @count) begin fetch next from @cursor into @xs,@rs print cast(@i as char) + @xs + cast(@rs as char) set @i = @i + 1 end --关闭游标 close @cursor end --利用游标结合循环，统计学院各种姓氏的人数 DECLARE @count int DECLARE @i int DECLARE @xing char(6) DECLARE @rs int DECLARE @sql varchar(8000) SET @sql='' SET @i=0 DECLARE @name_cursOR CURSOR SET @name_cursOR =CURSOR LOCAL SCROLL FOR SELECT DISTINCT SUBSTRING(SNAME,1,1) AS xing,count(*) AS rs FROM student GROUP BY SUBSTRING(SNAME,1,1) OPEN @name_cursOR SET @count=@@CURSOR_ROWS IF @count<= 0 BEGIN PRINT '没有记录' END ELSE BEGIN WHILE(@i<@count-300) BEGIN FETCH @name_cursOR INTO @xing,@rs --动态生成前 300 个姓氏 SQL 语句 SET @sql=@sql+'(SELECT COUNT(*) FROM student WHERE DNO=a.DNO AND SUBSTRING(SNAME,1,1)='''+@xing+''') AS '+@xing+',' SET @i=@i+1 END FETCH @name_cursOR INTO @xing,@rs --动态生成第 301 个姓氏 SQL 语句 SET @sql=@sql+'(SELECT count(*) FROM student WHERE DNO=a.DNO AND SUBSTRING(SNAME,1,1)='''+@xing+''') AS '+@xing CLOSE @name_cursOR PRINT @sql SET @sql='SELECT DNAME ,'+@sql+'FROM (SELECT DISTINCT STUDENT.DNO,DNAME FROM student,DEPT WHERE student.DNO=DEPT.DNO) AS a' EXEC(@sql) END --利用 CASE 实现学生表中学院编号到学院名称的映射 select sno,sname, (case when substring(sno,5,1) = '1' then '机电学院' when substring(sno,5,1) = '2' then '信息学院' when substring(sno,5,1) = '3' then '工商学院' end) as 学院 from student --定义一函数，实现如下功能，对于一给定的学号 studentSNO 和课程号 studentCNO 查询该值在 student 和 course 中是否存在，存在返回 1，不存在返回 0。 create function check (@sno char(8),@cno char(8)) returns integer as begin declare @num int if(exists(select * from student where sno = @sno) and exists(select * from course where cno = @cno)) set @num = 1 else set @num = 0 return @num end --根据教师名自定义变量，查询符合要求的教师授课情况 declare @tname char(8) set @tname = '张聪' select * from course where tname = @tname --求授课班号及选修该授课班号的学生人数 select cno,(select count(*) from sc where sc.cno = course.cno) as 选修人数 from course --定义一函数，根据学号返回学生的选课门数（参考 INSERT 触发器） create function choose_lesson (@sno char(8)) returns integer as begin declare @count int select @count = count(*) from sc where sno = @sno return @count end --修改学生的成绩，若大于 80 分，增加 5 分，否则，增加 8 分 update sc set grade = ( case when(grade > 80) then grade + 5 else grade + 8 end ) --（1） 按课程名称，统计其平均分，列出其课程名称和平均分 declare @cname char(8) set @cname = '线性代数' select cname,course.cno,avg(grade) from sc,course where cname = @cname group by cname,course.cno select * from course where cname = '线性代数' --（2） 求每个学生选课的门数及其平均分，列出其姓名、课程门数及平均分 select sname,student.sno,count(cno),avg(grade) from sc,student group by sname,student.sno --（3） 定义一函数，依据学生的姓名，查询其所选课程的门数 create function lesson_count (@sname char(8)) returns integer as begin declare @count int select @count = count(*) from sc where sno in (select sno from student where sname = @sname) return @count end --（4） 根据学院名称，统计学生人数，列出学院名称和学生人数 declare @dname char(16) set @dname = '机电工程学院' select dname,count(sno) as '人数' from dept,student where dname = @dname and dept.dno = student.dno group by dname --（5） 若存在学号为‘’的学生，则显示其姓名，否则，显示相应提示信息 declare @sno char(8) set @sno = '' begin if(exists(select * from student where sno = @sno)) select * from student where sno = @sno else print('不存在学生') end --（6） 查找每个学生超过他选修课程平均成绩的课程相关信息，列出学号，课程号，成绩，选课平均成绩 select sno,cno,grade,avg(grade) 平均成绩 from sc a where grade >= ( select avg(grade) from sc b where a.sno = b.sno) group by sno,cno,grade --（7） 创建一视图，统计每门课程的学习情况。若课程平均成绩超过 90，则其学习情况为优秀；若课程平均成绩在 80 和 90 之间，则其学习情况为优秀良好；依次类推。 create view count_view as select cno, (case when avg(grade) > 90 then '优秀' when avg(grade) > 80 then '良好' when avg(grade) > 60 then '合格' else '不合格' end) from sc group by cno --（8） 利用游标结合循环，统计各门课程的各种分数的人数。 select * from sc order by cno declare @i int,@count int declare @cursor cursor declare @rs int,@chengji float,@cno char(20) declare @sql char(8000) select @i=0,@count=0,@sql='' set @cursor = cursor local scroll for select cno,grade,count(*) as 人数 from sc group by cno,grade order by cno open @cursor set @count = @@cursor_rows print @count if @count <= 0 begin print '没有记录' end else begin while @i < @count begin print cast(@i as char) + @cno + cast(@chengji as char) + cast(@rs as char) set @i = @i + 1 end close @cursor end

讯享网