Oracle APEXの開発ツールに含まれているオブジェクト・ブラウザに、ルックアップ表の作成という機能が含まれています。この機能を使ってルックアップ表を作成する手順と、変更後にAPEXアプリケーションに実施する変更を紹介します。
SQLワークショップよりオブジェクト・ブラウザを開き表を選択すると、ルックアップ表の作成がボタンとして現れます。
この機能は表の列を選択し、その列に固有の値を保持する表（ルックアップ表）を作成し、選択した列をルックアップ表のサロゲート・キーに置き換えるというものです。
ルックアップ表の作成と列のサロゲート・キーへの置き換えは、APEXに組み込まれている手順にて実施されます。
実際にルックアップ表の作成と、APEXアプリケーションの変更を実施してみます。
最初にテスト用のデータをデータベースに準備します。
SQLワークショップのユーティリティより、データ・ワークショップを呼び出します。
データのロードを実行します。
データのロードのダイアログが開きます。コピー・アンド・ペーストを選択し、サンプル・データセットとして、プロジェクトとタスクを選択します。
次へ進みます。
データのロードをクリックします。
APEXの以前のバージョンでは、コピー・アンド・ペーストからロードするサンプル・データに日時データが含まれているとORA-18xxのエラーが発生しました。そのため、英語環境で作業を行う必要がありました。Autonomous Database上のOracle APEX 21.1では、この不具合は解消されています。
データのロードに成功したら、アプリケーションの作成を呼び出します。
アプリケーションが作成されました。
作成したアプリケーションを確認します。
現在はデータをロードした表DEMO_PROJECTSの列STATUSに、それぞれ文字列としてステータスが保存されています。
ファセット検索のページで、列STATUSは以下のように表示されています。
他のページでも列STATUSは文字列として扱われています。
これをルックアップ表を参照するように変更します。
SQLワークショップのオブジェクト・ブラウザにて表DEMO_PROJECTSを開き、ルックアップ表の作成を実行します。
ルックアップ表を作成する列を選択します。今回の対象はSTATUSです。列STATUSのデータ型はVARCHAR2(50)です。
作成するルックアップ表とサロゲート・キーに使う順序に名前を付けます。ここでは新規表名としてDEMO_STATUS_LOOKUP、新規順序としてDEMO_STATUS_LOOKUP_SEQを指定しています。（デフォルトのSTATUS_LOOKUP、STATUS_LOOKUP_SEQから変更しています）。
次へ進みます。
ルックアップ表の作成を実施する画面になります。ルックアップ表の作成をクリックすると、ルックアップ表DEMO_STATUS_LOOKUPが作成されます。表DEMO_PROJECTSの列STATUSはサロゲート・キーを保持する列STATUS_IDに置き換えられます。
SQLをクリックし、実際に実行されるSQLを確認します。そのままでも動きますが、いくつか（特にデータベースのバージョンが高いAutonomous Databaseでは）変更した方が良い点があります。
create table "DEMO_STATUS_LOOKUP"(
"STATUS_ID" number not null primary key,
"STATUS" varchar2(4000) not null
);
create sequence "DEMO_STATUS_LOOKUP_SEQ";
create or replace trigger "T_DEMO_STATUS_LOOKUP"
before insert or update on "DEMO_STATUS_LOOKUP" for each row
begin
if inserting and :new."STATUS_ID" is null then
for c1 in (select "DEMO_STATUS_LOOKUP_SEQ".nextval nv from dual)
loop
:new."STATUS_ID" := c1.nv;
end loop;
end if;
end;
insert into "DEMO_STATUS_LOOKUP" ( "STATUS" )
select distinct "STATUS" from "APEXDEV"."DEMO_PROJECTS"
where "STATUS" is not null;
alter table "DEMO_PROJECTS" add "STATUS2" number;
update "DEMO_PROJECTS" x
set "STATUS2" = (select "STATUS_ID" from "DEMO_STATUS_LOOKUP" where "STATUS" = x."STATUS");
alter table "DEMO_PROJECTS" drop column "STATUS";
alter table "DEMO_PROJECTS" rename column "STATUS2" to "STATUS_ID";
alter table "DEMO_PROJECTS" add foreign key ("STATUS_ID") references "DEMO_STATUS_LOOKUP"("STATUS_ID");
Oracle Database 19cを前提とすると、主キーは自動採番できるので順序とトリガーを作成する必要がありません。また、ルックアップ表の列STATUSのデータ型がVARCHAR2(4000)となっていて、元々のデータ型VARCHAR2(50)と一致していません。必須ではありませんが、列STATUSは一意なので、ユニーク制約をつけた方が良いと思われます。
さらに、表DEMO_PROJECTSの列STATUS_IDはルックアップ表の列STATUS_IDを参照します。参照制約は作成していますが、索引が作られていません。
これらの対応を適用した、ルックアップ表を作成するSQLは以下になります。
create table "DEMO_STATUS_LOOKUP"(
"STATUS_ID" number generated by default on null as identity primary key,
"STATUS" varchar2(50) not null
);
alter table "DEMO_STATUS_LOOKUP" add unique("STATUS");
insert into "DEMO_STATUS_LOOKUP" ( "STATUS" )
select distinct "STATUS" from "DEMO_PROJECTS"
where "STATUS" is not null;
alter table "DEMO_PROJECTS" add "STATUS2" number;
update "DEMO_PROJECTS" x set "STATUS2" = (select "STATUS_ID" from "DEMO_STATUS_LOOKUP" where "STATUS" = x."STATUS");
alter table "DEMO_PROJECTS" drop column "STATUS";
alter table "DEMO_PROJECTS" rename column "STATUS2" to "STATUS_ID";
alter table "DEMO_PROJECTS" add foreign key ("STATUS_ID") references "DEMO_STATUS_LOOKUP"("STATUS_ID");
索引については、一般的なB-Tree索引を作るか、分析用途であれば、ビットマップ結合索引を作るといった選択肢があります。
B-Tree索引の場合は、以下のSQLを実行して索引を作ります。
create index demo_projects_status_idx on demo_projects(status_id);
ビットマップ結合索引の場合は、以下のSQLを実行して索引を作ります。
create bitmap index demo_projects_status_bjix
on demo_projects(demo_status_lookup.status_id)
from demo_projects, demo_status_lookup
where demo_projects.status_id = demo_status_lookup.status_id
nologging compute statistics;
以上の作業の結果、表DEMO_STATUS_LOOKUPというルックアップ表が作成され、表DEMO_PROJECTSが列STATUS_IDより、ルックアップ表を参照するようになりました。
ルックアップ表DEMO_STATUS_LOOKUPが作成されたので、この表を参照するLOVを共有コンポーネントとして作成します。
共有コンポーネントのLOVを開きます。
作成済みのLOVが一覧されます。作成をクリックします。
LOVの作成として最初からを選択します。
次へ進みます。
名前はLOV_DEMO_STATUSとします。タイプとしてDynamicを選択します。
LOVソースの表/ビューの名前として、先ほど作成済みの表DEMO_STATUS_LOOKUPを選択します。
次へ進みます。
戻り列をSTATUS_ID、表示列をSTATUSとします。作成をクリックします。
共有コンポーネントのLOVとして、LOV_DEMO_STATUSが作成されました。
表DEMO_PROJECTSが変更されているため、アプリケーションもそれに対応して変更する必要があります。
ファセット検索のページを変更します。
クラシック・レポートのリージョンの上でコンテキスト・メニューを開いて、列の同期化を実行します。
列の同期化を実行すると列STATUSが削除され、代わりに列STATUS_IDが追加されます。列STATUS_IDを選択し、STATUSが文字列として表示されるように設定を変更します。
識別のタイプをプレーン・テキスト(LOVに基づく)に切り替えます。ヘッダーはStatus Idとなっているので、Statusに変更します。LOVのタイプとして共有コンポーネントを選択し、LOVとして、先ほど作成したLOV_DEMO_STATUSを選択します。
ファセットP3_STATUSを選択し、LOVのタイプを共有コンポーネント、LOVをLOV_DEMO_STATUSに変更します。ソースのデータベース列をSTATUSからSTATUS_ID、データ型はVARCHAR2からNUMBERに変更します。
対話モード・レポートのページも同様に、リージョンにて列の同期化を実行します。
列の同期化を行うと列STATUSが削除され、列STATUS_IDが追加されます。追加された列STATUS_IDを選択し、先ほど同じく識別のタイプ、LOV、ヘッダーを変更します。
以上の変更により、対話モード・レポートの表示も以前と同じになります。
フォームのリージョンでは、列の同期化の代わりにページ・アイテムの同期化を実行します。
ページ・アイテムを同期化するとページ・アイテムP5_STATUSが削除され、代わりにページ・アイテムP5_STATUS_IDが追加されます。追加されたページ・アイテムP5_STATUS_IDを選択し、ステータスを選択できるようにします。
識別のタイプとして選択リスト（またはポップアップLOV）を選択します。ラベルはStatus IdからStatusへ変更します。LOVのタイプに共有コンポーネント、LOVとしてLOV_DEMO_STATUSを選択します。
以上の変更で、Statusが以前と同じ表示になります。また、文字列の入力ではなく選択リストに入力方法が変わります。
ルックアップ表の作成と作成後の対応についての説明は以上になります。
今回、ルックアップ表を使うように変更したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/createloouptable.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
追記
あまり無いとは思いますが、例えば列PROJECTとTASK_NAMEの組み合わせでルックアップ表を作る手順を紹介します。
ルックアップ表（というかタスクのマスター表）を表DEMO_TASKSとして作成するために、以下のCREATE文を実行します。
create table demo_tasks(
task_id number generated by default on null as identity primary key,
project varchar2(50) not null,
task_name varchar2(255) not null
);
表DEMO_PROJECTSに列TASK_IDを追加します。
alter table demo_projects add (task_id number);
表DEMO_TASKSに値を設定します。
insert into demo_tasks(project, task_name)
select distinct project, task_name from demo_projects;
表DEMO_PROJECTSの列TASK_IDを、マスター表DEMO_TASKSを参照するように更新します。 列PROJECTとTASK_NAMEにNULLが無いという前提です。
update demo_projects p set task_id =
(select task_id from demo_tasks where p.project = project and p.task_name = task_name);
表DEMO_PROJECTSの列PROJECT、TASK_NAMEを参照する代わりに、表DEMO_TASKSを参照するビューDEMO_PROJECTS_Vを作成します。
create or replace view demo_projects_v
as
select p.id, t.project, t.task_name,
p.start_date, p.end_date, p.assigned_to, p.cost, p.budget, p.status_id, p.task_id
from demo_projects p join demo_tasks t on p.task_id = t.task_id;
表DEMO_PROJECTSより列PROJECTとTASK_IDを除きます。
alter table demo_projects drop column project;
alter table demo_projects drop column task_name;
レポートなどの表の内容を表示するコンポーネントについては、ビューを使うように置き換えることで対応できます。フォームや対話グリッドなどの編集を伴うコンポーネントについては、列TASK_IDを選択する際に追加列を表示するポップアップLOVを使用するか、更新を行うプロセスのソースをPL/SQL Codeに変更し、プロジェクトとタスク名を受け取って表DEMO_PROJECTSとDEMO_TASKSを同時に更新するようにコーディングする必要があるでしょう。
完
