APEX 22.1より、リージョンのプロパティとして並替え基準（英語ではOrder By)が追加されています。レポートやチャートのソースに適用するORDER BY句を、ソースとは別のプロパティとして定義できるようにしたものです。
サンプル・データセットのEMP/DEPTに含まれる表EMPの従業員（列ENAME）を、給与（列SAL）の昇順にして棒グラフで表示しています。上位５件と１０件をページ・アイテムで選択し、チャートの表示件数を切り替えています。
以前の実装方法と、APEX 22.1の並替え基準を使った実装方法を比較してみます。
準備作業
両方の実装を行うAPEXアプリケーションを作成します。
データにはサンプル・データセットのEMP/DEPTに含まれる表EMPを使用します。そのため、あらかじめサンプル・データセットをインストールしておきます。
SQLワークショップのユーティリティのサンプル・データセットを開き、名前EMP/DEPTのアクションインストールをクリックして、EMP/DEPTをインストールします。
ダイアログが開きます。今回の記事では言語としてJapaneseを選択していますが、Englishやその他の言語でも同様に作業できます。スキーマとして、ワークペースのデフォルト・パーシング・スキーマが選択されます。変更は不要です。
次へ進みます。
終了をクリックします。
サンプル・データセットがインストールされると、アクションがインストールから更新に変わります。
表EMPの準備ができたので、APEXアプリケーションを作成します。
アプリケーション・ビルダーより、アプリケーション作成ウィザードを呼び出します。
アプリケーションの名前は、新しいソート・アイテムとします。
このアプリケーションに上位件数を限定したチャートを実装します。22.1以前の実装を行うページと、新しいソート・アイテムを使った実装を行うページを、あらかじめ追加しておきます。
ページの追加をクリックします。
チャートを選択します。
ページ名は22.1以前、チャートのタイプとして棒、表またはビューはEMP、ラベル列にENAME、列値、値列にSALを指定し、ページの追加をクリックします。
まったく同じページを追加します。ページ名は新しいソート・アイテムとします。
アプリケーションの作成を実行します。
アプリケションが作成されました。これで準備は完了です。
22.1以前での実装
22.1以前では、以下の実装を行います。
- 件数を指定するページ・アイテムを作成する。
- ページ・アイテムの値が変わったら、チャートをリフレッシュする動的アクションを作成する。
- チャートやリージョンのソースを、設定した件数が反映されるように変更する。
リージョン22.1以前に、ページ・アイテムを作成します。
識別の名前をP2_TOPNとします。タイプに選択リストを選びます。ラベルは上位件数とします。ページ・アイテムP2_TOPNに数値が設定されていないとソースのSELECT文が不正になるため、検証の必須の値をONにします。LOVのタイプは静的値とします。追加値の表示はOFF、NULL値の表示もOFFとします。
LOVの静的値の設定です。静的値として、表示値５件に対し戻り値を5、表示値１０件に対し戻り値を10の、２件を作成します。実行時にソートはOFFにし、定義順で表示されるようにします。
詳細の保存されていない変更の警告は無視にします。無視にしていないと、件数を変更した後にページを移動しようとすると「行った変更が保存されない可能性があります。」と警告がポップアップします。そのポップアップの「このページを離れる」をクリックしないとページの移動ができません。
ソースのセッション・ステートの保持は（デフォルトではセッションごと(ディスク)ですが、無駄なので）リクエストごと(メモリーのみ)に変更します。デフォルトのタイプとして静的値を選び、静的値に10を指定します。
ページ・アイテムP2_TOPNでの選択が変更されたときに、チャートをリフレッシュする動的アクションを作成します。
ページ・アイテムP2_TOPNで動的アクションを作成します。
動的アクションの識別の名前は、上位件数の変更とします。タイミングはデフォルトで、イベントは変更、選択タイプはアイテム、アイテムはP2_TOPNになります。
TRUEアクションをリフレッシュに変更し、影響を受ける要素の選択タイプとしてリージョンを選び、リージョンに22.1以前を指定します。このリージョン22.1以前に、棒グラフのチャートが表示されています。
チャートに給与の降順で従業員が表示されるよう、シリーズのソースを変更します。
シリーズのシリーズ1を選択し、ソースのSQL問合せを以下に変更します。
select
empno
, ename
, sal
from emp
order by sal desc fetch first :P2_TOPN rows only送信するページ・アイテムとして、P2_TOPNを指定します。
以上で実装は完了です。アプリケーションを実行すると、先頭のGIF動画のように動作します。
検索結果の行数を制限するために、fetch first構文を使用しています。オラクルではこれ以外に、以下のような記述でも検索結果の行数を制限できます。
select
empno
, ename
, sal
from (
select
empno
, ename
, sal
from emp
order by sal desc
)
where rownum <= :P2_TOPNまたは、
select
empno
, ename
, sal
from (
select
empno
, ename
, sal
, row_number() over (order by sal desc) topn
from emp
)
where topn <= :P2_TOPNただし、これらの構文によるSELECT文では、次に紹介する並替え基準を使った実装はできません。fetch first構文が使えるようになったのは12cからで11gでは使用できませんが、Oracle APEX 22.1ではOracle Database 11gはサポート対象から外れているため、fetch first構文が使えない状況は発生しません。
APEX 22.1での実装
APEX 22.1の並替え基準を使って、同じ機能を実装します。
シリーズのシリーズ1を選択します。ソースのタイプを表/ビューに変更し、表名にEMPを指定します。ソースとなるSQL問合せにorder by句が含まれないよう、それを排除します。
並替え基準のタイプにアイテムを選択し、アイテムのOrder by項目がありませんをクリックします。
Order by句の句としてsal desc fetch first 5 rows onlyを記述し、キーはTOP5、表示は5件とします。次に、句としてsal desc fetch first 10 rows onlyを記述し、キーはTOP10、表示は10件とします。
以上を入力し、OKをクリックするとアイテムの名前で指定されているページ・アイテム、今回の例ではP3_ORDER_BYが作成されます。
以上で実装は完了です。
22.1以前での実装により近づけるため、作成されたページ・アイテムP3_ORDER_BYを選択し、プロパティを少し変更します。
ラベルを上位件数、ソースのセッション・ステートの保持をリクエストごと(メモリーのみ)、デフォルトの静的値をTOP5からTOP10に変更します。
以上で完成です。ページを実行すると、記事の先頭のGIF動画のように動作します。
今回作成したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/new-221-sort-item.zip
Oracle APEXによるアプリケーション作成の参考になれば幸いです。
追記 - リージョンが複数あるときの対応
以下のように、チャートとレポートのリージョンを連動させる実装を考えます。
ソースのSQL問合せとして以下を記述します。
select
*
from emp_dept_v
order by sal desc fetch first :P2_TOPN rows only送信するページ・アイテムとしてP2_TOPNを指定します。
ページ・アイテムP2_TOPNの値が変更されたときに、チャートに加えてクラシック・レポートもリフレッシュするようにTRUEアクションを作成します。
以上でページ・アイテムP2_TOPNの値が変更されると、チャートとレポートのリージョンに表示される内容が更新されます。
APEX 22.1の並替え基準では、このような実装はできません。
並替え基準となるページ・アイテムはリージョンに紐付き共用できないため、それぞれのリージョンで作成する必要があります。
結果として並べ替え基準が２つになります。
複数のリージョンを同期させる場合は、22.1以前の動的アクションを使った実装を行う必要があります。
完
