Oracle 23aiの新機能としてSQLドメインが実装されました。類似の概念にPostgreSQLのドメインがありますが、制約を集約するためのユーザー定義のデータ型であるPostgreSQLのドメイン¹と異なり、制約だけでなく表示方法やソート方法、属性情報を集約し再利用可能なオブジェクト（ユーザー定義のデータ型）となっており、SQLドメインを活用することでアプリケーションの開発工数の削減に寄与することができます。

Oracle DatabaseのCREATE DOMAIN文は下記となっており、DISPLAY句やORDER句、ANNOTATIONS句を指定できるようになっています。

CREATE DOMAIN DomainName AS <Data Type> 
[ DEFAULT <expression> [ ON NULL ] ] [ NOT NULL ] 
[ CONSTRAINT [ name ] CHECK (<expression>) [ ENABLE | DISABLE ] ] 
[ COLLATE collation ] 
[ DISPLAY <expression> ] 
[ ORDER <expression> ] 
[ ANNOTATIONS ( annotations ) ]

本記事では、公開されている下記の公式ブログを元にSQLドメインを試した後に、実際に日本の電話番号を定義するSQLドメインの作成を行ってみました。

オラクルエンジニア通信
23cの新しいSQLドメインを使用したコーディングの削減

メールアドレスに対するユーザー定義のSQLドメインの利用

この項では公式ブログに公開されている情報を参考に、SQLドメインを作成、利用する手順を確認してみました。OCI上のBaseDBにて、Oracle Database 23aiが稼働するインスタンスを作成し、PDBにsystemユーザーでログインし検証を行なっています。

sqlplus system/PASSWORD@db.jumpserverpubli.demovcn.oraclevcn.com:1521/PDB01.jumpserverpubli.demovcn.oraclevcn.com

SQLドメインの作成

EメールアドレスがNullだった時のデフォルト値の定義、挿入するメールアドレスのデータに＠マーク以下のEメールドメインの情報が含まれていることを確認する制約、カラムを参照した際にEメールドメインのみを表示するDOMAI_DISPLAY関数を定義しています。

create domain if not exists myemail_domain AS VARCHAR2(100)
default on null 'XXXX' || '@missingmail.com'
constraint email_c CHECK (REGEXP_LIKE (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$'))
display substr(myemail_domain, INSTR(myemail_domain, '@') + 1);

SQLドメインの利用したテーブルの作成

作成したmyemail_domainをp_emailカラムに適用し、テーブルを作成します。

create table person(
    p_id number(5), 
    p_name varchar2(50), 
    p_sal number, 
    p_email varchar2(100) domain myemail_domain
) annotations (display 'person_table');

作成した表定義を確認するとmyemail_domainが適用されていることを確認できます。

SQL> desc person;
 Name					Null?    Type
 --------------------- -------- ----------------------------
 P_ID						     NUMBER(5)
 P_NAME 					     VARCHAR2(50)
 P_SAL						     NUMBER
 P_EMAIL			   NOT NULL  VARCHAR2(100) SYSTEM.MYEMAIL_DOMAIN

SQLドメインの動作確認

まず、myemail_domainで定義されたp_emailカラムの制約に違反しないレコードを挿入しました。

SQL> insert into person values (1,'Bold',3000,null);
SQL> insert into person values (1,'Schulte',1000, 'mschulte@gmx.net');
SQL> insert into person values (1,'Walter',1000,'twalter@t_online.de');
SQL> insert into person values (1,'Schwinn',1000, 'Ulrike.Schwinn@oracle.com');
SQL> insert into person values (1,'King',1000, 'aking@aol.com');
SQL> commit;

Commit complete.

問題なく挿入できていることを確認できます。また、Null値がデフォルト値に置換されていることも確認できました。

SQL> set linesize 200
SQL> select * from person;

P_ID   P_NAME						   P_SAL P_EMAIL
---------- ----------------------------------------------------------------------
	 1 Bold 						    3000 XXXX@missingmail.com
	 1 Schulte						    1000 mschulte@gmx.net
	 1 Walter						    1000 twalter@t_online.de
	 1 Schwinn						    1000 Ulrike.Schwinn@oracle.com
	 1 King 						    1000 aking@aol.com

p_emailカラムの制約に違反するレコードを挿入しようとすると、ORA-11534エラーが発生します。

SQL> insert into person values (1,'Schulte',3000, 'mschulte%gmx.net');
insert into person values (1,'Schulte',3000, 'mschulte%gmx.net')
*
ERROR at line 1:
ORA-11534: check constraint (SYSTEM.SYS_C008416) involving column P_EMAIL due to domain constraint SYSTEM.EMAIL_C of domain SYSTEM.MYEMAIL_DOMAIN violated
Help: https://docs.oracle.com/error-help/db/ora-11534/

制約に違反したレコードはテーブルに挿入されません。

SQL> select * from person;

      P_ID P_NAME						   P_SAL P_EMAIL
---------- -------------------------------------------------- ---------- ----------
	 1 Bold 						    3000 XXXX@missingmail.com
	 1 Schulte						    1000 mschulte@gmx.net
	 1 Walter						    1000 twalter@t_online.de
	 1 Schwinn						    1000 Ulrike.Schwinn@oracle.com
	 1 King 						    1000 aking@aol.com

SQLドメイン作成時に指定したDOMAIN_DISPLAY関数を利用して、Eメールアドレスのドメインのみを参照することができます。

SQL> col p_name format a25
SQL> col DISPLAY format a25
SQL> select p_name, domain_display(p_email) "Display" from person;

P_NAME			  Display
------------------------- -------------------------
Bold			  missingmail.com
Schulte 		  gmx.net
Walter			  t_online.de
Schwinn 		  oracle.com
King			  aol.com

作成したSQLドメインはDROP文で削除することが可能です。先にSQLドメインを利用しているテーブルを削除してからSQLドメインの削除を行います。

SQL> drop table if exists person;

Table dropped.


SQL> drop domain if exists myemail_domain force;

Domain dropped.

国内電話番号ドメインを作成してみた

正規表現を利用して、市外局番、携帯電話、フリーダイヤルなどに対応したSQLドメインを作成してみました。今回は、ハイフンを利用する090-1111-2222の形式と、括弧を利用する090(1111)2222の形式の両方に対応していますが、表示する際はハイフンを利用する形式で統一できるようにDOMAIN_DISPLAY関数を定義しています。また、ハイフン、括弧を抜いて並び替えできるようにORDER句を定義しています。

CREATE DOMAIN IF NOT EXISTS jp_phonenumber_domain AS VARCHAR2(15)
NOT NULL
CONSTRAINT jp_phonenumber_c CHECK (REGEXP_LIKE (jp_phonenumber_domain, '^(((0(\d{1}[-(]?\d{4}|\d{2}[-(]?\d{3}|\d{3}[-(]?\d{2}|\d{4}[-(]?\d{1}|[5789]0[-(]?\d{4})[-)]?)|\d{1,4}\-?)\d{4}|0120[-(]?\d{3}[-)]?\d{3})$'))
DISPLAY REPLACE(REPLACE(jp_phonenumber_domain, '(', '-'), ')', '-')
ORDER LOWER(REPLACE(REPLACE(REPLACE(jp_phonenumber_domain, '(', ''), ')', ''), '-', ''));

作成したJP_PHONENUMBER_DOMAINを利用した表を作成します。

create table person     
( p_id number(5),
  p_name varchar2(50),
  p_sal number,
  p_email varchar2(4000) domain EMAIL_D, 
  p_phone varchar2(15) domain JP_PHONENUMBER_DOMAIN
);

制約に違反していないレコードを挿入できることを確認します。

SQL> insert into person values (1,'Bold',3000,null, '090-1111-1111');        

1 row created.

SQL> insert into person values (2,'Schulte',1000, 'mschulte@gmx.net', '03(0001)0001');

1 row created.

SQL> insert into person values (3,'White',1000, 'white@gmx.net', '05(0012)0012');

1 row created.

SQL> insert into person values (4,'Black',1000, 'black@gmx.net', '05-0022-0012');

1 row created.

制約に違反したレコード（ハイフンも括弧もない電話番号）は挿入できません。

SQL> insert into person values (5,'Green',1000,'green@t_online.de', '09012341234');
insert into person values (5,'Green',1000,'green@t_online.de', '09012341234')
*
ERROR at line 1:
ORA-11534: check constraint (SYSTEM.SYS_C008428) involving column P_EMAIL due
to domain constraint SYS.SYS_DOMAIN_C0030 of domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/

挿入されたレコードを確認します。

SQL> set linesize 200
SQL> col p_name format a25
SQL> col p_email format a30                   
SQL> SELECT * FROM PERSON;

      P_ID P_NAME			  P_SAL P_EMAIL 		       P_PHONE
---------- ------------------------- ---------- ------------------------------ ---------------
	 1 Bold 			   3000 			       　　　　 090-1111-1111
	 2 Schulte			   1000 mschulte@gmx.net	       03(0001)0001
	 3 White			   1000 white@gmx.net		       05(0012)0012
	 4 Black			   1000 black@gmx.net		       05-0022-0012

SQLドメインで定義した並び替え順序を確認してみます。

SQL> SELECT * FROM PERSON ORDER BY P_PHONE;

      P_ID P_NAME			  P_SAL P_EMAIL 		       P_PHONE
---------- ------------------------- ---------- ------------------------------ ---------------
	 2 Schulte			   1000 mschulte@gmx.net	       03(0001)0001
	 3 White			   1000 white@gmx.net		       05(0012)0012
	 4 Black			   1000 black@gmx.net		       05-0022-0012
	 1 Bold 			   3000 			       090-1111-1111

SQLドメインで定義したDOMAIN_DISPLAY関数を確認してみます。

SQL> SELECT P_ID, P_NAME, P_SAL, P_EMAIL, domain_display(P_PHONE)  "Phone Number" FROM PERSON ORDER BY P_PHONE;

      P_ID P_NAME			  P_SAL P_EMAIL 		       Phone Number
---------- ------------------------- ---------- ------------------------------ ---------------
	 2 Schulte			   1000 mschulte@gmx.net	       03-0001-0001
	 3 White			   1000 white@gmx.net		       05-0012-0012
	 4 Black			   1000 black@gmx.net		       05-0022-0012
	 1 Bold 			   3000 			               090-1111-1111

Tips

SQLドメインを作成する際に正規表現が適切かどうかがポイントになります。問題が発生した際に、下記のようなSELECT文を利用して、レコードの値（下記の場合は090-1111-1111）が正規表現と一致するか調べることが可能です。

SQL> select regexp_like( (select '090-1111-1111' from dual) , '^(((0(\d{1}[-(]?\d{4}|\d{2}[-(]?\d{3}|\d{3}[-(]?\d{2}|\d{4}[-(]?\d{1}|[5789]0[-(]?\d{4})[-)]?)|\d{1,4}\-?)\d{4}|0120[-(]?\d{3}[-)]?\d{3})$') from dual ;

REGEXP_LIKE
-----------
TRUE

「CREATE DOMAIN は新しいドメインを作成します。ドメインとは本質的には、特別な制約（使用可能な値集合に対する制限）を持ったデータ型です。」「ドメインを使用すると、共通な制約を 1 箇所に抽象化でき、メンテナンスに便利です。」 ↩