Oracle 10g中存儲(chǔ)過程中使用游標(biāo)

實(shí)例設(shè)計(jì)

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

    --創(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;                       --達(dá)到最大值后再重新循環(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)建存儲(chǔ)過程
             
      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為記錄型變量，臨時(shí)存放通過游標(biāo)從graduate表中提取的記錄
         graduaterecord graduate%rowtype;
       --定義graduatetotalscore為數(shù)值型變量，統(tǒng)計(jì)總分
         graduatetotalscore result.totalscore%type;
        --定義graduateflag 為字符型變量，根據(jù)結(jié)果放入落選或錄取
         graduateflag varchar2(4);
         --定義游標(biāo)graduatecursor,存放的是所有的graduate數(shù)據(jù)表中的記錄
         cursor graduatecursor is
                select * from graduate;
         --定義異常處理
         errormessage exception;
        
        begin
        --打開游標(biāo)
             open graduatecursor;
             if graduatecursor%not found then
                --如果沒有數(shù)據(jù)。拋出異常
                raise errormessage;
             end if;
             --如果有數(shù)據(jù)，將數(shù)據(jù)循環(huán)出來
             loop
                 fetch graduatecursor into graduaterecord;
             --計(jì)算總分
             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;
             --退出游標(biāo)循環(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)閉游標(biāo)
             close graduatecursor;
             commit;--提交結(jié)果
             --處理異常
             exception
                when errormessage then
                    dbms_output.put_line('無法打開數(shù)據(jù)表');
                /*程序執(zhí)行結(jié)束"*/
                end;
       end graduateprocess;--存儲(chǔ)過程結(jié)束

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]