控件中国网现已改版,您看到的是老版本网站的镜像,系统正在为您跳转到新网站首页,请稍候.......
中国最专业的商业控件资讯网产品咨询电话:023-67870900 023-67871946
产品咨询EMAIL:SALES@COMPONENTCN.COM

PL/SQL 演练

作者:未知 出处:不详 2010年02月01日 阅读:

一、功能设计

开发目标研究生招生系统,要求设计PL/SQL程序对考生的成绩数据进行处理,处理的逻辑是根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生、调剂考生、落选考生。

为此设计2个数据表,graduate数据表存放考生成绩,result数据表存放处理结果,PL/SQL程序完成的功能就是将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、采用执行主程序即可完成结果,可以在数据结果表中查看程序执行结果。

                                                           
                                                            【注:本文由控件中国网转载】

热推产品

  • ActiveReport... 强大的.NET报表设计、浏览、打印、转换控件,可以同时用于WindowsForms谀坔攀戀Forms平台下......
  • AnyChart AnyChart使你可以创建出绚丽的交互式的Flash和HTML5的图表和仪表控件。可以用于仪表盘的创......
首页 | 新闻中心 | 产品中心 | 技术文档 | 友情连接 | 关于磐岩 | 技术支持中心 | 联系我们 | 帮助中心 Copyright-2006 ComponentCN.com all rights reserved.重庆磐岩科技有限公司(控件中国网) 版权所有 电话:023 - 67870900 传真:023 - 67870270 产品咨询:sales@componentcn.com 渝ICP备12000264号 法律顾问:元炳律师事务所 重庆市江北区塔坪36号维丰创意绿苑A座28-5 邮编:400020
在线客服
在线客服系统
在线客服
在线客服系统