善用数据库实体SEQUENCE.docx

1、善用数据库实体SEQUENCEORACLE SEQUENCE之認識與應用邱麗如國立彰化師範大學電子計算機中心彰化市500進德路一號(04-7232105轉1523)E-mail:lirucc.ncue.edu.tw摘要SEQUENCE 是ORACLE 資料庫物件(DataBase Object)之一，SEQUENCE Generator(序列產生器) 可以用來產生一序列的整數值。本校招生系統即利用SEQUENCE之特性，製作每張考卷之試卷碼(試卷在一包試卷中的位置)，目的在減輕放榜後查榜工作量。依據SEQUENCE特性，SEQUENCE會以獨立的方式給個別使用者使用，讓每個使用者感覺正在使用自

2、己的 SEQUENCE，而不會與他人衝突。但在應用系統層次裡，因為作業方式的關係1，我們發現不能使用相同的SEQUENCE。在招生系統中我們就碰到這個問題，因此本文提出變通的作法。這些經驗讓我們進一步認識SEQUENCE 的用法及其限制。關鍵字：ORACLE ，SEQUENCE ，招生系統壹.前言本校各種獨立招生電腦化已實施多年，開始時尚未規劃試卷碼方式，故在放榜後之查榜作業常常弄得人仰馬翻，當考生前來查榜時承辦人員得從一箱箱之考卷中尋找每包考卷，再從此包中逐張找出考生之試卷，花費相當長的時間在找尋上。為了減少尋找時間，因此我們利用ORACLE SEQUENCE 製作每張考卷之試卷碼，並在每包

3、考卷之袋子上編好考場碼。在招生系統之成績登錄作業將此二項資料存入資料庫中，當放榜後再利用查榜作業按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置。以節省尋找時間、減輕工作人員體力負荷，並提昇作業效率。2、 認識 SEQUENCESEQUENCE 16是ORACLE資料庫系統之物件(DataBase Object)，當建立SEQUENCE 時系統會自動產生一系列遞增或遞減之唯一整數值。使用者可以利用 SEQUENCE 產生 primary key，當多個使用者使用相同SEQUENCE時，SEQUENCE 會以獨立的方式給個別使用者使用，因此每個使用者會感覺正在使用自己的 SEQUEN

4、CE，不會與他人相衝突。使用 SEQUENCE 之步驟如下：(一) 定義SEQUENCE GENERATOR,(二) 產生並取得 SEQUENCE NUMBERS(一) 定義SEQUENCE GENERATOR在使用 sequence 之前須先定義sequence generator，定義完之後才能利用此generator產生一序列的唯一整數值。其定義之語法如下，並在 SQL*PLUS 下建立(註：- 後面之文字為該行之解釋)：CREATE SEQUENCE user.sequence -取sequence generator名字INCREMENT BY n -預設值為1(遞增時)START

5、WITH n -省略時以minvalue或maxvalue開始MAXVALUE n | NOMAXVALUE -預設值為10e27(遞增)MINVALUE n | NOMINVALUE -預設值為 1(遞增)CYCLE | NOCYCLE -預設值為NOCYCLECACHE n | NOCACHE -預設值為 CACHE 20ORDER | NOORDERstart with n表示sequence number(序號)由n值開始，通常只在第一次產生序號時使用。Increment by n 表示序號每次遞增或遞減n值，當遞減時此值為負數。Maxvalue及minvalue定義sequence

6、之最大值及最小值。cycle 會讓序號值又從minvalue 開始(若是遞減會從maxvalue開始)，使用nocycle方式則其數值到maxvalue或minvalue時就不再產生了，若繼續使用會產生錯誤。cache 會讓系統預先配置一組數值在記憶體裡，如此會加快取得序號的速度，當此組最後一個數字被用完時，下一組數值就會被讀入記憶體裡，故使用了cache就不必每次都到磁碟機讀取序號，如此會提昇系統效能，當然其值需小於 ceil(maxvalue minvalue)/abs(increment)2。使用order 系統會確保序號一定依序產生，但即使未使用order，序號也會依序產生的。茲以底下

7、例子說明SQLcreate sequence paper -建立一個遞增之sequence generator名稱為 paper 2 increment by 1 -序列值每次增加 1 3 start with 1 -序列值由 1 開始 4 minvalue 1 -序列最小值為 1 5 maxvalue 50 -序列最大值為 50 6 cycle -回到 minvalue 7 cache 20; -系統預先產生20個值在記憶體裡(二)產生並取得SEQUENCE NUMBERS 定義完sequence generator後即可產生序號，產生及取得序號時會使用兩個系統虛擬欄位: nextval及c

8、urrval13。當參考到nextval時會驅動系統產生新的序號，且同時放入 currval，當使用者下一次使用與nextval相同值時就須參考currval，而不是nextval(因為會產生下一個值)。若第一次參考 currval 之前未先參考nextval，則會出現錯誤。產生序號之語法為 sequence_name.nextval，sequence_name是之前所定義之sequence generator 名稱，例paper.nextval。而使用與目前相同序號之語法為 equence_name.currval ， 例paper.currval。我們可以把這兩個虛擬欄位用在insert

9、、 update、 select 句子裡。須注意!在同一個輸出列裡，連續參考nextval 會產生相同號碼之序號。例如：SQL select paper.nextval a,paper.nextval b,paper.currval c from dual; A B C - A,B,C 為欄位別名 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

10、 7參、應用SEQUENCE： 試卷碼實作製作試卷碼目的在減輕查榜工作量，所以在成績輸入階段我們會在每包考卷袋子上編好考場碼。在第一次成績輸入作業時，將此項資料登入電腦中(如圖三之考場號碼001)。每當輸入者輸入一張試卷成績時，程式會利用SEQUENCE GENERATOR自動產生此張試卷之試卷碼(表示其在此包試卷之位置)，直到此包輸完為止，所有試卷依其在此包試卷中的位置循序產生自己的試卷碼。考場碼及試卷碼在此包試卷輸完後會同時存入資料庫中，下一包之試卷碼又從1開始。當放榜後再利用查榜作業(圖四)按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置，可節省相當多時間及人力，並提昇作業效

11、率。底下是招生系統試卷碼之實作過程介紹。(一)定義試卷碼之SEQUENCE GENERATOR 及產生SEQUENCE NUMBER首先我們在SQL*PLUS下建立一個名為paper之sequence generator，如下SQLcreate sequence paper -建立 sequence，名為 paper2 increment by 1 -每次增加3 start with 1 -序號由開始4 maxvalue 50 -序列最大值到505 cycle; - 又從1開始 定義完之後在招生系統之第一次成績輸入作業78中產生序號，產生方式如下:select paper.nextval in

12、to 試卷碼欄位from dual當sequence generator 就緒後，我們即測試使用，但使用時卻面臨了一些問題。(二)產生SEQUENCE NUMBER 之測試過程及困難原先我們認為每個使用者會獨立地依序產生自己的序號，與他人不會相衝突。可是在應用系統執行時卻碰到兩種問題:(1) 使用者無法同時作業,(2) 每包試卷份數不同無法使用CYCLE方式(1)使用者無法同時作業當兩個使用者同時進行成績登錄並使用同一個sequence時，各自所輸入的試卷無法產生連績的試卷碼，如圖一及圖二之使用者產生跳號的情形(圖中paper為試卷碼)。正常狀況我們要每包考卷其試卷碼從1開始，依序產生至此包之

13、試卷輸完為止。圖一圖二(2)每包試卷份數不同無法使用CYCLE方式因作業方式是一包試卷輸入完畢後，下一包之試卷碼又得從1開始，可是每一包之試卷數不固定，因此應用系統無法使用正常之 cycle 方式，讓序號到達mvaxvalue時 又從1開始。可是下一包之試卷碼一定要從1開始，怎麼辦?我們的想法是先 drop sequence，然後再重新 create 新的sequence 。但是當我們欲drop sequence時，另一個輸入者正在使用相同的 sequence，因此造成無法 drop sequence的訊息。所以提出以下之改進方式。(三)解決方式 以上兩種問題，解決方法只能每個使用者使用自己的

14、 sequence generator(因為當有人使用SEQUENCE時，其他人是無法 DROP 與 ALTER SEQUENCE的)，如此使用者也必須使用各自的第一次成績輸入作業程式。例如現在有甲、乙兩位輸入者，甲使用的第一次成績輸入作業為 score1，其sequence generator 名稱為paper1；乙使用的第一次成績輸入作業為 score2，其 sequence generator名稱為paper2。現以甲的輸入作業score1為例，每次完成輸入一包試卷後，應用系統會執行底下動作。先 drop sequence generator，然後再重新 create 新的sequenc

15、e generator，如此下一包之試卷碼一定會從1開始。其作法如下(這個動作可以隱含在應用系統中):SQLdrop sequence paper1; 2 create sequence paper13 increment by 1 4 start with 1;將來第一次成績輸入作業 score1，執行底下動作產生試卷碼：select paper1.nextval into 試卷碼欄位from dual乙輸入者其過程也與甲相同，只是將paper1改成paper2即可。當然這種作法令人覺得比較繁複，因為需有較多的應用程式。但是本校輸入成績的人力只有兩位，故可以符合我們的需求不會造成困擾，而且使用時相當容易，只要下一個SELECT指令，試卷碼即刻產生。圖三是正式使用的第一次成績輸入作業，密碼左邊欄位即是試卷碼，目前已輸入兩筆成績，故試卷碼產生至2。 圖三當然，假設成績輸入需要多位人力，為了避免過多輸入作業程式造成困擾，可改用一個非資料庫欄位當計數器製作試卷碼。(四)應用 SEQUENCE 之成果：試卷碼應用考場碼及試卷碼產生並儲存後，即可運用於查榜作業(圖四)。圖四中考生賴瑞文前來查榜時，憑其准考証號查詢出各科資訊，由此資訊得知國文在