Oracle 23aiの新機能としてSQLドメインが実装されました。類似の概念にPostgreSQLのドメインがありますが、制約を集約するためのユーザー定義のデータ型であるPostgreSQLのドメイン1と異なり、制約だけでなく表示方法やソート方法、属性情報を集約し再利用可能なオブジェクト(ユーザー定義のデータ型)となっており、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