主キーをナチュラルキーにしたときにウィザードが生成するページについて

 Oracle APEXは、表は自動生成のサロゲートキーを主キーにしていると、一番うまく扱ってくれます。

以前のバージョンではそれが顕著だった記憶があります。最新のバージョンの22.1のアプリケーション作成ウィザードとページ・作成ウィザードを使って、ナチュラルキーが主キーの表のレポートとフォームのページおよび対話グリッドのページを作成し、結果を確認してみます。

主キーの書式を検証するには開発者によるコーディングが必要で、それが自動生成されないのは仕方ありませんが、それを除くと自動生成されたページでは、最低でも以下の変更が必要です。

1. レポートの主キー列が非表示になる。ナチュラルキーの場合は表示したい。
2. フォームの主キー項目が非表示になる。そのため主キーを入力できない。（ページ作成ウィザードのみ）
3. フォームの主キー項目に設定されている値が変更可能である。主キーを変更されると問題が大きいので、変更不可が望ましい。
4. 対話グリッドでは、主キー列の代わりにROWIDを使用する。
5. 対話グリッドで主キー列が非表示、変更可能である点は、対話モード・レポートやフォームと同様の対応が必要です。

以下に状況の確認と、対処する手順について記述します。

準備作業

クイックSQLの以下のモデルより、確認作業で使用する表NK_TESTを作成します。列CODEが主キーでナチュラルキーを想定しています。キー値の自動生成は行いません。

# prefix: nk
test
    code vc8 /pk
    name vc80

SQLワークショップのユーティリティから、クイックSQLを起動します。上記のモデルを与え、SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実行します。

SQLスクリプトのレビュー画面が開きます。列CODEの値が自動生成されない（generated by default on null as identityといった指定がない）ことを確認します。

実行をクリックし、表NK_TESTを作成します。（確認画面で即時実行をクリックします。）

表NK_TESTが作成されたことを確認し、アプリケーションの作成をクリックします。（確認のダイアログが開くので、そこでもアプリケーションの作成をクリックします。）

アプリケーション作成ウィザードが起動します。

アプリケーションの名前をナチュラルキーの確認とします。表NK_TESTのフォーム付き対話モード・レポートのページが追加されていることを確認します。

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

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

ページ作成ウィザードを使って対話モード・レポートとフォームのページを作成します。

ページの作成をクリックします。

コンポーネントの対話モード・レポートを選択します。

ページ定義のレポートの名前をTest2、ページ・モードは標準とし、フォーム・ページを含めるはONにします。フォーム・ページ名もTest2とします。データ・ソースの表/ビューの名前はNK_TESTです。ナビゲーションのブレッドクラム、ナビゲーション共にONにします。

次へ進みます。

主キー列1にCode (Varchar2)を選択し、ページの作成をクリックします。

同様の手順で、編集可能対話グリッドのページも作成します。

コンポーネントの対話グリッドを選択します。

ページ定義の名前はTest3とします。ページ・モードは標準、フォーム・ページを含めるはOFFにします。対話グリッドの編集を有効にするため、データ編集用のフォームは不要です。データ・ソースの表/ビューの名前にNK_TESTを指定し、編集が有効をONにします。ナビゲーションのブレッドクラム、ナビゲーション共にONにします。

次へ進みます。

主キー列1にCode (Varchar2)を選択し、ページの作成をクリックします。

以上で準備は完了です。

対話モード・レポートとフォームの確認（アプリケーション作成ウィザード）

作成したアプリケーションを実行し、アプリケーション作成ウィザードによって作成された、対話モード・レポートとフォームを確認します。

ナビゲーション・メニューからTestを開き、作成をクリックします。レポートの表示を確認するには、最初にデータを投入する必要があります。

CodeとNameに適当な値を入力し、作成をクリックします。以下の例では、CodeにX-119、Nameにタブレット7と入力しています。

表NK_TESTにデータが１行投入されます。対話モード・レポートに、列CODEは表示されません。

ページ・デザイナを開き、列CODEの識別のタイプを非表示からプレーン・テキストに変更します。

主キー列のCODEもレポートに表示されるようになりました。

入力したデータを編集モードで開きます。鉛筆アイコンをクリックします。

先ほど入力したデータの編集フォームが開きます。ここでCodeが編集できてしまいます。

ページ・デザイナを開き、ページ・アイテムP3_CODEを選択します。

読取り専用のタイプにアイテムはNULLではないを選択し、アイテムにP3_CODEを指定します。これで、P3_CODEに値が設定されているときは編集不可になります。

再度、フォームを開き直します。Codeが編集不可でNameは編集できることを確認し、変更の適用をクリックします。

レポート上のNameの表示が変わっていることを確認します。

基本的な対応は、以上となります。

対話モード・レポートとフォームの確認（ページ作成ウィザード）

ページ作成ウィザードによって作成された対話モード・レポートとフォームを確認します。ナビゲーション・メニューよりTest2を開きます。

列CODEが非表示になっています。

ページ・デザイナで確認すると、なぜか列にROWIDが含まれています。

不要なので削除しますが、その前に、フォームを開く際のパラメータとしてROWIDが使用されていないことを確認します。

リージョンTest2を選択し、プロパティ・エディタの属性タブを開き、リンクのターゲットをクリックします。

フォームのページ・アイテムP5_CODEに列CODEの値#CODE#が渡されています。ROWIDは使われていないため、レポートの列ROWIDは問題なく削除できます。

列ROWIDを削除し、列CODEの識別のタイプをプレーン・テキストに変更します。

対話モード・レポートの対応は以上で完了です。

鉛筆アイコンをクリックし、編集フォームを開きます。

ページ作成ウィザードで作成されたフォームでは、主キー項目であるCODEを保持するページ・アイテムが非表示になっています。

ページ・デザイナを開き、主キーであるページ・アイテムP5_CODEのプロパティを適切に設定します。

識別のタイプをテキスト・フィールド、ラベルはCodeとします。外観のテンプレートをRequired - Floatingに変更します。主キーなので、検証の必須の値はONにします。

検証の必須の値の変更と、外観のテンプレートの設定は連動しません。必須の値の設定に合うように、手作業で外観のテンプレートを設定する必要があります。

ソースの主キーがONであることを確認します。読取り専用のタイプとしてアイテムはNULLではないを選択し、アイテムにP5_CODEを指定します。

基本的な対応は、以上となります。

対話グリッド

対話グリッドについても確認します。ナビゲーション・メニューからTest3を開きます。

列CODEが表示されていません。

ページ・デザイナで対話グリッドのページを開きます。

主キーがナチュラルキーの場合、対話グリッドでそのまま扱うのは困難です。そのため、ROWID列を使います。

ソースのROWID列を含めるをONにします。

列CODEを選択します。

識別のタイプを非表示からテキスト・フィールドに変更します。ヘッダーはCodeとします。列CODEは実際には主キーなので、検証の必須の値はONにします。対話グリッドとしての主キーはROWIDになっているので、ソースの主キーはOFFにします。読取り専用のタイプとしてアイテムはNULLではない、アイテムにCODEを選択し実行は各行とします。

対話グリッドの場合、実行を各行とするとアイテムとして列を指定します。この設定により、新規行であれば列CODEに値の入力を許可し、それ以外の既存行での列CODEの変更を禁止できます。

基本的な対応は、以上となります。

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

https://github.com/ujnak/apexapps/blob/master/exports/support-natural-key.zip

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

完

APEX 22.1の新機能 - 並替え基準を使う

 APEX 22.1より、リージョンのプロパティとして並替え基準（英語ではOrder By)が追加されています。レポートやチャートのソースに適用するORDER BY句を、ソースとは別のプロパティとして定義できるようにしたものです。

サンプル・データセットのEMP/DEPTに含まれる表EMPの従業員（列ENAME）を、給与（列SAL）の昇順にして棒グラフで表示しています。上位５件と１０件をページ・アイテムで選択し、チャートの表示件数を切り替えています。

APEX 22.1以前でも、このような実装はできましたが、APEX 22.1の新機能の並替え基準では、同じページをより簡単に実装できるようになりました。

以前の実装方法と、APEX 22.1の並替え基準を使った実装方法を比較してみます。

準備作業

両方の実装を行うAPEXアプリケーションを作成します。

データにはサンプル・データセットのEMP/DEPTに含まれる表EMPを使用します。そのため、あらかじめサンプル・データセットをインストールしておきます。

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

ダイアログが開きます。今回の記事では言語としてJapaneseを選択していますが、Englishやその他の言語でも同様に作業できます。スキーマとして、ワークペースのデフォルト・パーシング・スキーマが選択されます。変更は不要です。

次へ進みます。

データセットのインストールを実行します。

サンプル・データセットのインストールより作成されるAPEXアプリケーションは、色々な機能が実装された特別なアプリケーションになります。今回はそれらの機能は不要なので、このダイアログからアプリケーションの作成は行いません。

終了をクリックします。

サンプル・データセットがインストールされると、アクションがインストールから更新に変わります。

表EMPの準備ができたので、APEXアプリケーションを作成します。

アプリケーション・ビルダーより、アプリケーション作成ウィザードを呼び出します。

アプリケーションの名前は、新しいソート・アイテムとします。

このアプリケーションに上位件数を限定したチャートを実装します。22.1以前の実装を行うページと、新しいソート・アイテムを使った実装を行うページを、あらかじめ追加しておきます。

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

チャートを選択します。

ページ名は22.1以前、チャートのタイプとして棒、表またはビューはEMP、ラベル列にENAME、列値、値列にSALを指定し、ページの追加をクリックします。

まったく同じページを追加します。ページ名は新しいソート・アイテムとします。

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

アプリケションが作成されました。これで準備は完了です。

22.1以前での実装

22.1以前では、以下の実装を行います。

1. 件数を指定するページ・アイテムを作成する。
2. ページ・アイテムの値が変わったら、チャートをリフレッシュする動的アクションを作成する。
3. チャートやリージョンのソースを、設定した件数が反映されるように変更する。

リージョン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以前の動的アクションを使った実装を行う必要があります。

完