【文章內(nèi)容簡介】 oexec con_notice 3, 39。2015/6/23 22:0039。, 39。200000000139。, 39。201339。, 39。信息工程學院39。go圖47圖48/*查看各學院申請比賽經(jīng)費情況*/createprocedure show_dep_contestas select conHost, sum(conBudget) totalBudget from conInfor groupby conHost, conVerify having conVerify =39。審核通過39。goexec verify_contest4,39。審核通過39。goexec show_dep_contestgo圖49dropprocedure reg_contestgo/*報名參賽過程*/createprocedure reg_contest @conID int, @stuID char(10), @Guide varchar(30)as insertinto conResult ( conID, conStuID, conStuGuiName ) values ( @conID, @stuID, @Guide )goexec reg_contest 2, 39。201301311239。, 39。劉全中39。go圖410圖411dropproc set_scorego/*錄入成績過程*/createprocedure set_score @conID int, @stuID char(10), @result realas update conResult set conStuScore = @result where conID = @conID and conStuID = @stuIDgoexec set_score 2, 39。201301311239。, go/*成績排名過程*/dropproc con_rankgocreateprocedure con_rank @conID intas begin declare @n int, @preRank int, @score real, @preScore real select @n = 1, @preRank = 1, @preScore = 0 declare rank_cursor cursor forselect conStuScore from conResult where conID = @conID orderby conStuScore desc open rank_cursor fetchnextfrom rank_cursor into @score while(@@FETCH_STATUS= 0) begin if(@score = @preScore) begin update conResult set conStuRank = @preRank wherecurrentof rank_cursor end else begin update conResult set conStuRank = @n wherecurrentof rank_cursor select @preRank = @n end select @preScore = @score, @n = @n + 1 fetchnextfrom rank_cursor into @score end close rank_cursor deallocate rank_cursor select* from conResult where conID = @conID orderby conStuScore desc endgoexec con_rank3go圖412droptrigger verify_noticegocreatetrigger verify_notice on conInfor afterupdateas ifupdate(conVerify) begin declare @newVerify varchar(10), @noticeTo varchar(10), @conName varchar(30) select @newVerify = conVerify, @noticeTo = conTeaID, @conName = conName from inserted insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。000000000039。, 39。系統(tǒng)提示：您申請的39。+ @conName +39。審核情況已被修改為： 39。+ @newVerify ) insertinto teaNotice ( teaNotID, teaNotTo ) values ( (selectident_current(39。notice39。)), @noticeTo ) endgoexec verify_contest 6, 39。審核未通過39。go圖413圖414droptrigger result_noticegocreatetrigger result_notice on conResult afterupdateas if(update(conStuRank)) begin declare @score real, @rank int, @conName varchar(30), @StuID varchar(10), @conID varchar(10) select @conID = conID, @stuID = conStuID, @score = conStuScore, @rank = conStuRank from inserted select @conName = conName from conResult, conInfor where = insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。000000000039。, 39。系統(tǒng)提示：您參加的39。+ @conName +39。得分為：39。+cast(@score asvarchar)+39。排名為: 39。+cast(@rank asvarchar) ) insertinto stuNotice ( stuNotID, stuNotTo ) values ( (selectident_current(39。notice39。)), @stuID ) endgoexec con_rank 1go圖415圖416droptrigger con_change_noticegocreatetrigger con_change_notice on conInfor afterupdateas if(update(conTime)orupdate(conAddress)) begin declare @conName varchar(30), @conTime datetime, @conAddress varchar(50), @StuID varchar(10), @conID varchar(10) select @conID = conID, @conName = conName, @conTime = conTime, @conAddress = conAddress from inserted declare stuID_cursor cursor for select conStuID from conResult where conID = @conID open stuID_cursor fetchnextfrom stuID_cursor into @stuID while(@@FETCH_STATUS= 0) begin insertinto notice ( noticeTime, noticeHostID, noticeInfo ) values ( dateadd(minute, 5,getdate()), 39。000000000039。, 39。系統(tǒng)提示：您參加的39。+ @conName +39。信息已更改。\n\t 地址為：39。+ @conAddress +39。 時間為: 39。+cast(@conTime asvarchar) ) insertinto stuNotice ( stuNotID, stuNotTo ) values ( (selectident_current(39。notice39。)), @stuID ) fetchnextfrom stuID_cursor into @stuID end close stuID_cursor deallocate stuID_cursor endgoupdate conInforset conAddress =39。N811039。where conID = 1圖417 數(shù)據(jù)庫及數(shù)據(jù)庫對象建立按照邏輯設計的成果，由于存在外鍵約束，所以對建表的順序有約束。根據(jù)各個約束條件。分別建立學院信息表，專業(yè)信息表，教師信息表，學生信息表，賽事信息表，通知信息表，學生通知表，教師通知表，賽事成績表。DDL語句見附錄4。 數(shù)據(jù)入庫選擇一些學院和專業(yè)錄入到數(shù)據(jù)庫，然后隨機錄入教師和學生信息。然后根據(jù)，在錄入過程中根據(jù)外鍵約束做相應調(diào)整。 數(shù)據(jù)庫測試 基本表在建立和錄入數(shù)據(jù)時就已經(jīng)進行測試和調(diào)整了，具體可見附錄4中的DDL語句。下面主要對建立的存儲過程和觸發(fā)器測試申請比賽的過程：createorreplaceprocedure apply_contest( v_conName in %type, v_conInfor in %type, v_conLevel in %type, v_conHost in %type, v_conTeaId in %type, v_conBudget in %type, v_conApply in %type)isbegininsertinto conInfor( conid, conName, conInfor, conLevel, conHost, conTeaId, conBudget, conApply, conVerify)values( , v_conName, v_coninfor, v_conlevel, v_conhost, v_conteaid, v_conbudget, v_conapply,39。未審核39。)。end apply_contest。測試結果：圖418自動排名過程：createorreplaceprocedure con_rank( v_conid %type)is v_n number:=1。 v_preRank number:=1。 v_score number:=1。 v_preScore number:=0。