SQL ドメインとは

「SQL ドメイン（SQL DOMAIN）」は、Oracle Database 23cの新機能となる新しいオブジェクトです。SQL ドメインは以下の情報をカプセル化し、テーブルのデータ型の代替として使用することができます。

データ型
NULL 値の許容
デフォルト値
制約
表示
ソート順
COLLATION

これらの情報をまとめることで、複数のテーブルに分散化しているチェック制約やデフォルト値を一元管理することができます（ただし、現状では SQL ドメインの設定変更ができる項目はわずかです）。

SQL ドメインの作成

SQL ドメインの作成や削除の方法を記述します。

作成

SQL ドメインは CREATE DOMAIN 文で作成します。以下の例は SQL ドメイン「dom_email」を作成しています。データ型は VARCHAR2(100)、デフォルト値として default@hpe.com、制約としてアットマーク（@）と、ドット（.）を含む文字列であることをチェックしています。DISPLAY の指定は SQL ドメインを文字列出力する場合の出力設定です。下記の例では LOWER 関数で小文字化して出力しています。ORDER はソート順に使用するデータを決めます。下記の例では小文字化してからソートすることを指定しています。

SQL> CREATE DOMAIN dom_email AS VARCHAR2(100)
  2     DEFAULT ON NULL 'default@hpe.com'
  3     CONSTRAINT chk_email CHECK (regexp_like (dom_email, '^(\S+)\@(\S+)\.(\S+)$'))
  4     DISPLAY  LOWER(dom_email)
  5   	ORDER LOWER(dom_email);
Domain created.

上記の例以外に COLLATION を指定することもできます。しかし COLLATE 句を指定する場合には、初期化パラメーター max_string_size を 'EXTENDED' に指定しておく必要があります。以下の例では、初期化パラメーター max_string_size がデフォルト値であるため COLLATE 指定がエラーになっています。

SQL> CREATE DOMAIN dom_email AS VARCHAR2(100)
  2     CONSTRAINT chk_email CHECK (regexp_like (dom_email, '^(\S+)\@(\S+)\.(\S+)$'))
  3     DISPLAY  lower(dom_email)
  4     ORDER lower(dom_email)
  5     COLLATE binary_ci;
CREATE DOMAIN dom_email AS VARCHAR2(100)
*
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

変更

SQL ドメインの変更には ALTER DOMAIN 文を実行します。ただし変更できる属性は DISPLAY, ORDER, ANNOTATION のみです。

SQL> ALTER DOMAIN dom_email MODIFY ORDER UPPER(dom_email);
Domain altered.

削除

SQL ドメインの削除は DROP DOMAIN 文を実行します。使用中の SQL ドメインは削除できません。しかし DROP DOMAIN 文に FORCE 句を使うことで削除できるようになります。その場合、テーブルの列に使用していた場合、列のデータ型は SQLドメインのデータ型に変更されます。
下記の例では、使用中の SQL ドメインを削除しようとしてエラーになっています。FORCE 句を指定して強制的に削除しています。

SQL> DROP DOMAIN dom_email;
DROP DOMAIN dom_email
*
ERROR at line 1:
ORA-11502: The domain DOM_EMAIL to be dropped has dependent objects.

SQL> DROP DOMAIN dom_email FORCE;
Domain dropped.

SQL> DESCRIBE members
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MEMBER_ID                                          VARCHAR2(20)
 EMAIL                                              VARCHAR2(100)

SQL ドメインの使用

SQL ドメインの使用方法について説明します。

列のデータ型として

作成した SQL ドメインは、テーブル列のデータ型として指定できます。SQL*PlusのDESCRIBEコマンドでドメイン名の情報を出力することができます。

SQL> CREATE TABLE members (member_id VARCHAR2(20), email DOMAIN dom_email);
Table created.

SQL> DESC members
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------------
 MEMBER_ID                                          VARCHAR2(20)
 EMAIL                                     NOT NULL VARCHAR2(100) SCOTT.DOM_EMAIL

INSERT 文やUPDATE文では SQL ドメインを意識することはありません。CHECK制約の代わりに SQL ドメインの制約が使用されます。以下の例では email 列に制約違反のデータを格納しようとしてエラーになっています。

SQL> INSERT INTO members VALUES ('member123', 'test.com');
INSERT INTO members VALUES ('member123', 'test.com')
*
ERROR at line 1:
ORA-11534: check constraint (SCOTT.SYS_C008341) due to domain constraint
SYSTEM.CHK_EMAIL of domain SCOTT.DOM_EMAIL violated

DISPLAY と ORDER

ドメイン独自の出力方法を指定する DOMAIN_DISPLAY 関数と、ソート順を指定する DOMAIN_ORDER 関数が提供されています。以下の例では DOMAIN_DISPLAY 関数を使って、入力データを SQL ドメインの DISPLAY 句に指定した大文字に変換して出力しています。

SQL> INSERT INTO members VALUES ('member123', 'MEMBER123@EXAMPLES.COM');
1 row created.

SQL> SELECT DOMAIN_DISPLAY(email) FROM members ORDER BY DOMAIN_ORDER(email);

DOMAIN_DISPLAY(EMAIL)
--------------------------------------------------------------------------------
member123@examples.com

PL/SQL 上の制約

SQL ドメインは PL/SQL 変数としては使用できません。テーブル列と同じ型を使う場合には、%ROWTYPE 句で代替します。

SQL> DECLARE
  2     email DOMAIN dom_email;
  3  BEGIN
  4     NULL;
  5  END;
  6  /
     email DOMAIN dom_email;
                  *
ERROR at line 2:
ORA-06550: line 2, column 19:
PLS-00103: Encountered the symbol "DOM_EMAIL" when expecting one of the
following:
:= . ( @ % ; not null range default character
The symbol ":=" was substituted for "DOM_EMAIL" to continue.

Author: Noriyoshi Shinoda / Date: September 29, 2023