Oracle筆記（十二） 集合、序列

一、集合

在數學的操作之中存在交、差、並、補的概念，而在資料的查詢中也存在此概念，有如下幾個串連符號：

• UNION：串連兩個查詢，相同的部分不顯示；
• UNION ALL：串連兩個查詢，相同的部分顯示；
• INTERSECT：返回兩個查詢中的相同部分；
• MINUS：返回兩個查詢中的不同部分；

為了驗證以上的操作，下面建立一張只包含20部門僱員資訊的表：

CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20;

範例：驗證UNION

SELECT * FROM emp　　UNIONSELECT * FROM emp20;

範例：驗證UNION ALL

SELECT * FROM emp　　UNION ALLSELECT * FROM emp20;

範例：驗證INTERSECT

SELECT * FROM emp　　INTERSECTSELECT * FROM emp20;

範例：驗證MINUS

SELECT * FROM emp　　MINUSSELECT * FROM emp20;

在之前講解分組的時候曾經留過一道未完成的題目：所有領取獎金的人求出平均工資，所有不領取獎金的人求出平均工資，當時的實現代碼如下：

SELECT comm,AVG(sal)FROM empGROUP BY comm;

這種問題下只能依靠查詢的串連操作，準備兩個查詢：

• 第一個查詢負責查詢出所有領取獎金的僱員平均工資；
• 第二個查詢負責查詢出所有不領取獎金的僱員平均工資；

SELECT 'UNCOMM',AVG(sal) FROM emp WHERE comm IS NULL　　UNIONSELECT 'COMM',AVG(sal) FROM emp WHERE comm IS NOT NULL;

對於這種串連查詢，只需要清楚其概念即可。

二、序列

在許多的資料表之中都存在一種稱為自動成長列的操作，但是在Oracle之中，這種自動成長列並不是自動控制的，而是需要使用者手工的控制，這樣做主要是為了開發方便，建立序列的文法如下：

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

範例：建立序列

CREATE SEQUENCE myseq;

當一個序列建立完成之後 ，可以通過以下兩種方式訪問序列：

• 序列名稱.nextval：讓序列增長到下一個內容；
• 序列名稱.currval：取得當前序列的內容；

範例：驗證序列的操作

SELECT myseq.currval FROM dual;

可是直接執行上面的程式會發出如下的錯誤提示：“ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此會話中定義”

在Oracle之中如果要想操作currval，則首先必須使用nextval；

SELECT myseq.nextval FROM dual;SELECT myseq.currval FROM dual;

序列一般都作為主鍵使用，例如，下面定義一張表：

DROP TABLE mytab PURGE;CREATE TABLE mytab (    id NUMBER PRIMARY KEY,    name VARCHAR2(20) NOT NULL);

現在向mytab表中增加資料：

INSERT INTO mytab(id,name) VALUES(myseq.nextval,'姓名');

一定要記住，這個過程都是由使用者自己手工進行的，不能自動完成。

在預設情況下，序列從0開始，每次增長1，那麼現在也可以修改；

範例：建立序列，從10開始，每次增長2

DROP SEQUENCE myseq;CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;

範例：希望定義一個序列，這個序列可以在1、3、5、7、9之間迴圈出現；

DROP SEQUENCE myseq;CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 1 MAXVALUE 10 MINVALUE 1 CYCLE NOCACHE;

關於序列中的CACHE解釋：

      在Oracle資料庫之中，由於序列被經常使用到，所以Oracle為了攬效能，將序列的操作形式做了如下的處理。

      準備了一塊空間，這個空間之中，為使用者準備好了若干個已經產生好的序列，每次操作的時候都是從這塊空間之中取出序列的內容，但是這樣有一個問題，如果現在資料庫的執行個體關閉了，那麼儲存在這塊空間中的內容就有可能消失了，但是雖然消失了，可是資料庫已經增長好了，這樣就會出現跳號的事情，而如果要想取消掉這種問題，則最好的方式是將序列設定為不緩衝，使用NOCACHE聲明。