Oracle 10G中關于序列使用詳解

資料庫設計的三大範式第一條就是獨立的表結構中必須有唯一主鍵來辨別表中資料.在以往微軟的SQL Server(duo版本)平台上.手動編碼實作表中主鍵.并設定為自增列是極其簡單.編碼如下:

--簡單主鍵定義如下并設定為自增(隻取出主鍵定義) 

 typeid int not null primary key identity(1,1), 

在Oracle中如果要設定一個獨立表結構的主鍵為自增.其中就涉及到了 Oracle 10G中關于序列(Sequence)的使用.

(A)Sequence-序列的定義文法

SEquence-序列是一個資料庫項.它生成一個整數序列..Oracle中序列所生成的整數通常可以用來填充獨立表結構中數字類型的主鍵列.來實作同SQL Server自增效果. 但是序列的定義同SQL Server下定義差別很大.在Oracle 10G官方開發手冊中顯得有些複雜,先來看看定義序列的(精簡後,如有詳細請參考官方文檔.)文法:

--定義序列完整文法

Create Sequence Sequence_Name

[Start With start_num]--自增開始初始值

[Increment by increment_num]--每次增量 

[{MaxValue maximum_num | NoMaxValue}]

[{MinValue minimum_num | NoMinValue}]

[{Cycle | NoCycle}]

[{Cache cache_num | NoCache}]

[{Order | NoOrder}];

文法定義說明:

Sequence_Name:定義序列的名稱[有意義的命名].

Start With (start_num):用來定義序列的初始值.可選. 系統預設值為1.

Increment By(increment_num):指定序列每次自增的增量. 可選. 系統預設值同樣為1.

MaxValue(maximum_num):設定序列自增最大上限整數值.maximum_num必須大于或等于start_num即初始值.同時maximum_num必須大于序列最小下限值minimum_num.[保證有意以].

NoMaxValue:是系統對序列設定的預設值. 即指定升序序列的最大值為10的27次方.降序序列的最大值為-1. NoMaxValue為系統預設值. 同理MinValue.

MinValue(minimum_num):設定序列自增最小下限整數值. ,minmum_num必須小于或等于start_num即初始值. 而且minimum_num必須小于maximum_num.同理.

NoMinvalue:即使序列自增下限的預設值. 升序序列的最小值為1, 降序序列最小為負的10的26次方. NoMinValue為預設值.

Cycle:指定當序列即使已經達到序列自增的最大值或最小值時也繼續生成整數. 當升序序列達到最大值時. 下一個生成的值為最小值即初始值. 當降序序列達到最小值時. 下一個生成的值最大值. 以這種規則來循環.

NoCycle:正好反之:指定到序列自增到最大值或最小值時就不能再生成整數了. NoCycle是預設值.

Cache(cache_num):指定要保留在記憶體中整數的個數.預設緩存的格式為20個. 可以緩存的整數最少為2個. 可以緩存的整數個數最多為:Cell(maximum_num—minimum_num)/ABS(increment_num). 算法即:Cell(序列的最大上限值—最小下限值,)/ABS(每次自增的增量).

NoCache:指定不适用緩存整數資料.【個人建議不适用緩存來存儲資料. 當資料庫連接配接斷開後.自動清空記憶體資料, 導緻後面插入資料序列值不連續.效果  建議在建立不适用Cache存儲 後有詳解】.

Order:確定按照請求次序生成整數 【不常用的設定】.隻有在使用Real Application Cluster(RAC)時才可以使用Order選項設定.

Noorder:就是(以上)反之.(不在贅述) NoOrder為系統預設值.

詳細了解sequence序列的詳細文法定義. 定義一個簡單的Sequence序列.并使用在表中主鍵列中實作自增.代碼如下:

1 --建立序列

2 create sequence product_sequence

3 start with 1 --初始值預設為1

4 increment by 1--每次增量預設為1

5 maxvalue 10 --增量的最大上限

6 minvalue 1--增量的最小底限

7 cycle

8 nocache ;

(B)Use Sequence-使用序列

 序列生成的是一系列整數數字.一個序列中包含兩個"僞列" ，分别為"Currval"和"Nextval",可以分别用來擷取該序列的目前值和下一個值.

雖然我們在定義時指定序列product_sequence初始值為1但并沒有真正初始化該值. 當在檢索序列的目前值前,必須通過檢索序列的下一個值即Nextval來對序列進行初始化操作.在選擇了product_sequence.Nextval時,該序列就被初始化為1.如下為執行個體.

1 --初始化序列

 2 select product_sequence.nextval from ProductType

 3 

 4 --ProductType為表明.在初始化前 必須先建立表ProductType 如下:

 5 create table Producttype

 6 (

 7   typeid integer constraint producttype_pk primary key,

 8   typename varchar(120) default'CastChen Test Date!' not null,

 9   createdate date  default sysdate not null

10 );  

執行查詢結果如下:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/111441566.gif"></a>

上述為第一次執行後. 序列中初始化的值為定義時1. 如果在定義時沒有指定初始值. 預設值為1. 成功初始化後我們來獲得目前序列的值.

1 --擷取序列目前值

2 select product_sequence.currval from ProductType 

查詢結果如下:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/111448199.gif"></a>

當查詢Currval時,Nextval保持不變.而nextval隻有在再次查詢nextval以獲得下一個值是才會改變. 下面做一個執行個體. 注意nextval和Currval先後順序.

1 --同時檢視兩個僞列的值. 注意Nextval 在前 Currval在後.

2 select product_sequence.nextval, product_sequence.currval from ProductType 

<a target="_blank" href="http://blog.51cto.com/attachment/201201/111454983.gif"></a>

當查詢兩個值時Nextval在前. 前面提到再次查詢Nextval可以獲得序列的下一個值. 是以此處序列的目前值Currval為2.其實可以了解這樣這個過程: Nextval其實通過查詢來向序列指派的,而指派對象就是Currval. Currval同時又是對外通路序列值的唯一視窗.product_sequence.currval 就直接取到了序列的目前值.同理我們可以把product_sequence.currval放在主鍵指派, 而不用手動每次定義.  是否又再一次接近我們目的.

注意我們在定義使用了Cycle選項:即當序列自增到最大值或最小值時産生循環效果. 我們來看下執行個體. 

--一直執行指派語句 直到達到序列上限的最大值10

select product_sequence.nextval from ProductType

執行最後結果:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/111501333.gif"></a>

當我們再次執行指派語句時 結果:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/111506839.gif"></a>

序列的值有最大上限10 又再次恢複到初始值1,實作一次循環. 如果再次執行其實同第一次執行時一樣的,Cycle實作序列在一個特定範圍内循環定義使用.

(C)使用序列填充主鍵-(核心内容)

上面做的都是準備工作, 我們要的最後結果即是在主鍵列中通過序列來自動指派.當然前提表的主鍵定義時資料類型必須為整數. 在這裡我還要再次提一下Cache這個選項.在使用序列填充主鍵時,常用設定為NoCache即采取預設方式. 當關閉資料庫連接配接時所緩存的值會全部丢失. 導緻主鍵産生的數值不連續的現象. 使用序列來填充主鍵辨別:

1 --插入資料 時 序列值代替主鍵值定義

2 insert into ProductType(typeid,Typename,Createdate) 

3 values(product_sequence.nextval,'使用了序列定義主鍵',default) 

這種方式不必每次添加資料時檢視主鍵的數值. 害怕插入重複資料. 而主鍵列的指派工作完全交給序列來做. 我隻需把精力投放在其他地方.

上面操作基本實作我們當初預想. 但我也想提出一個問題:

在微軟的SQL Server平台上定義獨立表結構的主鍵時. 可以同時指定多個列共同辨別為該表的主鍵. 即如果多個合并在一起比對實作主鍵唯一辨別. 而Oracle 10G中序列其實就分離這種關系. 兩者之間互相獨立. 也就是說主鍵的定義和主鍵指派 是沒有關-系的. 完全分開的. 序列隻是負責獨立的對主鍵進行指派. 而至于主鍵限制的定義 沒有關系.

(D)修改和删除序列

可以通過Alert  Sequence子句來修改序列, 但是我在修改序列中常常會報錯 而且很頻繁.修改序列内容有如下限制:

(1)不能修改序列的初始值

(2)序列的最小值不能大于目前值

(3)序列的最大值不能小于目前值

修改序列的增量:

1 --修改序列的增量為2

2 alert sequence product_sequence increment by 2;

再次查詢資料結果将以每次增量為2 增加. 如果在不使用情況下删除序列:

1 --删除序列 極其簡單.

2 drop  sequence product_sequence;

當我們添加多張表表中序列時, 我們可以同查詢系統中User_sequences表來擷取相關表的關于序列的詳細資訊. User_sequences是字典表的一部分.

至此以上是在Oracle 10G關于序列的全部用法.

本文轉自chenkaiunion 51CTO部落格，原文連結:http://blog.51cto.com/chenkai/765110