实验六-游标、存储过程和触发器

实验六游标、存储过程与触发器一、实验目的掌握使用T-SQL实现游标、存储过程和触发器的创建，使用方法。二、实验内容在实验一、实验二创建的表中用T-SQL语句完成以下内容：1.使用游标实现将及格的选课信息输出。useStudent;DECLARESTCURSORFORSELECT*FROMCWHERECnoIN(SELECTCnoFROMSCWHEREGradebetween60and100)DECLARE@Cnonchar(10)DECLARE@Cnamenchar(20)DECLARE@CreditfloatDECLARE@Propertynchar(14)DECLARE@Tnamevarchar(10)openSTfetchSTINTO@Cno,@Cname,@Credit,@Property,@Tname;WHILE@@FETCH_STATUS=0beginPRINT@Cno+''+@Cname+''+cast(@Creditaschar(10))+''+@PropertyfetchnextfromSTINTO@Cno,@Cname,@Credit,@Property,@Tname;endcloseSTdeallocateST2.使用游标将SPJ表中的偶数行输出。usegongcheng1;DECLARESoCURSORFORselectSno,Pno,Jno,QTYfrom(select*,row_number()over(orderbygetdate())'rn'fromSPJ)twheret.rn%2=0DECLARE@Snochar(10)DECLARE@Pnochar(10)DECLARE@Jnochar(10)DECLARE@QTYINTOPENSofetchnextfromSoINTO@Sno,@Pno,@Jno,@QTYWHILE@@FETCH_STATUS=0beginPRINT@Sno+''+@Pno+''+@Jno++''+cast(@QTYaschar(10))fetchnextfromSoINTO@Sno,@Pno,@Jno,@QTYendcloseSodeallocateSo3.创建存储过程，查询赵永亮所修课程的课程信息，将课程号和课程名输出。CREATEPROCEDURECno_Cname@NAMEVARCHAR(20)ASSELECTC.Cno,C.CnameFROMSC,C,SWHERE(S.Sname=@NAMEANDSC.Cno=C.CnoANDSC.Sno=S.Sno)EXECCno_Cname'赵永亮'DROPPROCEDURECno_Cname4．创建存储过程，统计指定学生修课的平均成绩和选课门数，将统计的结果用输出参数返回。CREATEPROCEDUREAvgCount@snvarchar(20),@avg_gradeintoutput,@totalintoutputASSELECT@avg_grade=AVG(Grade),@total=COUNT(S.Sno)FROMSC,SwhereS.Sno=@snandSC.Sno=S.SnoDECLARE@Avggradeint,@totalintExecAvgCount'09105107',@avggradeoutput,@totaloutputSelect@avggrade,@total5．创建存储过程，在学生表Student中插入一条完整的元组。CREATEPROCEDUREInsert_S@Sno1nchar(8),@Sname1nchar(10),@Ssex1nchar(2),@Agesmallint,@Deptment1nchar(20)ASINSERTINTOS(Sno,Sname,Ssex,Sage,Deptment)VALUES(@Sno1,@Sname1,@Ssex1,@Age,@Deptment1)EXECInsert_S'09105110','唐烨','男',19,'信息'6．创建存储过程，根据用户指定的供应商号删除SPJ表中相应的供货信息。CREATEPROCEDUREDel_SPJ@Sel_Snonchar(10)ASDELETEFROMSPJWHERE@Sel_Sno=SnoEXECDel_SPJ'S1'7．创建存储过程，将指定零件的重量增加指定的值。CREATEPROCEDUREAdd_weight@Pno1char(10),@add_weightINTASUPDATEPSETWeight=Weight+@add_weightWHEREP.Pno=@Pno1EXECAdd_weight'P1',40dropPROCEDUREAdd_weight8．用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少于300，如果少于则自动改为300。CREATETRIGGERTYONSPJAFTERUPDATE,INSERTASDECLARE@sNOchar(10),@QtyINTSELECT@sNO=Sno,@Qty=QTYFROMINSERTEDIF@Qty300AND@sNOIN(SELECTSnoFROMSWHERECity='北京')UPDATESPJSETQTY=300INSERTINTOSPJ(Sno,Pno,Jno,QTY)VALUES('S2','P2','J1',100)DROPTRIGGERTY9．在SC关系中增加新属性列Status，用来记录课程成绩的等级，0－59分为“不合格”，60－69为“合格”，70－89为“良好”，90以上为“优秀”。要求status属性列的值由用户在插入、更新选课成绩时系统自动填写和更新。ALTERTABLESCADDstatuschar(10)CREATETRIGGERTY_addONSCFORUPDATE,INSERTASDECLARE@SNOnchar(8),@CNOnchar(10),@GRADEINT,@STATUSchar(10)SELECT@SNO=Sno,@CNO=Cno,@GRADE=GradeFROMINSERTEDSELECT@STATUS=CASEWHEN@GRADE60THEN'不合格'WHEN@GRADE=60and@GRADE70THEN'合格'WHEN@GRADE=70and@GRADE90THEN'良好'ELSE'优秀'endUPDATESCSETstatus=@STATUSWHERESno=@SNOANDCno=@CNOANDGrade=@GRADEINSERTINTOSC(Sno,Cno,Grade)VALUES('09105101','001',66)DROPTRIGGERTY_add三、实验总结这是数据结构最后一次实习。在本次实习中遇到了很多问题，在游标，存储过程以及触发器的使用过程中多多少少都存在一些问题。比如，使用游标将SPJ表中的偶数行输出，这个问题中就不知道怎么输出偶数行。但是最终在同学和老师的帮助下成功的完成了本次实验。