1、创建graduate数据表mis_01.sql create table "SCOTT"."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)TABLESPACE "USERS" 2、创建result数据表mis_02.sql create table "SCOTT"."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, "TOTALSORE" NUMBER(5,1) NOT NULL, "FLAG" VARCHAR2(4) NOT NULL)TABLESPACE "USERS" 3、录入数据mis_03.sql insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003080520,'张三丰','硕士',55,56,67,78,89); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003060555,'张翠山','硕士',66,78,78,89,92); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003056066,'张无忌','硕士',76,67,89,90,66); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003010989,'赵敏','硕士',45,59,74,66,56); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003050677,'周芷若','硕士',77,67,72,87,66); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003869401,'小昭','硕士',56,67,56,64,34); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003340987,'阿离','硕士',68,93,64,80,56); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003056709,'宋元桥','硕士',90,68,81,61,67); insert INTO "SCOTT"."GRADUATE" ("BH","XM","LB","YINGYU","ZHENGZHI","ZHUANYE1","ZHUANYE2","ZHUANYE3") VALUES (2003100894,'殷素素','硕士',69,73,62,70,75); 4、程序设计创建处理过程scott.graduateprocess.sql create or replace procedure scott.graduateprocess( tempzhengzhi in scott.graduate.zhengzhi%type, tempyingyu in scott.graduate.yingyu%type, tempzhuanye1 in scott.graduate.zhuanye1%type, tempzhuanye2 in scott.graduate.zhuanye2%type, tempzhuanye3 in scott.graduate.zhuanye3%type, temptotalscore in scott.result.totalscore%type)as/* 定义graduaterecord为记录型变量，临时存放通过游标从graduate表中提取的记录*/ graduaterecord scott.graduate%rowtype; /* 定义graduatetotalscore为数值型变量，统计总分 */ graduatetotalscore scott.result.totalscore%type; /* 定义graduateflag为字符变量，根据结果放入落选或录取，然后写入数据表result */ graduateflag varchar2(4); /*定义游标*/ cursor graduatecursor is select * from scott.graduate; errormessage exception; begin open graduatecursor; if graduatecursor%notfound then raise errormessage; end if; loop fetch graduatecursor into graduaterecord; graduatetotalscore:=graduaterecord.yingyu + graduaterecord.zhengzhi + graduaterecord.zhuanye1 + graduaterecord.zhuanye2 + graduaterecord.zhuanye3; 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; exit when graduatecursor%notfound; insert into scott.result(bh,xm,lb,zhengzhi,yingyu,zhuanye1,zhuanye2,zhuanye3,totalscore,flag) values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.zhengzhi,graduaterecord.yingyu,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduatetotalscore,graduateflag); end loop; close graduatecursor;commit;exceptionwhen errormessage then dbms_output.put_line('无法打开数据表，请联系管理员徐建明');end; 5、主程序设置主程序mainprcess调用名为graduateprocess的国车公来完成处理，代码设计如下 set serveroutput ondeclare score1 number(4,1); score2 number(4,1); score3 number(4,1); score4 number(4,1); score5 number(4,1); scoretotal number(5,1);begin score1:=50; score2:=56; score3:=60; score4:=62; score5:=64; scoretotal:=325; scott.graduateprocess(score1,score2,score3,score4,score5,scoretotal);end; 6、采用执行主程序即可完成结果，可以在数据结果表中查看程序执行结果。
发表于 @ 2008年04月30日 21:24:00|评论(loading...)|编辑