PL/SQL綜合執行個體

來源:互聯網
上載者:User


 
    本節將在前面學習的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”。
    在【表結構定義區】按照圖所示進行設定。
    完成設定後單擊按鈕。

    讀者也可以在【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”。
    在【表結構定義區】按照圖所示進行設定。
    完成設定後單擊按鈕。

    讀者也可以在【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所示。

    讀者也可以在【SQLPlus Worksheet】中執行下列SQL代碼,直接向資料表SCOTT.GRADUATE中錄入測試資料。


    【配套程式位置】:第9章\ insertgraduate.sql。

程式設計

    1. 建立處理過程scott.graduateprocess
    在【SQLPlus Worksheet】中執行下列PL/SQL代碼,建立處理過程scott.graduateprocess。執行結果如圖9.61所示。

    ―――――――――――――――――――――――――――――――――――――
    /*定義過程參數*/
    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所示。



    【配套程式位置】:第9章\ mainprocess1.sql。
    (2)利用【表資料編輯器】查看scott.result資料表的結果,如圖9.63所示。

    2. 第二組執行結果
    (1)將mainprocess.sql檔案稍做改動,在【SQLPlus Worksheet】中執行下列PL/SQL程式,執行結果如圖9.64所示。


    【配套程式位置】:第9章\ mainprocess1.sql。
    (2)利用【表資料編輯器】查看scott.result資料表的結果,如圖9.63所示。

    2. 第二組執行結果
    (1)將mainprocess.sql檔案稍做改動,在【SQLPlus Worksheet】中執行下列PL/SQL程式,執行結果如圖9.64所示。


    【配套程式位置】:第9章\ mainprocess2.sql。

    (2)利用【表資料編輯器】查看scott.result資料表的結果,如圖9.65所示。

    綜合運用PL/SQL的設計要素,就可以設計出很多複雜的處理常式,這也是DBA的一項重要任務。



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。