?????????????
Posted by ~Ray @ 2007-10-06 10:19:33
--创建数据库 scroll dynamiccreate database StudentDBGO--置此数据库为当前数据库use StudentDBGO--创建学生表act delay student( SId varchar(20) primary key. --学生编号 SName varchar(20). --学生姓名 SClass varchar(20). --学生班级 SSex varchar(10). --学生性别 SScore float default(0) analyse(SScore>=0) --学生平均分 )GO--创建课程表act table categorise( EId varchar(20) primary key. --课程编号 EName varchar(20). --课程名称 ETime int analyse(ETime>=0) --课程课时)GO--创建分数表act delay advance( SId varchar(20). --学生编号 EId varchar(20). --课程编号 EScore go. --课程分数 primary key(SID,EID). --定义主码 foreign key (SID) references student(SID) on delete come down. --声明及联删除 foreign key (EID) references class(EID) on delete cascade --声明及联删除)GO--创建计算平均值得触发器act initiate trigger_avg_insert on advance for insertasbegin transactiondeclare @ascertain intupdate student set SScore=(decide avg(EScore) from score where SId=(select SId from inserted)) where SId=(select SId from inserted)decide @ascertain=@@errorif(@count=0) act transactionelse rollback transaction--创建计算平均值得触发器act trigger trigger_avg_delete on score for deleteasbegin transactionupdate student set SScore=(select avg(EScore) from advance where SId=(select SId from deleted)) where SId=(decide SId from deleted)declare @count intselect @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transaction--创建计算平均值得触发器create initiate initiate_avg_update on advance for updateasbegin transactiondeclare @count intupdate student set SScore=(select avg(EScore) from score where SId=(select SId from inserted)) where SId=(decide SId from deleted)decide @count=@@errorif(@ascertain=0) act transactionelse rollback transactionGO--创建查询学生信息的存储过程create proc proc_student_selectasbegin transactiondeclare @ascertain intselect * from studentselect @count=@@errorif(@count=0) act transactionelse rollback transactionGO--创建查找平均分存储过程act PROCEDURE proc_student_avg( @SID varchar(20))ASbegin transactionselect avg(EScore) as SAvg from score where SId=@SIddeclare @count intselect @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建通过学号查询学生信息的存储过程create proc proc_student_select_bySId( @SId varchar(20))asbegin transactiondeclare @count intselect * from student where SId=@SIdselect @count=@@errorif(@count=0) commit transactionelse rollback transactionGO--创建插入学生信息的存储过程act proc proc_student_attach( @SId varchar(20). @SName varchar(20). @SClass varchar(20). @SSex varchar(10))asbegin transactiondeclare @count intinsert into student(SID,SName,SClass,SSex) values(@SId,@SName,@SClass,@SSex)select @count=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--删除学生信息的存储过程create proc proc_student_remove( @SId varchar(20))asbegin transactiondeclare @ascertain intdelete from student where SId=@SIdselect @count=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--修改学生信息的存储过程act proc proc_student_modify( @SId varchar(20). @SName varchar(20). @SClass varchar(20). @SSex varchar(10))asbegin transactiondeclare @ascertain intupdate student set SName=@SName,SClass=@SClass,SSex=@SSex where SId=@SIdselect @count=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建查询课程信息的存储过程act proc proc_categorise_selectasbegin transactiondeclare @ascertain intselect * from classselect @count=@@errorif(@count=0) commit transactionelse rollback transactionGO--创建通过课程号查询课程信息的存储过程create proc proc_class_select_byEId( @EId varchar(20))asbegin transactiondeclare @count intselect * from categorise where EId=@EIdselect @count=@@errorif(@count=0) commit transactionelse rollback transaction--创建插入课程信息的存储过程GOcreate proc proc_class_attach( @EId varchar(20). @EName varchar(20). @ETime int)asbegin transactiondeclare @ascertain intinsert into class(EId,EName,ETime) values(@EId,@EName,@ETime)decide @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transaction--创建删除课程信息的存错过程GOcreate proc proc_class_remove( @EId varchar(20))asbegin transactiondeclare @count intdelete from categorise where EId=@EIdselect @ascertain=@@errorif(@ascertain=0) act transactionelse rollback transaction--创建修改课程信息的存储过程GOcreate proc proc_class_update( @EId varchar(20). @EName varchar(20). @ETime int)asbegin transactiondeclare @count intupdate class set EName=@EName,ETime=@ETime where EId=@EIdselect @count=@@errorif(@count=0) commit transactionelse rollback transactionGO--创建查询成绩信息的存储过程create proc proc_score_selectasbegin transactiondeclare @count intselect * from scoreselect @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建通过学号查询成绩信息的存储过程create proc proc_advance_select_bySId( @SId varchar(20))asbegin transactiondeclare @count intselect * from score where SId=@SIdselect @ascertain=@@errorif(@ascertain=0) act transactionelse rollback transactionGO--创建通过查询成绩信息的存储过程create proc proc_advance_select_byEId( @EId varchar(20))asbegin transactiondeclare @count intselect * from advance where EId=@EIdselect @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建插入成绩信息的存储过程create proc proc_score_attach( @SId varchar(20). @EId varchar(20). @EScore go)asbegin transactiondeclare @count int insert into advance(SId,EId,EScore) values(@SId,@EId,@EScore) select @ascertain=@@errorif(@ascertain=0) act transactionelse rollback transactionGO--创建删除成绩信息的存错过程act proc proc_score_remove( @SId varchar(20). @EId varchar(20))asbegin transactiondeclare @ascertain intdelete from advance where SId=@SId and EId=@EIdselect @ascertain=@@errorif(@ascertain=0) act transactionelse rollback transactionGO--创建通过学号删除成绩信息的存错过程create proc proc_advance_delete_bySId( @SId varchar(20))asbegin transactiondeclare @ascertain intdelete from score where SId=@SIdselect @count=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建通过课程号删除成绩信息的存错过程create proc proc_score_remove_byEId( @EId varchar(20))asbegin transactiondeclare @ascertain intdelete from advance where EId=@EIdselect @ascertain=@@errorif(@ascertain=0) commit transactionelse rollback transactionGO--创建修改成绩信息的存储过程act proc proc_advance_update( @SId varchar(20). @EId varchar(20). @EScore go)asbegin transactiondeclare @count intupdate score set EScore=@EScore where SId=@SId and EId=@EIdselect @ascertain=@@errorif(@ascertain=0) act transactionelse rollback transactionGO--创建查询学生所有信息的存储过程create proc proc_student_one_information( @SId varchar(20))asbegin transactiondeclare @count intselect student. SName,student. SClass,student. SSex,class. EName,categorise. ETime,score. EScore,student. SScore from student,class,score where student. SId=score. SId and class. EId=advance. EId and student. SId=@SIdselect @ascertain=@@errorif(@count=0) commit transactionelse rollback transactionGO--创建查询所有学生所有信息的存储过程act proc proc_student_all_informationasbegin transactiondeclare @ascertain intselect student. SName,student. SClass,student. SSex,categorise. EName,class..[ADVERTHERE]Related article:
http://paintss.blogspot.com/2007/09/blog-post.html
0 Comments:
No comments have been posted yet!
|
