1183011程伟Word格式文档下载.docx
《1183011程伟Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《1183011程伟Word格式文档下载.docx(11页珍藏版)》请在冰豆网上搜索。
fromSCjoincoursecono=o
joinstudentsons.sno=sc.sno
wheredept=@dept
execp1'
计算机系'
(3)createprocedurep2
@deptchar(20),@aintoutput
select@a=count(*)
fromstudent
wheredept=@deptandsex='
男'
declare@Aint
execp2'
@Aoutput
print@A
(4)createprocedurep3
@snamechar(20),@semesterint,
@menshuintoutput,@avgnumeric(4,2)output
selectCOUNT(*),AVG(grade)
fromstudentsjoinscons.sno=sc.snojoincoursecono=o
wheresname=@snameandsemester=@semester
groupbysc.sno
declare@Aint,@Bnumeric(4,2)
execp3'
李勇'
2,@Aoutput,@Boutput
(5)createprocedurep4
@snochar(20),
@menshuintoutput
if(selectCOUNT(*)fromstudentwhere@sno=sno)=0
return1
if(selectCOUNT(*)fromscwhere@sno=sno)=0
return2
if(selectCOUNT(*)fromscwhere@sno=sno)=1
return0
select@menshu=COUNT(*)fromscgroupbysno
declare@Aint,@Bint
execp4'
0811101'
@Boutput
print@B
(6)createprocedurep5
@snochar(20)
if(selectCOUNT(*)fromstudentwhere@sno=sno)=0--学生不存在
if(selectCOUNT(*)fromscwhere@sno=sno)=0--学生没有选课
deletefromsc
wheresno=@sno
declare@Aint,@Bint,@TIPvarchar(40)
exec@A=p5'
set@TIP=case@A
when1then'
没有指定学生'
when2then'
该学生没有选课'
end
(7)createprocedurep6
@cnochar(10),@semesterint
updatecoursesetsemester=@semester
wherecno=@cno
execp6'
C001'
4
(8)createprocedurep7
@cnochar(10),@cnmechar(10),
@creditint,@semesterint
insertintocoursevalues(@cno,@cnme,@credit,@semester)
execp7'
C0010'
'
程伟'
20,6
存储过程
2、
(1)createtriggertri_dept
onStudentafterinsert,update
ifexists(select*frominserted
wheredeptnotin('
'
信息管理系'
数学系'
通信工程系'
))
rollback
(2)createTRIGGEReve_total_credit
ONcourseAFTERINSERT
AS
declare@tint
SELECT@t=SUM(C.Credit)FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
IF(@t<
20or@t>
30)
BEGIN
PRINT'
本学期课程学分限制在--30之间!
'
ROLLBACK
END
(3)CREATETRIGGEReve_total_cno
SELECT@t=COUNT(*)FROMCoursec
JOINSCSONS.cno=C.cno
0or@t>
6)
本学期选课门数不能超过门!
(4)createtriggertri_delCno
oncourseafterdelete
ifexists(select*fromdeleteddwhered.Cnoin(selectCnofromsc))
rollback
(5)