1.功能設(shè)計(jì)
某高校開發(fā)的研究生招生系統(tǒng)，要求設(shè)計(jì)pl/sql程序?qū)忌某煽?jī)數(shù)據(jù)進(jìn)行處理，處理的邏輯是根據(jù)每門專業(yè)課的最低分?jǐn)?shù)線和總分的最低分?jǐn)?shù)線自動(dòng)將考生歸類為錄取考生，調(diào)劑考生和落選考生。
為此設(shè)計(jì)兩個(gè)數(shù)據(jù)表，graduate數(shù)據(jù)表存放考生成績(jī)，resut數(shù)據(jù)表存放處理結(jié)果，pl/sql程序完成的功能就是將graduate數(shù)據(jù)表的數(shù)據(jù)逐行掃描，根據(jù)分?jǐn)?shù)線進(jìn)行判斷，計(jì)算出各科總分，在result數(shù)據(jù)表中將標(biāo)志字段自動(dòng)添加上"錄取"或"落選"。
2.數(shù)據(jù)表設(shè)計(jì)
--研究生表--
create table graduate(
BH NUMBER(10) NOT NULL,
XM VARCHAR2(10) NOT NULL,
LB VARCHAR2(10) NOT NULL,
YINGYU NUMBER(4,1) NOT NULL,
ZHENGZHI NUMBER(4,1) NOT NULL,
ZHUANYE1 NUMBER(4,1) NOT NULL,
ZHUANYE2 NUMBER(4,1) NOT NULL,
ZHUANYE3 NUMBER(4,1) NOT NULL
);
--結(jié)果表--
create table "RESULT"(
"BH" NUMBER(10) NOT NULL,
"XM" VARCHAR2(10) NOT NULL,
"LB" VARCHAR2(10) NOT NULL,
"YINGYU" NUMBER(4,1) NOT NULL,
"ZHENGZHI" NUMBER(4,1) NOT NULL,
"ZHUANYE1" NUMBER(4,1) NOT NULL,
"ZHUANYE2" NUMBER(4,1) NOT NULL,
"ZHUANYE3" NUMBER(4,1) NOT NULL,
"TOTALSCORE" NUMBER(5,1) NOT NULL,
"FLAG" VARCHAR2(4) NOT NULL
);
?3.錄入數(shù)據(jù)
insert into graduate values(2003080520,'張三豐','碩士',55,56,67,78,89);
insert into graduate values(2003060555,'張翠山','碩士',66,78,78,89,92);
insert into graduate values(2003056066,'張無忌','碩士',76,67,89,90,66);
insert into graduate values(2003010989,'趙敏','碩士',45,59,74,66,56);
insert into graduate values(2003050677,'周芷若','碩士',77,67,72,87,66);
insert into graduate values(2003869401,'小昭','碩士',56,67,56,64,34);
insert into graduate values(2003340987,'阿離','碩士',68,93,64,80,56);
insert into graduate values(2003056709,'宋遠(yuǎn)橋','碩士',90,68,81,61,67);
insert into graduate values(2003100894,'殷素素','碩士',69,73,62,70,75);
?4.程序設(shè)計(jì)
/*定義過程參數(shù)*/
create or replace procedure graduateprocess
(
tempzhengzhi in graduate.zhengzhi%type,
tempyingyu in graduate.yingyu%type,
tempzhuanye1 in graduate.zhuanye1%type,
tempzhuanye2 in graduate.zhuanye2%type,
tempzhuanye3 in graduate.zhuanye3%type,
temptotalscore in result.totalscore%type
)
as
/*定義graduaterecord為記錄型變量，臨時(shí)存放通過游標(biāo)從graduate數(shù)據(jù)表中提取的記錄*/
graduaterecord graduate%rowtype;
/*定義graduatetotalscore為數(shù)值型變量，統(tǒng)計(jì)總分*/
graduatetotalscore result.totalscore%type;
/*定義graduateflag為字符型變量，根據(jù)結(jié)果放入"落選"或"錄取"，然后寫入數(shù)據(jù)表result*/
graduateflag varchar2(4);
/*定義游標(biāo)graduatecursor，存放的是所有的graduate數(shù)據(jù)表中的記錄*/
cursor graduatecursor is select * from graduate;
/*定義異常處理errormessage*/
errormessage exception;
/*開始執(zhí)行*/
begin
/*打開游標(biāo)*/
open graduatecursor;
/*如果游標(biāo)沒有數(shù)據(jù)，激活異常處理*/
if graduatecursor%notfound then
raise errormessage;
end if;
/*游標(biāo)有數(shù)據(jù)，指針指向第一條記錄，每執(zhí)行fetch命令，就自動(dòng)下移，循環(huán)執(zhí)行到記錄提取完畢為止*/
loop
fetch graduatecursor into graduaterecord;
/*計(jì)算總分*/
graduatetotalscore := graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1
+ graduaterecord.zhuanye2 + graduaterecord.zhuanye3;
/*判斷單科和總分是否滿足錄取要求，若滿足，graduateflag變量值為"錄取"，否則為"落選"*/
if(graduaterecord.yingyu >= tempyingyu and graduaterecord.zhengzhi >= tempzhengzhi and
graduaterecord.zhuanye1 >= tempzhuanye1 and graduaterecord.zhuanye2 >= tempzhuanye2 and
graduaterecord.zhuanye3 >= tempzhuanye3 and graduatetotalscore >= temptotalscore) then
graduateflag := '錄取';
else
graduateflag := '落選';
end if;
/*當(dāng)游標(biāo)數(shù)據(jù)提取完畢后，退出循環(huán)*/
exit when graduatecursor%notfound;
/*向結(jié)果數(shù)據(jù)表result中插入處理后的數(shù)據(jù)*/
insert into result values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,
graduaterecord.yingyu,graduaterecord.zhengzhi,graduaterecord.zhuanye1,graduaterecord.zhuanye2,
graduaterecord.zhuanye3,graduatetotalscore,graduateflag);
end loop;
/*關(guān)閉游標(biāo)*/
close graduatecursor;
/*提交結(jié)果*/
commit;
/*異常處理，提示錯(cuò)誤信息*/
exception
when errormessage then
dbms_output.put_line('無法打開數(shù)據(jù)表');
/*程序執(zhí)行結(jié)束*/
end;
5.執(zhí)行結(jié)果，程序調(diào)用。
/*定義6個(gè)入口變量，分別對(duì)應(yīng)graduate數(shù)據(jù)表中的專業(yè)課和總分分?jǐn)?shù)線*/
declare
score1 number(4,1);
score2 number(4,1);
score3 number(4,1);
score4 number(4,1);
score5 number(4,1);
scoretotal number(5,1);
/*將分?jǐn)?shù)線賦值，在這里修改各值就代表不同的分?jǐn)?shù)線*/
begin
score1 := 50;
score2 := 56;
score3 := 60;
score4 := 62;
score5 := 64;
scoretotal := 325;
/*調(diào)用處理過程*/
graduateprocess(score1,score2,score3,score4,score5,scoretotal);
end;
?綜合運(yùn)用pl/sql的設(shè)計(jì)要素，就可以設(shè)計(jì)出很多復(fù)雜的處理程序，這也是DBA的一項(xiàng)重要任務(wù)。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
