数据库存储过程和游标

三张表，学生表，课程表和选课表

use [20140319];
 
create tableCourse(courseIdint,courseNamechar(10),primary key (courseId)); 
create tableStudent(studentIdint,namechar(10),primary key (studentId)); 
create tableChoose(studentIdint,courseIdint, primary key (studentId,courseId),foreignkey (studentId)references Student(studentId),foreignkey (courseId)references Course(courseId));
 
insert intoCourse values(001,'DB');
insert intoCourse values(002,'OS');
 
insert intoStudent values(1001,'Tom');
insert intoStudent values(1002,'Jack');
 
insert intoChoose values(1001,001);
insert intoChoose values(1001,002);
insert intoChoose values(1002,001);
 
insert intoCourse values(003,'MATH');
insert intoChoose values(1002,002);
insert intoChoose values(1002,003);
 
//存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
 
//1. 输入课程序号输出课程名称
create procedurecName   //申明存储过程
 
@courseNo int=001         //输入参数
 
as
select Course.courseName
from Course
where courseId=@courseNo
go
 
 
exec cName;                //执行存储过程
 
 
//2. 输入学生学号输出选课数
create procedurecNo
@stuNo int=1001
as
begin
select COUNT(*) fromChoose where studentId=@stuNo
end
 
exec cNo;
 
//3. 增加一门课程为课程代码和课程名称
create procedureaddCourse
@CNo int=0041,
@Cdd varchar='1004'//or char
as
insert intoCourse values(@CNo,@Cdd);
go
 
exec addCourse
 
//4. 输出选择两门课的学生姓名
create procedureprintNa1
@CSu int=3
as
select Student.namefrom Student,Choosewhere Student.studentId=Choose.studentIdgroup by Student.namehaving COUNT(*)=@CSu;
go
 
exec printNa1;

//5. 输出选修人最多的课程
create procedureprintMax
as
select Course.courseId,courseNameas num
from Course,Choose
where Choose.courseId=Course.courseId
group byCourse.courseId,courseName
having COUNT(*)=(
              select MAX(tmp.num)
              from
              (
                  select Course.courseId,courseName,COUNT(*)as num
                  from Course,Choose
                  where Choose.courseId=Course.courseId
                  group by Course.courseId,courseName
              )
              as tmp
              );
go
 
exec printMax;

//6. 游标输出学生姓名课程数量
create procedurecurse05
as
begin
 
//申明游标
declare curseTest cursor
for selectStudent.name,count(*)from Choose,Studentwhere Choose.studentId=Student.studentIdgroup by Student.name;
 
//打开游标
open curseTest
 
//循环一个游标
declare @name char(10),@sumint
    fetch nextfrom curseTestinto @name,@sum
   
while @@FETCH_STATUS=0
    begin
    print @name
    print @sum        
    fetch nextfrom curseTestinto @name,@sum
    end
 
//关闭一个游标
close curseTest
 
//释放游标
deallocate curseTest
 
end
go
 
exec curse05;