【Oracle 23ai】新機能・変更点完全ガイド｜BOOLEAN型・IF EXISTS・GROUP BYエイリアス・SQL Domains・VECTORまで実例で解説

-- Oracle 23ai: BOOLEAN 型が SQL 列として使用可能
CREATE TABLE products (
    product_id  NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    is_active   BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE
);

-- TRUE / FALSE / NULL を直接 INSERT できる
INSERT INTO products VALUES (1, 'Widget A', TRUE,  FALSE);
INSERT INTO products VALUES (2, 'Widget B', TRUE,  TRUE);
INSERT INTO products VALUES (3, 'Widget C', FALSE, FALSE);
INSERT INTO products VALUES (4, 'Widget D', TRUE,  NULL);  -- NULL も可
COMMIT;

-- WHERE 句でそのまま条件指定可能
SELECT product_id, name
FROM products
WHERE is_active;               -- WHERE is_active = TRUE と同義
-- 結果: id=1,2,4（is_active が TRUE のもの）

-- FALSE を指定
SELECT product_id, name
FROM products
WHERE NOT is_active;           -- is_active = FALSE

-- TO_CHAR で文字列変換（'TRUE'/'FALSE'/'')
SELECT name,
       TO_CHAR(is_active)  AS active_str,   -- 'TRUE' or 'FALSE' or ''（NULL）
       TO_CHAR(is_featured) AS featured_str
FROM products;

-- 数値・文字列からの変換
SELECT product_id,
       CASE WHEN is_active THEN 1 ELSE 0 END AS active_num,  -- 数値変換
       CASE WHEN is_active THEN 'Y' ELSE 'N' END AS active_char  -- 文字変換
FROM products;

-- 既存の NUMBER(1) / CHAR(1) データを BOOLEAN カラムに移行
ALTER TABLE legacy_table ADD is_active_bool BOOLEAN;
UPDATE legacy_table SET is_active_bool = (is_active_num = 1);  -- 0以外はTRUE

-- Oracle 23ai 以前: テーブルが既に存在すると ORA-00955 でエラー
-- CREATE TABLE employees ( ... );

-- Oracle 23ai: IF NOT EXISTS で存在チェックを省略可能
CREATE TABLE IF NOT EXISTS employees (
    employee_id  NUMBER         PRIMARY KEY,
    last_name    VARCHAR2(100)  NOT NULL,
    hire_date    DATE           DEFAULT SYSDATE,
    is_active    BOOLEAN        DEFAULT TRUE
);
-- → 既に存在する場合はスキップ（エラーにならない）
-- → 存在しない場合は作成

CREATE INDEX IF NOT EXISTS idx_emp_name ON employees (last_name);
-- → インデックスが存在しない場合のみ作成

CREATE SEQUENCE IF NOT EXISTS emp_seq START WITH 1 INCREMENT BY 1;

-- Oracle 23ai 以前: 存在しないテーブルを DROP すると ORA-00942
-- DROP TABLE temp_work;

-- Oracle 23ai: IF EXISTS で存在チェックを省略可能
DROP TABLE IF EXISTS temp_work;
-- → 存在しない場合はスキップ（エラーにならない）

DROP INDEX IF EXISTS idx_old_data;
DROP SEQUENCE IF EXISTS old_seq;
DROP VIEW IF EXISTS v_old_report;

-- 環境構築・マイグレーションスクリプトが何度実行しても安全になる

-- 旧来の書き方（BEGIN EXCEPTION WHEN OTHERS THEN NULL; END; が必要だった）
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE work_log';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE work_log ( ... );

-- Oracle 23ai の書き方（シンプル）
DROP TABLE IF EXISTS work_log;
CREATE TABLE IF NOT EXISTS work_log (
    log_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    log_dt   TIMESTAMP DEFAULT SYSTIMESTAMP,
    message  VARCHAR2(4000)
);

-- Oracle 23ai 以前: GROUP BY に元の式を書く必要があった
SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month,
       SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM');   -- 長い式を繰り返す必要があった

-- Oracle 23ai: エイリアスで GROUP BY できる
SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month,
       SUM(amount) AS total
FROM orders
GROUP BY order_month;   -- エイリアスをそのまま使用可能

-- CASE 式や複雑な関数の結果をエイリアスで GROUP BY
SELECT
    CASE
        WHEN salary < 300000  THEN '30万未満'
        WHEN salary < 500000  THEN '30〜50万'
        WHEN salary < 800000  THEN '50〜80万'
        ELSE '80万以上'
    END AS salary_range,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY salary_range   -- CASE 式を繰り返さずに済む
ORDER BY MIN(salary);

-- Oracle 23ai 以前
SELECT SYSDATE FROM dual;
SELECT 1 + 1 FROM dual;
SELECT USER FROM dual;
SELECT SYS_GUID() FROM dual;

-- Oracle 23ai: FROM DUAL を省略可能
SELECT SYSDATE;
SELECT 1 + 1;
SELECT USER;
SELECT SYS_GUID();

-- 関数呼び出し
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');
SELECT UPPER('hello world');

-- メールアドレス用 Domain の作成
CREATE DOMAIN email_domain AS VARCHAR2(320)
    DEFAULT NULL
    CONSTRAINT valid_email
        CHECK (REGEXP_LIKE(VALUE, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'))
    ANNOTATIONS (description 'RFC準拠メールアドレス');

-- 電話番号用 Domain の作成
CREATE DOMAIN phone_domain AS VARCHAR2(20)
    CONSTRAINT valid_phone
        CHECK (REGEXP_LIKE(VALUE, '^[0-9\-\+\(\) ]+$'));

-- テーブル定義で Domain を使用
CREATE TABLE customers (
    customer_id  NUMBER         PRIMARY KEY,
    name         VARCHAR2(100)  NOT NULL,
    email        email_domain,    -- Domain を型として指定
    phone        phone_domain
);

-- メールアドレスの CHECK 制約は Domain 側で管理される
INSERT INTO customers VALUES (1, '山田太郎', 'yamada@example.com', '03-1234-5678');
-- → OK

INSERT INTO customers VALUES (2, '佐藤花子', 'not-an-email', '090-9876-5432');
-- → ORA-02290: チェック制約（VALID_EMAIL）に違反しました

-- Domain 一覧の確認
SELECT domain_name, data_type, data_length, nullable
FROM user_domains
ORDER BY domain_name;

-- Domain を使用しているカラムの確認
SELECT table_name, column_name, domain_name
FROM user_tab_columns
WHERE domain_name IS NOT NULL;

-- Domain の制約を変更
ALTER DOMAIN email_domain
    ADD CONSTRAINT max_length CHECK (LENGTH(VALUE) <= 254);

-- Domain の削除（参照しているカラムがある場合は FORCE が必要）
DROP DOMAIN IF EXISTS email_domain;
-- DROP DOMAIN IF EXISTS email_domain FORCE;  -- 参照カラムも解除して削除

-- テーブル・カラムに Annotation を付与
CREATE TABLE employees (
    employee_id  NUMBER        PRIMARY KEY
        ANNOTATIONS (display_name '社員ID', mandatory),
    last_name    VARCHAR2(50)  NOT NULL
        ANNOTATIONS (display_name '姓', searchable),
    first_name   VARCHAR2(50)
        ANNOTATIONS (display_name '名'),
    salary       NUMBER(10, 2)
        ANNOTATIONS (display_name '給与', sensitive)
) ANNOTATIONS (
    description '社員マスターテーブル',
    version     '1.0'
);

-- Annotation の確認（USER_ANNOTATIONS）
SELECT object_name, object_type, column_name,
       annotation_name, annotation_value
FROM user_annotations
WHERE object_name = 'EMPLOYEES'
ORDER BY column_name, annotation_name;

-- 結果例:
-- EMPLOYEES  TABLE   (null)       DESCRIPTION  社員マスターテーブル
-- EMPLOYEES  COLUMN  EMPLOYEE_ID  DISPLAY_NAME 社員ID
-- EMPLOYEES  COLUMN  EMPLOYEE_ID  MANDATORY    (null)
-- EMPLOYEES  COLUMN  LAST_NAME    DISPLAY_NAME 姓
-- EMPLOYEES  COLUMN  SALARY       SENSITIVE    (null)

-- VECTOR(次元数, 型) で宣言
-- 型: FLOAT32（デフォルト）/ FLOAT64 / INT8
CREATE TABLE documents (
    doc_id     NUMBER PRIMARY KEY,
    title      VARCHAR2(500),
    content    CLOB,
    embedding  VECTOR(1536, FLOAT32)   -- OpenAI text-embedding-3-small は1536次元
);

-- ベクトルデータの INSERT（アプリ側で埋め込み生成後に格納）
INSERT INTO documents (doc_id, title, embedding)
VALUES (1, 'Oracle 23ai 入門',
        '[0.023, -0.045, 0.112, ...]'::VECTOR);   -- JSON配列形式で INSERT

-- TO_VECTOR 関数を使った変換
INSERT INTO documents (doc_id, title, embedding)
VALUES (2, 'PL/SQL 実践',
        TO_VECTOR('[0.087, -0.031, 0.094, ...]'));

-- 入力クエリのベクトルに最も近いドキュメントを検索
-- VECTOR_DISTANCE: ベクトル間の距離を計算する関数
--   COSINE（コサイン距離）/ EUCLIDEAN（ユークリッド距離）/ DOT（内積）/ MANHATTAN

-- 入力クエリの埋め込みを指定して類似ドキュメントをTOP-5で取得
SELECT doc_id, title,
       VECTOR_DISTANCE(embedding,
                       TO_VECTOR('[0.025, -0.042, 0.108, ...]'),
                       COSINE) AS similarity_distance
FROM documents
ORDER BY similarity_distance
FETCH FIRST 5 ROWS ONLY;
-- 距離が小さいほど類似度が高い

-- APPROX_NEAREST_NEIGHBOR ヒントで近似近傍探索（大量データで高速化）
SELECT /*+ VECTOR_INDEX(documents embedding_idx) */
       doc_id, title
FROM documents
ORDER BY VECTOR_DISTANCE(embedding,
                          TO_VECTOR('[0.025, -0.042, 0.108, ...]'),
                          COSINE)
FETCH APPROX FIRST 10 ROWS ONLY;

-- HNSW（Hierarchical Navigable Small World）インデックス
-- 近似近傍探索の高速化に使用
CREATE VECTOR INDEX idx_doc_embedding
    ON documents (embedding)
    ORGANIZATION INMEMORY NEIGHBOR GRAPH
    DISTANCE COSINE
    WITH TARGET ACCURACY 95
    PARAMETERS (type HNSW, neighbors 32, efconstruction 200);
-- type: HNSW（デフォルト）または IVF（Inverted File）

-- IVF インデックス（メモリ節約型、大規模データ向け）
CREATE VECTOR INDEX idx_doc_ivf
    ON documents (embedding)
    ORGANIZATION NEIGHBOR PARTITIONS
    DISTANCE COSINE
    WITH TARGET ACCURACY 90
    PARAMETERS (type IVF, neighbor_partitions 256);

-- Oracle 23ai 以前: テーブルごとに個別付与が必要
GRANT SELECT ON hr.employees   TO readonly_user;
GRANT SELECT ON hr.departments TO readonly_user;
GRANT SELECT ON hr.jobs        TO readonly_user;
-- ... スキーマ内の全テーブル分繰り返す

-- Oracle 23ai: スキーマ全体への一括付与
GRANT SELECT ANY TABLE ON SCHEMA hr TO readonly_user;

-- 複数の権限をスキーマ単位で付与
GRANT SELECT, INSERT, UPDATE ON SCHEMA app_schema TO app_user;

-- REVOKE も同様にスキーマ単位で可能
REVOKE SELECT ANY TABLE ON SCHEMA hr FROM readonly_user;

-- 付与したスキーマレベル権限の確認
SELECT grantee, owner, privilege, admin_option
FROM dba_tab_privs
WHERE type = 'SCHEMA'
ORDER BY grantee, owner;

-- または USER_SCHEMA_PRIVS（自分が付与を受けた権限）
SELECT *
FROM user_schema_privs;