はじめに
センスの良いSQLを書く技術を読んでいて、これまでMySQLなどのRDBMSでは書けていたけれど、Oracleでは書けなかったSQLが23aiから使用できる旨が書かれていました。（67ページのINSERT文など）
バージョンにも付随している「AI」に関する新機能はざっと目を通していましたが、基本的なSQL周りの新機能に対しては目を通していなかったので、これを機に今更ではありますが紹介します。
これまでOracle DBに触れてきていなかった方にとっては「このSQLってOracleでは書けなかったの！？」という衝撃も味わっていただきたく、ぜひ最後までお読みいただければと思います。
INSERT文で複数行をまとめて挿入
これまでOracle DBではINSERT文に VALUES を記述した場合は、1レコードしか挿入できませんでした。
INSERT INTO sample_table(col_1, col_2, col_3) VALUES ('AAA', 111, 'あああ');
INSERT INTO sample_table(col_1, col_2, col_3) VALUES ('BBB', 222, 'いいい');
INSERT INTO sample_table(col_1, col_2, col_3) VALUES ('CCC', 333, 'ううう');
23aiからは、 VALUES 以降に複数レコードを記述する構文が書けるようになりました。
INSERT INTO sample_table
(col_1, col_2, col_3)
VALUES
('AAA', 111, 'あああ'),
('BBB', 222, 'いいい'),
('CCC', 333, 'ううう')
;
MySQLなどではかなり昔からある書き方でしたが、ようやくOracleでも書けるようになりました。
Oracleで最も面倒だったINSERT文の記述を、大幅に短縮できます。
GROUP BYで指定するカラム名にエイリアスを指定
例として、支払日を年度に変換し、年度毎の売り上げを集計するクエリを記載します。
事前準備用クエリ
CREATE TABLE orders (
payment_date DATE NOT NULL,
sales_price NUMBER(10, 0) NOT NULL
);
INSERT INTO orders
VALUES
('2023-04-01', '10000000'),
('2023-05-01', '11000000'),
('2023-06-01', '12000000'),
('2023-07-01', '13000000'),
('2023-08-01', '14000000'),
('2023-09-01', '15000000'),
('2023-10-01', '16000000'),
('2023-11-01', '17000000'),
('2023-12-01', '18000000'),
('2024-01-01', '19000000'),
('2024-02-01', '20000000'),
('2024-03-01', '21000000'),
('2024-04-01', '20000000'),
('2024-05-01', '20500000'),
('2024-04-01', '21000000'),
('2024-05-01', '21500000'),
('2024-06-01', '22000000'),
('2024-07-01', '22500000'),
('2024-08-01', '23000000'),
('2024-09-01', '23500000'),
('2024-10-01', '24000000'),
('2024-11-01', '24500000'),
('2024-12-01', '25000000'),
('2025-01-01', '25500000'),
('2025-02-01', '26000000'),
('2025-03-01', '26500000'),
('2025-04-01', '22000000'),
('2025-05-01', '24000000'),
('2025-06-01', '26000000'),
('2025-07-01', '28000000')
;
COMMIT;
SELECT
CASE
WHEN TO_CHAR(payment_date, 'MM') IN ('01', '02', '03') THEN
TO_CHAR(TO_NUMBER(TO_CHAR(payment_date, 'YYYY')) - 1)
ELSE
TO_CHAR(payment_date, 'YYYY')
END AS financial_year,
SUM(sales_price) AS sales_price_summary
FROM
orders
GROUP BY
CASE
WHEN TO_CHAR(payment_date, 'MM') IN ('01', '02', '03') THEN
TO_CHAR(TO_NUMBER(TO_CHAR(payment_date, 'YYYY')) - 1)
ELSE
TO_CHAR(payment_date, 'YYYY')
END
ORDER BY
CASE
WHEN TO_CHAR(payment_date, 'MM') IN ('01', '02', '03') THEN
TO_CHAR(TO_NUMBER(TO_CHAR(payment_date, 'YYYY')) - 1)
ELSE
TO_CHAR(payment_date, 'YYYY')
END DESC
;
全く同じCASE文を、SELECT句、GROUP BY句、ORDER BY句の3箇所に記述する必要がありました。
あるいは、次のようにネストさせて書かなければなりませんでした。
SELECT
financial_year,
SUM(sales_price) AS sales_price_summary
FROM
(
SELECT
CASE
WHEN TO_CHAR(payment_date, 'MM') IN ('01', '02', '03') THEN
TO_CHAR(TO_NUMBER(TO_CHAR(payment_date, 'YYYY')) - 1)
ELSE
TO_CHAR(payment_date, 'YYYY')
END AS financial_year,
sales_price
FROM
orders
)
GROUP BY
financial_year
ORDER BY
financial_year
;
後者の例ではDRY原則に従えているので、ロジック自体はスッキリしますが、どうしてもネストは深くなってしまいます。
しかし、新機能では列名のエイリアスが指定できるようになったため、次のように書くことができます。
SELECT
CASE
WHEN TO_CHAR(payment_date, 'MM') IN ('01', '02', '03') THEN
TO_CHAR(TO_NUMBER(TO_CHAR(payment_date, 'YYYY')) - 1)
ELSE
TO_CHAR(payment_date, 'YYYY')
END AS financial_year,
SUM(sales_price) AS sales_price_summary
FROM
orders
GROUP BY
financial_year
ORDER BY
financial_year DESC
;
とてもスッキリ書けます。何度もCASE文を書く必要もありません。ネストも必要ありません。
FROM句なしのSELECT
これまでOracleでは、SELECT文にFROM句が必須でした。よって、 SYSDATE や何らかの計算結果を求める際には、dual表を利用する必要がありました。
SELECT SYSDATE, 24*3 FROM dual;
23aiにもdual表は存在しますが、FROM句を省略できるようになったため、次のように書くことで同じ結果が得られます。
SELECT SYSDATE, 24*3;
FROM dual を入力する必要がなくなるので、ちょっとした関数の動作確認をしたい場合などに、記述量が（ほんのわずかですが）減って楽になります。
DDL実行時のIF NOT EXISTS
CREATE TABLE などのDBオブジェクト作成時に、事前に存在をチェックできるようになりました。一部、実データを持たないオブジェクトに関しては CREATE OR REPLACE 構文によって、既存の同名オブジェクトが存在してもDDLがエラーにならないよう制御することができましたが、これまで REPLACE オプションを使えなかったオブジェクトに対しても事前チェックできるようになったことで、無用なエラーハンドリングが不要になります。
CREATE TABLE orders (
payment_date DATE NOT NULL,
sales_price NUMBER(10, 0) NOT NULL
);
次のコマンドの開始中にエラーが発生しました : 行 1 -
CREATE TABLE orders (
payment_date DATE NOT NULL,
sales_price NUMBER(10, 0) NOT NULL
)
エラー・レポート -
ORA-00955: すでに使用されているオブジェクト名です。
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:
CREATE TABLE IF NOT EXISTS orders (
payment_date DATE NOT NULL,
sales_price NUMBER(10, 0) NOT NULL
);
Table ORDERSは作成されました。
フィードバックがイケてない点（作成対象のオブジェクトが存在しているので、内部動作上はCREATE TABLEがスキップされているのに、「Table ORDERSは作成されました」と出力される点）はありますが、エラーは出なくなります。活躍するシチュエーションとして考えられるのは、ステージング環境が多数あって環境毎にDBの状態が異なっているような場合、あらかじめ IF NOT EXISTS をDDLに書いておくことで、エラーなくDDLを順次実行していけるようになります。
もちろん、 IF EXISTS も使用できます。
DROP TABLE IF EXISTS orders;
Table ORDERSが削除されました。
とは言え、環境がきちんと整ったらFlaywayなどのDBマイグレーションツールを使った方が、賢明ではあります。
BOOLEAN型
もうフラグを表すカラムの設定値を Y or N にするのか、 T or F にするのか、はたまた 1 or 0 にするのか、悩む必要はありません。素直にBOOLEAN型を使えば、全て解決です。
ただし、よほど特別な理由がない限りNOT NULL制約はつけましょう。TRUEとFALSEとNULLが混ざり合うと、もはや何を表したいのかが不明になります。
CREATE TABLE employees (
name VARCHAR2(10) NOT NULL,
is_retired BOOLEAN NOT NULL
);
INSERT INTO employees
VALUES
('Alice', FALSE),
('Brian', TRUE),
('Chris', FALSE),
('Donna', TRUE),
('Emily', FALSE)
;
COMMIT;
検索する時も、わざわざ等価式を書く必要はありません。
-- 退職した人たちを検索
SELECT
name,
is_retired
FROM
employees
WHERE
is_retired
;
--
-- この書き方でも検索可能
SELECT
name,
is_retired
FROM
employees
WHERE
is_retired = TRUE
;
--
-- さらに、この書き方でも検索可能
SELECT
name,
is_retired
FROM
employees
WHERE
is_retired = 1
;
--
-- 在職の人たちを検索
SELECT
name,
is_retired
FROM
employees
WHERE
NOT is_retired
;
--
-- CASE文での応用
SELECT
name,
is_retired,
CASE
WHEN is_retired THEN
'退職済'
ELSE
'在職'
END AS status
FROM
employees
;
BOOLEAN型は、データベース内部では 1 か 0 の値を持っていますが、プログラミング言語の基本と同じように真偽値に対する論理演算（ カラム名 = TRUE というような表現）は避ける方がスマートでしょう。
VALUEコンストラクタ
Oracle DBではdual表を駆使したダミーデータを用意してクエリを検証することがままありました。
WITH inventory AS (
SELECT 'デスク' AS item_name, TO_DATE('2025-03-01', 'YYYY-MM-DD') AS arrival_date FROM dual UNION ALL
SELECT 'ヘッドセット' AS item_name, TO_DATE('2025-07-01', 'YYYY-MM-DD') AS arrival_date FROM dual UNION ALL
SELECT 'モニター' AS item_name, TO_DATE('2023-03-01', 'YYYY-MM-DD') AS arrival_date FROM dual
)
SELECT
item_name,
arrival_date,
CEIL(MONTHS_BETWEEN(SYSDATE, arrival_date)) AS inventory_months
FROM
inventory
;
記述量が多くて、目がチカチカします。
23aiから搭載された「VALUEコンストラクタ」により、PostgreSQLと同じようにダミーデータを用意できるようになったため、よりシンプルに書くことができます。
SELECT
item_name,
arrival_date,
CEIL(MONTHS_BETWEEN(SYSDATE, arrival_date)) AS inventory_months
FROM
(
VALUES
('デスク', TO_DATE('2025-03-01', 'YYYY-MM-DD')),
('ヘッドセット', TO_DATE('2025-07-01', 'YYYY-MM-DD')),
('モニター', TO_DATE('2023-03-01', 'YYYY-MM-DD'))
)
inventory (item_name, arrival_date)
;
ご覧の通り、記述量を大幅に減らせます。
UPDATE/DELETE文内でのJOIN
これまではUPDATEやDELETE構文内で、操作対象以外のテーブルを結合することができなかったため、WHERE句の中でサブクエリをいかに書くか苦心された方も多いと思います。
SQL ServerではUPDATE文内でサブクエリを書かずにJOINできるのですが、Oracleでも同様のことができるようになりました。
事前準備用クエリ
CREATE TABLE departments (
id NUMBER PRIMARY KEY,
name VARCHAR2(20) NOT NULL
);
CREATE TABLE employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(10) NOT NULL,
saraly NUMBER(7) NOT NULL,
dept_id NUMBER NOT NULL,
FOREIGN KEY (dept_id) REFERENCES departments(id)
);
INSERT INTO departments (id, name)
VALUES
(1, 'Engineering'),
(2, 'Accounting')
;
INSERT INTO employees (id, name, saraly, dept_id)
VALUES
(1, 'Alice', 45000, 1),
(2, 'Brian', 78000, 2),
(3, 'Chris', 65000, 2),
(4, 'Donna', 90000, 1),
(5, 'Emily', 57000, 1)
;
COMMIT;
departments テーブルの部署名 Engineering に所属する従業員の saraly を 10% 賃上げするためのUPDATE文で見ていきます。
UPDATE employees e
SET e.saraly = e.saraly * 1.1
WHERE e.dept_id IN (
SELECT
d.id
FROM
departments d
WHERE
d.name = 'Engineering'
);
23aiからは、以下のように書くことができます。
UPDATE employees e
SET e.saraly = e.saraly * 1.1
FROM departments d
WHERE d.name = 'Engineering'
AND e.dept_id = d.id
;
UPDATE文と同階層にFROM句が出てくるため、かなり違和感がありますが、サブクエリを書く必要性がなくなったため、クエリ全体の見通しは良くなったと思います。
SQLドメイン
PostgreSQLで採用されている「ドメインオブジェクト」も、Oracleで使えるようになりました。
具体例として従業員の属性を例にドメインを作ってみます。
CREATE DOMAIN currency_usd AS NUMBER(20, 2)
DISPLAY TO_CHAR(currency_usd, 'FM$999,999,999,999,999,999.00')
;
CREATE DOMAIN job_title AS ENUM (
programmer = 'PG',
engineer = 'ENG',
manager = 'MAN',
tech_lead = 'TL'
);
CREATE DOMAIN mail_address AS VARCHAR2(255)
CONSTRAINT CHECK (REGEXP_LIKE(mail_address, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'))
;
DOMAINオブジェクト作成には、 CREATE ANY DOMAIN 権限が必要です。
次に、ドメインオブジェクトを参照するテーブルを作ります。
CREATE TABLE employees (
name VARCHAR2(20) NOT NULL,
job JOB_TITLE NOT NULL,
mail MAIL_ADDRESS NOT NULL,
saraly CURRENCY_USD NOT NULL
);
ドメインを参照した型でテーブルが作られたので、INSERTする際の型チェックが堅牢になります。まずは、正常にINSERTできるパターンをいくつか挙げていきます。
INSERT INTO employees
(name, job, mail, saraly)
VALUES
('Alice', 'PG', 'alice@sample.com', 5000000),
('Brian', JOB_TITLE.ENGINEER, 'brian@sample.com', 6000000.12)
;
挿入結果を確認します。
SELECT
name,
job,
mail,
saraly,
domain_display(saraly)
FROM
employees
ORDER BY
name
;
NAME JOB MAIL SARALY DOMAIN_DISPLAY(SARALY)
-------- ------ -------------------- ---------- ----------------------
Alice PG alice@sample.com 5000000 $5,000,000.00
Brian ENG brian@sample.com 6000000.12 $6,000,000.12
まず、JOBカラムにはドメインオブジェクト内で定義した右辺の値が設定されていることが確認できます。INSERTする際は、右辺の文字列をそのまま設定しても、他のプログラミング言語におけるENUMと同じような指定方法もできます。
また、CURRENCY_USD オブジェクト内で定義した DISPLAY の編集内容は、DOMAIN_DISPLAY 関数を噛ませることで有効化されます。
続いては、INSERTに失敗するパターンを見ていきます。
INSERT INTO employees VALUES ('Chris', 'CTO', 'chris@example.com', 60000);
次のコマンドの開始中にエラーが発生しました : 行 1 -
INSERT INTO employees VALUES ('Chris', 'CTO', 'chris@example.com', 60000)
エラー・レポート -
ORA-11534: チェック制約(SAMPLE.SYS_C008517、column JOBに関係し、ドメイン制約SAMPLE.SYS_DOMAIN_C0044 (ドメインSAMPLE.JOB_TITLE)に基づく)に違反しています
INSERT INTO employees VALUES ('Chris', JOB_TITLE.CTO, 'chris@example.com', 60000);
次のコマンド行の開始中にエラーが発生しました : 1 -
INSERT INTO employees VALUES ('Chris', JOB_TITLE.CTO, 'chris@example.com', 60000)
コマンド行 : 1 列 : 50 でのエラー
エラー・レポート -
SQLエラー: ORA-00984: ここでは列は使用できません。
00984. 00000 - "column not allowed here"
*Cause:
*Action:
INSERT INTO employees VALUES ('Chris', JOB_TITLE.MANAGER, 'chrisexample.com', 60000);
次のコマンドの開始中にエラーが発生しました : 行 1 -
INSERT INTO employees VALUES ('Chris', JOB_TITLE.MANAGER, 'chrisexample.com', 60000)
エラー・レポート -
ORA-11534: チェック制約(SAMPLE.SYS_C008516、column MAILに関係し、ドメイン制約SAMPLE.SYS_DOMAIN_C0043 (ドメインSAMPLE.MAIL_ADDRESS)に基づく)に違反しています
失敗パターンは以下の3つです。
- ドメイン
JOB_TITLEの右辺に存在しない値の挿入- ORA-11534 エラー
- ドメイン
JOB_TITLEの左辺に存在しない列挙へのアクセス- ORA-00984 エラー
- ドメイン
MAIL_ADDRESSの正規表現チェックに違反する文字列の挿入- ORA-11534 エラー
ドメインオブジェクトを使うことで、DBに保存される値がより厳密に制御できるようになったことがわかります。
まとめ
この熱量の勢いに任せて、本記事を書くに至りました。もののついでと思い、その他諸々も含めて調べましたが、当初の想定以上にSQL周りが改善されていて、よりユーザーフレンドリーなRDBMSになったように思えました。
出典