试验七

--使用数据库student，完成以下操作，并写出相应的代码
use student
--1.用函数实现：求某个专业选修了某门课程的学生人数，并调用函数求出计算机系“数据库”课程的选课人数。
go
create function st_count(@sdept varchar(10),@cname nvarchar(20))
returns int
begin 
declare @num int
select @num=count(*)
from sc
where sc.sno in (select sno from student where sdept=@sdept) and sc.cno in(select cno from course where cname = @cname)
return @num
end
print dbo.st_count('CS','数据库')
--2.用内嵌表值函数实现：查询某个专业所有学生所选的每门课的平均成绩；调用该函数求出计算机系的所有课程的平均成绩。
go
create function dbo.avg_grade(@sdept varchar(20))
returns table
return select avg(grade) 平均成绩
from sc
where sc.sno in(select sno from student where student.Sdept=@sdept)
select * from dbo.avg_grade('CS')
--3.创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分，调用该函数求出“200515002”的各门课成绩和学分。
go
create function st_cjxf(@sno varchar(20))
returns table
as
return select grade,ccredit,cname
from sc,course
where sc.Sno=@sno and course.cno=sc.cno
select * from dbo.st_cjxf('200515002')

--4.编写一个存储过程，统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，其输入参数是课程号，输出的是各级别人数及及格率，及格率的形式是90.25%，执行存储过程，在消息区显示1号课程的统计信息。
go
create procedure c_cj @cno varchar(20) , @yx_count int output,@lh_count int output,@zd_count int output,@jg_count int output,@jgl varchar(20) output
as
declare @count int
declare @all int
select @all=count(*)
from sc 
where cno=@cno
select @yx_count=count(*) from sc where grade between 90 and 100 and sc.cno=@cno
select @lh_count=count(*) from sc where grade between 80 and 89 and sc.cno=@cno
select @zd_count=count(*) from sc where grade between 70 and 79 and sc.cno=@cno
select @jg_count=count(*) from sc where grade between 60 and 69 and sc.cno=@cno
select @count = @yx_count+@lh_count+@zd_count+@jg_count
select @jgl= cast(@count*1.0/@all*100 as varchar(20))+'%'

declare @yx_count2 int ,@lh_count2 int ,@zd_count2 int ,@jg_count2 int ,@jgl2 varchar(20) 
exec c_cj '1',@yx_count2 output ,@lh_count2 output ,@zd_count2 output ,@jg_count2 output,@jgl2 output
print '优秀人数：'+cast(@yx_count2 as varchar(20))
print '良好人数: '+cast(@lh_count2 as varchar(20))
print '中等人数: '+cast(@zd_count2 as varchar(20))
print '及格人数: '+cast(@jg_count2 as varchar(20))
print '及格率: '+@jgl2
--5.创建一个带有输入参数的存储过程，该存储过程根据传入的学生名字，查询其选修的课程名和成绩，执行存储过程，在消息区显示赵箐箐的相关信息。
go
create procedure cx_st  @name nvarchar(10) 
as 
declare cxst scroll cursor for 
select course.cname,sc.grade
from student,course,sc
where student.sname=@name and sc.sno=student.sno and course.cno=sc.cno
declare @cname varchar(20),@grade int
open cxst
fetch next from cxst into @cname,@grade
while @@FETCH_STATUS=0
begin
print @cname+'  '+cast(@grade as varchar(10))
fetch next from cxst into @cname,@grade
end
close cxst
deallocate cxst
exec cx_st '赵菁菁'
--6.以基本表 course为基础，完成如下操作
--生成显示如下报表形式的游标：报表首先列出学生的学号和姓名，然后在此学生下，列出其所选的全部课程的课程号、课程名和学分；依此类推，直到列出全部学生。
go
declare @sno varchar(20)
declare @sname nvarchar(10)
declare @cname nvarchar(10)
declare @cno varchar(20)
declare @ccrediet int
declare st scroll cursor for
select sno,sname from student
open st
fetch next from st into @sno ,@sname
while @@FETCH_STATUS=0
begin
declare st_xx scroll cursor for
select course.cno,course.cname,course.Ccredit
from course
where course.cno in (select sc.cno from sc
where sc.sno =@sno
)
print '----------------------------------------'
print '学号'+'           '+'姓名'
print @sno+'    '+@sname
print '========================================'
print '课程号'+' '+'课程名'+' '+'学分'
open st_xx
fetch next from st_xx into @cno,@cname,@ccrediet
while @@FETCH_STATUS=0
begin
print @cno+'     '+@cname+'   '+cast(@ccrediet as varchar(20))
fetch next from st_xx into @cno,@cname,@ccrediet
end
close st_xx
deallocate st_xx
fetch next from st into @sno ,@sname
end
close st
deallocate st
--7.请设计一个存储过程实现下列功能：判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分，如果低于平均分，则将其成绩改为平均分，否则输出学号、姓名、班号、课程号、课程名、成绩。（提示：可以在存储过程内部使用游标）。
go
create procedure pd_cj @sdept varchar(20),@cname nvarchar(10),@rank int
as
declare @avg_grade int
select @avg_grade=avg(grade)
from sc,student,course
where student.sdept=@sdept and sc.sno = student.sno  and course.cname=@cname and sc.cno=course.Cno
declare p1 scroll cursor for
select student.sno,student.sname,course.cno,course.cname,sc.grade
from student,course,sc
where student.sdept=@sdept and course.Cname=@cname and sc.sno=student.sno and sc.cno=course.Cno
order by grade desc
declare @sno2 varchar(20),@sname2 nvarchar(10),@cno2 varchar(20),@cname2 varchar(20),@grade2 int
open p1
fetch absolute @rank from p1 into @sno2 ,@sname2 ,@cno2 ,@cname2 ,@grade2
if @grade2 <@avg_grade
begin
update sc
set grade=@avg_grade
where sc.cno=@sno2 and sc.cno = @cno2
end
else if  @grade2>=@avg_grade
begin 
print '学号         姓名  课程号  课程名  成绩'
print '---------------------------------------'
print @sno2 +'  '+'  '+@sname2 +'  '+@cno2 +'  '+@cname2 +'  '+cast(@grade2 as varchar(20))
end
close p1
deallocate p1
exec pd_cj 'CS','数据结构',1

--8.对student数据库设计存储过程，设计程序实现更新某个专业某门课成绩排名为第2的学生的成绩改为90分。（提示可以使用存储过程内部使用游标）
go
create procedure cgscore @sdept varchar(20) , @cname varchar(20)
as
declare scoreleg scroll cursor for
select grade
from sc,student,course
where student.sdept=@sdept and sc.sno=student.sno and course.Cname=@cname and sc.cno=course.cno
order by grade desc
open scoreleg
fetch absolute 2 from scoreleg
update sc
set grade = 90
where current of scoreleg
close scoreleg
deallocate scoreleg
exec cgscore 'IS','数据库'

--9.设计存储过程实现如果某个学生某门课程的成绩>=60，那么就把这门课程的学分加到该学生的总学分（student表的total项）上
go
CREATE PROCEDURE proc_credit @SNo CHAR(6),  @CNo CHAR(6),  @Total INT output
as
DECLARE @Score INT
SELECT @score=grade FROM sc WHERE SNo = @SNo AND CNo = @CNo;
IF @Score >= 60 
begin
declare @credit int
SELECT @credit=ccredit FROM course WHERE CNo = @CNo;
UPDATE student SET total = Total + @Credit WHERE SNo = @SNo;
END 
SELECT @Total = @Total FROM student WHERE SNo = @SNo; 
--10. 现有图书管理数据库， 其中包含如下几个表：
--读者表：reader(学号，姓名，性别，余额)
--借书表：lend（学号，书号，借书日期，应还日期，是否续借）
--欠款表：debt(学号，日期，欠款金额)
--还书表：return(学号，书号，还书日期) 
--请设计一个存储过程实现续借或还书操作，具体要求如下：
--只有没有超期的书才可以续借（借书和续借时间都为30天），并修改应还日期，否则只能还书；还书时删除借书表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，并且对超期图书，按照超期的天数计算出罚款金额（每天每本书罚款0.1元），并将罚款信息插入到欠款表中，同时将罚款从读者表的余额里扣除。
-- 假设数据库是SQL Server，参数是@SNo(学号)和@BNo(书号)
-- 创建一个存储过程，实现续借或还书操作
go
create procedure proc_lend_or_return @Sno CHAR(10), @Bno CHAR(10)
AS
  declare @Date date
  set @Date = GETDATE()
  declare @DueDate date
  select @DueDate = DueDate from lend where Sno = @Sno AND Bno = @Bno
  if @Date > @DueDate 
  begin
    declare @Days int
    set @Days = DATEDIFF(DAY, @DueDate, @Date)
    declare @Amount DECIMAL(10,2)
    set @Amount = @Days * 0.1
    insert into debt values(@SNo, @Date, @Amount)
    UPDATE reader set Balance = Balance - @Amount where SNo = @Sno
    delete from lend where Sno = @Sno AND Bno = @Bno
    insert into return values(@Snn, @Bno, @Date)
  end
  else
  begin
    declare @Renew bit
    SELECT @Renew = Renew FROM lend WHERE Sno = @Sno AND Bno = @Bno
    if @Renew = 0 
    begin
      set @DueDate = DATEADD(DAY, 30, @Date)
      UPDATE lend SET DueDate = @DueDate, Renew = 1 WHERE Sno = @SNo AND Bno = @Bno
    end
    else
      begin
      DECLARE @Days2 INT
      SET @Days2 = DATEDIFF(DAY, @DueDate, @Date)
      DECLARE @Amount2 DECIMAL(10,2)
      SET @Amount2 = @Days2 * 0.1
      INSERT INTO debt VALUES(@Sno, @Date, @Amount2)
      UPDATE reader SET Balance = Balance - @Amount2 WHERE Sno = @Sno
      DELETE FROM lend WHERE Sno = @Sno AND Bno = @Bno
      INSERT INTO return VALUES(@Sno, @Bno, @Date)
    END
  END