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

實例設計

    1. 功能設計
    某高校開發的研究生招生系統，要求設計PL/SQL程序對考生的成績數據進行處理，處理的邏輯是根據每門專業課的最低分數線和總分的最低分數線自動將考生歸類為錄取考生、調劑考生和落選考生。
    為此設計兩個數據表，graduate數據表存放考生成績，result數據表存放處理結果，PL/SQL程序完成的功能就是將graduate數據表中的數據逐行掃描，根據分數線進行判斷，計算出各科總分，在result數據表中將標志字段自動添加上“錄取”或“落選”。 -
 

    --創建數據庫表 --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
    )
    
    --創建序列
    create sequence my_suquence  --序列名稱
    minvalue 0                   --序列最小值
    start with 201112081                 --序列起始值
    maxvalue 999999999                  --序列最大值
    increment by 1               --序列的增長值
    nocache                      --不預先在內存中緩存
    cycle;                       --達到最大值后再重新循環
   
    --插入數據
    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');
   
  --創建數據庫表  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;
--創建存儲過程
             
      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為數值型變量，統計總分
         graduatetotalscore result.totalscore%type;
        --定義graduateflag 為字符型變量，根據結果放入落選或錄取
         graduateflag varchar2(4);
         --定義游標graduatecursor,存放的是所有的graduate數據表中的記錄
         cursor graduatecursor is
                select * from graduate;
         --定義異常處理
         errormessage exception;
        
        begin
        --打開游標
             open graduatecursor;
             if graduatecursor%not found then
                --如果沒有數據。拋出異常
                raise errormessage;
             end if;
             --如果有數據，將數據循環出來
             loop
                 fetch graduatecursor into graduaterecord;
             --計算總分
             graduatetotalscore:=graduaterecord.zhengzhi+graduaterecord.yingyu+graduaterecord.zhuanye1+graduaterecord.zhuanye2+graduaterecord.zhuanye3;
             --比較判斷條件，看是否將數據插入到result數據庫中
             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;
             --退出游標循環
             exit when graduatecursor%notfound;
             --向result數據庫中插入數據
             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;
             --關閉游標
             close graduatecursor;
             commit;--提交結果
             --處理異常
             exception
                when errormessage then
                    dbms_output.put_line('無法打開數據表');
                /*程序執行結束"*/
                end;
       end graduateprocess;--存儲過程結束

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]