查询所有学生的选课的信息,包括学号、姓名,课程号、课程名称和期末成绩
CREATE PROCEDURE Pstu_sc_c0 ---不带参数
AS
SELECT student.studentno,sname,course.courseno,cname
FROM student,course,score
WHERE student.studentno=score.studentno AND course.courseno=score.courseno
GO
创建一个存储过程,输出指定学生的学号、姓名、课程号、课程名称、期末成绩信息
CREATE PROCEDURE Pstu_sc_c1 @s_name nchar(10)
AS
SELECT student.studentno,sname,course.courseno,cname
FROM student,course,score
WHERE student.studentno=score.studentno AND course.courseno=score.courseno
AND sname=@s_name
GO
带输出参数的存储过程
指定某个学生的期末的成绩平均值
CREATE PROCEDURE Pstu_sc_c2 @sno char(12),@average numeric(6,2) OUTPUT
AS
SELECT @average=AVG(final)
FROM student,score
WHERE student.studentno=score.studentno AND student.studentno=@sno
go
输入参数指定默认值的存储过程
指定某个学生的期末的成绩平均值,如果不指定,返回所有学生的所有课程的期末成绩平均值。查看期末成绩低于70分的学生的名单。
CREATE PROCEDURE Pstu_sc_c3 @sno nchar(12)=NULL,@average numeric(6,2) OUTPUT
AS
SELECT @average=AVG(final)
FROM student,score
WHERE student.studentno=score.studentno AND (student.studentno=@sno OR @sno IS NULL)
SELECT student.studentno,sname,final
FROM student,score
WHERE student.studentno=score.studentno AND final<90
GO
存储过程的执行
EXECUTE Pstu_sc_c0
EXECUTE Pstu_sc_c1 @s_name='敬秉辰'
DECLARE @ave NUMERIC(6,2)
EXECUTE Pstu_sc_c2 @sno='18125111109',@average=@ave OUTPUT
SELECT @ave
DECLARE @ave1 NUMERIC(6,2)
EXECUTE Pstu_sc_c3 @average=@ave1 OUTPUT
SELECT @ave1