日日是Oracle APEX: スマート・フィルタを複数のリージョンの検索条件に適用する

2022年6月7日火曜日

スマート・フィルタを複数のリージョンの検索条件に適用する

複数のカード・リージョンの検索にスマート・フィルタを適用する方法について、Oracle APEXの開発チームのシニア・アーキテクトのSteve Muenchさんが、実装方法を個人の記事で紹介していたので試してみました。

スマート・フィルタを使って従業員を検索し、左のカード・リージョンに一覧しています。右のカード・リージョンには、検索された従業員が所属している部門を一覧しています。

アプリケーションの実装にはAlways FreeのAutonomous DatabaseのAPEX 21.2を使用しています。サンプル・データセットのEMP/DEPTに含まれる表EMP（左のカード・リージョンのソース）および表DEPT（右のカード・リージョンのソース）を、データとして使用しています。

では、アプリケーションの作成手順を紹介します。

アプリケーション作成ウィザードを起動します。アプリケーションの名前を複数リージョン・スマート・フィルタとします。

ホーム・ページを削除し、代わりにスマート・フィルタのページを追加します。

ページの追加で、スマート・フィルタを選択します。

ページ名を従業員検索とし、検索結果はレポートとして表示されるようレポートを選択します。ソースとなる表として、EMPを選択します。

ページの追加を実行します。

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

スマート・フィルタが実装されたページを含んだ、アプリケーションが作成されます。

これからの作業は、ページ番号１の従業員検索のページに実施します。

作成したアプリケーションを実行してみます。デフォルトで作成されたスマート・フィルタは、そのまま使用します。

ページ・デザイナを開いて、従業員を一覧するカード・リージョンを作成します。

識別のタイトルは従業員とします。タイプはカードを選択します。ソースの表名にEMPを選択します。

カード・リージョン従業員のAttributesを開き、カードに表示される内容を設定します。カードの主キー列１としてEMPNOを選択します。タイトルの列としてENAME、サブタイトルの列としてJOBを選択します。

もう一つカード・リージョンを作成します。検索された従業員が所属している部門の一覧を、カードとして一覧します。

識別のタイトルは部門とします。タイプはカードです。ソースの表名にDEPTを選択します。リージョン従業員の右横に配置するため、レイアウトの新規行の開始をOFFにします。

カード・リージョン部門のAttributesを開き、カードに表示される内容を設定します。カードの主キー列１としてDEPTNOを選択します。タイトルの列としてDNAME、サブタイトルの列としてLOCを選択します。

新たに追加したリージョン従業員と部門の検索条件は、リージョン検索結果に表示される結果から作成されます。そのためにパイプライン表関数empno_empを作成します。表関数の引数としてリージョン検索結果が必要になるため、詳細の静的IDとしてEmployeesを設定します。

スマート・フィルタが適用された結果より、従業員番号を取り出す表関数emono_empの定義は以下です。APEX_REGION.OPEN_QUERY_CONTEXTを呼び出しています。

	drop type t_empno_emp_tab;

	create or replace type t_empno_emp_row as object(
	empno number
	);
	/

	create or replace type t_empno_emp_tab as table of t_empno_emp_row;
	/

	create or replace function empno_emp(
	p_app_id in number
	, p_page_id in number
	, p_region in varchar2
	) return t_empno_emp_tab pipelined
	is
	l_region_id number;
	l_context apex_exec.t_context;
	l_idx_empno 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はいわゆる結果セットと同じ。
	l_context := apex_region.open_query_context(
	p_page_id => p_page_id
	, p_region_id => l_region_id
	);
	-- 列EMPNOの位置を取得する。
	l_idx_empno := apex_exec.get_column_position(l_context, 'EMPNO');
	while apex_exec.next_row(l_context)
	loop
	pipe row(
	t_empno_emp_row(
	-- 検索結果から従業員番号を、表関数の列の値として返す。
	apex_exec.get_number(l_context, l_idx_empno)
	)
	);
	end loop;
	apex_exec.close(l_context);
	end empno_emp;
	/

view raw empno_emp.sql hosted with ❤ by GitHub

SQLワークショップのSQLスクリプトを呼び出して実行し、ファンクションempno_empおよび、このファンクションが使用するタイプt_empno_emp_row、t_empno_emp_tabを作成します。

カード・リージョンに、このパイプライン表関数を使った条件を設定します。

リージョン従業員のソースのWHERE句は、以下になります。

empno in (
    select empno from table(
        empno_emp(
            p_app_id => :APP_ID
            , p_page_id => :APP_PAGE_ID
            , p_region => 'Employees'
        )
    )
)

リージョン部門のソースのWHERE句は、以下になります。

deptno in (
    select deptno from emp where empno in (
        select empno from table(
            empno_emp(
                p_app_id => :APP_ID
                , p_page_id => :APP_PAGE_ID
                , p_region => 'Employees'
            )
        )
    )
)