实验五-存储过程20103191王晓燕

1实验五存储过程学号：20103191姓名：王晓燕专业：应用物理班级：2010232一、实验目的（1）掌握T-SQL流控制语句。（2）掌握创建存储过程的方法。（3）掌握存储过程的执行方法。（4）掌握存储过程的管理和维护。二、实验内容1、创建简单存储过程（1）创建一个名为stu_pr的存储过程：该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息，并创建该存储过程。存储过程的创建语句：ifexists(selectnamefromsysobjectwherename=’stu_pr’andtype=’p’)Dropprocedurestu_prgocreateprocedurestu_prAsselect*fromstudentleftouterjoinscon(student.sno=sc.sno)go存储过程的执行测试结果：22、创建带参数的存储过程（1）创建一个名为stu_proc1的存储过程：查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。存储过程的创建语句：createprocedurestu_proc1@sdeptchar(10)=”%”,@snamevarchar(8)=”林%“Asselectstudent.sno,sname,datediff(year,birth,getdate())age,course.cname,gradeFromstudent,sc,courseWheresyudent.sno=sc.snoandcourse.cno=sc.cnoandsdept=@sdeptandsname=@snamego存储过程的执行测试结果：3（2）创建一个名为student_sc的存储过程：可查询出某学号段的同学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可设默认值）。执行该存储过程。存储过程的创建语句：createprocedurestudent_sc@startchar(8),@endchar(8)Asselectstudent.sno,sname,sum(grade)Fromstudent,scWherestudent.snobetween@[e-mail]=sc.snogroupbystudent.sno,snamego存储过程的执行测试结果：43、创建带输出参数的存储过程（1）创建一个名为course_average的存储过程，可查询某门课程考试的平均成绩。平均成绩可以输出，以便进一步调用。存储过程的创建语句：createprocedurecourse_average@cnochar(3),@averintoutputAsselect@aver=average(grade)fromscwherecno=@cnogodeclare@averintexecutecourse_average'001',@averoutputprint'该课程的平均分:'+cast(@averaschar(8))存储过程的执行测试结果：5（2）创建一执行该存储过程的批处理，要求当平均成绩小于60时，显示信息为：“XX课程的平均成绩为：XX，其平均分未达60分”。超过60时，显示信息为：“XX课程的平均成绩为：XX”。批处理语句：declare@averintexecutecourse_average'002',@averoutputif@aver60print'001课程的平均分为：'+cast(@averaschar(8))+'其平均分未达'elseprint'该课程平均分大于'执行测试结果：64、创建带重编译及加密选项的存储过程创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号由调用时输入）存储过程的创建语句：createprocedureupdate_sc@snochar(8),@cnochar(3)WithencryptionAsupdatescsetgrade='60'wheresno=@snoandcno=@cno存储过程的执行测试结果：75、使用T-SQL语句管理和维护存储过程（1）使用sp_helptext查看存储过程student_sc的定义脚本。结果：8（2）使用select语句查看student_sc存储过程的定义脚本。（提示：通过查询表sysobjects和表syscomments，存储过程的名称保存在表sysobjects的name列，定义脚本保存在表syscomments的text列。）(selecttextfromsysobjects,syscommentswherename=’student_sc’andsysobjects.id=syscomments.id)结果：（3）将存储过程stu_pr改为查询学号为2011001的学生的详细资料。（提示：使用alterprocedure语句修改）结果：alterprocedurestu_prAsselect*fromstudentwheresno=’20110001’9（4）删除存储过程stu_pr。（提示：使用dropprocedure语句删除）结果：dropprocedurestu_pr6、使用SQLServerManagementStudio管理存储过程10（1）在SQLServerManagementStudio中重新创建刚删除的存储过程stu_pr。结果：createprocedurestu_prAsselect*fromstudentwheresno=’20110001’（2）查看存储过程stu_pr，并将该过程修改为查询051班女生的所有资料。结果：executesp_helptextstu_prUnionAlterprocedurestu_prAsslect*fromstudentwhereclassno=’051’andsex=’女‘11（3）删除存储过程stu_pr。结果：