PL/SQL 演练

一、功能设计

 开发目标研究生招生系统，要求设计PL/SQL程序对考生的成绩数据进行处理，处理的逻辑是根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生、调剂考生、落选考生。
 为此设计2个数据表，graduate数据表存放考生成绩，result数据表存放处理结果，PL/SQL程序完成的功能就是将graduate数据表中的数据逐行扫描，根据分数线进行判断，计算各科总分，在result数据表中将标志字段自动添加上“录取”或“落选”。

二、数据表设计

graduate数据表结果如下:

图略
 
result数据表结构如下;

图略
 
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;
exception
when errormessage then
  dbms_output.put_line('无法打开数据表，请联系管理员徐建明');
end;

5、主程序设置

主程序mainprcess调用名为graduateprocess的国车公来完成处理，代码设计如下

set serveroutput on
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);
begin
    score1:=50;
    score2:=56;
    score3:=60;
    score4:=62;
    score5:=64;
    scoretotal:=325;
    scott.graduateprocess(score1,score2,score3,score4,score5,scoretotal);
end;

6、采用执行主程序即可完成结果，可以在数据结果表中查看程序执行结果。