[Oracle] Oracle 產生自動編號方式

[kapar]

Oracle 沒有類似 MS-SQL 可以直接修改欄位屬性，設定成自動編號欄位，所以我們必須透過 Sequence 物件的 nextval 方法，取得其下一個值，然後將此值新增至 TABLE 中，製造出有自動編號的效果。

方法

Code:

Sequence 物件的語法：
CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

//建立 Table
Create Table MarsTest(
  ID_ NUMBER(10,0) NOT NULL,
  Content VARCHAR2(250)
);

//建立 Sequence
1.使用預設值
Create Sequence Seq_MarsTest;

2.使用自訂
Create Sequence Seq_MarsTest
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Code:

//新增資料
INSERT INTO?MarsTest (ID_, Content)
VALUES (Seq_MarsTest.NEXTVAL, 'MarsTest');

從上面的例子，我們也可以發現到，我們是在 INSERT 時，才將 Sequence 與 Table 產生關係，所以 Sequence 不只是提供給特定 Table 使用，也能給其他任一個 Table 共用。

[kapar]

Sequence Object 建立方式 :

Code:

        Create Sequence SequenceObjectName

                MINVALUE 1

                MAXVALUE 99999999

                INCREMENT BY 1

                START WITH 1

                NOCACHE

                CYCLE;

參數說明如下 :

    SequenceObjectName : 循序物件名稱

        MinValue : 最小值

        MaxValue : 最大值

        Increment By : 每次增加

        Start With : 從多少開始

        Nocache : 不快取

        Cycle : 當取至最大值後, 是否循環再由最小值開始

 
取用一組新的值 :
       

Code:

SELECT 循序物件名稱.NEXTVAL FROM DUAL

得知目前使用到幾號 :
       

Code:

SELECT 循序物件名稱.CURRVAL FROM DUAL

[kapar]

另一個詳細說明:

關於oracle自動編號

在access中有自動編號的資料類型，MSSQL和MYSQL也都有自動增長的資料類型，插入記錄時不用操作此字段，會自動獲得資料值，而oracle 沒有自動增長的資料類型，我們需要建立一個自動增長的序列號，插入記錄時要把序列號的下一個值賦於此字段，可以預見的是，有此功能，我們可以把資料從 ACCESS、MSSQL或MYSQL遷移到oracle了！

Code:

create sequence type_id increment by 1 start with 1;

這句中，type_id為序列號的名稱，每次增長為1，起始序號為1。如果要刪除序列，用drop sequence 序列名就可以了！！
序列可以保證多個用戶對同一張表進行操作時產生唯一的整數,利用序列可以自動產生主關鍵字,序列只存在於資料庫字典中.

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE |NOCYCLE}]
[{CACHE n|NOCACHE}];

說明:

INCREMENT BY--指定插入的數值
START WITH--指定初始值
MAXVALUE--定義序列產生的最大編號.預設的MAXVALUE就是NOMAXVALUE,對於遞增序列為10^27,對於遞減序列為-1
MINVALUE--定義序列的最小編號,預設的MINVALUE為NOMINVALUE,對於遞增序列為1,遞減序列為-10^26.
CYCLE--配置序列在達到界限值時重複編號
NOCYCLE--達到界限值時不重複編號,這是預設值,當你試圖產生MAXVALUE+1時將返回異常.
CACHE--定義在暫存中保留的序列編號塊的大小,預設值為20.
NOCACHE--強制資料庫詞典對於產生的每個序列編號進行更新,保證在產生的編號中沒有空缺,但這樣會降低性能.

產生一個序列

Code:

CREATE SEQUENCE dept_deptid_seq
INCREAMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;

//如果是用來產生主鍵值的話,不要用CYCLE選項,而且命名序列時最好能表現它的潛在用途以便於理解.

確認序列

Code:

SELECT sequence_name,min_value,max_value,increament_by,last_number
FROM user_sequences;

//如果你指定了NOCACHE選項,那麼LAST_NUMBER列將顯示下一可用的序列號.

使用NEXTVAL可以取得序列中的下一個編號,但問題常常出現在對話初始序列之前查詢其當前序列號CURRVAL

Code:

CREATE SEQUENCE emp_seq
NOMAXVALUE
NOCYCLE;

然後查詢

Code:

SELECT emp_seq.currval
FROM dual;

將返回錯誤,問題就在於你試圖引用CURRVAL之前,在你的對話中並沒有使用NEXTVAL先初始化此序列.

Code:

SELECT emp_seq.nextval
FROM dual;

這樣再查詢CURRVAL就不會出錯了.

使用序列

Code:

INSERT INTO departments(department_id,department_name,location_id)
VALUES (dept_deptid_seq.NEXTVAL,'Support',2500);

對序列進行緩衝存儲可以提高性能,因為這樣就不必對每個產生的編號都更新資料庫字典表,只需要對每一組編號進行更新即可.這樣,在我們查詢NEXTVAL時就直接從緩衝中提取,速度將快很多,但是進行序列緩衝帶來的負面影響就是當資料庫被回復時,比如說系統崩潰,手動ROLLBACK資料時,在緩衝中存儲的序列值將會丟失,這也就是為什麼會出現空缺(GAPS),如果產生序列時指定的是NOCACHE,那麼可以在USER_SEQUENCES表裡查詢下一個可用的序列號值,這個查詢並不會產生增加序列值的動作.

修改序列

Code:

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;

規則:
>必須為序列的所有者或者擁有ALTER特權
>修改對於以後的序列號生效
>序列必須是被刪除然後重新產生(使所有相關的對象失效,並且失去相應的關聯)
>修改時還要滿足些其他的驗證條件,比如說新的MAXVALUE不可以比現在的序列號低

刪除序列

Code:

DROP SEQUENCE dept_deptid_seq;

>必須要時序列的所有者或者有DROP ANY SEQUENCE的權限

索引
索引是通過取得特定的行訊息而與預設的全表掃瞄相比大大提高系統性能的資料結構.可是顯式的手動建立,也可一由ORACLE自動產生,它們是與之索引的表相獨立的,就是說,可以在任何時候建立或者刪除索引而對基表或者其他索引無任何影響(當你刪除表時,相關的索引將會被刪除).

有兩種索引
>唯一索引--當你定義了一列含有主鍵或者唯一鍵約束時將自動產生一個唯一索引(可以手動建立,但是推薦由ORACLE自動建立)
>非唯一索引--當你手動為一個查詢中的連接建立一個外鍵索引來加速查詢速度時

建立索引

Code:

CREATE INDEX index
ON table (column[,column]...);

e.g.

CREATE INDEX emp_last_name_idx
ON employees(last_name);

何時使用索引
>當一個列包含的數值範圍較大時
>當一列包含大量空值時
>一個或者多個列經常一起在WHERE子句或者JOIN子句中使用時
>表非常大,但是大多數的查詢只要求檢索少於2-4個百分點的行記錄

何時不該使用索引
>表很小時
>列並不經常作為查詢中的條件使用時
>大多數查詢都檢索多於4個百分點的行記錄
>表經常被更新
>索引列被作為表達式的一部分引用

索引雖然可以很大程度提高檢索性能,但是越多的索引,意味著在DML操作之後ORACLE就將花越多的功夫去更新索引.所以,一定要適時使用,

確認索引
查詢資料庫字典視圖USER_INDEXES和USER_IND_COLUMNS可以檢索到索引的相關信息

Code:

SELECT ic.index_name,ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix,user_ind_columns ic
WHERE ic.inde_name=ix.index_name
AND ic.table_name='EMPLOYEES';

基於函數的索引

Code:

CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));

//但是如果想保證ORACLE使用索引而不是全表掃瞄就必須保證函數值非空值,就是需要加個WHERE子句指定非空值如

Code:

SELECT *
FROM employees
WHERE UPPER(last_name_ IS NOT NULL
ORDER BY UPPER(last_name);

如果沒有WHERE子句,則將會進行全表掃瞄非使用索引了.

刪除索引

Code:

DROP INDEX index;

//當你刪除一個表時,索引和約束將會自動刪除,但是視圖和序列將會保留.

同義詞
同義詞經常用來通過為一個本地或者遠程對象給定一個通用的名字來簡化SQL.同義詞可以指向一個表,視圖,序列,過程,函數或者本地中的資料庫物件,或者通過一個資料庫連接指向另一個資料庫中的對象.公共同義詞可供所有用戶使用,而專用同義詞則只能供其所有者或者獲得了相關授權的帳戶所有者使用.

比如說SCOTT擁有表EMP,所有用戶都使用自己的用戶名登入資料庫,並且必須引用該表,即SCOTT.EMP,如果我們為其產生一個同義詞EMP,每個對該表具有相關特權的人都可以簡單地在自己的SQL或者PL/SQL語句中以EMP的形式來引用它,不需要再指出所有者了.

Code:

CREATE [PUBLIC] SYNONYM synonym
FOR object;

e.g.
CREATE SYNONYM d_sum
FOR dept_sum_vu;

DROP SYNONYM d_sum;

CREATE PUBLIC SYNONYM dept
FOR alice.departments;

DROP PUBLIC SYNONYM dept;
//僅僅資料庫管理員可以刪除公共同義詞

關於Oracle的序列（Sequence）使用序列是一資料庫對象，利用它可產生唯一的整數。一般使用序列自動地產生主鍵值。對我們程式人員來講，精力時間有限，我們只學最有用的知識。大家請看：

1） 建立序列命令

CREATE SEQUENCE [user.]sequence_name
[increment by n]
[start with n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue];
[NOCYCLE]　 --
INCREMENT BY： 指定序列號之間的間隔，該值可為正的或負的整數，但不可為0。序列為升序。忽略該子句時，缺省值為1。
START WITH：指定產生的第一個序列號。在升序時，序列可從比最小值大的值開始，預設值為序列的最小值。對於降序，序列可由比最大值小的值開始，預設值為序列的最大值。

MAXVALUE：指定序列可產生的最大值。
NOMAXVALUE：為升序指定最大值為1027，為降序指定最大值為-1。
MINVALUE：指定序列的最小值。
NOMINVALUE：為升序指定最小值為1。為降序指定最小值為-1026。
NOCYCLE：一直累加，不循環

2） 更改序列命令

ALTERSEQUENCE [user.]sequence_name
[INCREMENT BY n]
[MAXVALUE n| NOMAXVALUE ]
[MINVALUE n | NOMINVALUE]；

修改序列可以：
    修改未來序列值的增量。
    設定或取消最小值或最大值。
    改變緩衝序列的數目。
    指定序列號是否是有序。

注意：
1，第一次NEXTVAL返回的是初始值
2，可以alter除start至以外的所有sequence參數.如果想要改變start值，必須 drop sequence 再 re-create .

3） 清除序列命令
DROP SEQUENCE [user.]sequence_name；
用於資料庫中刪除一序列。
4）牛刀小試
     4.1）建立一個序列號的語法：

Code:

-- Create sequence
create sequence NCME_QUESTION_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;

/////////////////////////////////////////////////////////////////////////////////////////
4.2）SQL中取序列號的用法：

Code:

SELECT NCME_QUESTION_SEQ.nextval FROM dual
SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual

注意：在使用序列的時候，有時需要有用戶名，就像這樣：

Code:

insert into system.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0, system.CONID.nextval);