优化Myschool数据库设计第七章 使用存储过程

--示例1：常用系统存储过程的使用
EXEC sp_databases --列出当前系统中的数据库
EXEC sp_renamedb 'School','MySchool' --改变数据库名称

USE MySchool
GO

EXEC sp_tables --当前数据库中可查询对象的列表
EXEC sp_columns Student --查看表Student中列的信息
EXEC sp_help Student --查看Student的所有信息
EXEC sp_helpconstraint Student --查看表Student的约束
EXEC sp_helptext 'vw_Student_Result_info' --查看视图的语句文本
EXEC sp_stored_procedures --返回当前数据库中的存储过程列表

--示例2：创建无参存储过程，查询Java Logic最近一次考试平均分以及未通过考试的学员名单
USE MySchool
GO

--检测是否存在存储过程
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_GetAverageResult')
DROP PROC usp_GetAverageResult
GO

--创建存储过程：查询java logic最近一次考试的平均分
CREATE PROC usp_GetAverageResult
AS
DECLARE @subjectNo int --课程编号
DECLARE @date datetime --最近考试时间

--查询java logic的课程编号
SELECT @subjectNo = subjectNo FROM Subject WHERE SubjectName = 'java logic'
--查询java logic最近一次考试时间
SELECT @date = MAX(ExamDate) FROM Result WHERE SubjectNo = @subjectNo

DECLARE @avg float --平均分变量
SELECT @avg = AVG(StudentResult)
FROM Result WHERE SubjectNo = @subjectNo
AND ExamDate = @date
Print '平均分：' + Convert(Varchar(5),@avg)

--根据平均分给出评价
IF(@avg>70)
Print '考试成绩：优秀'
ELSE
Print '考试成绩：较差'

Print '------------------------------------------------------'
Print '参加本次考试没有通过的学员：'
SELECT StudentName,Student.StudentNo,StudentResult
FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE SubjectNo = @subjectNo AND ExamDate = @date AND StudentResult<60
GO

--调用执行存储过程
EXEC usp_GetAverageResult

--示例3：创建带参存储过程
USE MySchool
GO
SET NOCOUNT ON

--检测是否存在存储过程
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_unpass')
DROP PROC usp_unpass
GO

--创建存储过程：查询指定课程最近一次考试中不及格的学员信息
CREATE PROC usp_unpass
@subName varchar(50), --课程名称
@score int --及格线
AS
DECLARE @subjectNo int --课程编号
DECLARE @date datetime --最近考试时间

--查询课程编号
SELECT @subjectNo = subjectNo FROM Subject WHERE SubjectName = @subName
--查询最近一次考试时间
SELECT @date = MAX(ExamDate) FROM Result WHERE SubjectNo = @subjectNo

Print '考试及格线是：' + Convert(Varchar(5),@score)
Print '----------------------------------------------------------------------------'
Print '参加最近一次'+@subName+'考试没有达到分数线的学员：'
SELECT StudentName,Student.StudentNo,StudentResult
FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE SubjectNo = @subjectNo AND ExamDate = @date AND StudentResult<@score
GO

--调用执行存储过程
EXEC usp_unpass 'winforms',50
EXEC usp_unpass @subName='winforms',@score=50



--示例4：带默认参数值的存储过程
USE MySchool
GO
SET NOCOUNT ON

--检测是否存在存储过程
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_unpass')
DROP PROC usp_unpass
GO

--创建存储过程：查询指定课程最近一次考试中不及格的学员信息
CREATE PROC usp_unpass
@subName varchar(50), --课程名称
@score int = 60 --利用赋值运算符为参数赋默认值60
AS
DECLARE @subjectNo int --课程编号
DECLARE @date datetime --最近考试时间

--查询课程编号
SELECT @subjectNo = subjectNo FROM Subject WHERE SubjectName = @subName
--查询最近一次考试时间
SELECT @date = MAX(ExamDate) FROM Result WHERE SubjectNo = @subjectNo

Print '考试及格线是：' + Convert(Varchar(5),@score)
Print '----------------------------------------------------------------------------'
Print '参加最近一次'+@subName+'考试没有达到分数线的学员：'
SELECT StudentName,Student.StudentNo,StudentResult
FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE SubjectNo = @subjectNo AND ExamDate = @date AND StudentResult<@score
GO

--调用执行存储过程
EXEC usp_unpass 'winforms',default
EXEC usp_unpass @subName='winforms'

--练习1：创建存储过程，查询指定学期课程信息
USE MySchool
GO

--判断存储过程是否存在
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp__query_subject')
DROP PROC usp__query_subject
GO

--创建存储过程：查看指定学期所开设的课程信息，
--如果没有指定哪个学期，默认显示所有学期的课程信息

CREATE PROC usp__query_subject
@gradeName varchar(50) = NULL
AS
IF(@gradeName IS NOT NULL)
--查询指定学期的总课时和开设的课程名称、课时
SELECT GradeName AS 年级名称,'' AS 课程名称,SUM(ClassHour) 学时
FROM Grade INNER JOIN Subject ON Grade.GradeId = Subject.GradeId
WHERE GradeName = @gradeName
GROUP BY GradeName
UNION
SELECT gradeName,subjectName,ClassHour
FROM Grade INNER JOIN Subject ON Grade.GradeId = Subject.GradeId
WHERE GradeName = @gradeName
ELSE
--查询每个学期的总课时和开设的课程名称、课时
SELECT GradeName AS 年级名称,'' AS 课程名称,SUM(ClassHour) AS 学时
FROM Grade INNER JOIN Subject ON Grade.GradeId = Subject.GradeId
GROUP BY GradeName
UNION
SELECT GradeName,subjectName,ClassHour
FROM Grade INNER JOIN Subject ON Grade.GradeId = Subject.GradeId
GO

--调用存储过程
EXEC usp__query_subject 'S2'
EXEC usp__query_subject

 

--示例5：创建带输出参数的存储过程
USE MySchool
GO

--判断存储过程是否存在
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_query_num')
DROP PROC usp_query_num
GO

--创建存储过程：获得参加考试的学生人数和未通过的学生人数
CREATE PROC usp_query_num
@UnPassNum INT OUTPUT, --输出参数,未通过人数
@TotalNum INT OUTPUT, --输出参数,参加考试总人数
@SubjectName NCHAR(10), --输入参数，课程名称
@Pass INT = 60 --输入参数：及格线
AS
DECLARE @SubjectNo int --课程编号
DECLARE @date datetime --最近考试时间
--查询课程编号
SELECT @SubjectNo = SubjectNo From Subject WHERE SubjectName = @SubjectName
--查询最近考试时间
SELECT @date = MAX(ExamDate) FROM Result WHERE SubjectNo = @SubjectNo

--输出课程名称、最近一次考试日期和及格分数线
PRINT @SubjectName + '课程在' + CAST(@date AS varchar(20)) + '考试的及格线是' + CAST(@Pass AS varchar(10))
PRINT '-----------------未通过学员的信息如下------------------'
SELECT Student.StudentNo,StudentName,StudentResult
FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE SubjectNo = @SubjectNo
AND ExamDate = @date
AND StudentResult<@Pass

--查询未通过人数
SELECT @UnPassNum = COUNT(*) FROM Result
WHERE SubjectNo = @SubjectNo AND ExamDate = @date AND StudentResult <@Pass
--查询考试总人数
SELECT @TotalNum = COUNT(*) FROM Result
WHERE SubjectNo = @SubjectNo AND ExamDate = @date
GO

--调用存储过程
DECLARE @UnPassNum int --未通过人数
DECLARE @TotalNum int --参加考试总人数
EXEC usp_query_num @UnPassNum output,@TotalNum output,'java logic'

PRINT '考试总人数：' + cast(@TotalNum AS VARCHAR(10)) + '人，'
+ '未通过人数：' + cast(@UnPassNum AS varchar(10)) + '人'



--示例6：创建有错误处理的存储过程
USE MySchool
GO

--判断存储过程是否存在
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name = 'usp_query_num')
DROP PROC usp_query_num
GO

--创建存储过程：获得参加考试的学生人数和未通过的学生人数
CREATE PROC usp_query_num
@UnPassNum INT OUTPUT, --输出参数,未通过人数
@TotalNum INT OUTPUT, --输出参数,参加考试总人数
@SubjectName NCHAR(10), --输入参数，课程名称
@Pass INT = 60 --输入参数：及格线
AS
DECLARE @SubjectNo int --课程编号
DECLARE @date datetime --最近考试时间
--查询课程编号
SELECT @SubjectNo = SubjectNo From Subject WHERE SubjectName = @SubjectName
--查询最近考试时间
SELECT @date = MAX(ExamDate) FROM Result WHERE SubjectNo = @SubjectNo

--判断及格线是否在0-100分之间
IF(NOT @Pass BETWEEN 0 AND 100)
BEGIN
RAISERROR('及格线错误，请指定0－100之间的分数，统计中断退出',16,1)
RETURN ---立即返回，退出存储过程
END

--输出课程名称、最近一次考试日期和及格分数线
PRINT @SubjectName + '课程在' + CAST(@date AS varchar(20)) + '考试的及格线是' + CAST(@Pass AS varchar(10))
PRINT '-----------------未通过学员的信息如下------------------'
SELECT Student.StudentNo,StudentName,StudentResult
FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE SubjectNo = @SubjectNo
AND ExamDate = @date
AND StudentResult<@Pass

--查询未通过人数
SELECT @UnPassNum = COUNT(*) FROM Result
WHERE SubjectNo = @SubjectNo AND ExamDate = @date AND StudentResult <@Pass
--查询考试总人数
SELECT @TotalNum = COUNT(*) FROM Result
WHERE SubjectNo = @SubjectNo AND ExamDate = @date
GO

--调用存储过程
DECLARE @UnPassNum int --未通过人数
DECLARE @TotalNum int --参加考试总人数
EXEC usp_query_num @UnPassNum output,@TotalNum output,'java logic',101

PRINT '参加考试总人数：' + CAST(@TotalNum AS varchar(10)) + '人'
PRINT '未通过人数：' + cast(@UnPassNum AS varchar(10)) + '人'