文章內(nèi)容

Oracle 10g中存儲過程中使用游標

發(fā)布時間: 2012/9/19 17:17:28

實例設(shè)計

    1. 功能設(shè)計
    某高校開發(fā)的研究生招生系統(tǒng)，要求設(shè)計PL/SQL程序?qū)忌某煽償?shù)據(jù)進行處理，處理的邏輯是根據(jù)每門專業(yè)課的最低分數(shù)線和總分的最低分數(shù)線自動將考生歸類為錄取考生、調(diào)劑考生和落選考生。
    為此設(shè)計兩個數(shù)據(jù)表，graduate數(shù)據(jù)表存放考生成績，result數(shù)據(jù)表存放處理結(jié)果，PL/SQL程序完成的功能就是將graduate數(shù)據(jù)表中的數(shù)據(jù)逐行掃描，根據(jù)分數(shù)線進行判斷，計算出各科總分，在result數(shù)據(jù)表中將標志字段自動添加上“錄取”或“落選”。 -

    --創(chuàng)建數(shù)據(jù)庫表 --graduate
    CREATE TABLE "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
    )

    --創(chuàng)建序列
    create sequence my_suquence --序列名稱
    minvalue 0                   --序列最小值
    start with 201112081                 --序列起始值
    maxvalue 999999999                  --序列最大值
    increment by 1               --序列的增長值
    nocache                      --不預(yù)先在內(nèi)存中緩存
    cycle;                       --達到最大值后再重新循環(huán)

    --插入數(shù)據(jù)
    insert into graduate values(my_suquence.nextval,'張三豐','碩士','56','56','67','78','79');
    insert into graduate values(my_suquence.nextval,'張無極','碩士','34','56','56','48','79');
    insert into graduate values(my_suquence.nextval,'張翠山','碩士','44','78','76','88','39');
    insert into graduate values(my_suquence.nextval,'趙敏','碩士','67','56','69','73','70');
    insert into graduate values(my_suquence.nextval,'周芷若','碩士','43','56','45','78','67');
    insert into graduate values(my_suquence.nextval,'小昭','碩士','64','66','64','76','79');

--創(chuàng)建數(shù)據(jù)庫表 result
     CREATE TABLE "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
     )

     select * from result;
--創(chuàng)建存儲過程

      create or replace procedure graduateprocess(
             tempzhangzhi in graduate.zhengzhi%type,--將表里面的類型賦給該變量
             tempyingyu in graduate.yingyu%type,
             tempzhuanye1 in graduate.zhuanye1%type,
             tempzhuanye2 in graduate.zhuanye2%type,
             tempzhuanye3 in graduate.zhuanye3%type,
             temptotalscore in result.totalscore%type)
       as
       --定義graduate為記錄型變量，臨時存放通過游標從graduate表中提取的記錄
         graduaterecord graduate%rowtype;
       --定義graduatetotalscore為數(shù)值型變量，統(tǒng)計總分
         graduatetotalscore result.totalscore%type;
        --定義graduateflag 為字符型變量，根據(jù)結(jié)果放入落選或錄取
         graduateflag varchar2(4);
         --定義游標graduatecursor,存放的是所有的graduate數(shù)據(jù)表中的記錄
         cursor graduatecursor is
                select * from graduate;
         --定義異常處理
         errormessage exception;

        begin
        --打開游標
             open graduatecursor;
             if graduatecursor%not found then
                --如果沒有數(shù)據(jù)。拋出異常
                raise errormessage;
             end if;
             --如果有數(shù)據(jù)，將數(shù)據(jù)循環(huán)出來
             loop
                 fetch graduatecursor into graduaterecord;
             --計算總分
             graduatetotalscore:=graduaterecord.zhengzhi+graduaterecord.yingyu+graduaterecord.zhuanye1+graduaterecord.zhuanye2+graduaterecord.zhuanye3;
             --比較判斷條件，看是否將數(shù)據(jù)插入到result數(shù)據(jù)庫中
             if(graduaterecord.yingyu>=tempyingyu and
                graduaterecord.zhengzhi>=tempzhengzhi and
                graduaterecord.zhuanye1>=tempzhuanye1 and
                graduaterecord.zhuanye2>=tempzhuanye2 and
                graduaterecord.zhuanye3>=tempzhuanye3 and
                graduaterecord.totalscore>=temptotalscore )
             then
               graduateflag:='錄取'
             else
                graduateflag:='落選'
             --退出if
             end if;
             --退出游標循環(huán)
             exit when graduatecursor%notfound;
             --向result數(shù)據(jù)庫中插入數(shù)據(jù)
             insert into result(BH,xm,lb,yingyu,zhengzhi,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
             values(graduaterecord.BH,graduaterecord.XM,graduaterecord.LB,graduaterecord.YINGYU,graduaterecord.ZHENGZHI,
             graduaterecord.ZHUANYE1,graduaterecord.ZHUANYE2,graduaterecord.ZHUANYE3,graduatetotalscore,graduateflag);
             end loop;
             --關(guān)閉游標
             close graduatecursor;
             commit;--提交結(jié)果
             --處理異常
             exception
                when errormessage then
                    dbms_output.put_line('無法打開數(shù)據(jù)表');
                /*程序執(zhí)行結(jié)束"*/
                end;
       end graduateprocess;--存儲過程結(jié)束