优化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)) + '人'