善用数据库实体SEQUENCEWord下载.docx

1、這些經驗讓我們進一步認識SEQUENCE 的用法及其限制。關鍵字：ORACLE ，SEQUENCE ，招生系統壹.前言本校各種獨立招生電腦化已實施多年，開始時尚未規劃試卷碼方式，故在放榜後之查榜作業常常弄得人仰馬翻，當考生前來查榜時承辦人員得從一箱箱之考卷中尋找每包考卷，再從此包中逐張找出考生之試卷，花費相當長的時間在找尋上。為了減少尋找時間，因此我們利用ORACLE SEQUENCE 製作每張考卷之試卷碼，並在每包考卷之袋子上編好考場碼。在招生系統之成績登錄作業將此二項資料存入資料庫中，當放榜後再利用查榜作業按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置。以節省尋找時間、減輕工

2、作人員體力負荷，並提昇作業效率。2、 認識 SEQUENCESEQUENCE 16是ORACLE資料庫系統之物件（DataBase Object），當建立SEQUENCE 時系統會自動產生一系列遞增或遞減之唯一整數值。使用者可以利用 SEQUENCE 產生 primary key，當多個使用者使用相同SEQUENCE時，SEQUENCE 會以獨立的方式給個別使用者使用，因此每個使用者會感覺正在使用自己的 SEQUENCE，不會與他人相衝突。使用 SEQUENCE 之步驟如下：（一） 定義SEQUENCE GENERATOR,（二） 產生並取得 SEQUENCE NUMBERS（一） 定義SEQ

3、UENCE 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 | NOMINVAL

4、UE -預設值為 1（遞增）CYCLE | NOCYCLE -預設值為NOCYCLECACHE n | NOCACHE -預設值為 CACHE 20ORDER | NOORDERstart with n表示sequence number（序號）由n值開始，通常只在第一次產生序號時使用。Increment by n 表示序號每次遞增或遞減n值，當遞減時此值為負數。Maxvalue及minvalue定義sequence 之最大值及最小值。cycle 會讓序號值又從minvalue 開始（若是遞減會從maxvalue開始），使用nocycle方式則其數值到maxvalue或minvalue時就不再產

5、生了，若繼續使用會產生錯誤。cache 會讓系統預先配置一組數值在記憶體裡，如此會加快取得序號的速度，當此組最後一個數字被用完時，下一組數值就會被讀入記憶體裡，故使用了cache就不必每次都到磁碟機讀取序號，如此會提昇系統效能，當然其值需小於 ceil（maxvalue minvalue）/abs（increment）2。使用order 系統會確保序號一定依序產生，但即使未使用order，序號也會依序產生的。茲以底下例子說明SQLcreate sequence paper -建立一個遞增之sequence generator名稱為 paper 2 increment by 1 -序列值每次增加

6、 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及currval13。當參考到nextval時會驅動系統產生新的序號，且同時放入 currval，當使用者下一次使用與nextval相同值時就須參考currval，而不是nextval

7、（因為會產生下一個值）。若第一次參考 currval 之前未先參考nextval，則會出現錯誤。產生序號之語法為 sequence_name.nextval，sequence_name是之前所定義之sequence generator 名稱，例paper.nextval。而使用與目前相同序號之語法為 equence_name.currval ， 例paper.currval。我們可以把這兩個虛擬欄位用在insert 、 update、 select 句子裡。須注意!在同一個輸出列裡，連續參考nextval 會產生相同號碼之序號。例如： select paper.nextval a,paper.

8、nextval b,paper.currval c from dual; A B C - A,B,C 為欄位別名 5 5 5若是分開nextval才能產生下一個序號，如下 select paper.nextval a from dual; select paper.nextval b from dual; select paper.currval c from dual; A B C 6 7 7參、應用SEQUENCE： 試卷碼實作製作試卷碼目的在減輕查榜工作量，所以在成績輸入階段我們會在每包考卷袋子上編好考場碼。在第一次成績輸入作業時，將此項資料登入電腦中（如圖三之考場號碼001）。每當輸入

9、者輸入一張試卷成績時，程式會利用SEQUENCE GENERATOR自動產生此張試卷之試卷碼（表示其在此包試卷之位置），直到此包輸完為止，所有試卷依其在此包試卷中的位置循序產生自己的試卷碼。考場碼及試卷碼在此包試卷輸完後會同時存入資料庫中，下一包之試卷碼又從1開始。當放榜後再利用查榜作業（圖四）按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置，可節省相當多時間及人力，並提昇作業效率。底下是招生系統試卷碼之實作過程介紹。（一）定義試卷碼之SEQUENCE GENERATOR 及產生SEQUENCE NUMBER首先我們在SQL*PLUS下建立一個名為paper之sequence ge

10、nerator，如下create sequence paper -建立 sequence，名為 paper2 increment by 1 -每次增加3 start with 1 -序號由開始4 maxvalue 50 -序列最大值到505 cycle; - 又從1開始 定義完之後在招生系統之第一次成績輸入作業78中產生序號，產生方式如下:select paper.nextval into 試卷碼欄位from dual當sequence generator 就緒後，我們即測試使用，但使用時卻面臨了一些問題。（二）產生SEQUENCE NUMBER 之測試過程及困難原先我們認為每個使用者會獨立地

11、依序產生自己的序號，與他人不會相衝突。可是在應用系統執行時卻碰到兩種問題:（1） 使用者無法同時作業,（2） 每包試卷份數不同無法使用CYCLE方式（1）使用者無法同時作業當兩個使用者同時進行成績登錄並使用同一個sequence時，各自所輸入的試卷無法產生連績的試卷碼，如圖一及圖二之使用者產生跳號的情形（圖中paper為試卷碼）。正常狀況我們要每包考卷其試卷碼從1開始，依序產生至此包之試卷輸完為止。圖一圖二（2）每包試卷份數不同無法使用CYCLE方式因作業方式是一包試卷輸入完畢後，下一包之試卷碼又得從1開始，可是每一包之試卷數不固定，因此應用系統無法使用正常之 cycle 方式，讓序號到達mv

12、axvalue時 又從1開始。可是下一包之試卷碼一定要從1開始，怎麼辦?我們的想法是先 drop sequence，然後再重新 create 新的sequence 。但是當我們欲drop sequence時，另一個輸入者正在使用相同的 sequence，因此造成無法 drop sequence的訊息。所以提出以下之改進方式。（三）解決方式 以上兩種問題，解決方法只能每個使用者使用自己的 sequence generator（因為當有人使用SEQUENCE時，其他人是無法 DROP 與 ALTER SEQUENCE的），如此使用者也必須使用各自的第一次成績輸入作業程式。例如現在有甲、乙兩位輸入者

13、，甲使用的第一次成績輸入作業為 score1，其sequence generator 名稱為paper1；乙使用的第一次成績輸入作業為 score2，其 sequence generator名稱為paper2。現以甲的輸入作業score1為例，每次完成輸入一包試卷後，應用系統會執行底下動作。先 drop sequence generator，然後再重新 create 新的sequence generator，如此下一包之試卷碼一定會從1開始。其作法如下（這個動作可以隱含在應用系統中）:drop sequence paper1;2 create sequence paper13 incremen

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