数据库的存储过程

数据库的存储过程 实验六 存储过程 一、实验目的 (1)掌握T-SQL流控制语句。 (2)掌握创建存储过程的方法。 (3)掌握存储过程的执行方法。 (4)掌握存储过程的管理和维护。 二、实验内容 1、创建简单存储过程 (1)创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息(含未选课同学的信息)。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除～”信息，否则就给出“不存在，可创建～”的信息。 if exists (select * from sysobjects where name='stu_pr' and type='P') begin drop procedure stu_pr print '已删除～' end else print '不存在，可创建～' create procedure stu_pr as 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' 执行: exec stu_pr 2、创建带参数的存储过程 (1)创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。执行该存储过程，用多种参数加以测试。 if exists (select * from sysobjects where name='stu_proc1' and type='P') begin drop procedure stu_proc1 print '已删除～' end else print '不存在，可创建～' create procedure stu_proc1 @sdept varchar(10)='%',@sname varchar(10)='林%' as select Sname,s.Sno,YEAR(getdate())-YEAR(Birth) Age,Cname,Grade from Student 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='P') drop procedure Student_sc go create procedure Student_sc @sno_begin varchar(10)='20110001',@sno_end varchar(10)='20110103' as 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_sc 3、创建带输出参数的存储过程 (1)创建一个名为Course_sum的存储过程，可查询某门课程考试的总成绩。 总成绩可以输出，以便进一步调用。 if exists (select name from sysobjects where name='Course_sum' and type='P') drop procedure Course_sum create procedure Course_sum @cname varchar(10)='%' as select SUM(grade) total_grade,COUNT(sno) sno from Course c,SC where c.Cno=sc.Cno and Cname like @cname 执行: exec Course_sum '高数' (2)创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为:“XX课程的总成绩为:XX,其总分未达100分”。超过100时，显示信息为:“XX课程的总成绩为:XX”。 create procedure sum_grade2 @cname varchar(10),@@sum smallint output as select @@sum=sum(grade) from Course c,SC where c.Cno=sc.Cno and Cname like @cname declare @@sumgrade smallint exec sum_grade2 '高数',@@sumgrade output if @@sumgrade<100 begin print '高数的总成绩为:'+CAST(@@sumgrade AS varchar(20))+'，其总分未达到分。' end else print '高数的总成绩为:'+CAST(@@sumgrade AS varchar(20))+'。' 4、创建带重编译及加密选项的存储过程 创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定 课程号的学生的课程成绩。(学号、课程号由调用时输入) if exists (select name from sysobjects where name='update_sc' and type='P') drop procedure update_sc create procedure update_sc @cno varchar(10),@sno varchar(10),@grade int with recompile,encryption --重编译，加密 as update SC set grade=@grade where SC.Cno=@cno and SC.Sno=@sno exec update_sc '001','20110001','80' 5、使用T-SQL语句管理和维护存储过程 (1)使用sp_helptext查看存储过程Student_sc的定义脚本 sp_helptext Student_sc (2)使用select语句查看Student_sc存储过程的定义脚本(提示:通过查询 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf sysobjects 和表syscomments) select o.id, c.text from sysobjects o inner join syscomments c on o.id = c.id where o.type = 'p' and o.name = 'Student_sc' (3)将存储过程stu_pr改为查询学号为2011001的学生的详细资料。 alter procedure stu_pr @sno varchar(10) as select distinct * from Student where Sno='20110001' exec stu_pr @sno='2011001' (4)删除存储过程stu_pr。 drop procedure stu_pr 6、使用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 here AS BEGIN -- 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' END (2)查看存储过程stu_pr，并将该过程修改为查询051班女生的所有资料。 可编程性?存储过程?stu_pr，右击stu_pr?修改 出现如下画面: 修改SQL语句，使之能查询051班所有女生的资料的存储过程 ALTER PROCEDURE [dbo].[stu_pr] -- Add the parameters for the stored procedure here AS BEGIN -- 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，右击，选择“删除”