MySchool 07 上机
来源:互联网 发布:java path环境变量设置 编辑:程序博客网 时间:2024/06/05 16:31
- --第七章上机一
- 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
- --上机五
- CREATE PROCEDURE usp_insert_subject
- @SubjectNo int OUTPUT,
- @GradeId int OUTPUT,
- @GradeName varchar(50),
- @SubjectName varchar(50),
- @ClassHour int = 36
- AS
- DECLARE @errNum int
- SET @errNum = 0
- IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)
- RETURN -1
- BEGIN TRANSACTION
- IF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName)
- BEGIN
- INSERT INTO Grade (GradeName) VALUES (@GradeName)
- SET @errNum = @errNum + @@ERROR
- SELECT @GradeId=@@IDENTITY
- END
- ELSE
- SELECT @GradeId=GradeId FROM Grade WHERE GradeName = @GradeName
- INSERT INTO Subject (SubjectName,ClassHour,GradeId)
- VALUES (@SubjectName,@ClassHour,@GradeId)
- SET @errNum = @errNum + @@ERROR
- SELECT @SubjectNo=@@IDENTITY
- IF (@errNum > 0)
- BEGIN
- ROLLBACK TRANSACTION
- RETURN 0
- END
- ELSE
- BEGIN
- COMMIT TRANSACTION
- RETURN 1
- END
- GO
- --调用存储过程
- DECLARE @SubjectNo int
- DECLARE @GradeId int
- DECLARE @GradeName varchar(50)
- DECLARE @SubjectName varchar(50)
- DECLARE @ClassHour int
- DECLARE @rt int
- SET @GradeName = 'Y2'
- SET @SubjectName = 'Linux'
- SET @ClassHour = 10
- EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHour
- IF (@rt = 1)
- BEGIN
- PRINT '增加课程'+@SubjectName+'记录成功'
- PRINT '学期编号是' + CAST(@GradeId AS varchar(10)) + ',学期名称是' + @GradeName
- PRINT '课程编号是' + CAST(@SubjectNo AS varchar(10)) + ',课程名称是' + @SubjectName
- END
- ELSE if (@rt = 0)
- PRINT '增加课程记录失败!'
- ELSE
- PRINT '学期名称或课程名称不能为空,请重新执行!'
- GO
1 0
- MySchool 07 上机
- 优化MySchool 第六章 上机
- 优化MySchool 第六章上机
- 优化MySchool 第七章上机
- 优化MySchool 第七章 上机
- 优化MySchool 第六章 上机
- 优化MySchool 数据库设计 第二章上机
- 优化MySchool 数据库设计 第二章 上机
- 优化MySchool数据库 第三章上机 简答
- MySchool 04 高级查询 上机题
- MySchool 03 编程上机练习 上机和打印直角三角形
- S2 第一本书myschool 第二章上机1-7
- 优化MYSCHOOL第七章存储过程 上机简答
- S2 优化MySchool 数据库设计 第七章 上机
- ACCP7.0S2优化myschool数据库设计第二章上机练习1-7
- ACCP7.0S2优化MySchool数据库设计第三章上机1-3
- ACCP7.0S2优化myschool数据库设计第四章上机1-4
- myschool简介
- Week Training: 508 Most Frequent Subtree Sum
- CAS实现SSO单点登录原理
- AS: Unsupported method: AndroidProject.getPluginGeneration().
- CodeForces
- [转]PHP7内核剖析之变量的内部实现
- MySchool 07 上机
- wdcp组件安装
- PAT 1005
- PHP字符串函数
- 泛型的限制
- leetcode448 Find All Numbers Disappeared In An Array Java
- spring mvc controller间跳转 重定向 传参
- Cocoapods详解之---创建私有仓库篇
- Delphi xe10 安卓开发环境配置问题处理1