数据库实验六实验报告

梧 州 学 院

实 验 报实验课程：数据库原理学 院：信息与电子工程学院专 业：物联网工程班 别：14物联网工程姓 名：梁嘉欣学 号：201400210040告

梧州学院实验报告

专业班级：14物联网工程实验课程：数据库原理组 号：第六组实验时间：2016.06.21姓名：梁嘉欣实验名称：存储过程同组成员：指导教师：黄宏本

实验设备及环境：SQL server 2014 实验目的： 1、掌握T-SQL流控制语句； 2、掌握创建存储过程的方法； 3、掌握存储过程的执行方法； 4、掌握存储过程的管理和维护； 一、实验原理及内容： 实验原理： 存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行同样的程序要快。 1、存储过程类型 1）系统存储过程 存储在master数据库中，以sp_为前缀。可以在其他数据库中对其进行调用。 2）用户自定义存储过程 由用户创建并能完成某一特定功能的存储过程，或称本地存储过程。 2、创建存储过程 存储过程的三个组成部分： （1）所有的输入参数以及传给调用者的输出参数。 （2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。 （3）返回给调用者的状态值，以指明调用是成功还是失败。 3、T-SQL创建存储过程的基本语法格式： CREATE PROC[EDURE] 存储过程名称 参数定义 AS SQL语句 实验内容： 1、 创建简单存储过程

创建一个名为stu-pr的存储过程，该存储过程能查询出051班学生的全部资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息），要求在创建存储过程前判断该存储过程是否已经建立，若已创建先删除，并给出“已删除！”信息，否则给出“不存在，可创建！”的信息。 执行语句为： Create procedure stu-pr as select student.*,cno,grade from student left outer join sc on (student.sno=sc.sno) where classno=’051’ exeute stu-pr(创建存储过程) 2、 创建带参数的存储过程 （1）创建一个名为stu-proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄、选修课程名、成绩。系名和姓名在调用该存储时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。 执行语句为： create procedure stu_proc1 @depart char(20)='%',@name varchar(8)='林%' as select student.sno ,sname,DATEDIFF (year,birth,getdate())as age,cname,grade from student ,sc ,course where student .sno=sc.sno and sc.cno =course .cno and sdept like @depart and sname like @name exec stu_proc1; exec stu_proc1'计算机系','张虹' （2）创建一个名为student-sc的存储过程，可查询某学号段的同学的学号、姓名、总成绩（学号起始号与终止号在调用时输入，可设默认值），执行该存储过程。 执行if exists (select name from sysobjects where name='student_sc'and type='p') begin print '已删除！' drop procedure student_sc end else print '不存在，可创建！' go create procedure student_sc @sno1 nchar(8),@sno2 nchar(8) as

select student.sno,sname,sum(grade)总成绩 from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and student.sno>=@sno1 and student.sno<=@sno2 group by student.sno,sname execute student_sc '20110000','20110003' 3、 建带输出参数的存储过程 （1）创建一个名为coursce_averagede 的存储过程，可查询某门课程考试的平均成绩。总成绩可以输出，以便进一步调用。 执行语句为： create proc course_average @cno char(3),@avg_grade decimal(4,1) output as select @avg_grade=AVG(grade) from sc where cno=@cno select *from sc declare @avg decimal(4,1) exec course_average '001',@avg output print '课程平均成绩是:' + cast(@avg as char(5)) （2）创建一个执行该存储过程的批处理，要求当平均成绩小于60分时显示信息为“xx课程的平均成绩为：xx，其平均分未达60分”，超过60分时显示信息为“xx课程的平均成绩为：xx“。 执行语句为： 批处理语句： declare @avg int declare @cname nvarchar(20) Exec course_average @cname,@avg out begin if @avg <60 print cast(@cname as varchar)+ '课程的平均成绩为'+cast(@avg as varchar)+ ',其平均分未达到分. ' else

print cast(@cname as varchar)+ '课程的平均成绩为'+cast(@avg as varchar) end 执行测试结果： declare @avg int declare @cname nvarchar(20) set @cname='高数' Exec course_average @cname,@avg out begin if @avg <60 print cast(@cname as varchar)+ '课程的平均成绩为'+cast(@avg as varchar)+ ',其平均分未达到分. ' else print cast(@cname as varchar)+ '课程的平均成绩为'+cast(@avg as varchar) end 4、 创建带重编译及加密选项的存储过程 创建一个名为update_sc并带编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩（学号、课程号在调用时输入） 执行语句为： if exists (select name from sysobjects where name=’update_sc’and type=’p’) begin print ‘已删除！’ drop procedure update_sc end else print ‘不存在，可创建！’ go create procedure update_sc @sno char(8),@cno char(3),@grade tinyint With RECOMPILE ,ENCRYPTON As Update SC_20103322 Set Grade=@grade Where Sno=@sno and Cno=@cno 5、 使用T-SQL语句管理和维护存储过程

本文来源：https://www.bwwdw.com/article/omag.html