《数据库原理及应用实验七》由会员分享，可在线阅读，更多相关《数据库原理及应用实验七（12页珍藏版）》请在金锄头文库上搜索。
1、实验七 存储过程一、实验目的（1）掌握T-SQL流控制语句。（2）掌握创建存储过程的方法。（3）掌握存储过程的执行方法。（4）掌握存储过程的管理和维护。二、实验内容1、创建简单存储过程（1）创建一个名为stu_pr的存储过程，该存储过程能查询出051班学生的所有资料，包括学生的基本信息、学生的选课信息（含未选课同学的信息）。要求在创建存储过程前请判断该存储过程是否已创建，若已创建则先删除，并给出“已删除！”信息，否则就给出“不存在，可创建！”的信息。if exists (select name from sysobjects where name=stu_prand type=p) begin
2、 print 已删除！ drop procedure stu_pr endelseprint 不存在，可创建！gocreate procedure stu_procasselect *from S left outer join SC on (S.Sno=SC.Sno) left outer join C on (C.Cno=SC.Cno)where Sdept=CSexec stu_proc2、创建带参数的存储过程（1）创建一个名为stu_proc1的存储过程，查询某系、某姓名的学生的学号、姓名、年龄，选修课程名、成绩。系名和姓名在调用该存储过程时输入，其默认值分别为“%”与“林%”。执行该
3、存储过程，用多种参数加以测试。if exists (select name from sysobjects where name=stu_proc1 and type=p) begin print 已删除! drop procedure stu_proc1 end else print 不存在,可创建! go create procedure stu_proc1 Sdept char(8)=%,Sname varchar(8)=李% as select Sdept,S.Sno,Sname,Sage,Cname,Grade from S,SC,C where S.Sno=SC.Sno and C
4、.Cno=SC.Cno and Sdept like Sdept and Sname like Sname执行所创建存储过程：use studentexecute stu_proc1 CS,李勇execute stu_proc1 信息安全,胡光璟（2）创建一个名为studentStudent_sc的存储过程，可查询出某段学号的同学的学号、姓名、总成绩。（学号起始号与终止号在调用时输入，可设默认值）。执行该存储过程。if exists (select name from sysobjects where name=Student_scand type=p) begin print 已删除！ dr
5、op procedure student_sc endelseprint 不存在，可创建！gocreate procedure Student_scSno1 char(8),Sno2 char(8)asselect S.Sno,Sname,SUM(Grade)总成绩from S,SC,Cwhere S.Sno=SC.Sno and C.Cno=SC.Cno and S.Sno=Sno1 and S.Sno=Sno2 group by S.Sno,Sname执行所创建存储过程：execute Student_sc 95000,950093、创建带输出参数的存储过程（1）创建一个名为courseC
6、ourse_sum的存储过程，可查询某门课程考试的总成绩。总成绩可以输出，以便进一步调用。if exists (select name from sysobjects where name=Course_sumand type=p) begin print 已删除！ drop procedure Course_sum endelseprint 不存在，可创建！gocreate procedure Course_sumCname varchar(20),sum int outputasselect sum=sum(Grade)from SC,Cwhere C.Cno=SC.Cno and Cna
7、me=Cname group by SC.Cno,Cnamedeclare ping intexec Course_sum 数学,ping outputprint 高数的考试总成绩为：+cast(ping as varchar(20)（2）创建一执行该存储过程的批处理，要求当总成绩小于100时，显示信息为：“XX课程的总成绩为：XX,其总分未达100分”。超过100时，显示信息为：“XX课程的总成绩为：XX”。declare sum int declare Cname varchar(20) Exec Course_sum cname,sum out begin if sum 100 prin
8、t cast(cname as varchar)+课程的总成绩为:+cast(sum as varchar)+ ,其总分未达分 else print cast(cname as varchar)+课程的总成绩为:+cast(sum as varchar) end declare sum int declare Cname varchar(20) set Cname=数学Exec Course_sum cname,sum out begin if sum 100 print cast(cname as varchar)+课程的总成绩为:+cast(sum as varchar)+ ,其总分未达分
9、 else print cast(cname as varchar)+课程的总成绩为:+cast(sum as varchar) end 4、创建带重编译及加密选项的存储过程创建一个名为update_sc、并带重编译及加密选项的存储过程，可更新指定学号、指定课程号的学生的课程成绩。（学号、课程号由调用时输入）if exists (select name from sysobjects where name=update_scand type=p) begin print 已删除！ drop procedure update_sc endelseprint 不存在，可创建！gocreate pr
10、ocedure update_sc sno char(8),cno char(3),grade tinyintwith RECOMPILE , ENCRYPTIONasupdate SCset Grade=grade where Sno=sno and Cno=cno declare sno char(8),cno char(3),grade tinyint set sno=95001 set cno=3set grade=100exec update_sc sno,cno,gradebegin print cast(sno as varchar) +的+cast(cno as varchar
11、)+课程成绩为：+cast(grade as varchar)end5、使用T-SQL语句管理和维护存储过程（1）使用sp_helptext查看存储过程studentStudent_sc的定义脚本exec sp_helptext student_sc（2）使用select语句查看studentStudent_sc存储过程的定义脚本（提示：通过查询表sysobjects和表syscomments） select * from sysobjects,syscommentswhere name = Student_sc（3）将存储过程stu_proc改为查询学号为95001的学生的详细资料。alte
12、r procedure stu_procasselect *from S left outer join SC on (S.Sno=SC.Sno) left outer join C on (C.Cno=SC.Cno)where S.Sno=95001（4）删除存储过程stu_proc。drop procedure stu_proc6、使用SQL Server Management Studio管理存储过程（1）在SQL Server Management Studio中重新创建刚删除的存储过程stu_prcreate procedure stu_procasbeginselect *from
13、 S left outer join SC on (S.Sno=SC.Sno) left outer join C on (C.Cno=SC.Cno)where S.Sno=95001end（2）查看存储过程stu_proc，并将该过程修改为查询051班女生的所有资料。ALTER procedure dbo.stu_procasbeginselect *from S left outer join SC on (S.Sno=SC.Sno) left outer join C on (C.Cno=SC.Cno)where S.Sno=95001 and Ssex=男end（3）删除存储过程stu_pr
