数据库实验六

1、实验六 游标、存储过程与触发器一、实验目的掌握使用T-SQL实现游标、存储过程和触发器的创建，使用方法。二、实验内容在实验一、实验二创建的表中用T-SQL语句完成以下内容：1创建存储过程，根据指定的学生姓名查询该学生所修课程的课程信息，将课程号和课程名输出，并使用exec语句执行存储过程。gocreate procedure student_info s_sname varchar(10) as select Sname,C.Cno,Cname from C,S,SC where S.Sno = SC.Sno and C.Cno = SC.Cno and Sname = s_sname执行存储

2、过程EXEC student_info s_sname = '孙天'2创建存储过程，根据指定的学生学号统计该学生选修的所有课程的平均成绩和选课门数，将统计的结果用输出参数返回，并使用exec语句执行存储过程。gocreate procedure avg_count s_sno varchar(10), avg_grade int output, total int output as select avg_grade = AVG(Grade), total = COUNT(S.Sno) from S,SC where S.Sno = SC.Sno and S.Sno = s_s

3、no执行存储过程declare grade int, to intexec avg_count '09105101', grade output, to outputselect grade as 平均成绩, to as 选课门数3创建存储过程，根据用户指定的供应商号删除SPJ表中相应的供货信息，并使用exec语句执行存储过程。gocreate procedure cno_info s_sno varchar(10) as delete from SPJ where SPJ.Sno = s_sno执行存储过程exec cno_info s_sno = 'S3'其

4、中没有S3的供应信息4创建存储过程，将指定零件的重量增加指定的值，并使用exec语句执行存储过程。gocreate procedure p_weight p_pno varchar(10) as update P set Weight = Weight + 2 where Pno = p_pno执行存储过程之前执行存储过程之后 exec p_weight p_pno = 'P1'5使用游标实现将SC表中及格的选课信息输出。declare sc_sno varchar(10), sc_cno varchar(10), sc_grade int declare c1 cursor

5、for select Sno, Cno, Grade from SC open c1 fetch c1 into sc_sno, sc_cno, sc_grade while(FETCH_STATUS=0) begin if(sc_grade >= 60) select sc_sno as 学号, sc_cno as 课程号, sc_grade as 成绩 fetch c1 into sc_sno, sc_cno, sc_grade end close c1deallocate c16使用游标将SPJ表中的偶数行输出。declarespj_sno varchar(10), spj_pno

6、 varchar(10), spj_jno varchar(10), spj_qty smallint, n int = 5declare c1 cursor static for select Sno, Pno, Jno, QTY from SPJopen c1fetch absolute n from c1 into spj_sno, spj_pno, spj_jno, spj_qtywhile(FETCH_STATUS=0)beginselect spj_sno,spj_pno,spj_jno,spj_qtyset n=n+5fetch absolute n from c1 into s

7、pj_sno,spj_pno,spj_jno,spj_qtyendclose c1deallocate c17用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少于300，如果少于则自动改为300。创建触发器之前创建触发器create trigger t1 on SPJafter insert,updateasdeclare sno varchar(10), pno varchar(10), jno varchar(10), qty smallint, city varchar(10)declare c1 cursor for select S.Sno,Pno,Jno,QTY,Cit

8、y from inserted,Sopen c1fetch next from c1 into sno,pno,jno,qty,citywhile(fetch_status=0)begin if qty < 300 and sno in (select Sno from S where City = '北京') update SPJ set QTY = 300 where Sno = sno and Pno = pno and Jno = jno fetch next from c1 into sno,pno,jno,qty,cityendclose c1dealloca

9、te c1测试触发器insert into SPJvalues('S2','P2','J2',100)update SPJset QTY = 100where Sno = 'S2' and Pno = 'P3' and Jno = 'J1'8在SC关系中增加新属性列Status，用来记录课程成绩的等级，059分为“D”，6069为“C”，7089为“B”，90以上为“A”。要求status属性列的值由用户在插入、更新选课成绩时系统自动填写和更新。先为SC表新增加一列alter table SCadd

10、 status varchar(10)create trigger t1 on SCafter insert,updateasdeclare grade int,sno varchar(10),cno varchar(10)declare c1 cursor for select sno,cno,grade from insertedopen c1fetch next from c1 into sno,cno,gradewhile(fetch_status=0)beginif grade >= 90 update sc set status = 'A' where sno

11、 = sno and cno = cnoelse if grade >= 70 update sc set status = 'B' where sno = sno and cno = cnoelse if grade >= 60 update sc set status = 'C' where sno = sno and cno = cnoelse update sc set status = 'D' where sno = sno and cno = cnofetch next from c1 into sno,cno,gradeendclose c1deallocate c1测试触发器（插入）insert into SC(Sno,Cno,Grade)values('09105104','003',95)update SCset Grade = 70where Sno = '09105101' and Cno = '002'