最近看了一个考试的题目,要求
创建存储过程,输入班号,显示此班级的系名,专业名,入学年份和 班级人数
存储过程本来没有讲的太多,所以不是很熟悉,以前也写过但是都没有通过,这次终于通过了,所以写在这里,可以与大家分享,不过这是一个最简单的存储过程了。
这里是三张表
STUDENT
(学号,姓名,性别,年龄,班号)
CLASS
(班号,专业名,系号,入学年份)
DEPARTMENT
(系号,系名)
首先创建完这三张表后
就可以执行下面的存储过程了
create proc pro_cla
(
@classno int
)
as
begin
select @classno=classno
from class
select count(xuehao),projectname,departmentname,ruxueyear
From student s join class c
on s.classno=c.classno join DEPARTMENT d
on c.departmentid=d.departmentid
where c.classno=@classno
group by projectname,departmentname,ruxueyear
end
(
@classno int
)
as
begin
select @classno=classno
from class
select count(xuehao),projectname,departmentname,ruxueyear
From student s join class c
on s.classno=c.classno join DEPARTMENT d
on c.departmentid=d.departmentid
where c.classno=@classno
group by projectname,departmentname,ruxueyear
end
然后执行下面的查询就可以验证存储过程了
select *
from class
where classno='140'
from class
where classno='140'
转载于:https://blog.51cto.com/lovetiantian/435180