??????? SEQUENCE

Document Sample
??????? SEQUENCE Powered By Docstoc
					專題報導 -   ORACLE SEQUENCE 之認識與應用                                      16


                ORACLE SEQUENCE 之認識與應用

                                邱麗如


                    國立彰化師範大學電子計算機中心


                彰化市 500 進德路一號(04-7232105 轉 1523)


                       E-mail:liru@cc.ncue.edu.tw




                                 摘要

   SEQUENCE 是 ORACLE 資料庫物件(DataBase Object)之一,SEQUENCE Generator(序
列產生器) 可以用來產生一序列的整數值。本校招生系統即利用 SEQUENCE 之特性,製作每張考
卷之試卷碼(試卷在一包試卷中的位置),目的在減輕放榜後查榜工作量。依據 SEQUENCE 特性,
SEQUENCE 會以獨立的方式給個別使用者使用,讓每個使用者感覺正在使用自己的
SEQUENCE,而不會與他人衝突。但在應用系統層次裡,因為作業方式的關係[1],我們發現不能
使用相同的 SEQUENCE。在招生系統中我們就碰到這個問題,因此本文提出變通的作法。這些經
驗讓我們進一步認識 SEQUENCE 的用法及其限制。
關鍵字:ORACLE ,SEQUENCE ,招生系統


壹.前言


   本校各種獨立招生電腦化已實施多年,開始時尚未規劃試卷碼方式,故在放榜


後之查榜作業常常弄得人仰馬翻,當考生前來查榜時承辦人員得從一箱箱之考卷中


尋找每包考卷,再從此包中逐張找出考生之試卷,花費相當長的時間在找尋上。為


了減少尋找時間,因此我們利用 ORACLE SEQUENCE 製作每張考卷之試卷碼,


並在每包考卷之袋子上編好考場碼。在招生系統之「成績登錄作業」將此二項資料


存入資料庫中,當放榜後再利用「查榜作業」按此資訊尋找相同編號之考卷袋子,

                                                    中興大學計資中心技術通訊第 17 期
專題報導 -     ORACLE SEQUENCE 之認識與應用                                       17


並快速翻到此試卷碼之考卷位置。以節省尋找時間、減輕工作人員體力負荷,並提


昇作業效率。


貳、     認識 SEQUENCE


     SEQUENCE [1]~[6]是 ORACLE 資料庫系統之物件(DataBase Object),當建


立 SEQUENCE 時系統會自動產生一系列遞增或遞減之唯一整數值。使用者可以利


用 SEQUENCE 產生 primary key,當多個使用者使用相同 SEQUENCE 時,


SEQUENCE 會以獨立的方式給個別使用者使用,因此每個使用者會感覺正在使用


自己的 SEQUENCE,不會與他人相衝突。使用 SEQUENCE 之步驟如下:(一) 定


義 SEQUENCE GENERATOR,(二) 產生並取得 SEQUENCE NUMBERS


(一) 定義 SEQUENCE GENERATOR


  在使用 sequence 之前須先定義 sequence generator,定義完之後才能利用此


generator 產生一序列的唯一整數值。其定義之語法如下,並在 SQL*PLUS 下建


立(註:-- 後面之文字為該行之解釋):


      CREATE SEQUENCE [user.]sequence      --取 sequence generator 名字


      [INCREMENT BY n]                  --預設值為 1(遞增時)


      [START WITH n]                    --省略時以 minvalue 或 maxvalue 開始


      [MAXVALUE n | NOMAXVALUE]         --預設值為 10e27(遞增)


      [MINVALUE n | NOMINVALUE]         -預設值為 1(遞增)

                                                    中興大學計資中心技術通訊第 17 期
    專題報導 -     ORACLE SEQUENCE 之認識與應用                                    18


         [CYCLE | NOCYCLE]               --預設值為 NOCYCLE


         [CACHE n | NOCACHE]              --預設值為 CACHE 20


         [ORDER | NOORDER]


        start with n 表示 sequence number(序號)由 n 值開始,通常只在第一次產生序


    號時使用。Increment by n 表示序號每次遞增或遞減 n 值,當遞減時此值為負數。


    Maxvalue 及 minvalue 定義 sequence 之最大值及最小值。cycle 會讓序號值又從


    minvalue 開始(若是遞減會從 maxvalue 開始),使用 nocycle 方式則其數值到


    maxvalue 或 minvalue 時就不再產生了,若繼續使用會產生錯誤。cache 會讓系統


    預先配置一組數值在記憶體裡,如此會加快取得序號的速度,當此組最後一個數字


    被用完時,下一組數值就會被讀入記憶體裡,故使用了 cache 就不必每次都到磁碟


    機讀取序號,如此會提昇系統效能,當然其值需小於 ceil(maxvalue –


    minvalue)/abs(increment)[2]。使用 order 系統會確保序號一定依序產生,但即使未


    使用 order,序號也會依序產生的。


        茲以底下例子說明


         SQL>create sequence paper    --建立一個遞增之 sequence generator 名稱為


paper


          2 increment by 1           --序列值每次增加 1


          3 start with 1             --序列值由 1 開始


                                                    中興大學計資中心技術通訊第 17 期
專題報導 -       ORACLE SEQUENCE 之認識與應用                                            19


        4 minvalue 1                  --序列最小值為 1


        5 maxvalue 50                  --序列最大值為 50


        6 cycle                       --回到 minvalue


        7 cache 20;                     --系統預先產生 20 個值在記憶體裡


(二)產生並取得 SEQUENCE NUMBERS


    定義完 sequence generator 後即可產生序號,產生及取得序號時會使用兩個系


                             。
統虛擬欄位: nextval 及 currval[1][3] 當參考到 nextval 時會驅動系統產生新的序號,


且同時放入 currval,當使用者下一次使用與 nextval 相同值時就須參考 currval,而


不是 nextval(因為會產生下一個值)。若第一次參考 currval 之前未先參考 nextval,


則會出現錯誤。


    產生序號之語法為 sequence_name.nextval,sequence_name 是之前所定義之


sequence generator 名稱,例 paper.nextval。而使用與目前相同序號之語法為


equence_name.currval , 例 paper.currval。


    我們可以把這兩個虛擬欄位用在 insert 、 update、 select 句子裡。須注意!在


同一個輸出列裡,連續參考 nextval 會產生相同號碼之序號。例如:


            SQL> select paper.nextval a,paper.nextval b,paper.currval c from


dual;


                       A          B            C          -- A,B,C 為欄位別名

                                                             中興大學計資中心技術通訊第 17 期
 專題報導 -   ORACLE SEQUENCE 之認識與應用                                  20


                    5           5           5


 若是分開 nextval 才能產生下一個序號,如下




          SQL> select paper.nextval a from dual;


          SQL> select paper.nextval b from dual;


          SQL> select paper.currval c from dual;


                  A             B            C


                  6              7          7




參、應用 SEQUENCE: 試卷碼實作


  製作試卷碼目的在減輕查榜工作量,所以在成績輸入階段我們會在每包考卷袋子


上編好考場碼。在第一次成績輸入作業時,將此項資料登入電腦中(如圖三之考場號碼


001)。每當輸入者輸入一張試卷成績時,程式會利用 SEQUENCE GENERATOR 自


動產生此張試卷之試卷碼(表示其在此包試卷之位置),直到此包輸完為止,所有試卷


依其在此包試卷中的位置循序產生自己的試卷碼。考場碼及試卷碼在此包試卷輸完後


會同時存入資料庫中,下一包之試卷碼又從 1 開始。當放榜後再利用查榜作業(圖四)


按此資訊尋找相同編號之考卷袋子,並快速翻到此試卷碼之考卷位置,可節省相當多


時間及人力,並提昇作業效率。底下是招生系統試卷碼之實作過程介紹。


                                                   中興大學計資中心技術通訊第 17 期
專題報導 -   ORACLE SEQUENCE 之認識與應用                                 21


(一)定義試卷碼之 SEQUENCE GENERATOR 及產生 SEQUENCE NUMBER


 首先我們在 SQL*PLUS 下建立一個名為 paper 之 sequence generator,如下


         SQL>create sequence paper     --建立 sequence,名為 paper


           2   increment by 1        --每次增加1


           3   start with 1          --序號由1開始


           4   maxvalue 50           --序列最大值到 50


           5   cycle;                -- 又從 1 開始




定義完之後在招生系統之第一次成績輸入作業[7][8]中產生序號,產生方式如下:


         select paper.nextval


         into 試卷碼欄位


         from dual


當 sequence generator 就緒後,我們即測試使用,但使用時卻面臨了一些問題。


(二)產生 SEQUENCE NUMBER 之測試過程及困難


  原先我們認為每個使用者會獨立地依序產生自己的序號,與他人不會相衝突。可


是在應用系統執行時卻碰到兩種問題:(1) 使用者無法同時作業,(2) 每包試卷份數不同


無法使用 CYCLE 方式




                                            中興大學計資中心技術通訊第 17 期
專題報導 -   ORACLE SEQUENCE 之認識與應用                  22


(1)使用者無法同時作業


  當兩個使用者同時進行成績登錄並使用同一個 sequence 時,各自所輸入的試卷


無法產生連績的試卷碼,如圖一及圖二之使用者產生跳號的情形(圖中 paper 為試卷


碼)。正常狀況我們要每包考卷其試卷碼從 1 開始,依序產生至此包之試卷輸完為止。




                             圖一




                                  中興大學計資中心技術通訊第 17 期
專題報導 -    ORACLE SEQUENCE 之認識與應用                             23




                              圖二


(2)每包試卷份數不同無法使用 CYCLE 方式


   因作業方式是一包試卷輸入完畢後,下一包之試卷碼又得從 1 開始,可是每一包


之試卷數不固定,因此應用系統無法使用正常之 cycle 方式,讓序號到達 mvaxvalue


時 又從 1 開始。可是下一包之試卷碼一定要從 1 開始,怎麼辦?我們的想法是先 drop


sequence,然後再重新 create 新的 sequence 。但是當我們欲 drop sequence 時,


另一個輸入者正在使用相同的 sequence,因此造成無法 drop sequence 的訊息。所


以提出以下之改進方式。


(三)解決方式


   以上兩種問題,解決方法只能每個使用者使用自己的 sequence generator(因為


當有人使用 SEQUENCE 時,其他人是無法 DROP 與 ALTER SEQUENCE 的),如


                                            中興大學計資中心技術通訊第 17 期
專題報導 -    ORACLE SEQUENCE 之認識與應用                                  24


此使用者也必須使用各自的第一次成績輸入作業程式。例如現在有甲、乙兩位輸入者,


甲使用的第一次成績輸入作業為 score1,其 sequence generator 名稱為 paper1;


乙使用的第一次成績輸入作業為 score2,其 sequence generator 名稱為 paper2。


現以甲的輸入作業 score1 為例,每次完成輸入一包試卷後,應用系統會執行底下動


作。先 drop sequence generator,然後再重新 create 新的 sequence generator,


如此下一包之試卷碼一定會從 1 開始。其作法如下(這個動作可以隱含在應用系統中):


          SQL>drop sequence paper1;


            2 create sequence paper1


            3 increment by 1


            4   start with 1;


將來第一次成績輸入作業 score1,執行底下動作產生試卷碼:


          select paper1.nextval


         into 試卷碼欄位


         from dual


    乙輸入者其過程也與甲相同,只是將 paper1 改成 paper2 即可。當然這種作法


令人覺得比較繁複,因為需有較多的應用程式。但是本校輸入成績的人力只有兩位,


故可以符合我們的需求不會造成困擾,而且使用時相當容易,只要下一個 SELECT


指令,試卷碼即刻產生。圖三是正式使用的第一次成績輸入作業,密碼左邊欄位即是


                                                 中興大學計資中心技術通訊第 17 期
專題報導 -   ORACLE SEQUENCE 之認識與應用                  25


試卷碼,目前已輸入兩筆成績,故試卷碼產生至 2。




                                       圖三




  當然,假設成績輸入需要多位人力,為了避免過多輸入作業程式造成困擾,可改


用一個非資料庫欄位當計數器製作試卷碼。




(四)應用 SEQUENCE 之成果:試卷碼應用


  考場碼及試卷碼產生並儲存後,即可運用於查榜作業(圖四)。圖四中考生賴瑞文


前來查榜時,憑其准考証號查詢出各科資訊,由此資訊得知國文在編號 115 之袋子


裡,而且位在此包試卷之第 34 張。其它科目意義相同,按此考場碼及試卷碼資訊,


尋找考卷時就不用逐箱逐包逐張找了(箱子上會記載那些編號放在此箱裡)。




                                  中興大學計資中心技術通訊第 17 期
專題報導 -   ORACLE SEQUENCE 之認識與應用                      26




                                            圖四




肆、結語


                            ,
  以上的應用使我們對 SEQUENCE 有進一步的認識 雖然 SEQUENCE 有其限制


但只要使用得宜,也能為我們解決困難的工作。在招生作業裡考場碼及試卷碼的製作


及搭配查榜作業,使得工作人員不必疲於尋找考卷,不但工作效率提昇了,工作人員


也不必浪費太多體力在搬動箱子及翻遍每一包試卷上。最後我們將使用 SEQUENCE


須注意的地方也提出如下[1] [2] [3] [4]:


1.當兩個使用者同時使用相同的 SEQUENCE 時,任一使用者可能會有跳號情況產


 生,如圖一及圖二。


2.使用 CACHE 功能,當 CACHING SEQUENCE NUMBERS 時,在系統關機後已


 經產生但尚未使用的 SEQUENCE NUMBERS 會遺失掉。



                                      中興大學計資中心技術通訊第 17 期
專題報導 -   ORACLE SEQUENCE 之認識與應用                          27


3.使用 CACHE 功能,當 CACHING SEQUENCE NUMBERS 時,在 EXPORT 及


 IMPORT 時可能會發生跳號及重複使用的情形。例如:


  A.假設目前在 CACHE 之一組號碼為 21~40,目前被使用的號碼是 33,當


    EXPORT 時是 40 被存入資料庫而不是 33。當 SEQUENCE 被 IMPORT 回來


    時,下一組號碼 41~60 會被產生,且 41 會先被使用,之前因為應用系統結束


    而尚未使用的號碼就跳掉了。


  B.同上之 CACHE,在 EXPORT 時正在使用 40,且 40 被存入資料庫。當此組最


    後一個 number 40 被用完時,應用系統繼續進行,下一組數值會被讀入記憶體


    裡即 41~60。當 IMPORT 時應用系統已使用到 46,但當初 EXPOORT 時資料


    庫之記錄是 40,故當 SEQUENCE 被 IMPORT 回來時,下一組號碼 41~60


    會被產生,且 41 會被拿來使用,這樣就與之前被使用過的號碼重複了。


4.使用 CACHE 功能,當 CACHING SEQUENCE NUMBERS 時,使用者在應用系


 統交易中沒有作 COMMIT 動作或交易不正常中止,也會有跳號情形產生。


5.使用 nocycle 方式則值到 maxvalue 或 minvalue 時就不再產生了,若繼續使用系


 統會產生錯誤。


6.若第一次參考 currval 之前未先參考 nextval,則會出現錯誤。




  為了避免在 import 時序號 重複使用或跳號情形發生,最好以手動方式 drop


sequence 再重新 create sequence ,且由正確的號碼開始。以上是使用 sequence


                                         中興大學計資中心技術通訊第 17 期
  專題報導 -       ORACLE SEQUENCE 之認識與應用                                                     28


  後的心得,提出本文做為應用系統開發者彼此經驗的交流。




 伍、參考文獻:


[1] ORACLE 公司 (1991) Oracle SQL Language Reference Manual。


[2] ORACLE 公司 (1999) Oracle8i SQL        Reference, Volume 2


[3] ORACLE 公司 (1991) ORACLE RDBMS Database Administrator’ s Guide 手冊


[4] ORACLE 公司 (1991) ORACLE RDBMS performance tuning guide


[5] ORACLE 公司 (1999) oracle8i administrator’ s guide


[6] ORACLE 公司 (1999) oracle8i parallel server concepts and administration release 8.1.5


[7] 北方交通大學自動化系統研究所 (1997) ORACLE Developer /2000 開發工具指南。


[8] ORACLE 公司 (1999)      Developer 2000 開發工具線上輔助功能。




                                                               中興大學計資中心技術通訊第 17 期

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:3/30/2012
language:
pages:13