在 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： 完成這兩步，我們就已經有了 INCREMENT (遞增)的功能了！那，怎麼做到 AUTO (自動)呢？是的，就是用 Trigger！ Step3. 建立 Trigger：
- -- 新增紀錄
- 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_INCREMENT 都具備了，大功告成了！
- 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;
但是，如果就這樣結束，那本篇介紹的，不過是台兩光的三輪拼裝車。 這種欄位，有極大機率 在實作情況下，需要即時取回剛剛新增那筆紀錄的 id 資料。在 MySQL 中有個 LAST_INSERT_ID() 來取得同一個連線 Session 中 AUTO_INCREMENT 所得到的 ID。那 Oracle 怎麼實作這功能？直接跟 Sequence 要它當時的值？如果在多人存取的環境下，這樣的做法能保證跟 Sequence 要到的那個數字，是你的還是後面又有人塞資料時候拿到的！？ INSERT ... RETURNING ... 是我們要的解答：
- -- 新增紀錄
- 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
- */
這做法雖然是輛拼裝車的，但是還不是太難使，倒也堪用。 不過，如果有機會能官方內建，總比建這建那拼拼湊湊的好！ 難道，像人生一樣，就是要有那麼點遺憾，才是人生嗎？XD 原文出處: http://abu.tw/2008/06/oracle-autoincrement.html
- 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;
