实验七 T-SQL语句高级应用（一）

最新推荐文章于 2023-05-24 22:30:56 发布

原创最新推荐文章于 2023-05-24 22:30:56 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql 数据库 #sql server #数据 #sql

数据库专栏收录该内容

9 篇文章

订阅专栏

本文通过四个实验任务详细介绍了T-SQL游标的高级应用，包括如何使用游标输出学生姓名、选修课程名称和成绩，统计各学院教师开设课程的选修率，计算学生期末成绩等级，以及使用存储过程查看游标属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实验七 T-SQL语句高级应用（一）

1、实验目的

（1）掌握游标、大对象类型数据的基本概念

（2）掌握游标处理结果集的基本过程

2、实验内容

（1）使用游标输出学生姓名、选修课程名称和期末考试成绩，形成报表格式。

declare @sname nchar(10)

declare @cname nchar(10)

declare @final nchar(10)

DECLARE Stu2 CURSOR STATIC

FOR

SELECT sname,cname,final

FROM student,course,score

WHERE student.studentno = score.studentno and

course.courseno = score.courseno

ORDER BY score.studentno

open Stu2

fetch from Stu2

into @sname,@cname,@final

print @sname + @cname + @final

while @@fetch_status = 0

begin

fetch next from Stu2

into @sname,@cname,@final

print @sname + @cname + @final

end

close Stu2

deallocate Stu2

（2）使用游标统计每个学院教师所开课程的选修率。

declare @num float

declare @monitor nchar(30)

declare @all float

declare @avg float

set @num = (select count(*)from student)

declare cou cursor static

for

select monitor,count(*) as '人数'

from student,class

where class.classno = student.classno

group by monitor

open cou

fetch cou

into @monitor,@all

set @avg = @all / @num

print @monitor + cast(@avg as nchar(8))

while @@fetch_status = 0

begin

fetch next from cou

into @monitor,@all

set @avg = @all / @num

print @monitor + cast(@avg as nchar(8))

end

close cou

deallocate cou

declare @department nchar(30)

declare @num int

declare @avg float

declare cur cursor static

for

select department,count(*) as '选修课数'

from class

where

class.classno in (select student.classno from studentgroup byclassno)

group by department

open cur

fetch cur

into @department,@num

set @avg = @num / (select count(*)from class where department =@department)

print @department

print @avg

while @@fetch_status = 0

begin

fetch next from cur

into @department,@num

set @avg = @num / (select count(*)from class where department =@department)

print @department

print @avg

end

close cur

deallocate cur

（3）使用游标计算学生期末成绩的等级。

等级分为：90—100 优秀，80-90 良好，70-80，中等，60-70 及格，60以下不及格。

declare @sname nchar(30)

declare @cname nchar(30)

declare @final float

declare stu cursor static

for

select sname,final,cname

from student,score,course

where student.studentno = score.studentno and

course.courseno = score.courseno

open stu

fetch stu

into @sname,@final,@cname

if @final >=90

print N'优 ' + @sname + @cname

else if @final >=80 and @final <90

print N'良'+ @sname + @cname

else if @final >=70 and @final <80

print N'中'+ @sname + @cname

else if @final >=60 and @final <70

print N'及'+ @sname + @cname

else if @final <60

print N'差'+ @sname + @cname

while @@fetch_status = 0

begin

fetch next from stu

into @sname,@final,@cname

if @final >=90

print N'优'+ @sname + @cname

else if @final >=80 and @final <90

print N'良'+ @sname + @cname

else if @final >=70 and @final <80

print N'中'+ @sname + @cname

else if @final >=60 and @final <70

print N'及'+ @sname + @cname

else if @final <60

print N'差'+ @sname + @cname

end

close stu

deallocate stu

（4）使用存储过程查看游标属性，理解各列取值的含义。

declare @final2 float

declare @final1 float

declare stu111 cursor

for

select final,final

from score

declare @stu111 cursor

exec teaching1.dbo.sp_cursor_list

@cursor_return = @stu111 output,@cursor_scope = 2

open stu111

fetch stu111

into @final1,@final1

while @@fetch_status = 0

begin

fetch next from @stu111

end

close stu111

deallocate stu111

close @stu111

deallocate @stu111

（5）创建一个文本文件（test101.txt）并将数据利用指定格式符（/）进行分隔排列，文件内容如下：

102512107/c05109/62.00/82.00

1137221508/c08109/95.00/90.00

1137221508/c08123/89.00/85.00

使用BULK INSERT命令将文件插入score表中。

BULK INSERT score

FROM 'D:test.txt'

WITH(FIELDTERMINATOR ='/')