2008-06-24

在 Oracle 中實作自動遞增欄位 (Oracle AUTO_INCREMENT)

阿布洛格 Oracle在 MySQL 的資料庫中,有一種方便的欄位型態 AUTO_INCREMENT,有自動遞增的功能,多半使用於 ID 欄位,作為 PRIMARY KEY。類似自動遞增的功能,在 MSSQL 有 Identity,甚至連 MS Access 都有個 AutoNumber 都可以很方便的去宣告使用。

人沒有完美的,資料庫同樣也是。功能強大的 Oracle 卻沒有內建這樣方便的功能欄位,從我開始接觸 Oracle 以來,一直覺得遺憾與不解的,幾年下來,看起來原廠是沒去 implement 這樣的功能欄位的打算,是令人有些扼脕。但是,我們還是有機會用拼拼湊湊的方式,搞一個那樣子的欄位出來。

Step1. 建立 Table:
CREATE TABLE tbl (
id  NUMBER PRIMARY KEY,
val VARCHAR2(30)
);


Step2. 建立 Sequence:
CREATE SEQUENCE tbl_id_seq;

完成這兩步,我們就已經有了 INCREMENT (遞增)的功能了!
-- 新增紀錄
INSERT INTO tbl (id, val) VALUES (tbl_id_seq.nextval, 'row1_value');
INSERT INTO tbl (id, val) VALUES (tbl_id_seq.nextval, 'row2_value');

-- 撈出資料
SELECT * FROM tbl;
/**
輸出結果為:
ID VAL
---------- ------------------------------
1 row1_value
2 row2_value
*/

那,怎麼做到 AUTO (自動)呢?是的,就是用 Trigger!

Step3. 建立 Trigger:
CREATE OR REPLACE TRIGGER tbl_trg
BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
IF :new.id IS NULL THEN
SELECT tbl_id_seq.nextval INTO :new.id FROM dual;
END IF;
END;


這樣,AUTO_INCREMENT 都具備了,大功告成了!
-- 新增紀錄
INSERT INTO tbl (val) VALUES ('row3_value');
INSERT INTO tbl (id, val) VALUES (null, 'row4_value');

-- 撈出資料
SELECT * FROM tbl;
/**
輸出結果為:
ID VAL
---------- ------------------------------
1 row1_value
2 row2_value
3 row3_value
4 row4_value
*/

但是,如果就這樣結束,那本篇介紹的,不過是台兩光的三輪拼裝車。

這種欄位,有極大機率在實作情況下,需要即時取回剛剛新增那筆紀錄的 id 資料。在 MySQL 中有個 LAST_INSERT_ID() 來取得同一個連線 Session 中 AUTO_INCREMENT 所得到的 ID。那 Oracle 怎麼實作這功能?直接跟 Sequence 要它當時的值?如果在多人存取的環境下,這樣的做法能保證跟 Sequence 要到的那個數字,是你的還是後面又有人塞資料時候拿到的!?

INSERT ... RETURNING ... 是我們要的解答:
DECLARE
i NUMBER;
BEGIN
INSERT INTO tbl (val) VALUES ('row5_value') RETURNING id INTO i;
DBMS_OUTPUT.put_line('id : ' || i);

FOR rec IN (SELECT id, val FROM tbl)  
LOOP  
DBMS_OUTPUT.put_line('rec.id = '  || rec.id ||  
'; rec.val = ' || rec.val);  
END LOOP; 
/**
輸出結果為:
id : 5
rec.id = 1; rec.val = row1_value
rec.id = 2; rec.val = row2_value
rec.id = 3; rec.val = row3_value
rec.id = 4; rec.val = row4_value
rec.id = 5; rec.val = row5_value
*/

INSERT INTO tbl (id, val) VALUES (NULL, 'row6_value') RETURNING id INTO i;
DBMS_OUTPUT.put_line('id : ' || i);
FOR rec IN (SELECT id, val FROM tbl)  
LOOP  
DBMS_OUTPUT.put_line('rec.id = ' || rec.id || '; rec.val = ' || rec.val);  
END LOOP; 
/**
輸出結果為:
id : 6
rec.id = 1; rec.val = row1_value
rec.id = 2; rec.val = row2_value
rec.id = 3; rec.val = row3_value
rec.id = 4; rec.val = row4_value
rec.id = 5; rec.val = row5_value
rec.id = 6; rec.val = row6_value
*/
END;


這做法雖然是輛拼裝車的,但是還不是太難使,倒也堪用。
不過,如果有機會能官方內建,總比建這建那拼拼湊湊的好!
難道,像人生一樣,就是要有那麼點遺憾,才是人生嗎?XD


2019.12.25 UPDATE

Oracle 總算在 2013 年發表的 Oracle 12c 起,開始支援 AUTO_INCREMENT 欄位,而且支援了兩種做法。

方法一. 使用 IDENTITY:
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]


在建立 Table 時,簡單使用下述語法即可輕鬆建立 AUTO_INCREMENT 欄位:
CREATE TABLE tbl (
id  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
val VARCHAR2(30)
);

可以指定起始值(starting value)與步進值(increment value):
CREATE TABLE tbl (
id  NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
val VARCHAR2(30)
);



方法二. 使用 Sequence:
CREATE TABLE tbl (
id  NUMBER(10) DEFAULT tbl_id_seq.nextval NOT NULL,
val VARCHAR2(30)
);



是不是簡單太多了呢?

8 回應 :

匿名

用Oracle 的 Sequences 就可以啦..

要遞增或遞減都可以, 不用這麼麻煩...

To 樓上匿名的先生/小姐:
等你有能力看懂我上頭寫的是啥了再來指教比較不會鬧笑話…

匿名

受益良多, 尤其是RETURNING的使用,
如此我就可以用OCI(Oracle Call Interface)實作
MySQL的LAST_INSERT_ID功能了

匿名先生/小姐:
再與您分享,如果 DB 環境允許,包成 Package/Procedure 來叫用,用 out 參數把 id return 出來,即便是在 AP 僅能或僅想用 OCI 介面實作的時候,也可以靈活應用 Oracle 的相關功能與實作,而 AP 也不致於因為包含了太多邏輯而造成閱讀與維護上的困擾。

不知道為啥,看到 OCI 我就聯想到直接把一些邏輯用的 SQL 寫在 AP 裡的景況,所以就回了這段…分享分享…希望是我多心了…請多海涵…^^"

匿名

感謝布萊恩的分享!
我的SQL都是很簡單的, 反而是OCI的使用讓我比較困擾,
和MySQL及SQLServer的API比起來, OCI顯得囉嗦許多.

一句簡單的SQL, 我要寫三個版本給MySQL,SQLServer和
Oracle用,再各自用C API去執行SQL.

貨比三家, Oracle最讓人頭大, 為了Oracle的AUTO_INCREMENT,
三千煩惱絲掉了一千五, 好險在您這兒找到了RETURNING,
餘下的一千五還在我腦袋瓜上, 真是感謝您!

小弟在網路上的代號是IRON, 您就姑且這麼呼我吧

匿名

請問我現在遇到一個問題,我在設好自動遞增好,該張table在insert時的確會自動遞增,但問題來了,他把我所有insert時沒填值(null)的欄位全都自動遞增一個號碼了。但其實,我只想遞增PK欄位(ID →流水號)。有辦法限定只遞增某個欄位嗎?

匿名

CREATE OR REPLACE TRIGGER trg_PM_FORM_DEFINE
BEFORE INSERT ON PM_FORM_DEFINE
FOR EACH ROW
BEGIN
IF :new.PM_FORM_ID IS NULL THEN
SELECT SQ_PM_FORM_ID.nextval INTO :new.PM_FORM_ID FROM dual;
END IF;
END;
/
這是我的squence

匿名先生/小姐:
以這個 trigger 的語法,應該不致於發生所有 NULL 欄位都被填入 sq_pm_form_id.NEXTVAL 的情況。因為它就指定取得的 sq_pm_form_id.NEXTVAL assign 給 NULL 狀態的 pm_form_define.pm_form_id。

可以 show 你的 table DDL 跟 INSERT 的 DML 給我看嗎?(敏感欄位/資料請逕自移除, 給我看有問題的部份即可)
or 你有足夠的權限,就先搞個小 table 自行測試一下它的行為也是釐清問題不錯的方式。

張貼留言

讓阿布知道你對這篇文章的想法吧!