PL/SQL综合实例_Oracle教程

    本节将在前面学习的PL/SQL基础上分析一个较复杂的实例，以教会读者编写完整的PL/SQL程序的方法。

实例设计

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

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)

创建数据表

    为了简化起见，实例的两个数据表都建立在默认的scott用户下，这样读者可以完全模拟实例一致的环境进行学习。
    1. 创建graduate数据表
    在图9.58所示的创建表的【一般信息】选项卡中按照下列步骤进行配置。
    在【名称】文本框中输入“GRADUATE”。
    在【方案】下拉列表框中选择“SCOTT”。
    在【表空间】下拉列表框中选择“USERS”。
    在【表结构定义区】按照图所示进行设置。
    完成设置后单击 PL/SQL综合实例

按钮。

(图片较大，请拉动滚动条观看)
    读者也可以在【SQLPlus Worksheet】中执行下列SQL代码，直接创建数据表SCOTT.GRADUATE。
    ――――――――――――――――――――――――――――――――――――――
    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"
    ――――――――――――――――――――――――――――――――――――――
    【配套程序位置】：第9章\ creategraduate.sql。
    2. 创建result数据表
    在图9.59所示的创建表的【一般信息】选项卡中按照下列步骤进行配置。

    在【名称】文本框中输入“RESULT”。
    在【方案】下拉列表框中选择“SCOTT”。
    在【表空间】下拉列表框中选择“USERS”。
    在【表结构定义区】按照图所示进行设置。
    完成设置后单击 PL/SQL综合实例

按钮。

    读者也可以在【SQLPlus Worksheet】中执行下列SQL代码，直接创建数据表SCOTT.RESULT。
    ――――――――――――――――――――――――――――――――――――――
    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,
        "TOTALSCORE" NUMBER(5, 1) NOT NULL,
        "FLAG" VARCHAR2(4) NOT NULL)
    TABLESPACE "USERS"
    ――――――――――――――――――――――――――――――――――――――
    【配套程序位置】：第9章\ createresult.sql。

录入数据

    利用【表数据编辑器】向SCOTT.GRADUATE数据表录入下列实验数据。如图9.60所示。
PL/SQL综合实例

    读者也可以在【SQLPlus Worksheet】中执行下列SQL代码，直接向数据表SCOTT.GRADUATE中录入测试数据。

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)
    【配套程序位置】：第9章\ insertgraduate.sql。

程序设计

    1. 创建处理过程scott.graduateprocess
    在【SQLPlus Worksheet】中执行下列PL/SQL代码，创建处理过程scott.graduateprocess。执行结果如图9.61所示。
PL/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);
    /*定义游标graduatecursor，存放的是所有的graduate数据表中的记录*/
      cursor graduatecursor is
        select * from scott.graduate;
    /*定义异常处理errormessage*/
    errormessage exception;
    /*开始执行*/
    begin
       /*打开游标*/
      open graduatecursor;
    /*假如游标没有数据，激活异常处理*/
      if graduatecursor%notfound then
         raise errormessage;
      end if;
    /*游标有数据，指针指向第一条记录，每执行fetch命令，就自动下移，循环执行到记录提取完毕为止*/
      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;
    /*当游标数据提取完毕后，退出循环”*/
    exit when graduatecursor%notfound;
    /*向结果数据表result中插入处理后的数据*/
    insert into
    scott.result(bh,xm,lb,zhengzhi,yingyu,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
    values(graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.zhengzhi,graduate
    record.yingyu,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduat
    etotalscore,graduateflag);
    end loop;
    /*关闭游标*/
    close graduatecursor;
    /*提交结果*/
    commit;
    /*异常处理，提示错误信息*/
    exception
    when errormessage then
        dbms_output.put_line('无法打开数据表');
    /*程序执行结束”*/
    end;
    ―――――――――――――――――――――――――――――――――――――――――――
    【配套程序位置】：第9章\ creategraduateprocess.sql。

    2. 主程序mainprocess设计
    主程序调用名为graduateprocess的过程来完成处理，代码设计如下：
    ―――――――――――――――――――――――――――――――――――――
    set serveroutput on
    /*定义6个入口变量，分别对应graduate数据表中的专业课和总分分数线*/
    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;
    ――――――――――――――――――――――――――――――――――――――――――――
    【配套程序位置】：第9章\ mainprocess.sql。
    在上述过程设计中，综合使用了异常处理、游标、变量等PL/SQL程序设计要素，通过主程序向带参数执行的过程传递参数。

执行结果

    1.第一组执行结果
    （1）将mainprocess.sql文件稍做改动，在【SQLPlus Worksheet】中执行下列PL/SQL程序，执行结果如图9.62所示。
PL/SQL综合实例

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)
【配套程序位置】：第9章\ mainprocess1.sql。
（2）利用【表数据编辑器】查看scott.result数据表的结果，如图9.63所示。
PL/SQL综合实例

    2. 第二组执行结果
    （1）将mainprocess.sql文件稍做改动，在【SQLPlus Worksheet】中执行下列PL/SQL程序，执行结果如图9.64所示。

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)
    【配套程序位置】：第9章\ mainprocess1.sql。
    （2）利用【表数据编辑器】查看scott.result数据表的结果，如图9.63所示。
PL/SQL综合实例

    2. 第二组执行结果
    （1）将mainprocess.sql文件稍做改动，在【SQLPlus Worksheet】中执行下列PL/SQL程序，执行结果如图9.64所示。

(图片较大，请拉动滚动条观看)

(图片较大，请拉动滚动条观看)
    【配套程序位置】：第9章\ mainprocess2.sql。
PL/SQL综合实例

（2）利用【表数据编辑器】查看scott.result数据表的结果，如图9.65所示。
PL/SQL综合实例

综合运用PL/SQL的设计要素，就可以设计出很多复杂的处理程序，这也是DBA的一项重要任务。