善用数据库实体SEQUENCE.docx

善用数据库实体SEQUENCE.docx

《善用数据库实体SEQUENCE.docx》由会员分享，可在线阅读，更多相关《善用数据库实体SEQUENCE.docx（8页珍藏版）》请在冰豆网上搜索。

善用数据库实体SEQUENCE

ORACLESEQUENCE之認識與應用

邱麗如

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

彰化市500進德路一號（04-7232105轉1523）

E-mail:

liru@cc.ncue.edu.tw

摘要

SEQUENCE是ORACLE資料庫物件（DataBaseObject）之一，SEQUENCEGenerator（序列產生器）可以用來產生一序列的整數值。

本校招生系統即利用SEQUENCE之特性，製作每張考卷之試卷碼（試卷在一包試卷中的位置），目的在減輕放榜後查榜工作量。

依據SEQUENCE特性，SEQUENCE會以獨立的方式給個別使用者使用，讓每個使用者感覺正在使用自己的SEQUENCE，而不會與他人衝突。

但在應用系統層次裡，因為作業方式的關係[1]，我們發現不能使用相同的SEQUENCE。

在招生系統中我們就碰到這個問題，因此本文提出變通的作法。

這些經驗讓我們進一步認識SEQUENCE的用法及其限制。

關鍵字：

ORACLE，SEQUENCE，招生系統

壹.前言

本校各種獨立招生電腦化已實施多年，開始時尚未規劃試卷碼方式，故在放榜後之查榜作業常常弄得人仰馬翻，當考生前來查榜時承辦人員得從一箱箱之考卷中尋找每包考卷，再從此包中逐張找出考生之試卷，花費相當長的時間在找尋上。

為了減少尋找時間，因此我們利用ORACLESEQUENCE製作每張考卷之試卷碼，並在每包考卷之袋子上編好考場碼。

在招生系統之「成績登錄作業」將此二項資料存入資料庫中，當放榜後再利用「查榜作業」按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置。

以節省尋找時間、減輕工作人員體力負荷，並提昇作業效率。

2、認識SEQUENCE

SEQUENCE[1]~[6]是ORACLE資料庫系統之物件（DataBaseObject），當建立SEQUENCE時系統會自動產生一系列遞增或遞減之唯一整數值。

使用者可以利用SEQUENCE產生primarykey，當多個使用者使用相同SEQUENCE時，SEQUENCE會以獨立的方式給個別使用者使用，因此每個使用者會感覺正在使用自己的SEQUENCE，不會與他人相衝突。

使用SEQUENCE之步驟如下：

（一）定義SEQUENCEGENERATOR,

（二）產生並取得SEQUENCENUMBERS

（一）定義SEQUENCEGENERATOR

在使用sequence之前須先定義sequencegenerator，定義完之後才能利用此generator產生一序列的唯一整數值。

其定義之語法如下，並在SQL*PLUS下建立（註：

--後面之文字為該行之解釋）：

CREATESEQUENCE[user.]sequence--取sequencegenerator名字

[INCREMENTBYn]--預設值為1（遞增時）

[STARTWITHn]--省略時以minvalue或maxvalue開始

[MAXVALUEn|NOMAXVALUE]--預設值為10e27（遞增）

[MINVALUEn|NOMINVALUE]-預設值為1（遞增）

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

[CACHEn|NOCACHE]--預設值為CACHE20

[ORDER|NOORDER]

startwithn表示sequencenumber（序號）由n值開始，通常只在第一次產生序號時使用。

Incrementbyn表示序號每次遞增或遞減n值，當遞減時此值為負數。

Maxvalue及minvalue定義sequence之最大值及最小值。

cycle會讓序號值又從minvalue開始（若是遞減會從maxvalue開始），使用nocycle方式則其數值到maxvalue或minvalue時就不再產生了，若繼續使用會產生錯誤。

cache會讓系統預先配置一組數值在記憶體裡，如此會加快取得序號的速度，當此組最後一個數字被用完時，下一組數值就會被讀入記憶體裡，故使用了cache就不必每次都到磁碟機讀取序號，如此會提昇系統效能，當然其值需小於ceil（maxvalue–minvalue）/abs（increment）[2]。

使用order系統會確保序號一定依序產生，但即使未使用order，序號也會依序產生的。

茲以底下例子說明

SQL>createsequencepaper--建立一個遞增之sequencegenerator名稱為paper

2incrementby1--序列值每次增加1

3startwith1--序列值由1開始

4minvalue1--序列最小值為1

5maxvalue50--序列最大值為50

6cycle--回到minvalue

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

（二）產生並取得SEQUENCENUMBERS

定義完sequencegenerator後即可產生序號，產生及取得序號時會使用兩個系統虛擬欄位:

nextval及currval[1][3]。

當參考到nextval時會驅動系統產生新的序號，且同時放入currval，當使用者下一次使用與nextval相同值時就須參考currval，而不是nextval（因為會產生下一個值）。

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

產生序號之語法為sequence_name.nextval，sequence_name是之前所定義之sequencegenerator名稱，例paper.nextval。

而使用與目前相同序號之語法為equence_name.currval，例paper.currval。

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

須注意!

在同一個輸出列裡，連續參考nextval會產生相同號碼之序號。

例如：

SQL>selectpaper.nextvala,paper.nextvalb,paper.currvalcfromdual;

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

555

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

SQL>selectpaper.nextvalafromdual;

SQL>selectpaper.nextvalbfromdual;

SQL>selectpaper.currvalcfromdual;

ABC

677

參、應用SEQUENCE：

試卷碼實作

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

在第一次成績輸入作業時，將此項資料登入電腦中（如圖三之考場號碼001）。

每當輸入者輸入一張試卷成績時，程式會利用SEQUENCEGENERATOR自動產生此張試卷之試卷碼（表示其在此包試卷之位置），直到此包輸完為止，所有試卷依其在此包試卷中的位置循序產生自己的試卷碼。

考場碼及試卷碼在此包試卷輸完後會同時存入資料庫中，下一包之試卷碼又從1開始。

當放榜後再利用查榜作業（圖四）按此資訊尋找相同編號之考卷袋子，並快速翻到此試卷碼之考卷位置，可節省相當多時間及人力，並提昇作業效率。

底下是招生系統試卷碼之實作過程介紹。

（一）定義試卷碼之SEQUENCEGENERATOR及產生SEQUENCENUMBER

首先我們在SQL*PLUS下建立一個名為paper之sequencegenerator，如下

SQL>createsequencepaper--建立sequence，名為paper2incrementby1--每次增加１3startwith1--序號由１開始

4maxvalue50--序列最大值到50

5cycle;--又從1開始

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

selectpaper.nextval

into試卷碼欄位　　　　　　　　　　　

fromdual　　　　　　

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

（二）產生SEQUENCENUMBER之測試過程及困難

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

可是在應用系統執行時卻碰到兩種問題:

（1）使用者無法同時作業,

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

（1）使用者無法同時作業

當兩個使用者同時進行成績登錄並使用同一個sequence時，各自所輸入的試卷無法產生連績的試卷碼，如圖一及圖二之使用者產生跳號的情形（圖中paper為試卷碼）。

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

圖一

圖二

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

因作業方式是一包試卷輸入完畢後，下一包之試卷碼又得從1開始，可是每一包之試卷數不固定，因此應用系統無法使用正常之cycle方式，讓序號到達mvaxvalue時又從1開始。

可是下一包之試卷碼一定要從1開始，怎麼辦?

我們的想法是先dropsequence，然後再重新create新的sequence。

但是當我們欲dropsequence時，另一個輸入者正在使用相同的sequence，因此造成無法dropsequence的訊息。

所以提出以下之改進方式。

（三）解決方式

以上兩種問題，解決方法只能每個使用者使用自己的sequencegenerator（因為當有人使用SEQUENCE時，其他人是無法DROP與ALTERSEQUENCE的），如此使用者也必須使用各自的第一次成績輸入作業程式。

例如現在有甲、乙兩位輸入者，甲使用的第一次成績輸入作業為score1，其sequencegenerator名稱為paper1；乙使用的第一次成績輸入作業為score2，其sequencegenerator名稱為paper2。

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

先dropsequencegenerator，然後再重新create新的sequencegenerator，如此下一包之試卷碼一定會從1開始。

其作法如下（這個動作可以隱含在應用系統中）:

SQL>dropsequencepaper1;

2createsequencepaper1

3incrementby14startwith1;

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

selectpaper1.nextval

into試卷碼欄位　　　　　　　　　　　

fromdual　　　　

乙輸入者其過程也與甲相同，只是將paper1改成paper2即可。

當然這種作法令人覺得比較繁複，因為需有較多的應用程式。

但是本校輸入成績的人力只有兩位，故可以符合我們的需求不會造成困擾，而且使用時相當容易，只要下一個SELECT指令，試卷碼即刻產生。

圖三是正式使用的第一次成績輸入作業，密碼左邊欄位即是試卷碼，目前已輸入兩筆成績，故試卷碼產生至2。

圖三

當然，假設成績輸入需要多位人力，為了避免過多輸入作業程式造成困擾，可改用一個非資料庫欄位當計數器製作試卷碼。

（四）應用SEQUENCE之成果：

試卷碼應用

考場碼及試卷碼產生並儲存後，即可運用於查榜作業（圖四）。

圖四中考生賴瑞文前來查榜時，憑其准考証號查詢出各科資訊，由此資訊得知國文在