实验九 游标的使用

-- 2.使用教师已经录入相关数据的教材中的“cjgl”数据库，完成教学补充材料上游标的相关例题的上机调试
-- 读取第1行数据
Declare mycrsr1 Cursor FOR Select * FROM student;
OPEN mycrsr1;
FETCH NEXT from mycrsr1;
CLOSE mycrsr1;
-- 用@@FETCH_STATUS控制在一个WHILE循环中的游标活动 
DECLARE mycrsr1 cursor 
FOR SELECT * FROM Student
OPEN  mycrsr1 
FETCH NEXT from  mycrsr1   
WHILE @@FETCH_STATUS = 0 
	BEGIN 
      	  FETCH NEXT from mycrsr1 
	END 
CLOSE mycrsr1 
DEALLOCATE mycrsr1 
--通过游标逐行按成绩高低输出选修课程的男学生姓名、性别、课程名、成绩。
DECLARE @vsname char(9), @vsex char(2), @vcname char(20),@vgrade smallint,@vmc smallint 
select @vcname='数据库'
select @vmc=1
print '--------数据库成绩单--------'
DECLARE mycrsr2 CURSOR FOR 
SELECT sname,ssex,cname,grade
FROM student,course,sc
WHERE student.sno=sc.sno and course.cno=sc.cno 
and ssex='男'and cname=@vcname
ORDER BY grade desc
OPEN mycrsr2
FETCH NEXT FROM mycrsr2 INTO  @vsname,@vsex,@vcname,@vgrade
WHILE @@FETCH_STATUS=0
BEGIN
   print '第'+ltrim(str(@vmc))+'名'
   print @vsname+@vsex+convert(char(6),@vgrade)
   FETCH NEXT FROM mycrsr2 INTO @vsname,@vsex,@vcname,@vgrade
   select @vmc=@vmc+1
END
CLOSE mycrsr2 
DEALLOCATE mycrsr2 
GO

--3
-- (1)通过游标逐行输出班级表 Class 的信息。
DECLARE crsr1 CURSOR FOR SELECT * FROM Class;
OPEN crsr1;
FETCH NEXT FROM crsr1
WHILE @@FETCH_STATUS = 0
BEGIN
	FETCH NEXT FROM crsr1
END
CLOSE crsr1;
DEALLOCATE crsr1;

--（2）通过游标按成绩高低输出班级号为‘01311’选修‘操作系统’课程的学生名次，学号，姓名、成绩
DECLARE CURSOR1 CURSOR FOR 
SELECT Student.Sno , Student.Sname , CJ.Grade
FROM Student , Course , CJ
WHERE Student.Sno = CJ.Sno AND Course.Cno = CJ.Cno AND Student.Clno = '01311' AND Course.Cname = '操作系统'
ORDER BY Grade DESC;
OPEN CURSOR1;

IF @@FETCH_STATUS <> -2
BEGIN
DECLARE @pos INT , @Sno CHAR(7), @Sname CHAR(10) , @Grades DECIMAL(4,1);
SET @pos = 1;
FETCH NEXT FROM CURSOR1 INTO @Sno , @Sname , @Grades;
PRINT '第'+ltrim(str(@pos))+'名是'+@Sname+'同学，学号为:'+@Sno+'，成绩为:'+ltrim(str(@Grades));
SET	@pos = @pos + 1;

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CURSOR1 INTO @Sno , @Sname , @Grades;
PRINT '第'+ltrim(str(@pos))+'名是'+@Sname+'同学，学号为:'+@Sno+'，成绩为:'+ltrim(str(@Grades));
SET	@pos = @pos + 1;
END;
END;

CLOSE CURSOR1;
DEALLOCATE CURSOR1;

-- （3）不使用计数 count（）函数，通过游标编程计算班级号为‘01311’
--选修‘操作系统’课程的各成绩等级（优秀：90~100，良好：80~89，中等：70~79，
--及格：60~69，不及格：<=59）的学生人数，并输出。
DECLARE CURSOR1 CURSOR FOR
SELECT /*Student.Sno ,*/ Grade FROM Student , CJ ,Course
WHERE Student.Sno = CJ.Sno AND Course.Cno = CJ.Cno 
AND Student.Clno = '01311' AND Course.Cname = '操作系统';
OPEN CURSOR1;
DECLARE @excellent INT,@good INT,@medium INT,@pass INT,@fail INT,@Grades DECIMAL(4,1);
SET @excellent = 0;
SET @good = 0;
SET @medium = 0;
SET @pass = 0;
SET @fail = 0;

FETCH NEXT FROM CURSOR1 INTO @Grades;

WHILE @@FETCH_STATUS = 0 
BEGIN
	IF @Grades BETWEEN 90 AND 100
BEGIN
SET @excellent = @excellent + 1;
END;
IF @Grades BETWEEN 80 AND 89
BEGIN
SET @good = @good + 1;
END;
IF @Grades BETWEEN 70 AND 79
BEGIN
SET @medium = @medium + 1;
END;
IF @Grades BETWEEN 60 AND 69
BEGIN
SET @pass = @pass + 1;
END;
IF @Grades BETWEEN 0 AND 59
BEGIN
SET @fail = @fail + 1;
END;
FETCH NEXT FROM CURSOR1 INTO @Grades;
END;
PRINT '良好学生有：'+ltrim(str(@excellent))+'人';
PRINT '良好学生有：'+ltrim(str(@good))+'人';
PRINT '中等学生有：'+ltrim(str(@medium))+'人';
PRINT '及格学生有：'+ltrim(str(@pass))+'人';
PRINT	'不及格学生有：'+ltrim(str(@fail))+'人';

CLOSE CURSOR1;
DEALLOCATE CURSOR1;