存儲過程實現(xiàn)學生成績由百分制到等級制.doc

存儲過程訪問數(shù)據(jù)庫一、 實驗內容和要求在實驗2建立的數(shù)據(jù)庫TEST的基礎之上進行。編寫存儲過程，完成以下功能：1、 統(tǒng)計“離散數(shù)學”課程的成績分布情況，即按照各分數(shù)段來統(tǒng)計人數(shù)；2、 統(tǒng)計任意一門課程的平均成績；3、 將學生選課成績從百分制改為等級制（即A、B、C、D、E）。以sa身份登錄SQL Server系統(tǒng)，執(zhí)行sp_addlogin SYSTEM,MANAGER,TESTsp_adduser SYSTEM,SYSTEM創(chuàng)建登錄名SYSTEM,然后以登錄名SYSTEM的身份登錄。（一）、統(tǒng)計“離散數(shù)學”課程的成績分布情況1、創(chuàng)建存儲過程（1）創(chuàng)建需要的表結構CREATE TABLE Rank( division CHAR(20), number INT)（2）編寫存儲過程CREATE PROCEDURE proStatisticMark(sName CHAR(50)AS BEGIN DECLARE less60 int,b60a70 int,b70a80 int,b80a90 int,more90 int,curcno char(4) SET less60=0 SET b60a70=0 SET b70a80=0 SET b80a90=0 SET more90=0 SET curcno= SELECT curcno=CNO FROM Course WHERE CNAME=sName IF curcno= SELECT 輸入錯誤，不存在此用戶。 SELECT less60=count(*) FROM SC WHERE CNO=curcno AND GRADE=60 AND GRADE=70 AND GRADE=80 AND GRADE=90 DELETE FROM Rank INSERT INTO Rank VALUES(0,60),less60) INSERT INTO Rank VALUES(60,70),b60a70) INSERT INTO Rank VALUES(70,80),b70a80) INSERT INTO Rank VALUES(80,90),b80a90) INSERT INTO Rank VALUES(90,100,more90) END 2、執(zhí)行存儲過程EXECUTE proStatisticMark 離散數(shù)學EXECUTE proStatisticMark 運籌學然后在表RANK中查看執(zhí)行結果。SELECT *FROM Rank（二）、統(tǒng)計任意一門課程的平均成績1、創(chuàng)建存儲過程（1）創(chuàng)建所需要的表結構。CREATE TABLE Avggrade( cname CHAR(50), avgg NUMERIC(10,6)（2）編寫存儲過程CREATE PROCEDURE proCollectAvggradeAS BEGIN DECLARE curname CHAR(50),curcno CHAR(4),curavgg NUMERIC(10,6) DECLARE curCourse CURSOR FOR SELECT CNO,CNAME FROM Course OPEN curCourse FETCH curCourse INTO curcno,curname WHILE (FETCH_STATUS=0) BEGIN SELECT curavgg=AVG(GRADE) FROM SC WHERE CNO=curcno GROUP BY CNO INSERT INTO Avggrade VALUES(curname,curavgg) FETCH curCourse INTO curcno,curname END CLOSE curCourse DEALLOCATE curCourseEND2、執(zhí)行存儲過程EXECUTE proCollectAvggrade然后在表Avggrade中查看結果。SELECT *FROM Avggrade（三） 在表中將學生選課成績從百分制改為等級制（即、B、C、D、E）1、 創(chuàng)建存儲過程在SC中增設1列：ALTER TABLE SC ADD newgrade CHAR(1) 存儲過程：CREATE PROCEDURE proChangeCriticalASBEGIN DECLARE chgrade CHAR(1),cursno int,curcno int,curgrade int DECLARE curSC CURSOR FOR SELECT SNO,CNO,GRADE FROM SC OPEN curSC FETCH curSC INTO cursno,curcno,curgrade WHILE(FETCH_STATUS=0) BEGIN IF curgrade60 SET chgrade=E ELSE IF curgrade70 SET chgrade=D ELSE IF curgrade80 SET chgrade=C ELSE IF curgrade90 SET chgrade=B ELSE IF curgrade=100 SET chgrade=A UPDATE SC SET newgrade=chgrade WHERE SNO=cursno AND CNO=curcno FETCH curSC INTO cursno,curcno,curgrade END CLOSE curSC DEALLOCATE curSC