1、6实验六 存储过程实验六 存储过程一、实验目的(1)掌握T-SQL流控制语句。(2)掌握创建存储过程的方法。(3)掌握存储过程的执行方法。(4)掌握存储过程的管理和维护。二、实验内容1、创建简单存储过程(1)创建一个名为stu_pr的存储过程,该存储过程能查询出051班学生的所有资料,包括学生的基本信息、学生的选课信息(含未选课同学的信息)。要求在创建存储过程前请判断该存储过程是否已创建,若已创建则先删除,并给出“已删除!”信息,否则就给出“不存在,可创建!”的信息。if exists (select * from sysobjects where name=stu_pr and type=P
2、)begin drop procedure stu_pr print 已删除!endelse print 不存在,可创建!create procedure stu_prasselect distinct * from Student s left join SC on s.Sno=SC.Sno left join Course c on c.Cno=sc.Cnowhere Classno=051执行:exec stu_pr2、创建带参数的存储过程(1)创建一个名为stu_proc1的存储过程,查询某系、某姓名的学生的学号、姓名、年龄,选修课程名、成绩。系名和姓名在调用该存储过程时输入,其默认值
3、分别为“%”与“林%”。执行该存储过程,用多种参数加以测试。if exists (select * from sysobjects where name=stu_proc1 and type=P)begin drop procedure stu_proc1 print 已删除!endelse print 不存在,可创建!create procedure stu_proc1sdept varchar(10)=%,sname varchar(10)=林%as select Sname,s.Sno,YEAR(getdate()-YEAR(Birth) Age,Cname,Grade from Stu
4、dent s,Course c,SC where s.Sno=sc.Sno and c.Cno=sc.Cno and s.Sname like sname and s.Sdept like sdept执行:、exec stu_proc1、exec stu_proc1 sdept =%,sname =林%(2)创建一个名为Student_sc的存储过程,可查询出某段学号的同学的学号、姓名、总成绩。(学号起始号与终止号在调用时输入,可设默认值)。执行该存储过程。if exists (select name from sysobjects where name=Student_sc and type
5、=P) drop procedure Student_scgocreate procedure Student_sc sno_begin varchar(10)=20110001,sno_end varchar(10)=20110103as select s.Sno,Sname,SUM(grade) total_grade from Student s,SC where s.Sno=sc.Sno and s.Sno between sno_begin and sno_end group by s.Sno,Sname执行:exec Student_sc3、创建带输出参数的存储过程(1)创建一个名
6、为Course_sum的存储过程,可查询某门课程考试的总成绩。总成绩可以输出,以便进一步调用。if exists (select name from sysobjects where name=Course_sum and type=P)drop procedure Course_sumcreate procedure Course_sumcname varchar(10)=%asselect SUM(grade) total_grade,COUNT(sno) sno from Course c,SCwhere c.Cno=sc.Cno and Cname like cname 执行:exec
7、 Course_sum 高数(2)创建一执行该存储过程的批处理,要求当总成绩小于100时,显示信息为:“XX课程的总成绩为:XX,其总分未达100分”。超过100时,显示信息为:“XX课程的总成绩为:XX”。create procedure sum_grade2cname varchar(10),sum smallint outputasselect sum=sum(grade)from Course c,SCwhere c.Cno=sc.Cno and Cname like cnamedeclare sumgrade smallintexec sum_grade2 高数,sumgrade o
8、utputif sumgrade100begin print 高数的总成绩为:+CAST(sumgrade AS varchar(20)+,其总分未达到分。endelse print 高数的总成绩为:+CAST(sumgrade AS varchar(20)+。4、创建带重编译及加密选项的存储过程创建一个名为update_sc、并带重编译及加密选项的存储过程,可更新指定学号、指定课程号的学生的课程成绩。(学号、课程号由调用时输入)if exists (select name from sysobjects where name=update_sc and type=P)drop procedu
9、re update_sccreate procedure update_sccno varchar(10),sno varchar(10),grade intwith recompile,encryption -重编译,加密asupdate SCset grade=gradewhere SC.Cno=cno and SC.Sno=snoexec update_sc 001,20110001,805、使用T-SQL语句管理和维护存储过程(1)使用sp_helptext查看存储过程Student_sc的定义脚本sp_helptext Student_sc(2)使用select语句查看Student
10、_sc存储过程的定义脚本(提示:通过查询表sysobjects和表syscomments) select o.id, c.textfrom sysobjects o inner join syscomments c on o.id = c.idwhere o.type = p and o.name = Student_sc(3)将存储过程stu_pr改为查询学号为2011001的学生的详细资料。alter procedure stu_prsno varchar(10)asselect distinct * from Studentwhere Sno=20110001exec stu_pr sn
11、o=2011001(4)删除存储过程stu_pr。drop procedure stu_pr6、使用SQL Server Management Studio管理存储过程(1)在SQL Server Management Studio中重新创建刚删除的存储过程stu_pr选择数据库Student_info可编程性存储过程,右击“存储过程”新建存储过程出现如下画面:修改存储过程的名称为stu_pr修改SQL语句,使之成为完整的存储过程CREATE PROCEDURE stu_pr - Add the parameters for the stored procedure hereASBEGIN -
12、 SET NOCOUNT ON added to prevent extra result sets from - interfering with SELECT statements. SET NOCOUNT ON; - Insert statements for procedure here SELECT distinct * from Student s left join SC on s.Sno=SC.Sno left join Course c on c.Cno=sc.Cno where Classno=051END(2)查看存储过程stu_pr,并将该过程修改为查询051班女生的所
13、有资料。可编程性存储过程stu_pr,右击stu_pr修改出现如下画面:修改SQL语句,使之能查询051班所有女生的资料的存储过程ALTER PROCEDURE dbo.stu_pr - Add the parameters for the stored procedure hereASBEGIN - SET NOCOUNT ON added to prevent extra result sets from - interfering with SELECT statements. SET NOCOUNT ON; - Insert statements for procedure here SELECT distinct * from Student s left join SC on s.Sno=SC.Sno left join Course c on c.Cno=sc.Cno where Classno=051 and Sex=女END(3)删除存储过程stu_pr选择存储过程stu_pr,右击,选择“删除”
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1