[大数据]数据库上机实验三（游标）

关于数据库实验一、二和数据库的创建请参考此处
什么叫游标？游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
游标可以类比于C语言文件中的指针，可以根据需要使得选择对应的行。
游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
这些都是固定的格式，对于游标使用还算是比较常规的。
声明游标：

declare st_cursor cursor 
for select colum from table where ...

打开游标：

open st_cursor

读取游标

fetch next from into @你的变量

关闭游标

close st_cursor

释放游标

deallocate st_cursor

本次实验内容是实验三+《数据库原理及应用》书P199 第12-14习题

1.对各出版社的图书比例情况进行分析，即图书比例高于50%为“很高”，图书 比例高于30%为“较高”，图书比例高于10%为“一般”，并按图书比例递增排列

-- Test3-1
use Library2128
select
	出版社,cast(cast(cast(count(出版社) as decimal(4,1))/cast((select count(*)
from
	book2128) as decimal(4,1))*100 as decimal(4,1)) as varchar(5)) +'%'  百分比,
case
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.5 then '很高'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.3 then '较高'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.1 then '一般'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))<0.1 then '较低'
end 比例
from book2128
group by 出版社
order by cast(count(出版社) as decimal(4,1))/cast((select count(*)from book2128) as 	decimal(4,1))

2.对各系学生的借书比例情况进行分析，即图书比例高于50%的为“很高”，图 书比例高于30%的为“较高”，图书比例高于10%的为“一般”，并按借书比例递减 排序

-- Test3-2
select
	出版社,cast(cast(cast(count(出版社) as decimal(4,1))/cast((select count(*)
from
	book2128) as decimal(4,1))*100 as decimal(4,1)) as varchar(5)) +'%'  百分比,
case
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.5 then '很高'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.3 then '较高'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))>0.1 then '一般'
	when cast(count(出版社)as decimal(4,1)) /cast((select count(*)from book2128) as 	decimal(4,1))<0.1 then '较低'
end 比例
from book2128
group by 出版社
order by cast(count(出版社) as decimal(4,1))/cast((select count(*)from book2128) as 	decimal(4,1))
desc

3.采用游标方式对图书价格进行评价

-- Test3-3
declare @bno char(20),@percent char(20),@price int
declare st_cursor cursor
	for select distinct book2128.图书名,book2128.定价
	from book2128
print 'BOOK                      Evaluate'
print '---------------------------------'
open st_cursor
fetch next from st_cursor into @bno,@price
while @@FETCH_STATUS=0
begin
	set @percent= case
	when @price > 50 then 'A'
	when @price > 30 then 'B'
	when @price > 20 then 'C'
	when @price > 10 then 'D'
	else 'E'
	end
print @bno+'  '+@percent
fetch next from st_cursor into @bno,@price
end
close st_cursor
deallocate st_cursor
go

4.采用游标方式统计出每个出版社图书的借出率

-- Test3-4
declare @pub char(20),@percent decimal(4,1)
declare st_cursor cursor
	for select distinct t1.出版社,cast(借阅数*总数 as decimal(4,1)) 借出率
	from
	(
		select count(*) 总数,bk.出版社
		from book2128 bk
		group by bk.出版社
	) t1,
	(
		select bk.出版社,count(bor.图书编号) 借阅数
		from
			borrow2128 bor,book2128 bk
		where bor.图书编号=bk.图书编号
		group by bk.出版社
	) t2
	where t1.出版社=t2.出版社
open st_cursor
fetch next from st_cursor into @pub,@percent
print '出版社                   借出率'
print '----------------------------'
while @@FETCH_STATUS=0
begin
	print @pub+'    '+(CAST(@percent As char(5)))+'%'
	fetch next from st_cursor into @pub,@percent
end
close st_cursor
deallocate st_cursor
go

5.编写一个程序，采用游标方式输出所有课程的平均分

-- P199-12
set nocount  on
declare  @sclass nvarchar(10),@savg float
declare st_cursor cursor
    for select c.课程名,avg(s.分数)
         from course2128 c,score2128 s
         where c.课程号=s.课程号
         group by (c.课程名)
open st_cursor
fetch  next from st_cursor  into @sclass,@savg
print 'class       Avg'
print '-------------'
while @@fetch_status=0
begin
    print  cast(@sclass as nvarchar(8))+'  '+
           cast(@savg as nvarchar(10))
    fetch next  from st_cursor into @sclass,@savg
end
close st_cursor
deallocate  st_cursor
go

6.编写一个程序，采用游标方式输出所有学号、课程号和成绩等级

-- P199-13
declare @fs int,@dj nvarchar(2),@sno int,@cno nvarchar(10)
declare st_cursor cursor
    for select 学号,课程号,分数
        from score2128
        where 分数 is not null
open st_cursor
fetch next from st_cursor into @sno,@cno,@fs
print 'NUM     CLASS     SCORE'
print '-----------------------'
while @@fetch_status=0
begin
    set @dj=case
        when @fs>=90 then 'A'
		when @fs>=80 then 'B'
		when @fs>=70 then 'C'
		when @fs>=60 then 'D'
		else 'E'
end
print cast(@sno as char(8))+' '+@cno+'    '+@dj
	fetch next from st_cursor into @sno,@cno,@fs
end
close st_cursor
deallocate st_cursor
go

7.编写一个程序，采用游标方式输出各班级各课程的平均分

--P199 -14
declare @sclass char(10),@savg float
declare st_cursor cursor
	for select st.班号,avg(sc.分数)
		from student2128 st,score2128 sc
		where st.学号=sc.学号
		group by st.班号
open st_cursor
fetch next from  st_cursor into @sclass,@savg
print 'Class      Score'
print '-----------------'
while @@fetch_status=0
begin
	print @sclass+cast(@savg as char(10))
	fetch next from st_cursor into @sclass,@savg
end
close st_cursor
deallocate st_cursor
go