存儲過程實驗

1、一、統(tǒng)計“數(shù)據(jù)庫原理”的成績分布情況，即按照各分?jǐn)?shù)段統(tǒng)計人數(shù)1、創(chuàng)建需要的表create table rank(分?jǐn)?shù)段 char(20), 人數(shù) int);2、創(chuàng)建存儲過程CREATE PROCEDURE statistic_mark AS DECLARE less60 INT, b60a70 INT , b70a80 INT , b80a90 INT , more90 INT , curcno CHAR(4); /*設(shè)字符型變量curcno 存放輸入的課程名稱參數(shù)*/BEGIN SELECT curcno=cno /*根據(jù)課程名稱在Course 表中查詢查詢該課程號*/ FROM Cours

2、e WHERE cname = '數(shù)據(jù)庫' SELECT less60=count(*) /*查詢分?jǐn)?shù)低于60 分的學(xué)生人數(shù)*/ FROM sc WHERE cno = curcno AND grade < 60; SELECT b60a70=count(*) /*查詢分?jǐn)?shù)在60 到70 之間的學(xué)生人數(shù)*/ FROM sc WHERE cno = curcno AND grade >= 60 AND grade < 70; SELECT b70a80=count(*) /*查詢分?jǐn)?shù)在70 到80 之間的學(xué)生人數(shù)*/ FROM sc WHERE cno = cu

3、rcno AND grade >= 70 AND grade < 80; SELECT b80a90=count(*) /*查詢分?jǐn)?shù)在 80 到90 之間的學(xué)生人數(shù)*/ FROM sc WHERE cno = curcno AND grade >= 80 AND grade < 90; SELECT more90=count(*) /*查詢分?jǐn)?shù)高于90 的學(xué)生人數(shù)*/ FROM sc WHERE cno = curcno AND grade >= 90; /* 向表Rank 中插入五條記錄，顯示成績分布情況*/ INSERT INTO Rank VALUES(&#

4、39;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; 3、執(zhí)行存儲過程exec statistic_mark；4、查詢結(jié)果select * from rank;二、統(tǒng)計任意一門課的平均成績1、創(chuàng)建需

5、要的表CREATE TABLE Avggrade( cname CHAR(50), avgg NUMERIC(10, 6); 2、創(chuàng)建存儲過程CREATE PROCEDURE collect_avggrade AS DECLARE /*聲明變量*/ curname CHAR(50), curcno CHAR(4), curavgg NUMERIC(10, 6); declare mycursor CURSOR FOR /*聲明游標(biāo)mycursor 查詢課程號和名稱*/ SELECT cno, cname FROM course; BEGIN OPEN mycursor; /*打開游標(biāo)*/ FE

6、TCH mycursor INTO curcno, curname; while (fetch_status=0) begin SELECT curavgg=AVG(grade) FROM SC /*求該課程的平均值送變量 */ WHERE cno = curcno; INSERT INTO Avggrade VALUES(curname, curavgg); /* 向Avggrade 表中插入記錄，顯示課程名稱和平均成績*/ FETCH mycursor INTO curcno, curname; end CLOSE mycursor; /*關(guān)閉游標(biāo)*/ END;3、執(zhí)行存儲過程 exec

7、collect_avggrade;4、查詢結(jié)果SELECT * FROM Avggrade;三、將學(xué)生選課成績從百分制改為等級制（即A、B、C、D、E）1、在SC表中加入新列newgradeALTER TABLE SC ADD newgrade CHAR(1); -由于之前的成績值為整數(shù)型，而修改后的結(jié)果為字符型，因此在SC中先增加一列newgrade，存放換算后的成績 2、創(chuàng)建存儲過程法(1)、用IF.ELSE.語句CREATE PROC change_gradeAS DECLARE -聲明變量 chgrade CHAR(1), curgrade int; declare mycursor

8、cursor for select grade from sc; BEGIN open mycursor; FETCH mycursor INTO curgrade; while fetch_status=0 -循環(huán)處理每個學(xué)生的成績 begin IF curgrade < 60 -如果成績低于60 分，將成績置為E select chgrade = 'E' ELSe IF curgrade < 70 -如果成績>60&<70，將成績置為D select chgrade = 'D' ELSe IF curgrade < 80

9、 -如果成績>70&<80，將成績置為C select chgrade = 'C' ELSe IF curgrade < 90 -如果成績>80&<90，將成績置為B select chgrade = 'B' ELSE -如果成績高于90 分，將成績置為A select chgrade = 'A' UPDATE SC SET newgrade = chgrade -/修改newgrade 列值，填入換算后的成績 where grade=curgrade; FETCH mycursor INTO cur

10、grade; end; close mycursor; END;法(2)、用when語句CREATE PROCEDURE change_grade AS DECLARE curgrade int; declare mycursor cursor for select grade from sc; BEGIN open mycursor; FETCH mycursor INTO curgrade; while fetch_status=0 begin UPDATE SC SET newgrade = -修改newgrade 列值，填入換算后的成績 case when curgrade <

11、60 -如果成績低于60 分，將成績置為E then 'E' when curgrade < 70 -如果成績>60&<70，將成績置為D then 'D' when curgrade < 80 -如果成績>70&<80，將成績置為C then 'C' when curgrade < 90 -如果成績>80&<90，將成績置為B then 'B' when curgrade >= 90 -如果成績高于90 分，將成績置為A then 'A' end where grade=curgrade FETCH mycursor INTO curgrade; end close mycursor;end3、執(zhí)行存儲過程exec change_grade;4、修改表SCALTER TABLE SC DROP COLUMN grade; -刪除原有的列g(shù)rade EXEC sp_rename 'sc.newgrade', 'Grade', 'COLUMN' -重命名newgrade列為Grade5、查詢結(jié)果selec