【Oracle Database 23ai 検証】最新のSQLを使いこなそう！ -新機能編-

アシストでは、日本オラクル社の協力を得てOracle Database 23aiの検証プログラムを行っています。これから数回の連載で、23aiの最新情報をお届けする予定です。

第一回目として、本記事では23aiの最新SQLについてご紹介します。
23aiでは開発に役立つ多くのSQL関連機能が追加されました。その中から、いくつかのSQL新機能をピックアップして、活用例や検証結果とともにご紹介します！

「アプリケーション使用状況ドメイン（以下、ドメイン）」とは、データ型、制約、デフォルト値などの列属性をカプセル化したオブジェクトです。この機能により、これまで個々の列に定義していた列属性を一元管理することができます。

Oracle Database 21cまでは、複数の表に「メールアドレス」のような同じ列があっても、各列にデータ型や制約などの定義が必要でした。
23aiからは、事前に作成したドメインを指定するだけで列の定義が完了します。

メールアドレスの列属性として、以下の属性を含めたドメインを作成してみました。
　・データ型　　：VARCHAR2(100)
　・チェック制約：登録するデータが「<空白以外の文字列>@<文字列>.<文字列>」に一致すること
　・表示と順序の制御（後述）
※ドメインを作成するユーザーには、CREATE DOMAIN権限が必要です。

/* D_EMAILドメインを作成 */
SQL> CREATE DOMAIN d_email AS VARCHAR2(100) NOT NULL
 2    CONSTRAINT chk_email CHECK (regexp_like (d_email, '^(\S+)\@(\S+)\.(\S+)$'))
 3    DISPLAY  '***' || SUBSTR(d_email,INSTR(d_email,'@'))
 4    ORDER SUBSTR(d_email,INSTR(d_email,'@')+1);

このD_EMAILドメインをCUSTOMER表のEMAIL列に定義してみます。

/* EMAIL列にD_EMAILドメインを定義 */
SQL> CREATE TABLE customer (name VARCHAR2(100), email DOMAIN d_email);


表が作成されました。


/* 制約に違反しないデータ（john@aaa.com）を挿入 */
SQL> INSERT INTO customer
  2  VALUES ('JOHN','john@aaa.com');


1行が作成されました。


/* 制約に違反するデータ（@の前に文字がない）を挿入 */
SQL> INSERT INTO customer
  2  VALUES ('JESSICA','@aaa.com');
INSERT INTO customer
*
行1でエラーが発生しました。:
ORA-11534: チェック制約(SCOTT.SYS_C0013684)に、ドメイン制約SCOTT.CHK_EMAIL 
(ドメインSCOTT.D_EMAIL)が原因で違反しました ヘルプ:
https://docs.oracle.com/error-help/db/ora-11534/

「列名 DOMAIN ドメイン名」でドメインを指定するだけで、簡単に列属性を定義できました。

また、今回はドメイン作成時にDISPLAY句とORDER句を指定しています。これにより、SELECT時に列値の表示や順序を以下のように制御することができます。
　・メールアドレスの@前を「***」とマスクして表示（DISPLAY句で設定）
　・@以降を昇順に並び替える（ORDER句で設定）

以下でEMAIL列をSELECTした結果を見てみましょう。

/* 列値の表示を制御（DOMAIN_DISPLAY関数を使用） */
SQL> SELECT DOMAIN_DISPLAY(email) AS email FROM customer;


EMAIL
--------------------
***@ccc.com
***@aaa.com
***@bbb.com


3行が選択されました。


/* 列値の順序を制御 (DOMAIN_ORDER関数を使用) */
SQL> SELECT email FROM customer ORDER BY DOMAIN_ORDER(email);


EMAIL
--------------------
john@aaa.com
emily@bbb.com
michaerl@ccc.com


3行が選択されました。

このようにドメインによりデータ型や制約だけではなく、表示や順序の設定も列に定義できるようになりました。

今回の例はメールアドレスでしたが、他にもクレジットカード情報や住所など、属性が同じ列が複数表にある場合は、ドメインを使用すると列の定義や管理が楽になりそうですね。

「アプリケーション使用用途注釈」は、表や列に開発者用のコメントである注釈を追加できる機能です。同じような機能として、21cまではコメント機能がありました。

21cのコメント機能と比べて、様々なオブジェクトに関する機密情報や取り扱い方法を、開発者間でより詳細に共有できるようになりました。

以下はEMPLOYEE表とその表を構成する各列に注釈を定義した例です。

SQL> CREATE TABLE employee (
  2  id NUMBER(5)
  3   ANNOTATIONS (display 'Employee ID', "GROUP" 'Emp_Info'),
  4  name VARCHAR2(50)
  5   ANNOTATIONS (display 'Employee Name', "GROUP" 'Emp_Info'),
  6  salary NUMBER
  7   ANNOTATIONS (display 'Employee Salary', UI_Hidden)
  8  )
  9   ANNOTATIONS (display 'Employee Table');


表が作成されました。


SQL> SELECT object_type,column_name,annotation_name, annotation_value
 2   FROM user_annotations_usage
 3   WHERE object_name ='EMPLOYEE';


OBJECT_TYPE              COLUMN_NAME               ANNOTATION             ANNOTATION_VALUE
--------------------     ---------------------     ----------------       --------------------------
TABLE                                              DISPLAY                Employee Table
TABLE                    ID                        DISPLAY                Employee ID
TABLE                    ID                        GROUP                  Emp_Info
TABLE                    NAME                      DISPLAY                Employee Name
TABLE                    NAME                      GROUP                  Emp_Info
TABLE                    SALARY                    DISPLAY                Employee Salary
TABLE                    SALARY                    UI_HIDDEN


7行が選択されました。

各列と表に複数の注釈を定義できました。また、「DISPLAY」や「GROUP」のように各注釈に対して名前を付けることもできます。これにより、例えばID列はDISPLAY注釈が「Employee ID」のため、アプリケーションからは「Employee ID」と表示する、またGROUP注釈が「Emp_Info」のため社員情報を表す列である、ということを開発者間で共有できます。

従来のコメント機能も23aiで使用できますが、開発者間のデータ管理をより強化するために注釈の使用を検討してみてはいかがでしょうか。

BOOLEANデータ型は23aiで新しく追加されたデータ型で、TRUE/FALSEの真理値を格納できます。例えば、ユーザーアカウントが有効かどうかなどの状態を表す列にこのデータ型を使用することができます。

21cまでは、真理値を格納したいときは’Y’/’N’や0/1で代用することが多かったと思いますが、23aiからはBOOLEANデータ型を使えます。また、BOOLEANデータ型を持つOracle Database以外のデータベースとの連携や移行の際も、SQLを書き換える必要がなくなりました。

なお、BOOLEANデータ型でTRUE/FALSEに変換される値は以下のとおりです。

以下に、BOOLEANデータ型の列を持つ表を作成しました。なお、BOOLEANデータ型にはNULL値も含めることができます。

/* ACTIVE列をBOOLEANデータ型で定義 */
SQL> CREATE TABLE example(id INTEGER,active BOOLEAN);


表が作成されました。


/* 真理値とNULL値を格納 */
SQL> INSERT INTO example
  2  VALUES (1, TRUE),
  3         (2, FALSE),
  4         (3, 'yes'),
  5         (4, 'no'),
  6         (5, NULL);


5行が作成されました。


SQL> SELECT * FROM example;


        ID ACTIVE
---------- -----------
         1 TRUE
         2 FALSE
         3 TRUE
         4 FALSE
         5

真理値の検索には、「WHERE 列名」でTRUEの行、「WHERE NOT 列名」でFALSEの行を検索できます。

/* TRUEの行を検索 */
SQL> SELECT * FROM example WHERE active;

        ID ACTIVE
---------- -----------
         1 TRUE
         3 TRUE

2行が選択されました。

/* FALSEの行を検索 */
SQL> SELECT * FROM example WHERE NOT active;

        ID ACTIVE
---------- -----------
         2 FALSE
         4 FALSE

2行が選択されました。

/* NULLの行を検索 */
SQL> SELECT * FROM example WHERE active IS NULL;
        ID ACTIVE
---------- -----------
         5

1行が選択されました。

これまで真理値を別の値で代用していた方は、BOOLEANデータ型の利用をご検討ください。

今回は3つのSQL新機能をご紹介しました。これからOracle Database23aiをご利用予定の方は、ぜひこれらの新機能をご検討ください。
また、SQL機能の第2弾として機能拡張編をお届けする予定です。ぜひ後続の記事もご覧ください。

SQL以外の23ai 新機能についても【Oracle Database 23ai 検証】のタイトルでご紹介していく予定ですので、ご期待ください！

本記事に関連して、他にもOracle Database 23aiの新機能や機能拡張についてご紹介している記事もございます。あわせてご活用ください。