ファセットによる検索結果の合計や平均を計算する

 Oracle APEXのPL/SQL APIとして提供されているファンクションAPEX_REGION.OPEN_QUERY_CONTEXTの引数にp_outer_sqlがあります。これを使用して、以下の機能を実装します。

1. サンプル・データセットに含まれる表EMPを準備する。
2. 表EMPのファセット検索ページを作成する。
3. ファセットを使って表EMPの検索（従業員の絞り込み）を行う。
4. 検索結果の列SALおよびCOMMの合計、平均、最小値、最大値、件数を別のレポートに表示する。
5. 合計値などは、ファセットを変更する度に更新する。

作成したアプリケーションは、以下のように動作します。

以下より、実装手順を紹介します。

実装にはAlways FreeのAutonomous Databaseを使用します。

サンプル・データセットのEMP/DEPTのインストール

SQLワークショップのユーティリティに含まれるサンプル・データセットを開き、EMP/DEPTをインストールします。

すでにインストール済みの場合は、ボタンが更新になっています。その場合は、表EMPおよびDEPTが作成済みなので、追加の作業は不要です。

表EMPやDEPTを変更している場合（列をドロップしている等）は、更新を開いてインストール済みのデータセットを削除し、再度インストールし直す必要があります。データのみ変更している場合（例えば表EMPの行をすべて削除している等）は、リフレッシュするといった準備が必要になります。

アプリケーションの作成

アプリケーションを作成します。

アプリケーション・ビルダーを開き作成を実行して、アプリケーション作成ウィザードを起動します。

名前はファセット検索サマリーとします。ホーム・ページの編集をクリックして、このページを削除します。

削除をクリックします。

ページの追加をクリックし、ファセット検索のページを追加します。

ファセット検索を選択します。

ページ名は従業員検索とします。表示形式はレポートとし、ソースとなる表にEMPを選択します。編集は行わないため、フォームを含めるはチェックしません。

ページの追加をクリックします。

以上でアプリケーションの作成を実行します。

アプリケーションが作成されます。

今回の機能はページ１の従業員検索のページに実装します。

集計をレポートするパイプライン表関数の作成

ファセット検索の結果に集計関数を適用するパイプライン表関数emp_summaryを作成します。

以下のコードを実行します。

	drop type "T_EMP_SUMMARY_TAB";
	/
	/*
	* 表EMPのサマリーとなる行のタイプ
	*/
	CREATE OR REPLACE EDITIONABLE TYPE "T_EMP_SUMMARY_ROW" as object(
	sal_sum number
	, sal_avg number
	, sal_min number
	, sal_max number
	, sal_cnt number
	, comm_sum number
	, comm_avg number
	, comm_min number
	, comm_max number
	, comm_cnt number
	);
	/
	/*
	* パイプライン表関数の返り値となるタイプ
	*/
	CREATE OR REPLACE EDITIONABLE TYPE "T_EMP_SUMMARY_TAB" as table of t_emp_summary_row;
	/
	/*
	* 表EMPのファセット検索の結果がレポートされているリージョンを引数として、
	* そのSUM、AVG、MIN、MAX、COUNTを返すパイプライン表関数の実装。
	*/
	create or replace function emp_summary(
	p_app_id in number
	, p_page_id in number
	, p_region in varchar2
	) return t_emp_summary_tab pipelined
	is
	l_region_id number;
	l_context apex_exec.t_context;
	/*
	* p_outer_sqlの定義。
	* ファセット検索の結果が#APEX$SOURCE_DATA#であり、
	* その結果に集計関数を適用している。
	*/
	C_OUTER_SQL constant varchar2(4000) := q'~
	select
	sum(sal) sal_sum
	, avg(sal) sal_avg
	, min(sal) sal_min
	, max(sal) sal_max
	, count(sal) sal_cnt
	, sum(comm) comm_sum
	, avg(comm) comm_avg
	, min(comm) comm_min
	, max(comm) comm_max
	, count(comm) comm_cnt
	from #APEX$SOURCE_DATA#
	~';
	l_idx_sal_sum pls_integer;
	l_idx_sal_avg pls_integer;
	l_idx_sal_min pls_integer;
	l_idx_sal_max pls_integer;
	l_idx_sal_cnt pls_integer;
	l_idx_comm_sum pls_integer;
	l_idx_comm_avg pls_integer;
	l_idx_comm_min pls_integer;
	l_idx_comm_max pls_integer;
	l_idx_comm_cnt pls_integer;
	begin
	-- リージョンの静的IDからリージョンIDを取得する。
	select region_id into l_region_id from apex_application_page_regions
	where application_id = p_app_id and page_id = p_page_id
	and static_id = p_region;
	-- ファセット検索の結果の合計値などの検索を実行する。
	l_context := apex_region.open_query_context(
	p_page_id => p_page_id
	, p_region_id => l_region_id
	, p_outer_sql => C_OUTER_SQL
	);
	-- 列の位置を取得する。
	l_idx_sal_sum := apex_exec.get_column_position(l_context, 'SAL_SUM');
	l_idx_sal_avg := apex_exec.get_column_position(l_context, 'SAL_AVG');
	l_idx_sal_min := apex_exec.get_column_position(l_context, 'SAL_MIN');
	l_idx_sal_max := apex_exec.get_column_position(l_context, 'SAL_MAX');
	l_idx_sal_cnt := apex_exec.get_column_position(l_context, 'SAL_CNT');
	l_idx_comm_sum := apex_exec.get_column_position(l_context, 'COMM_SUM');
	l_idx_comm_avg := apex_exec.get_column_position(l_context, 'COMM_AVG');
	l_idx_comm_min := apex_exec.get_column_position(l_context, 'COMM_MIN');
	l_idx_comm_max := apex_exec.get_column_position(l_context, 'COMM_MAX');
	l_idx_comm_cnt := apex_exec.get_column_position(l_context, 'COMM_CNT');
	-- 検索結果を取り出す。whileループを使っているが、検索結果は１行のみ。
	while apex_exec.next_row(l_context)
	loop
	pipe row(
	t_emp_summary_row(
	apex_exec.get_number(l_context, l_idx_sal_sum)
	, apex_exec.get_number(l_context, l_idx_sal_avg)
	, apex_exec.get_number(l_context, l_idx_sal_min)
	, apex_exec.get_number(l_context, l_idx_sal_max)
	, apex_exec.get_number(l_context, l_idx_sal_cnt)
	, apex_exec.get_number(l_context, l_idx_comm_sum)
	, apex_exec.get_number(l_context, l_idx_comm_avg)
	, apex_exec.get_number(l_context, l_idx_comm_min)
	, apex_exec.get_number(l_context, l_idx_comm_max)
	, apex_exec.get_number(l_context, l_idx_comm_cnt)
	)
	);
	end loop;
	-- コンテキストを閉じる。
	apex_exec.close(l_context);
	return;
	-- 必要に応じて例外処理は定義する。
	end emp_summary;
	/

view raw facet_summary.pls hosted with ❤ by GitHub

SQLワークショップのSQLスクリプトを開きます。

作成を実行します。

コードを貼り付け、適当なスクリププト名を入力して実行をクリックします。

即時実行をクリックします。

エラーなく処理が完了したら、パイプライン表関数emp_summaryの作成は完了です。

集計結果レポートの追加

従業員検索のページに、集計結果を表示するレポートを追加します。

集計のソースとなるパイプライン表関数emp_summaryは、検索結果を表示するリージョンの静的IDを引数とします。検索結果を表示するリージョンはEmployeesなので、これの詳細の静的IDとしてemployeesを設定します。

集計結果を表示するリージョンを、クラシック・レポートとして作成します。

すでにあるレポートと表示形式を合わせるため、リージョンEmployeesを重複させます。リージョンEmployees上でコンテキスト・メニューを表示させ、重複を実行します。

重複として作成されたリージョンの識別のタイトルを集計結果に変更します。ソースのタイプをSQL問合せとし、SQL問合せとして以下を記述します。

select * from emp_summary(

    :APP_ID,

    :APP_PAGE_ID,

    'employees'

)

集計結果が、検索結果のレポートが表示された後にリフレッシュされるように、動的アクションを作成します。

リージョンEmployees上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。

作成した動的アクションの識別の名前は集計結果のリフレッシュとします。タイミングはデフォルトで、イベントはリフレッシュ後、選択タイプはリージョン、リージョンとしてEmployeesが選択されます。

動的アクションとして実行されるTRUEアクションの、識別のアクションとしてリフレッシュを選択します。影響を受ける要素の選択タイプはリージョン、リージョンとして検索結果を選択します。

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画の動作をします。

レポートなどのソースにRESTソースを指定したときに、ローカル後処理としてSQL問合せを書けることは知っていたのですが、APEX_REGION.OPEN_QUERY_CONTEXTの引数として指定できることを最近知りました。それで、使い方を確認するためにアプリケーションを作成してみました。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/emp-summary-with-facet.sql

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完