蘇州大學(xué)數(shù)據(jù)庫課程設(shè)計(jì)(2)-資料下載頁
2025-05-07 21:17本頁面
【正文】 GPA float declare @score int select @stu_id = stu_id, @course_id = course_id, @class_id = class_id,@term = term, @rebuild_id = rebuild_id, @score_ordtime = score_ordtime, @score_mid = score_mid, @score_termial =score_termial,@score_expe = score_expe, @score_final = score_final,@course_credit = course_credit, @GPA = GPA from inserted select @score = score_final from FinalScore where stu_id = @stu_id and course_id = @course_id if(@score @score_final) begin update FinalScore set term = @term, score_ordtime = @score_ordtime, score_mid = @score_mid, score_termial = @score_termial,score_expe = @score_expe, score_final = @score_final, course_credit = @course_credit, GPA = @GPA where stu_id = @stu_id and course_id = @course_id end go go create trigger UpdateRebuildScore on RebuildScore for update as declare @stu_id varchar(20), @course_id varchar(20), @class_id varchar(20), @term int, @rebuild_id int, @score_ordtime int, @score_mid int, @score_termial int,@score_expe int, @score_final int, @course_credit int, @GPA float declare @score int select @stu_id = stu_id, @course_id = course_id, @class_id = class_id,@term = term, @rebuild_id = rebuild_id, @score_ordtime = score_ordtime, @score_mid = score_mid, @score_termial =score_termial,@score_expe = score_expe, @score_final = score_final,@course_credit = course_credit, @GPA = GPA from inserted select @score = score_final from FinalScore where stu_id = @stu_id and course_id = @course_id if(@score @score_final) begin update FinalScore set term = @term, score_ordtime = @score_ordtime, score_mid = @score_mid, score_termial = @score_termial,score_expe = @score_expe, score_final = @score_final, course_credit = @course_credit, GPA = @GPA where stu_id = @stu_id and course_id = @course_id end go 代碼 create role U_JWMS grant select,insert,update,delete on Class to U_JWMS。 grant select,insert,update,delete on College to U_JWMS。 grant select,insert,update,delete on Course to U_JWMS。 grant select,insert,update,delete on CourseClass to U_JWMS。 grant select,insert,update,delete on CourseSelect to U_JWMS。 grant select,insert,update,delete on FinalScore to U_JWMS。 grant select,insert,update,delete on Major to U_JWMS。 grant select,insert,update,delete on RebuildScore to U_JWMS。 grant select,insert,update,delete on Secretary to U_JWMS。 grant select,insert,update,delete on Student to U_JWMS。 grant select,insert,update,delete on Teacher to U_JWMS。 grant select,insert,update,delete on Teaching to U_JWMS。 grant select,insert,update,delete on TempScore to U_JWMS。 grant select,insert,update,delete on TPlan to U_JWMS。 go create role U_Web grant select on Class to U_Web。 grant select on College to U_Web。 grant select on Course to U_Web。 grant select on CourseClass to U_Web。 grant select,update,delete,insert on CourseSelect to U_Web。 grant select on FinalScore to U_Web。 grant select on Major to U_Web。 grant select on RebuildScore to U_Web。 grant select on Secretary to U_Web。 grant select on Student to U_Web。 grant select on Teacher to U_Web。 grant select on Teaching to U_Web。 grant select,update,insert,delete on TempScore to U_Web。 grant select on TPlan to U_Web。 go GPA函數(shù) /*計(jì)算績(jī)點(diǎn)函數(shù) */ go create function CalJD(@score int) returns float as begin declare @JD float if(@score = 60) begin set @JD = (@score 50) / 10 end else begin set @JD = 0 end return @JD end go /*計(jì)算 GPA函數(shù) */ go create function CalGPA(@stu_id varchar(20)) returns float as begin declare @sum_JD float, @sum_credit int, @GPA float select @sum_JD = sum((score_final)) from FinalScore where stu_id = @stu_id select @sum_credit = sum(course_credit) from FinalScore where stu_id = @stu_id set @GPA = @sum_JD / @sum_credit return @GPA end go （獲取最高分?jǐn)?shù) ，由于之前重修成績(jī)輸入時(shí)觸發(fā)自動(dòng)比較 更新，所以最高的成績(jī)已經(jīng)存儲(chǔ)在 FinalScore表中 ） go create proc GetAllScore @stu_id varchar(20) as begin select stu_name,course_name,score_final from Student,Course,FinalScore where = and = end go
教學(xué)課件相關(guān)推薦
鄂ICP備17016276號(hào)-1