存儲(chǔ)過(guò)程實(shí)現(xiàn)學(xué)生成績(jī)由百分制到等級(jí)制.doc

存儲(chǔ)過(guò)程訪問(wèn)數(shù)據(jù)庫(kù)一、 實(shí)驗(yàn)內(nèi)容和要求在實(shí)驗(yàn)2建立的數(shù)據(jù)庫(kù)TEST的基礎(chǔ)之上進(jìn)行。編寫存儲(chǔ)過(guò)程，完成以下功能：1、 統(tǒng)計(jì)“離散數(shù)學(xué)”課程的成績(jī)分布情況，即按照各分?jǐn)?shù)段來(lái)統(tǒng)計(jì)人數(shù)；2、 統(tǒng)計(jì)任意一門課程的平均成績(jī)；3、 將學(xué)生選課成績(jī)從百分制改為等級(jí)制（即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)計(jì)“離散數(shù)學(xué)”課程的成績(jī)分布情況1、創(chuàng)建存儲(chǔ)過(guò)程（1）創(chuàng)建需要的表結(jié)構(gòu)CREATE TABLE Rank( division CHAR(20), number INT)（2）編寫存儲(chǔ)過(guò)程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 輸入錯(cuò)誤，不存在此用戶。 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í)行存儲(chǔ)過(guò)程EXECUTE proStatisticMark 離散數(shù)學(xué)EXECUTE proStatisticMark 運(yùn)籌學(xué)然后在表RANK中查看執(zhí)行結(jié)果。SELECT *FROM Rank（二）、統(tǒng)計(jì)任意一門課程的平均成績(jī)1、創(chuàng)建存儲(chǔ)過(guò)程（1）創(chuàng)建所需要的表結(jié)構(gòu)。CREATE TABLE Avggrade( cname CHAR(50), avgg NUMERIC(10,6)（2）編寫存儲(chǔ)過(guò)程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í)行存儲(chǔ)過(guò)程EXECUTE proCollectAvggrade然后在表Avggrade中查看結(jié)果。SELECT *FROM Avggrade（三） 在表中將學(xué)生選課成績(jī)從百分制改為等級(jí)制（即、B、C、D、E）1、 創(chuàng)建存儲(chǔ)過(guò)程在SC中增設(shè)1列：ALTER TABLE SC ADD newgrade CHAR(1) 存儲(chǔ)過(guò)程：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