USE MySchool
GO
EXEC sp_columns Student --查看表Student中列的信息
EXEC sp_help Student --查看表Student的所有信息
EXEC sp_helpconstraint Student --查看表Student的约束
/*---创建存储过程----*/
CREATE PROCEDURE usp_grade_subject
AS
SELECT GradeName,SubjectName,ClassHour FROM Grade
INNER JOIN Subject
ON Grade.GradeId=Subject.GradeId
ORDER BY Subject.GradeId,SubjectNo
GO
/*---调用执行存储过程---*/
EXEC usp_grade_subject
CREATE PROCEDURE usp_query_subject
@GradeName VARCHAR(50) = NULL
AS
IF @GradeName IS NULL
SELECT GradeName,SubjectName,ClassHour FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
UNION
SELECT GradeName,' ',SUM(ClassHour)FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
GROUP BY GradeName
ELSE
SELECT GradeName,SubjectName,ClassHour FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE GradeName=@GradeName
UNION
SELECT GradeName,' ',SUM(ClassHour)FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE GradeName=@GradeName
GROUP BY GradeName
GO
EXEC usp_query_subject 's2'
CREATE PROCEDURE usp_query_subject
@CourseNum INT OUTPUT,
@HourNum INT OUTPUT,
@GradeName VARCHAR(50)
AS
IF LEN(@GradeName) = 0
BEGIN
PRINT '学期名称不能为空'
RETURN
END
PRINT '---------学期课程信息如下------------'
SELECT GradeName,SubjectName,ClassHour FROM Grade
LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE GradeName=@GradeName
SELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour)
FROM Grade
INNER JOIN Subject ON Grade.GradeId=Subject.GradeId
WHERE GradeName=@GradeName
GO
优化MySchool 第六章 上机
最新推荐文章于 2019-12-06 22:27:21 发布
这篇博客介绍了如何在MySchool数据库中查看表Student的信息,并创建了两个存储过程usp_grade_subject和usp_query_subject。usp_grade_subject展示年级和科目的课时信息,而usp_query_subject允许按年级查询科目及其课时,还可以返回课程数量和总课时数。示例中展示了如何调用这两个存储过程并执行特定查询。
摘要由CSDN通过智能技术生成