实验六 游标、存储过程与触发器.doc

信 息 工 程 学 院实验报告的内容与格式按任课教师的要求书写。 数据库原理与应用(SQL Server 2008) 实验/实习报告存储过程与触发器实验六 游标、存储过程与触发器一、实验目的掌握使用T-SQL实现游标、存储过程和触发器的创建，使用方法。二、实验内容在实验一、实验二创建的表中用T-SQL语句完成以下内容：1使用游标实现将SC表中及格的选课信息输出。declareSno varchar(10),Cno varchar(10),Grade intdeclare cursor_SC cursor for select Sno,Cno,Grade from SCopen cursor_SCfetch cursor_SC into Sno,Cno,Gradewhile(FETCH_STATUS=0)beginif Grade=60select Sno sno,Cno cno,Grade grade fetch cursor_SC into Sno,Cno,Gradeendclose cursor_SCdeallocate cursor_SC 2使用游标将SPJ表中的偶数行输出。declaresno varchar(10),pno varchar(20),jno varchar(10),qty smallint ,n int=2declare cursor_SPJ cursor static for select sno,Pno,Jno,qty from SPJopen cursor_SPJfetch absolute n from cursor_SPJ into sno,pno,jno,qtywhile(FETCH_STATUS=0)beginselect sno sno,pno pno,jno pno,qty qtyset n=n+2fetch absolute n from cursor_SPJ into sno,pno,jno,qtyendclose cursor_SPJdeallocate cursor_SPJ3创建存储过程，根据指定的学生姓名查询该学生所修课程的课程信息，将课程号和课程名输出，并使用exec语句执行存储过程。创建存储过程：CREATE PROCEDURE student_xx s_name varchar(10)AS SELECT Cname, SC.Cno FROM s,sc,c WHERE s.sno=sc.sno and o=o and Sname=s_name 执行存储过程：EXEC student_xx 孙天孙天的学号为09105101，他的选课信息为：对应的课程名为：可知对应了，查询的结果。4创建存储过程，根据指定的学生学号统计该学生选修的所有课程的平均成绩和选课门数，将统计的结果用输出参数返回，并使用exec语句执行存储过程。创建存储过程：CREATE PROCEDURE Avg_Count S_Sno varchar(20), avg_grade int output, total int outputAS SELECT avg_grade = AVG(Grade), total = COUNT(*) FROM SC ,C WHERE So = C.cno and Sno=S_Sno使用exec语句执行存储过程：Declare a int,b intExec Avg_Count 09105101 ,a output,b outputSelect a avg_grade,b total他的平均分=（65+55+94+50）/4=66 ，可知，结果正确。5创建存储过程，根据用户指定的供应商号删除SPJ表中相应的供货信息，并使用exec语句执行存储过程。创建存储过程：CREATE PROCEDURE SPJ_gh sn varchar(20)AS DELETE FROM SPJ WHERE sn = Sno执行：EXEC SPJ_gh S1删除信息前：删除后：6创建存储过程，将指定零件的重量增加指定的值，并使用exec语句执行存储过程。创建存储过程：goCREATE PROCEDURE P_weig cname varchar(20),no varchar(10)AS UPDATE P SET Weight=Weight+no WHERE Pname = cname Exec P_weig 螺母,20执行前：执行后7用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少于300，如果少于则自动改为300。drop TRIGGER t1CREATE TRIGGER t1 ON SPJ FOR INSERT,UPDATE AS declare sno varchar(4),pno varchar(4),jno varchar(4),city varchar(20), qty int select city=city , sno=inserted.sno, pno=pno,jno=jno,qty=qty from inserted,S where inserted.sno=s.sno if city=北京 and qty300 update SPJ set qty=300 where sno=sno and pno=pno and jno=jno 检验：update spjset qty=250where sno=s2 and Jno=J2 执行前：执行后，蓝色区域部分为更新数据：再次检验：update spjset qty=550where sno=s2 and Jno=J2 8在SC关系中增加新属性列Status，用来记录课程成绩的等级，059分为“D”，6069为“C”，7089为“B”，90以上为“A”。要求status属性列的值由用户在插入、更新选课成绩时系统自动填写和更新。alter table SC add status varchar(10)goCREATE TRIGGER t1_sc ON SC FOR INSERT,UPDATE AS declare sno varchar(20),cno varchar(20),grade int,status varchar(10) select sno=Sno,cno=Cno,grade=Grade from inserted select status= case when grade =60 and grade=70 and grade90 then B else A end up