【Oracle】シーケンス（SEQUENCE）完全ガイド｜CREATE SEQUENCE・NEXTVAL・CURRVAL・CACHE・CYCLE・Oracle 12c 以降の IDENTITY 列まで解説

-- 最もシンプルな作成（すべてデフォルト値）
CREATE SEQUENCE order_id_seq;
-- INCREMENT BY 1（1ずつ増加）
-- START WITH 1（1から開始）
-- MAXVALUE 9999999999999999999999999999（最大値）
-- MINVALUE 1（最小値）
-- NOCYCLE（最大値に達したら停止）
-- CACHE 20（20個の値をメモリに事前生成）
-- NOORDER（RAC 環境でも発行順序を保証しない）

-- よくある主キー用のシーケンス設定
CREATE SEQUENCE order_id_seq
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 999999999      -- 明示的に最大値を設定（省略可）
    NOCYCLE                 -- 最大値に達したらエラー（推奨）
    CACHE 100               -- パフォーマンスのため 100 個キャッシュ
    NOORDER;                -- 発行順序を保証しない（シングルノードでは関係ない）

-- 逆順（デクリメント）シーケンス
CREATE SEQUENCE priority_seq
    START WITH 100
    INCREMENT BY -1
    MAXVALUE 100
    MINVALUE 1
    NOCYCLE
    NOCACHE;

-- CYCLE: 最大値に達したら最小値から繰り返す
CREATE SEQUENCE daily_seq
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 9999
    CYCLE                   -- 9999 の次は MINVALUE(1) から再開
    CACHE 50;

-- NEXTVAL: 次の値を取得する（取得するたびに値が進む）
SELECT order_id_seq.NEXTVAL FROM DUAL;  -- 1
SELECT order_id_seq.NEXTVAL FROM DUAL;  -- 2

-- CURRVAL: 現在のセッションで最後に取得した NEXTVAL の値を返す
-- ※ 同一セッションで NEXTVAL を少なくとも1回呼び出した後でないと使えない
SELECT order_id_seq.CURRVAL FROM DUAL;  -- 2（直前の NEXTVAL と同じ）

-- INSERT で NEXTVAL を使う（最もよくある使い方）
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_id_seq.NEXTVAL, 100, SYSDATE);

-- 親子テーブルに同じ NEXTVAL を使う（CURRVAL を活用）
INSERT INTO orders (order_id, customer_id) VALUES (order_id_seq.NEXTVAL, 100);
INSERT INTO order_items (order_id, item_id, product_id, qty)
VALUES (order_id_seq.CURRVAL, 1, 5001, 3);   -- CURRVAL で親の order_id を再利用

-- DEFAULT 値に NEXTVAL を使う（Oracle 12c 以降）
CREATE TABLE products (
    product_id  NUMBER DEFAULT product_id_seq.NEXTVAL PRIMARY KEY,
    name        VARCHAR2(100) NOT NULL
);
INSERT INTO products (name) VALUES ('Widget');  -- product_id に自動採番

-- NEXTVAL が使える場所
-- ① SELECT ... FROM DUAL（単純な取得）
-- ② INSERT の VALUES 句
-- ③ SELECT リスト（INSERT INTO ... SELECT でも可）
-- ④ DEFAULT 値（12c 以降）

-- NEXTVAL が使えない場所
-- ① WHERE 句
-- ② GROUP BY / ORDER BY / HAVING 句
-- ③ DEFAULT 値（11g 以前）
-- ④ サブクエリの SELECT リスト（一部制限あり）
-- ⑤ CHECK 制約

-- ギャップが発生する主なパターン:
-- ① INSERT を ROLLBACK した場合（NEXTVAL で取得した値は戻らない）
BEGIN
    INSERT INTO orders (order_id, ...) VALUES (order_id_seq.NEXTVAL, ...);
    -- 何らかのエラーで ROLLBACK
    ROLLBACK;  -- order_id の値は失われる（次の NEXTVAL は連続していない）
END;
/

-- ② インスタンスの再起動（キャッシュされた値が失われる）
-- CACHE 100 に設定している場合、再起動ごとに最大 100 個の欠番が発生する可能性がある

-- ③ INSERT ALL でのシーケンス（INSERT ALL では NEXTVAL が1回だけ評価される・同じ値になる）
-- NG: INSERT ALL で NEXTVAL を使うと全行同じ値になりユニーク制約違反になる
-- → INSERT ALL ではシーケンスを使わず、別途採番してからデータを挿入する

-- NOCACHE: ギャップを最小化する（ただし毎回ディスク I/O が発生し低速）
CREATE SEQUENCE no_gap_seq
    START WITH 1 INCREMENT BY 1
    NOCACHE;   -- キャッシュなし。再起動でもギャップが発生しにくいが遅い

-- ORDER: RAC 環境で複数ノードをまたいでも採番順序を保証する
-- （シングルノードでは NOORDER で問題ない）
CREATE SEQUENCE ordered_seq
    START WITH 1 INCREMENT BY 1
    CACHE 50 ORDER;   -- RAC で順序保証が必要な場合

-- シーケンスの現在値を確認する
SELECT sequence_name, last_number, cache_size, increment_by,
       cycle_flag, order_flag
FROM USER_SEQUENCES
WHERE sequence_name = 'ORDER_ID_SEQ';
-- last_number: 次に払い出す値（CACHE 分だけ先読みしているため実際より大きい場合がある）

-- CACHE サイズを変更する
ALTER SEQUENCE order_id_seq CACHE 200;

-- MAXVALUE を変更する
ALTER SEQUENCE order_id_seq MAXVALUE 9999999999;

-- INCREMENT BY を変更する
ALTER SEQUENCE order_id_seq INCREMENT BY 1;

-- シーケンスをリセットする（値を初期値に戻す）
-- Oracle 18c 以降: ALTER SEQUENCE ... RESTART が使える
ALTER SEQUENCE order_id_seq RESTART;         -- START WITH 値に戻す
ALTER SEQUENCE order_id_seq RESTART START WITH 1;  -- 指定した値に戻す（18c 以降）

-- 18c 未満: 一度 DROP して再作成する（最もシンプル）
DROP SEQUENCE order_id_seq;
CREATE SEQUENCE order_id_seq START WITH 1 INCREMENT BY 1 CACHE 100;

-- 18c 未満の代替: 現在値を取得してから大きな INCREMENT BY で一気に進める
-- → 現在値を確認して、目的の値までジャンプさせる手法
-- ①現在の last_number を確認: SELECT last_number FROM USER_SEQUENCES WHERE sequence_name='ORDER_ID_SEQ';
-- ②INCREMENT BY を (目的値 - last_number) に設定して NEXTVAL を1回取得
ALTER SEQUENCE order_id_seq INCREMENT BY -100;   -- 100 戻す例
SELECT order_id_seq.NEXTVAL FROM DUAL;            -- 値を移動
ALTER SEQUENCE order_id_seq INCREMENT BY 1;       -- 元の増分に戻す

-- GENERATED ALWAYS AS IDENTITY: 常に自動採番（INSERT で値を指定するとエラー）
CREATE TABLE orders_v2 (
    order_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date  DATE DEFAULT SYSDATE
);

INSERT INTO orders_v2 (customer_id) VALUES (100);     -- OK: order_id は自動採番
INSERT INTO orders_v2 (order_id, customer_id) VALUES (9999, 100); -- ORA-32795: エラー

-- GENERATED BY DEFAULT AS IDENTITY: デフォルトは自動採番だが INSERT で値を指定できる
CREATE TABLE orders_v3 (
    order_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id NUMBER NOT NULL
);

INSERT INTO orders_v3 (customer_id) VALUES (100);             -- OK: 自動採番
INSERT INTO orders_v3 (order_id, customer_id) VALUES (9999, 100); -- OK: 値を指定できる

-- GENERATED BY DEFAULT ON NULL AS IDENTITY: NULL 挿入時に自動採番する
CREATE TABLE orders_v4 (
    order_id    NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    customer_id NUMBER NOT NULL
);

-- IDENTITY 列のシーケンスオプションを指定する
CREATE TABLE orders_v5 (
    order_id NUMBER GENERATED ALWAYS AS IDENTITY (
        START WITH 1000
        INCREMENT BY 1
        CACHE 50
        NOCYCLE
    ) PRIMARY KEY,
    customer_id NUMBER
);

-- IDENTITY 列で内部的に作成されたシーケンスを確認する
SELECT table_name, column_name, identity_column, default_on_null
FROM USER_TAB_COLUMNS
WHERE identity_column = 'YES';