Answer-实验六.doc

实验六：存储过程的应用一、实验目的 l 掌握利用查询分析器或对象资源管理器创建存储过程;l 掌握存储过程的执行与维护;l 理解使用存储过程来维护数据完整性。2、 实验内容本次实验所用的数据库主要包括的数据表为：学生、课程和成绩，其创建脚本如下：CREATE TABLE studentinfo(学号 char(10), 姓名 char(8), 性别 char(2), 年级 int,出生日期 datetime)GOCREATE TABLE course(课程号 char(4), 课程名 varchar(20), 学分 int)GOCREATE TABLE stumark(考试号 char(10), 学号 char(10), 笔试成绩 decimal(18,1),机试成绩 decimal(18,1) GO （1）一个能向创建学生表中插入一条记录的存储过程Insert_student,该过程需要5个参数，分别用来传递学号、姓名、性别、年级、出生日期5个值。 create proc insert_studentsno char(10),sname char(8), sex char(2),sgrade int, sbirth datetimeas begininsert into studentinfo values(sno,sname,sex,sgrade,sbirth)end（2）写出执行存储过程Insert_student的SQL语句，向学生表中插入一个新同学，并提供相应的实参值（实参值自定）。 exec insert_student 100001,张三,男,1,1990/1/1（3）创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要三个参数，分别用来传递课程号、课程名、学分，但允许参数“学分”的默认值为2，即当执行存储过程Insert_course时，未给参数“学分”提供实参值时，存储过程将按默认值2进行运算。create proc insert_coursecno char(4),cname varchar(20), credit int=2 as begininsert into course values(cno,cname, credit)end （4）执行存储过程Insert_course，向课程表Course中插入一门新课程。分两种情况写出相应的SQL命令： 提供三个实参值执行存储过程Insert_courseexec insert_course 1001,db,4 只提供二个实参值执行存储过程Insert_course，即：不提供与参数“学分”对应的实参值。 exec insert_course cno=1002,cname=os,credit=defaultexec insert_course 1003, net,default（5） 创建一个名为Query_stusystem的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的姓名、年级、性别及出生日期。 create procedure query_stusystem sno char(10) as begin select 姓名,年级,性别,出生日期 from studentinfo where 学号=snoend（6） 执行存储过程Query_stusystem，查询学号为”20060201”的学生的学号、班级号、性别及出生日期。写出完成此功能的SQL命令。 exec query_stusystem 20060201（7） 修改存储过程Query_stusystem，增加自定义处理错误信息，用来提示指定的学生学号不存在。ALTER procedure query_stusystem sno char(10) as IF (select count(*) from studentinfo where 学号=sno)=0begin PRINT 不存在该学生RETURNendELSE BEGIN select 姓名,年级,性别,出生日期 from studentinfo where 学号=snoEND（8） 请创建存储过程，查看本次考试平均分以及未通过考试的学员名单。Create proc check_score test_num char(10)as beginselect test_num as 考试号,avg(笔试成绩) 笔试平均成绩, avg(机试成绩) 机试平均成绩 from stumark where 考试号=test_numselect 姓名 as 未通过考试名单 from studentinfo,stumark where studentinfo.学号=stumark.学号 and (笔试成绩60 or 机试成绩60) and 考试号=test_numend执行： exec check_score 0001（9） 修改上例：由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。 分析：上述存储过程添加2个输入参数writtenPass（笔试及格线）和labPass （机试及格线）alter proc check_score test_num char(10),writtenpass decimal(18,1),labpass decimal(18,1) as begin select test_num as 考试号, avg(笔试成绩) 笔试平均成绩, avg(机试成绩) 机试平均成绩 from stumarkwhere 考试号=test_numselect 姓名 as 未通过考试名单 from studentinfo ,stumark where stusystem.学号=stumark.学号 and (笔试成绩writtenpass or 机试成绩=writtenpass and 机试成绩=labpass and 考试号=test_numselect num=count(*) from stumark where 笔试成绩=writtenpass and 机试成绩=labpass and 考试号=test_numEnd-执行：declare num int exec check_score2 0001,75,75,num output select num 通过考试人数方法二：在查询中使用COMPUTE子句create proc check_score2_1 test_num char(10),writtenpass decimal(18,1),labpass decimal(18,1)as beginselect 姓名as 通过考试名单 from stusystem,stumark where stusystem.学号=stumark.学号 and 笔试成绩=writtenpass and 机试成绩=labpass and 考试号=test_num compute count(姓名)end（11） 思考：如何返回及格率？（提示：用输出参数存储过程中用查询赋值语句分别求出及格人数与总人数，再求出及格率。）alter proc check_score3 test_num char(10),writtenpass decimal(18,1),labpass decimal(18,1),rate int output As begindeclare passnum real,sum_num realselect passnum=count(*) from stumark where 笔试成绩=writtenpass and 机试成绩=labpass and 考试号=test_numselect sum_num=count(*)