關於oracle自動編號

在access中有自動編號的資料類型，MSSQL和MYSQL也都有自動成長的資料類型，插入記錄時不用操作此欄位，會自動獲得資料值，而oracle沒有自動成長的資料類型，我們需要建立一個自動成長的序號，插入記錄時要把序號的下一個值賦於此欄位，可以預見的是，有此功能，我們可以把資料從ACCESS、MSSQL或MYSQL遷移到oracle了！
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--強制資料詞典對於產生的每個序列編號進行更新,保證在產生的編號中沒有空缺,但這樣會降低效能.

產生一個序列
CREATE SEQUENCE dept_deptid_seq
INCREAMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
//如果是用來產生主索引值的話,不要用CYCLE選項,而且命名序列時最好能體現它的潛在用途以便於理解.

確認序列
SELECT sequence_name,min_value,max_value,increament_by,last_number
FROM user_sequences;
//如果你指定了NOCACHE選項,那麼LAST_NUMBER列將顯示下一可用的序號.

使用NEXTVAL可以訪問序列中的下一個編號,但問題常常出現在會話初始序列之前查詢其當前序號CURRVAL
CREATE SEQUENCE emp_seq
NOMAXVALUE
NOCYCLE;
然後查詢
SELECT emp_seq.currval
FROM dual;
將返回錯誤,問題就在於你視圖引用CURRVAL之前,在你的會話中並沒有使用NEXTVAL先初始化此序列.
SELECT emp_seq.nextval
FROM dual;
這樣再查詢CURRVAL就不會出錯了.

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

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

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

刪除序列
DROP SEQUENCE dept_deptid_seq;
>必須要時序列的所有者或者有DROP ANY SEQUENCE的許可權

索引
索引是通過擷取特定的行資訊而與預設的全表掃描相比大大提高系統效能的資料結構.可是顯式的手動建立,也可一由ORACLE自動產生,它們是與之索引的表相獨立的,就是說,可以在任何時候建立或者刪除索引而對基表或者其他索引無任何影響(當你刪除表時,相關的索引將會被刪除).
有兩種索引
>唯一索引--當你定義了一列含有主鍵或者唯一鍵約束時將自動產生一個唯一索引(可以手動建立,但是推薦由ORACLE自動建立)
>非唯一索引--當你手動為一個查詢中的串連建立一個外鍵索引來加速查詢速度時

建立索引
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可以檢索到索引的相關資訊
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';

基於函數的索引
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
//但是如果想保證ORACLE使用索引而不是全表掃描就必須保證函數值非空,就是需要加個WHERE子句指定非空如
SELECT *
FROM employees
WHERE UPPER(last_name_ IS NOT NULL
ORDER BY UPPER(last_name);
如果沒有WHERE子句,則將會進行全表掃描非使用索引了.

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

同義字
同義字經常用來通過為一個本地或者遠程對象給定一個通用的名字來簡化SQL.同義字可以指向一個表,視圖,序列,過程,函數或者本機資料庫中的包,或者通過一個資料庫連接指向另一個資料庫中的對象.公用同義字可供所有使用者使用,而專用同義字則只能供其所有者或者獲得了相關授權的帳戶所有者使用.
比如說SCOTT擁有表EMP,所有使用者都使用自己的使用者名稱登陸資料庫,並且必須引用該表,即SCOTT.EMP,如果我們為其產生一個同義字EMP,每個對該表具有相關特權的人都可以簡單地在自己的SQL或者PL/SQL語句中以EMP的形式來引用它,不需要再指出所有者了.

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）建立一個序號的語句：
-- Create sequence
create sequence NCME_QUESTION_SEQ
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;

/////////////////////////////////////////////////////////////////////////////////////////
4.2）SQL中取序號的用法：
SELECT NCME_QUESTION_SEQ.nextval FROM dual
SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual
 
注意：在使用序列的時候，有時需要有使用者名稱，就像這樣：
insert into system.CONSERVATOR(CONSERVATORNAME,CONPASS,CONTRUENAME,CONSEX,CONID)values('JG','123456','000',0, system.CONID.nextval);