日日是Oracle APEX: Oracle APEX 21.2新機能(2) - マルチバリュー・ファセット

2021年10月27日水曜日

Oracle APEX 21.2新機能(2) - マルチバリュー・ファセット

Oracle APEX 21.2のファセット検索に、マルチバリュー・ファセットのサポートが追加されました。

マルチバリュー・ファセットについては、Oracle APEXの開発チームが短い解説動画をYouTubeで公開しています。

マルチバリュー・ファセットを実装することにより、使い方の確認を行います。サンプル・データセットのEMP/DEPTから自動的に作成したアプリケーションを元にします。このアプリケーションは、表EMPに対するファセット検索のページを含んでいます。

従業員が持っているスキルの情報をデータセットに追加します。以下の定義で表SKILLSを作成します。表EMPへの参照制約を定義している都合上、列EMPNOには索引SKILLS_I1を作成しています。

-- create tables
create table skills (
    id                             number generated by default on null as identity 
                                   constraint skills_id_pk primary key,
    empno                          number
                                   constraint skills_empno_fk
                                   references emp on delete cascade,
    skill                          varchar2(80) not null
)
;

-- table index
create index skills_i1 on skills (empno);

SQLスクリプトとして実行するか、SQLコマンドからCREATE TABLEとCREATE INDEXをそれぞれ１回ずつ実行してもよいでしょう。

従業員を編集するページにスキルを入力するページ・アイテムを追加します。複数のスキルを空白で区切って入力します。

ページ・デザイナにてフォームのページを開きます。フォーム・リージョンであるEmployeeにページ・アイテムを作成します。

識別の名前はP4_SKILLS、タイプにはテキスト・フィールドを選択します。ラベルはSkillsとします。ソースの設定のフォーム・リージョンは選択しません(- 選択 -のままにする)。フォーム・リージョンのソースは表EMPであり、スキルはソースに含まれていないためです。タイプはSQL問合せ(単一の値を返す)を選択し、スキルを空白で区切って一行で表示するように、SQL問合せとして以下を記述します。

select listagg(skill, ' ') within group (order by id) from skills where empno = :P4_EMPNO

使用としてセッション・ステートの既存の値を常に置換、セッション・ステートの保持はリクエストごと(メモリーのみ)とします。

フォームが送信されたときにページ・アイテムP4_SKILLSを受け取って、表SKILLSに書き込むプロセスを作成します。識別の名前をスキルの編集とします。タイプにはコードの実行を選択します。編集可能リージョンは無指定(- 選択 -のまま)にします。ページ・アイテムはフォーム・リージョンEmployeeに配置されていますが、データとしては関連付けされていません。ソースの位置はローカル・データベース、言語はPL/SQLとし、PL/SQLコードとして以下を記述します。

delete from skills where empno = :P4_EMPNO;
insert into skills(empno, skill) select :P4_EMPNO, column_value from table(apex_string.split(:P4_SKILLS, ' '));

作成および変更の適用のボタンが押されたときにのみ実行されるよう、サーバー側の条件のタイプにリクエストは値に含まれるを選択し、値としてCREATE SAVEを指定します。削除については、表EMPの親レコードが削除されるときにカスケードで削除されるので、追加の処理は不要です。

以上で従業員が持つスキルの編集ができるようになりました。このスキルを対象に、マルチバリュー・ファセットを作成します。

最初にレポートのリージョンであるEmployeesに列SKILLSを追加します。ソースのタイプをSQL問合せに変更し、SQL問合せとして、以下を記述します。従業員一人が持っているスキルをJSON_ARRAYAGG関数でまとめ、JSONの配列として１行にしています。JSON_ARRAYAGGの代わりにLISTAGG関数を使うこともできます。

select 
    e.EMPNO,
    e.ENAME,
    e.JOB,
    e.MGR,
    e.HIREDATE,
    e.SAL,
    e.COMM,
    e.DEPTNO,
    s.SKILLS
from EMP e left outer join 
(
    select empno, 
        json_arrayagg(skill order by id returning varchar2(400)) skills
    from skills group by empno
) s
on e.empno = s.empno

ファセット検索のテストを行うため、スキルの情報をあらかじめ入力しておきます。

マルチバリュー・ファセットを作成します。リージョン検索にてファセットの作成を実行します。識別の名前はP3_SKILLS、タイプにチェック・ボックス・グループを選択します。ラベルはスキルです。LOVのタイプは個別値とします。ここまでの設定内容は通常のファセットと同様です。

マルチバリュー・ファセットの場合は、複数の値のタイプとして区切りリストまたはJSON配列を選択します。今回はJSON_ARRAYAGG関数によって１行にまとめているためJSON配列を選んでいます。LISTAGG関数でまとめた場合は区切りリストを選択します。フィルタの結合にはAND(論理積)を選択しています。ファセットにてチェックを入れた項目をすべて含んでいる行が検索結果に含まれます。OR(和集合)を選ぶとチェックを入れた項目のどれかが含まれていれば検索結果に含まれます。