Sqlserver存储过程

创建mydb数据库，并创建 学生表（学号、姓名、性别、年龄、专业）、课程表（课程编号、课程名称、先行课号、学分）和选课表（学号、课程号、成绩）
create database mydb
go
use mydb
go

CREATE TABLE Student          
(
Sno CHAR(9),
Sname CHAR(20),
Sgender CHAR(2),
Sage SMALLINT,
Sdept  CHAR(20)
)

insert into Student values('200215121','李勇','男',20,'CS')
insert into Student values('200215122','刘晨','女',19,'CS')
insert into Student values('200215123','王敏','女',18,'MA')
insert into Student values('200215125','张立','男',19,'IS')
 
CREATE TABLE Course
(
Cno CHAR(4),
Cname  CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT
)

insert into Course values('1','数据库','5',4)
insert into Course values('2','数学',NULL,2)
insert into Course values('3','信息系统','1',4)
insert into Course values('4','操作系统','6',3)
insert into Course values('5','数据结构','7',4)
insert into Course values('6','数据处理',NULL,2)
insert into Course values('7','PASCAL语言','6',4)


CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT
)

insert into SC values('200215121','1',92)
insert into SC values('200215121','2',85)
insert into SC values('200215121','3',88)
insert into SC values('200215122','2',90)
insert into SC values('200215123','3',80)


--1.创建存储过程proc_stu，统计各专业学生的人数
create procedure proc_stu
@count int output,
@major varchar(50) output
as
select @count=COUNT(*),@major=Sdept from Student group by Sdept 


--2.创建存储过程proc_cou，计算并显示开设课程的平均学分

create procedure proc_cou
@Credit_avg int output
as
select @Credit_avg=AVG(Ccredit) from Course
declare @Credit_avg decimal(10,2)
exec proc_cou @Credit_avg output
print @Credit_avg 

--3.创建存储过程proc_cx，查询无人愿意选的课程（今后会取消这类课程）
create procedure proc_cx
as
select Cname from Course where Cno not in( 
select Cno from SC )

--4.创建存储过程proc_sc，查看学生的选课情况（即用户输入姓名，存储过程显示该学生的选课信息）
create procedure proc_sc
@sname varchar(50)
as
select * from Course where Cno in(select Cno from SC where Sno in(select Sno from Student where Sname=@sname ))

declare @sname varchar(50)
exec proc_sc @sname='李勇'
--5.创建存储过程proc_fac，该存储过程可以计算数字的阶乘（例如，输入会计算！的值并显示,考虑使用输出参数）
create procedure proc_fac
@num int,
@result  int output
as
begin
    while @num > 0
    begin
        set @result = @result * @num
        set @num = @num - 1; 
    end
end
drop procedure proc_fac

declare @result int
set @result=1
exec proc_fac 5, @result output
print @result