【Oracle】データ型完全ガイド｜NUMBER・VARCHAR2・DATE・TIMESTAMP・INTERVAL・CLOB・BLOB の選び方と注意点

-- NUMBER(p, s):
--   p = 有効桁数（全体の桁数）
--   s = スケール（小数点以下の桁数）
-- 例:
--   NUMBER(5,2) → 最大 999.99（整数部3桁・小数部2桁）
--   NUMBER(10,0) → 最大 9999999999（整数10桁）
--   NUMBER(6,-2) → スケール負数: 最大 9999900（100の位で丸める）

CREATE TABLE products (
    product_id   NUMBER(10)        PRIMARY KEY,   -- 整数
    unit_price   NUMBER(10, 2)     NOT NULL,       -- 金額（小数2桁）
    weight_kg    NUMBER(6, 3),                     -- 重量（3桁小数）
    tax_rate     NUMBER(5, 4)                      -- 税率（0.1000 など）
);

-- 格納される値と精度の例
-- NUMBER(5,2) に 1234.5 → エラー（整数部が3桁を超える）
-- NUMBER(5,2) に 123.456 → 123.46 に丸められる
-- NUMBER(5,2) に 123.454 → 123.45 に丸められる

-- BINARY_FLOAT / BINARY_DOUBLE は近似値になることに注意
SELECT 0.1 + 0.2 FROM DUAL;               -- NUMBER: 0.3 （正確）
SELECT (0.1 + 0.2) - 0.3 FROM DUAL;       -- NUMBER: 0  （正確）
-- BINARY_FLOAT では 0.300000012 のような近似値になる可能性がある

-- CHAR(5) は常に5バイトに空白埋めして格納される
-- → 'AB' を格納すると内部的には 'AB   '（空白3個）になる

CREATE TABLE test_types (
    c1 CHAR(5),
    c2 VARCHAR2(5)
);
INSERT INTO test_types VALUES ('AB', 'AB');

-- CHAR は末尾空白を無視して比較するため = では一致するが LENGTH は異なる
SELECT LENGTH(c1), LENGTH(c2) FROM test_types;
-- CHAR: 5, VARCHAR2: 2

-- CHAR 列を WHERE = で比較する場合、空白は無視されるが
-- || で連結すると 'AB   Suffix' のように空白が含まれる
SELECT c1 || 'Suffix' FROM test_types;  -- 'AB   Suffix'（CHAR の空白が残る）
SELECT c2 || 'Suffix' FROM test_types;  -- 'ABSuffix'（VARCHAR2 は空白なし）

-- 実務: CHAR は固定長コードのみに使用。それ以外は VARCHAR2 を使う

-- DATE: 秒単位まで。ミリ秒は切り捨て
-- TIMESTAMP: 小数秒（デフォルト6桁=マイクロ秒）まで格納

CREATE TABLE events (
    event_id     NUMBER          PRIMARY KEY,
    event_date   DATE,           -- 秒単位
    event_ts     TIMESTAMP(3),   -- ミリ秒単位（f=3）
    event_ts_tz  TIMESTAMP(6) WITH TIME ZONE   -- タイムゾーン付き
);

-- DATE に SYSDATE（秒単位）を格納
INSERT INTO events (event_id, event_date) VALUES (1, SYSDATE);

-- TIMESTAMP に SYSTIMESTAMP（マイクロ秒）を格納
INSERT INTO events (event_id, event_ts, event_ts_tz)
VALUES (2, SYSTIMESTAMP, SYSTIMESTAMP);

-- TIMESTAMP(3) に格納すると 123.456789 → 123 にミリ秒で切り捨て
-- TO_TIMESTAMP で文字列から変換
SELECT TO_TIMESTAMP('2024-03-15 10:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF')
FROM DUAL;

-- INTERVAL YEAR TO MONTH: 年・月の期間を格納
-- INTERVAL DAY TO SECOND: 日〜秒の期間を格納

-- INTERVAL リテラル
SELECT
    INTERVAL '1-6' YEAR TO MONTH     AS "1年6ヶ月",
    INTERVAL '5' YEAR                AS "5年",
    INTERVAL '3' MONTH               AS "3ヶ月",
    INTERVAL '2 04:30:00' DAY TO SECOND AS "2日4時間30分",
    INTERVAL '45' MINUTE             AS "45分"
FROM DUAL;

-- DATE との演算（数値の加算は日単位）
SELECT
    SYSDATE + 30            AS "30日後",
    SYSDATE + INTERVAL '3' MONTH AS "3ヶ月後",   -- 月末日処理あり
    SYSDATE + INTERVAL '2:30' HOUR TO MINUTE AS "2時間30分後"
FROM DUAL;

-- 2つの日付の差（INTERVAL で取得）
SELECT
    (TIMESTAMP '2024-12-31 23:59:59' - TIMESTAMP '2024-01-01 00:00:00')
    DAY(3) TO SECOND    AS diff
FROM DUAL;
-- 結果: +365 23:59:59.000000

-- 月数差は MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(DATE '2025-03-31', DATE '2024-09-30') FROM DUAL;  -- 6

-- CLOB に長文テキストを格納
CREATE TABLE documents (
    doc_id      NUMBER PRIMARY KEY,
    doc_title   VARCHAR2(200),
    doc_content CLOB             -- 長いHTMLや本文
);

-- CLOB に INSERT（4000バイト以内ならリテラルで可）
INSERT INTO documents VALUES (
    1,
    'Oracle入門',
    'これは長い文書の内容です。'
);

-- 4000バイト超の場合は DBMS_LOB.WRITEAPPEND か TO_CLOB() を使う
DECLARE
    v_clob CLOB := EMPTY_CLOB();
    v_chunk VARCHAR2(32767);
BEGIN
    INSERT INTO documents VALUES (2, '大きな文書', v_clob)
    RETURNING doc_content INTO v_clob;

    v_chunk := RPAD('あ', 10000, 'あ');   -- 10000文字
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_chunk), v_chunk);
    COMMIT;
END;
/

-- CLOB の部分取得
SELECT DBMS_LOB.SUBSTR(doc_content, 100, 1) AS first_100
FROM documents
WHERE doc_id = 2;

-- CLOB の全文検索（LIKE は CLOB にも使えるが大きなデータでは遅い）
SELECT doc_title
FROM documents
WHERE doc_content LIKE '%Oracle%';

-- Oracle 23ai 以前は NUMBER(1) や CHAR(1 CHAR) で代用していた
-- Oracle 23ai 以降は BOOLEAN 型が使用可能
CREATE TABLE feature_flags (
    flag_id     NUMBER PRIMARY KEY,
    flag_name   VARCHAR2(100),
    is_enabled  BOOLEAN DEFAULT FALSE NOT NULL
);

INSERT INTO feature_flags VALUES (1, 'dark_mode', TRUE);
INSERT INTO feature_flags VALUES (2, 'beta_api', FALSE);

SELECT flag_name, is_enabled
FROM feature_flags
WHERE is_enabled = TRUE;

-- IS TRUE / IS FALSE での比較も可能
SELECT flag_name FROM feature_flags WHERE is_enabled IS TRUE;
SELECT flag_name FROM feature_flags WHERE is_enabled IS NOT TRUE;  -- FALSE or NULL