シーケンス とトリガーを使った自動採番機能（オートナンバー）
CREATE TABLE のデフォルト設定を使用する 
Access などにあるオートナンバ機能を トリガー と SEQUENCE で実現する
AUTONUMBER_TEST テーブルに SEQUENCE を使いオートナンバー列 SEQ_NO を設定する
-- 作成 DDL
-- 発番用シーケンス
CREATE SEQUENCE RIVUS.AUTONUMBER_SEQ
/
-- テストテーブル
CREATE TABLE RIVUS.AUTONUMBER_TEST (
-- オートナンバー列
SEQ_NO NUMBER DEFAULT AUTONUMBER_SEQ.NEXTVAL PRIMARY KEY,
USR_REM VARCHAR2(10)
)
/
SQL> insert into autonumber_test (usr_rem) values ('あいう');
1行が作成されました。
SQL> select * from autonumber_test;
SEQ_NO USR_REM
---------- ------------------------------
1 あいう
Oracle 12c より前の場合
トリガーを使用して実装することができる。しかし、この方法はあまり勧められない方法ではある。
監査や作業ログのようなエンドユーザーやアプリケーションのプログラマからは一見、設定する意味や存在価値がないと
思われるようなカラムに対しては設置することで重宝がられるだろう。
あまりお勧めはしない理由は大きく２つ
パフォーマンス
SQL 内に簡単な１カラムを増やすことと割が合わないパフォーマンス。
トリガを発生させることで、本来は SQL エンジンだけの動作で済むものが、 SQL エンジン ⇒ PL/SQL エンジン ⇒ トリガーの実行 (DUAL 表へのアクセス(※)) となる。これは発生頻度、件数との相談。
また、このトリガー は ダイレクト・パス・インサート とは共存できない。これは一部のアプリケーションによっては譲歩し難い大きなデメリットになる。
(※) Oracle 11g では PL/SQL から直接シーケンスオブジェクトにアクセスできる。
データ整合性
トリガーの設置はインポートとの相性も考慮しておく必要がある。既存環境に IGNORE=Y のモードで インポート を行うとトリガーが起動する。このときオートナンバ列にリレーションを設置している場合には細心の注意を払う必要がある。
あるテーブルの全てのトリガーを一時的に無効化したい場合には
ALTER TABLE table_name {ENABLE|DISABLE} ALL TRIGGERS ;
のようにトリガーを無効化しておくと起動しなくなる。
このように使用するテーブルやカラム対象と件数を事前に考慮して設置する必要があり、多用は運用時になって困ることになる。
シーケンスとトリガーによる実装方法
AUTONUMBER_TEST テーブルに SEQUENCE を使いオートナンバー列 SEQ_NO をエミュレートする
-- 作成 DDL
-- テストテーブル
CREATE TABLE RIVUS.AUTONUMBER_TEST (
SEQ_NO NUMBER PRIMARY KEY, -- オートナンバー列
USR_REM VARCHAR2(10)
)
/
-- 発番用シーケンス
CREATE SEQUENCE RIVUS.AUTONUMBER_SEQ
/
-- 発番用トリガー
CREATE OR REPLACE TRIGGER RIVUS.AUTONUMBER_NUMBERING
BEFORE INSERT ON AUTONUMBER_TEST
FOR EACH ROW
DECLARE
eIKENAIYO EXCEPTION;
-- (オプション) 例外の付け替え DUP_VAL_ON_INDEX
PRAGMA EXCEPTION_INIT(eIKENAIYO, -1);
BEGIN
IF (:NEW.SEQ_NO IS NOT NULL) THEN
RAISE eIKENAIYO;
END IF;
-- Oracle 11g 以降
NEW.SEQ_NO := AUTONUMBER_SEQ.NEXTVAL ;
-- Oracle 11g より前
-- SELECT AUTONUMBER_SEQ.NEXTVAL INTO :NEW.SEQ_NO FROM DUAL ;
END;
/
発番された番号を取得する
発番されたSEQ_NO を RETURNING で INSERT 時に取得する (PL/SQL)
DECLARE
vCurrNo NUMBER;
BEGIN
INSERT INTO AUTONUMBER_TEST (USR_REM) VALUES ('あいう')
RETURNING SEQ_NO INTO vCurrNo;
-- イケナイ例
-- INSERT INTO AUTONUMBER_TEST (SEQ_NO, USR_REM) VALUES ( 1, 'あいう')
-- RETURNING SEQ_NO INTO vCurrNo;
--
COMMIT;
DBMS_OUTPUT.PUT_LINE('採番された番号は ' || vCurrNo);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('それはイケナイ');
ROLLBACK;
END;
/
...
採番された番号は 1
