在 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