第八章 使用PL SQL編寫存儲過程訪問數(shù)據(jù)庫

1、暨南大學(xué)本科實驗報告專用紙(附頁)暨南大學(xué)本科實驗報告專用紙課程名稱 數(shù)據(jù)庫系統(tǒng) 成績評定 實驗項目名稱 使用pl/sql編寫存儲過程訪問數(shù)據(jù)庫 指導(dǎo)教師 實驗項目編號 實驗項目類型 實驗地點 學(xué)生姓名 學(xué)號 學(xué)院 系 專業(yè) 實驗時間 2012年 月 日下午一、 實驗?zāi)康氖煜な褂么鎯^程來進(jìn)行數(shù)據(jù)庫應(yīng)用程序的設(shè)計。二、 實驗內(nèi)容對學(xué)生-課程數(shù)據(jù)庫，編寫存儲過程，完成下面功能：1. 統(tǒng)計離散數(shù)學(xué)的成績分布情況，即按照各分?jǐn)?shù)段統(tǒng)計人數(shù)；2. 統(tǒng)計任意一門課的平均成績；3. 將學(xué)生選課成績從百分制改為等級制（即a、b、c、d、e）。要求：提交源程序并表示必要的注釋。保證程序能正確編譯和運(yùn)行，認(rèn)真填寫

2、實驗報告。三、 實驗步驟實驗之前，已經(jīng)建立數(shù)據(jù)庫，有student，course和sc三張基本表，登陸用戶名為tiger,密碼為tiger。接下來的實驗就是對該數(shù)據(jù)庫編寫存儲過程。（一）統(tǒng)計“離散數(shù)學(xué)”課程的成績分布情況1. 創(chuàng)建存儲過程（1） 創(chuàng)建需要的表結(jié)構(gòu)。因為存儲過程執(zhí)行后在客戶端并沒有返回值，因此需要建立一個表來存放執(zhí)行后的結(jié)果，并返回到客戶端顯示。根據(jù)實驗要求，要統(tǒng)計選修“離散數(shù)學(xué)”課程的學(xué)生的成績分布，因此需要建立表rank，其中第一列division顯示成績分?jǐn)?shù)段劃分，第二列number顯示成績在該分?jǐn)?shù)段的學(xué)生人數(shù)。create table rank（division char

3、（20），number int）；（2） 編寫存儲過程。create or replace procedure statistic_mark(name char(50)asdeclareless60 int :=0;b60a70 int :=0;b70a80 int :=0;b80b90 int :=0;more90 int :=0;curcno char(4);beginselect cno into curcnofrom coursewhere cname =name;if not founo then raise exception end if;select count(*) into

4、 less60from scwhere cno =curcno and grade =60 and grade=70 and grade=80 and grade=90 ;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í)行存儲過程編寫好存儲過

5、程statistic_mark之后，在“查詢分析器”中選擇菜單中的“單事務(wù)執(zhí)行”命令，這樣系統(tǒng)就創(chuàng)建好了存儲過程然后使用perform調(diào)用該過程，在表rank中查看執(zhí)行的結(jié)果。perform procedure statistic_mark(離散數(shù)學(xué))；select* from rank； (二)統(tǒng)計任意一門課程的平均成績1.創(chuàng)建存儲過程（1）創(chuàng)建需要的表結(jié)構(gòu)。根據(jù)實驗要求，要統(tǒng)計任意一門課程的平均成績，因此需要建立表avggrade，其中第一列cname 顯示被統(tǒng)計的課程名稱，第二列avg顯示選修了該課程的學(xué)生的平均成績。create table avggrade（cname char(50

6、),avg numeric(10,6)）；（3） 編寫存儲過程create or replace procedure collect_avg（）asdeclare /聲明變量curname char(50);curno char(4);curavgg char(10,6);cursor mycursor for /聲明游標(biāo)mycursor查詢課程號和課程名稱select cno,cname from course;begin open mycursor; /打開游標(biāo) if mycursor%isopen then /條件控制，游標(biāo)打開時進(jìn)行以下處理 loop /循環(huán)控制 fetch mycur

7、sor into curcno,curname; /游標(biāo)推進(jìn)一行取結(jié)果送變量 exit when(mycursor%notfound); /如果沒有返回值，則退出循環(huán) select avg(grade)into curavgg from sc /求該課程的平均值送變量 where cno = curcno; /向avggrade /表中插入記錄，顯示課程名稱和平均成績 insere into avggrade values(curname,curavgg);end loop; /結(jié)束循環(huán)控制end if; /結(jié)束條件控制close mycursor;end;2. 執(zhí)行存儲過程首先執(zhí)行編寫好的存

8、儲過程collect_avg,然后在表avggrade中查看執(zhí)行結(jié)果。perform procedure collect_avg();select * from avggrade;(3) 在表sc中將學(xué)生選課成績從百分制改為等級制1. 創(chuàng)建存儲過程根據(jù)實驗要求，本實驗中存儲過程的執(zhí)行不需要在客戶端返回結(jié)果，因此不需要建立相應(yīng)的表結(jié)構(gòu)來存放存儲過程的執(zhí)行結(jié)果。直接編寫存儲過程。create or replace procedure change_critical()asdeclare chgrade char(1); currecord record;beginalter table sc ad

9、d column(newgrade char(1);for currecord in select*from sc loopif currecord.grade60 then chgrade =e;elsif currecord.grade70 then chgrade =d;elsif currecord.grade80 then chgrade =c;elsif currecord.grade90 then chgrade =b;else chgrade =a;end if;update sc set newgrade =chgradewhere sno =currecord.sno an

10、d cno=o;end loop;alter table sc drop column grade;alter table sc rename newgrade to grade;end;2執(zhí)行存儲過程perform procedure change_critical();(4) 刪除存儲過程存儲過程一旦建立，則將被保存在數(shù)據(jù)庫中，便于用戶隨時，反復(fù)地調(diào)用和執(zhí)行。如果不再需要該存儲過程，可以將其刪除。刪除存儲過程statistic_mark。 drop procedure statistic_mark;(1) 刪除存儲過程collect_avg,drop procedure collect_avg,