数据库实验六实验报告

梧州学院实验报告实验课程：数据库原理学院：信息与电子工程学院专业：物联网工程班别：14物联网工程姓名：梁嘉欣学号：201400210040梧州学院实验报告专业班级：14物联网工程姓名：梁嘉欣实验课程：数据库原理实验名称：存储过程组号：第六组同组成员：实验时间：2016.06.21指导教师：黄宏本实验设备及环境：SQLserver2014实验目的：1、掌握T-SQL流控制语句；2、掌握创建存储过程的方法；3、掌握存储过程的执行方法；4、掌握存储过程的管理和维护；一、实验原理及内容：实验原理：存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。它的运行速度比独立运行同样的程序要快。1、存储过程类型1）系统存储过程存储在master数据库中，以sp_为前缀。可以在其他数据库中对其进行调用。2）用户自定义存储过程由用户创建并能完成某一特定功能的存储过程，或称本地存储过程。2、创建存储过程存储过程的三个组成部分：（1）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。（3）返回给调用者的状态值，以指明调用是成功还是失败。3、T-SQL创建存储过程的基本语法格式：CREATEPROC[EDURE]存储过程名称参数定义ASSQL语句实验内容：1、创建简单存储过程创建一个名为stu-pr的存储过程，该存储过程能查询出051班学生的全部资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息），要求在创建存储过程前判断该存储过程是否已经建立，若已创建先删除，并给出“已删除！”信息，否则给出“不存在，可创建！”的信息。执行语句为：Createprocedurestu-prasselectstudent.*,cno,gradefromstudentleftouterjoinscon(student.sno=sc.sno)whereclassno=’051’exeutestu-pr(创建存储过程)2、创建带参数的存储过程（1）创建一个名为stu-proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄、选修课程名、成绩。系名和姓名在调用该存储时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。执行语句为：createprocedurestu_proc1@departchar(20)='%',@namevarchar(8)='林%'asselectstudent.sno,sname,DATEDIFF(year,birth,getdate())asage,cname,gradefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnoandsdeptlike@departandsnamelike@nameexecstu_proc1;execstu_proc1'计算机系','张虹'（2）创建一个名为student-sc的存储过程，可查询某学号段的同学的学号、姓名、总成绩（学号起始号与终止号在调用时输入，可设默认值），执行该存储过程。执行ifexists(selectnamefromsysobjectswherename='student_sc'andtype='p')beginprint'已删除！'dropprocedurestudent_scendelseprint'不存在，可创建！'gocreateprocedurestudent_sc@sno1nchar(8),@sno2nchar(8)asselectstudent.sno,sname,sum(grade)总成绩fromstudent,sc,coursewherestudent.sno=sc.snoandcourse.cno=sc.cnoandstudent.sno=@sno1andstudent.sno=@sno2groupbystudent.sno,snameexecutestudent_sc'20110000','20110003'3、建带输出参数的存储过程（1）创建一个名为coursce_averagede的存储过程，可查询某门课程考试的平均成绩。总成绩可以输出，以便进一步调用。执行语句为：createproccourse_average@cnochar(3),@avg_gradedecimal(4,1)outputasselect@avg_grade=AVG(grade)fromscwherecno=@cnoselect*fromscdeclare@avgdecimal(4,1)execcourse_average'001',@avgoutputprint'课程平均成绩是:'+cast(@avgaschar(5))（2）创建一个执行该存储过程的批处理，要求当平均成绩小于60分时显示信息为“xx课程的平均成绩为：xx，其平均分未达60分”，超过60分时显示信息为“xx课程的平均成绩为：xx“。执行语句为：批处理语句：declare@avgintdeclare@cnamenvarchar(20)Execcourse_average@cname,@avgoutbeginif@avg60printcast(@cnameasvarchar)+'课程的平均成绩为'+cast(@avgasvarchar)+',其平均分未达到分.'elseprintcast(@cnameasvarchar)+'课程的平均成绩为'+cast(@avgasvarchar)end执行测试结果：declare@avgintdeclare@cnamenvarchar(20)set@cname='高数'Execcourse_average@cname,@avgoutbeginif@avg60printcast(@cnameasvarchar)+'课程的平均成绩为'+cast(@avgasvarchar)+',其平均分未达到分.'elseprintcast(@cnameasvarchar)+'课程的平均成绩为'+cast(@avgasvarchar)end4、创建带重编译及加密选项的存储过程创建一个名为update_sc并带编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩（学号、课程号在调用时输入）执行语句为：ifexists(selectnamefromsysobjectswherename=’update_sc’andtype=’p’)beginprint‘已删除！’dropprocedureupdate_scendelseprint‘不存在，可创建！’gocreateprocedureupdate_sc@snochar(8),@cnochar(3),@gradetinyintWithRECOMPILE,ENCRYPTONAsUpdateSC_20103322SetGrade=@gradeWhereSno=@snoandCno=@cno5、使用T-SQL语句管理和维护存储过程（1）使用sp-helptext查看存储过程student-sc的定义脚本。执行语句：execsp_helptextstudent_sc（2）使用SELECT语句查看student-sc存储过程的定义脚本。执行语句为：select*fromsysobjectswherename='student_sc'（3）将存储过程stu-pr改为查询学号为“201101”的学生的详细资料。执行语句为：alterprocedurestu_prasselect*fromstudentleftouterjoinscon(student.sno=sc.sno)leftouterjoincourseon(course.cno=sc.cno)wheresno='2011001'（4）删除存储过程stu-pr。执行语句为：dropprocedurestu_pr6、使用SQLServerMannagementStudio管理存储过程（1）在SQLServerMannagementStudio中重新创建刚删除的存储过程stu-pr。执行语句为：createprocedurestu_prasbeginselect*fromStudent_20103322leftouterjoinSC_20103322on(Student_20103322.Sno=SC_20103322.Sno)leftouterjoinCourse_20103322on(Course_20103322.Cno=SC_20103322.Cno)whereStudent_20103322.Sno=’2011001’end（2）查看存储过程stu-pr，并将该过程改为查询“051:”班女生的所有资料。执行语句为：ALTERprocedure[dbo].[stu_pr]asbeginselect*fromStudent_20103322leftouterjoinSC_20103322on(Student_20103322.Sno=SC_20103322.Sno)leftouterjoinCourse_20103322on(Course_20103322.Cno=SC_20103322.Cno)whereStudent_20103322.Sno=’2011001’andSex=’女’end（3）删除存储过程stu-pr。点击stu-pr，右键删除即可。二、实验步骤与结果：略三、实验总结（心得体会，及对本实验的改进建议）了解数据库存储过程的基本原理，和基本的语法结构，熟记关键词，和其用法。存储过程提供了许多标准SQL语句所没有的高级特性。（1）其传递参数和执行逻辑表达式的功能为处理各种复杂的任务提供了可能；（2）由于其是经过编译后存储在服务器上，这就减少了执行过程所需的网络传输宽带和执行时间;（3）存储过程具有安全性，可以授予用户具有执行某个存储过程的权限，但是该用户可以不必直接对存储过程中引用的对象具有权限，这就提供了很大的灵活性。四、指导老师评议：成绩评定：指导老师签名：