1.功能設(shè)計
某高校開發(fā)的研究生招生系統(tǒng)，要求設(shè)計pl/sql程序?qū)忌某煽償?shù)據(jù)進行處理，處理的邏輯是根據(jù)每門專業(yè)課的最低分數(shù)線和總分的最低分數(shù)線自動將考生歸類為錄取考生，調(diào)劑考生和落選考生。
為此設(shè)計兩個數(shù)據(jù)表，graduate數(shù)據(jù)表存放考生成績，resut數(shù)據(jù)表存放處理結(jié)果，pl/sql程序完成的功能就是將graduate數(shù)據(jù)表的數(shù)據(jù)逐行掃描，根據(jù)分數(shù)線進行判斷，計算出各科總分，在result數(shù)據(jù)表中將標志字段自動添加上"錄取"或"落選"。
2.數(shù)據(jù)表設(shè)計
--研究生表--
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,'宋遠橋','碩士',90,68,81,61,67);
insert into graduate values(2003100894,'殷素素','碩士',69,73,62,70,75);
?4.程序設(shè)計
/*定義過程參數(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為記錄型變量，臨時存放通過游標從graduate數(shù)據(jù)表中提取的記錄*/
graduaterecord graduate%rowtype;
/*定義graduatetotalscore為數(shù)值型變量，統(tǒng)計總分*/
graduatetotalscore result.totalscore%type;
/*定義graduateflag為字符型變量，根據(jù)結(jié)果放入"落選"或"錄取"，然后寫入數(shù)據(jù)表result*/
graduateflag varchar2(4);
/*定義游標graduatecursor，存放的是所有的graduate數(shù)據(jù)表中的記錄*/
cursor graduatecursor is select * from graduate;
/*定義異常處理errormessage*/
errormessage exception;
/*開始執(zhí)行*/
begin
/*打開游標*/
open graduatecursor;
/*如果游標沒有數(shù)據(jù)，激活異常處理*/
if graduatecursor%notfound then
raise errormessage;
end if;
/*游標有數(shù)據(jù)，指針指向第一條記錄，每執(zhí)行fetch命令，就自動下移，循環(huán)執(zhí)行到記錄提取完畢為止*/
loop
fetch graduatecursor into graduaterecord;
/*計算總分*/
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;
/*當游標數(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)閉游標*/
close graduatecursor;
/*提交結(jié)果*/
commit;
/*異常處理，提示錯誤信息*/
exception
when errormessage then
dbms_output.put_line('無法打開數(shù)據(jù)表');
/*程序執(zhí)行結(jié)束*/
end;
5.執(zhí)行結(jié)果，程序調(diào)用。
/*定義6個入口變量，分別對應(yīng)graduate數(shù)據(jù)表中的專業(yè)課和總分分數(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);
/*將分數(shù)線賦值，在這里修改各值就代表不同的分數(shù)線*/
begin
score1 := 50;
score2 := 56;
score3 := 60;
score4 := 62;
score5 := 64;
scoretotal := 325;
/*調(diào)用處理過程*/
graduateprocess(score1,score2,score3,score4,score5,scoretotal);
end;
?綜合運用pl/sql的設(shè)計要素，就可以設(shè)計出很多復(fù)雜的處理程序，這也是DBA的一項重要任務(wù)。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
