2021年2月26日金曜日

ページ・アイテムの受け渡しを確認するアプリの作成

 ページ・アイテムをパラメータとして渡して、別のページに遷移するために利用できる、いくつかの方法があります。

  1. 動的アクションで実装する。
  2. ボタンにターゲットを指定する。
  3. 送信後にブランチする。
それぞれページ・アイテムの扱いが異なるので、アプリケーションを作ってみました。


このアプリケーションは、Oracle APEXの動作を理解するためのもので、作成方法自体は解説しません。作成したアプリケーションのエクスポートを以下に置きました。インポートすると動作確認ができます。

https://github.com/ujnak/apexapps/blob/master/exports/pageitemvalues.sql

動的アクションによる遷移


動的アクションでページ遷移を行います。


ページ・アイテムのP1_REQUEST、P1_SESSION、P1_USERの値を遷移先のP2_REQUEST、P2_SESSION、P2_USERへ設定します。

単純なページ遷移であればURLを引数として、apex.navigation.redirectを呼び出すことでページの遷移が行われます。

apex.navigation.redirect(apex.item("P1_URL").getValue());


通常はページ遷移の際に引数としてチェックサムを追加する必要があります。これはサーバー側でしか計算できません。そのため、サーバー側でAPEX_PAGE.GET_URLを呼び出して、ページ遷移のためのURLを取得します。取得したURLは、ページ・アイテムP1_URLに戻します。

:P1_URL := apex_page.get_url( p_page => :APP_PAGE_ID, p_items => 'P1_REQUEST,P1_SESSION,P1_USER', p_values => '\' || :P1_REQUEST || '\,\' || :P1_SESSION || '\,\' || :P1_USER || '\' );

動的アクションで上記の計算を行うには、p_valuesとして渡される値を、送信するアイテムとして指定します。

ページ・アイテムにカンマ(,)やコロン(:)が含まれる可能性がある場合は、ページ・アイテムの値をバックスラッシュで囲みます。これにより、ページ・アイテムの中間で値が分割されることを防ぎます。


送信するアイテムとして指定されているので、URLはボタンをクリックした時点でのページ・アイテムの値が反映されます。よって、遷移先のページ・アイテムの値は、画面上の値になります。


実際にボタンをクリックしてページ遷移をします。遷移先のページ・アイテムに画面上に入力されていた値が渡されていることが確認できます。


ボタンによるリダイレクト


最も一般的なページ遷移の方法だと思います。ページ・アイテムの値を引き継いで、別ページに遷移します。


ボタンの動作アクションとして、このアプリケーションのページにリダイレクトを選択し、ターゲットとなるページを設定します。


ターゲットの設定で、引き渡すアイテムの設定を行います。


名前として遷移先のページ・アイテムの名前、として、このボタンが存在しているページのページ・アイテムの値を指定します。ここで値が&P1_REQUEST.といった置換文字列として指定されています。置換文字列の評価はページが生成されるときなので、引き渡される値はページが生成された時点での値になります。ページ生成後に画面に入力された値は渡されません。


実際にページを遷移して確認できます。



送信後にブランチ


通常はフォームを送信して、レポートのページに戻る、という処理で使われている設定です。ページを遷移させるために使用することはあまりありません。


ボタンのアクションページの送信として、ブランチによってページ遷移をします。


ブランチの動作タイプとしてページまたはURL(リダイレクト)を選択します。ブランチがボタンを押したときのみに動作するように、サーバー側の条件としてボタン押下時にボタン名を指定します。


ターゲットのアイテムの設定は、リダイレクトの場合と同じです。


ボタンのアクションページの送信の場合、画面上のページ・アイテムの値はすべてHTTPのPOSTリクエストとしてサーバーへ送信されます。そのため、ここに現れている置換文字列はボタンを押した時点での画面上の値になります


実際にページを遷移して確認できます。


セッション・ステートの保持


セッション・ステートの保持の設定が異なる3つのページ・アイテムを作成しています。
  • P1_REQUEST - リクエストごと(メモリーのみ)
  • P1_SESSION - セッションごと(ディスク)
  • P1_USER - ユーザーごと(ディスク)
ボタンのアクションが、単純にページの送信だけのときの動作を確認します。


この場合、単にページ・アイテムをサーバーに送信して、その後、自ページを再表示します。


それぞれのページ・アイテムに値を設定し、送信後自ページへをクリックします。


セッション・ステートの保持リクエストごと(メモリーのみ)と設定されているページ・アイテムP1_REQUESTの値は、ページの送信後、値が無くなっています。


P1_SESSIONおよびP1_USERは、サーバーが送信されたページ・アイテムを受け取った時点でディスクに保存されますが、リクエストごと(メモリーのみ)の場合は保存しません。送信されたページを処理するプロセス(プロセス・ビューに登録されている一連のプロセス)からは値を参照することができますが、それが終了し画面のレンダリングが実行される時点(レンダリング・ビューの処理)では、ページ・アイテムの値は消えています。

そう考えると、セッション・ステートはつねにディスクに保存した方がよい、と思えますが、以下の2点の理由より、基本はリクエストごと(メモリーのみ)を設定するのが望ましいです。
  1. リクエストごと(メモリーのみ)はパフォーマンス面で有利です。
  2. ディスクに保存は、グローバル変数に似ています。どのページからでも変更される可能性があるため、デバッグが難しくなります。メモリーのみの場合は、そのとき処理しているリクエスト内がページ・アイテムのスコープになります。
グローバルなスコープを持たせる場合は、ページ・アイテムではなくアプリケーション・アイテムの利用を検討すべきです。ページ・アイテムのソースをアプリケーション・アイテムとすることも可能です。

ページ・アイテムのセッション・ステートの保持をセッションごと(ディスク)、ユーザーごと(ディスク)として場合でも、異なるページのページ・アイテムを直接参照することは避けるべきです。ページをコピーしたり作り直した場合など、ページ番号は変わることがあるためです。ページを変更する際に、そのページ上のページ・アイテムを直接参照している他のページがあっても、それを発見するのは困難です。

1ページの中で処理を完結するようにすることにより、Oracle APEXのアプリケーションのデバッグが容易になります。

本記事は以上になります。

Oracle APEXのアプリケーション開発の一助になれば幸いです。

表をピボットさせて表示する

 以前にも表のピボットについては記事を書きました。似たような相談を受けたのですが、結果が少し違った実装になったので作業を記録します。

それぞれの従業員についてコンピュータ言語の習得レベルを確認したい、というのが目的です。それで、確認しやすいように、以下の形式にできないか、というのが要件です。

それぞれの言語が列となり、横並びになっています。表示上は見やすいです。しかし、このまま表にする前提で、クイックSQLのモデルを定義すると以下のようになります。

# prefix: pvt
# semantics: default
language_skills
    name  vc80 /nn
    COBOL vc16 /check Excellent, Moderate, Acceptable
    FORTRAN vc16 /check Excellent, Moderate, Acceptable
    C vc16 /check Excellent, Moderate, Acceptable

新しい言語、例えばPythonやJavaScriptを追加しようとすると、表に列を追加する必要があります。このような表定義は望ましいものではないので、一般的には以下のように定義します。

# prefix: pvt
# semantics: default
languages
    language_name vc16 /nn

employee_skills
    employee_name  vc80 /nn
    language /reference languages /nn
    skill_level vc16 /check Excellent, Moderate, Acceptable /nn

まずはこのモデルを元にアプリケーションを作成します。

SQLワークショップからユーティリティクイックSQLを開きます。クイックSQLが開いたら、上記のモデルを入力します。続けて、SQLの生成SQLスクリプトを保存レビューおよび実行を行います。

生成されたDDLのレビュー画面で実行をクリックします。(この時点ではアプリケーションの作成は行いません。先に表を作成します)

続く画面で即時実行を行い、DDLの実行が成功したのを確認したのち、アプリケーションの作成をクリックして、アプリケーション作成ウィザードを開始します。

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

アプリケーションの作成画面が開きます。名前は任意ですが、ここでは言語スキルと設定しています。表PVT_LANGUAGESとPVT_EMPLOYEE_SKILLSのレポートとフォームが作成されることを確認し、アプリケーションの作成を実行します。


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

アプリケーションを実行し、確認のために使用するデータを投入します。

表PVT_LANGUAGES(メニューからはLanguages)に比較的伝統的な言語、COBOL、FORTRAN、Cの3つの言語を登録します。

従業員の言語スキルのレベルも登録します。以下の画面ではオラクル太郎さんはCOBOLが得意(EXCELLENT)、オラクル花子さんはFORTRANがそこそこできる(MODERATE)、オラクル二郎さんはCが少しできる(ACCEPTABLE)、という情報を登録しています。

テストに使うデータを登録しました。これから、上記のように縦並びで表示されているデータを言語ごとに横並びにしていきます。

最初にクラシック・レポートで、横並びの表示を行います。

ページの作成をクリックし、ページ作成ウィザードを開始します。

コンポーネントからレポートを選択します。

続いてクラシック・レポートを選びます。

ページ名ピボット・レポートとし、ブレッドクラムBreadcrumbとして、に進みます。

ナビゲーション・プリファレンスとして新規ナビゲーション・メニュー・エントリの作成を選択し、に進みます。


表/ビューの名前としてPVT_EMPLOYEE_SKILLS(表)を選択します。ただし、ソースの設定はページ作成後に変更します。ですので、ここでの設定は重要ではありません。作成を実行します。


ページが作成されるので、実行してみます。


今のところ、表の形式そのままのレポートになっています。これから表をピボットさせる設定を行います。ページ・デザイナを開き、レポートのソースタイプSQL問合わせを返すファンクション本体に変更します。SQL問合わせを戻すPL/SQLファンクション本体として、以下を記述します。
declare
    l_sql1 varchar2(4000) := 'select k.employee_name row_key, k.employee_name';
    l_sql2 varchar2(4000) := ' from (select employee_name from pvt_employee_skills group by employee_name) k ';
    l_idx integer := 0;
    l_tbl varchar2(8);
begin
    for l in
    (
      select id, language_name from pvt_languages order by 1
    )
    loop
      l_idx := l_idx + 1;
      -- construct SQL
      l_tbl := 't' || l_idx;
      l_sql1 := l_sql1 || ',' || l_tbl || '.' || l.language_name;
      l_sql2 := l_sql2 || ' left outer join (select employee_name, skill_level as ' || l.language_name || ' from pvt_employee_skills where language = ' || l.id || ') ' || l_tbl || ' on k.employee_name = ' || l_tbl || '.employee_name ';
    end loop;
   return (l_sql1 || l_sql2);
end;

クラシック・レポートでは特に主キー列は必須ではありません。今回の形式の表示では列EMPLOYEE_NAMEが一意になるのですが、従業員名をそのまま主キーにするのも考えものなので、(実際は従業員名ですが)列ROW_KEYを追加しています。

列ROW_KEYは非表示にします。


ページを実行すると、以下の表示になっていることが確認できます。


PL/SQLのコードで問い合わせの結果として横並びになるようなSQLを生成しています。実際に出力されるSQLは以下になります。
select k.employee_name row_key, k.employee_name,
    t1.COBOL,
    t2.FORTRAN,
    t3.C 
from (select employee_name from pvt_employee_skills group by employee_name) k
    left outer join (select employee_name, skill_level as COBOL from pvt_employee_skills where language = 1) t1
        on k.employee_name = t1.employee_name  
    left outer join (select employee_name, skill_level as FORTRAN from pvt_employee_skills where language = 21) t2
        on k.employee_name = t2.employee_name  
    left outer join (select employee_name, skill_level as C from pvt_employee_skills where language = 22) t3
        on k.employee_name = t3.employee_name
スキルレベルを登録されている言語ごとに取り出して、それをジョインすることでスキルレベルがそれぞれの言語ごとの列として見えるようなSQLを作っています。

これで良さそうな感じですが、新たな言語が追加される場合を検討します。そもそも言語の数が不変であれば、言語をそれぞれの列として定義していても問題ありません。今回の要件は登録されている言語が変わったときに、できるだけアプリケーションへの影響を少なくすることです。

Pythonを追加してみます。


続けて、オラクル太郎さんがPythonをちょっと勉強した(ACCEPTABLE)という情報を追加します。

再度、ピボット・レポートを表示させます。しかし、変化はありません。


レポート列が追加されない理由は、レポート列はソースを評価したときに作成されるためです。なので、リージョンのコンテキスト・メニューより列の同期化を実行します。


列の同期化を実行すると、列が追加されます。


このままでは言語を追加する度にページ・デザイナを開いて、列の同期化を実行する必要がでてきます。そういった作業を不要にするために、クラシック・レポートには(クラシック・レポート限定です)、汎用列名の使用、および汎用列数というプロパティがソースに追加されています。これをONにすると、ソースの定義に依存せず、あらかじめ決まった数の列が生成されます。

汎用列名の使用ONにし汎用列数10に設定し、レポートを表示します。


列の名前がCol01、Col02、Col03...となっています。表示されているのはCol06までで、使用されていない汎用列はレポートに含まれていません。


汎用列を使用すると列のヘッダーが列名から決定されません。特に今回は列名は、定義された言語で決まるので、コード内で列のヘッダーを設定します。

最初の列COL01は列ROW_KEYなので、これは非表示とします。


続く列COL02はつねに従業員名なので、これはヘッダー従業員名とします。


それ以外は非表示のページ・アイテムを作成し、そのページ・アイテムの値をヘッダーにします。


ページ・アイテムは必要な数だけ作成します。ページ・アイテムの名前はコードの中で扱いやすい名前(ここではページ・アイテムのプレフィックス+汎用列名)にし、タイプ非表示にします。


レポートのソース中でヘッダーとなるページ・アイテムに文字列を設定します。
declare
    l_sql1 varchar2(4000) := 'select k.employee_name row_key, k.employee_name';
    l_sql2 varchar2(4000) := ' from (select employee_name from pvt_employee_skills group by employee_name) k ';
    l_idx integer := 0;
    l_tbl varchar2(8);
begin
    for l in
    (
      select id, language_name from pvt_languages order by 1
    )
    loop
      l_idx := l_idx + 1;
      -- set column header
      apex_util.set_session_state(p_name => 'P6_COL' || to_char(l_idx + 2, 'FM00'), p_value => l.language_name, p_commit => FALSE);
      -- construct SQL
      l_tbl := 't' || l_idx;
      l_sql1 := l_sql1 || ',' || l_tbl || '.' || l.language_name;
      l_sql2 := l_sql2 || ' left outer join (select employee_name, skill_level as ' || l.language_name || ' from pvt_employee_skills where language = ' || l.id || ') ' || l_tbl || ' on k.employee_name = ' || l_tbl || '.employee_name ';
    end loop;
   return (l_sql1 || l_sql2);
end;
apex_util.set_session_stateプロシージャを呼び出して、ページ・アイテムに言語の名前を設定する処理を1行だけ追加しています。


以上で汎用列を使う設定は完了です。ピボット・レポートを表示させて結果を確認します。

カラム名が言語の名前になっています。

言語にJavaScriptを追加し、オラクル花子さんにJavaScriptが得意(EXCELLENT)という情報を入力したのち、ピボット・レポートを表示すると以下のように表示されます。ソースの再評価なしで列の表示が追加されていることが確認できます。


クラシック・レポートについては以上になります。

対話モード・レポート、対話グリッドについては汎用列の設定はありません。ですので、言語が追加される度に列の同期化を行う必要があります。それを前提として、以下の設定を行うことで、レポートの表示と編集が可能になります。

表PVT_EMPLOYEE_SKILLSを対象とした標準的なレポートとフォームを最初に作成します。この手順についての詳細は省略します(後でほとんど変更するため)。レポート・タイプ対話モード・レポートレポート・ページ名は従業員一覧、フォーム・ページ名は従業員編集とします。フォーム・ページ・モードモーダル・ダイアログとします。


以降、ほとんどデフォルトのまま進んで(ソースの表/ビューの名前にはPVT_EMPLOYEE_SKILLSを選択します)、対話モード・レポートとフォームのページを作成します。

対話モード・レポートのページを開いて、最初にソースの設定を変更します。ソースはクラシック・レポートと同様にタイプSQL問合せを返すファンクション本体とし、同じコード(汎用列の対応がないもの - apex_util.set_session_stateプロシージャを含まない)をSQL問合せを戻すPL/SQLファンクション本体として設定します。


ROW_KEY非表示とします。


続いてフォームのページを開き、ソースを対話モード・レポートと同様に変更します。


ROW_KEYを選択し、タイプ非表示主キーONにします。


フォームが作成されたときの主キーはP8_IDだったのですが、それが削除され、P8_ROW_KEYが主キーになっています。そのため、DELETE、SAVE、CREATEのボタンのサーバー側の条件からアイテムの指定が消えています。ここにP8_ROW_KEYを設定します。

以下はDELETEの例ですが、SAVECREATEについても、アイテムとしてP8_ROW_KEYを設定します。


データの追加/更新/削除は、PL/SQL Codeで行います。行の自動処理(DML)のターゲット・タイプPL/SQL Codeに変更し、以下のコードによって表の操作を行います。登録されている言語の数だけ、merge文を実行しています。スキルレベルが空白の場合は、その言語の行は削除します。
declare
    l_skill_level pvt_employee_skills.skill_level%type;
begin
    case
    when :APEX$ROW_STATUS in ('C','U') then
        for c in (select id, language_name from pvt_languages)
        loop
            l_skill_level := v('P8_' || upper(c.language_name));
            if l_skill_level is not null then
                merge into pvt_employee_skills k
                using
                (
                    select :P8_EMPLOYEE_NAME as employee_name, 
                        c.id as language,
                        l_skill_level as skill_level
                    from dual
                ) e
                on (k.employee_name = e.employee_name and k.language = e.language)
                when matched then
                    update set 
                        skill_level = e.skill_level
                when not matched then
                    insert 
                        (employee_name, language, skill_level)
                    values
                        (e.employee_name, e.language, e.skill_level);
            else
                delete from pvt_employee_skills where employee_name = :P8_EMPLOYEE_NAME and language = c.id;
            end if;
        end loop;
        if :P8_ROW_KEY is null then
            :P8_ROW_KEY := :P8_EMPLOYEE_NAME;
        end if;
    when :APEX$ROW_STATUS = 'D' then
       delete from pvt_employee_skills where employee_name = :P8_EMPLOYEE_NAME;
  end case;
end;
行のロックPL/SQL Codeとし、更新対象の従業員のデータをロックします。
declare
    type pvt_employee_skills_t is table of pvt_employee_skills%rowtype;
    rs pvt_employee_skills_t;
begin
    select * bulk collect into rs from pvt_employee_skills where employee_name = :P8_EMPLOYEE_NAME
    for update nowait;
end;

最後に対話モード・レポートのページに戻り、対話モード・レポートのリージョンを選択し、Attributesを開きます。リンクターゲットアイテムの設定で、P8_IDを渡す設定になっている部分を、名前P8_ROW_KEY、値を#ROW_KEY#へ変更します。


以上で対話モード・レポートとフォームについては出来上がりました。動作を確認してみましょう。


対話グリッドのページも作成してみます。

こちらもフォームと同様に、表PVT_EMPLOYEE_SKILLSを対象とした標準的なページを最初に作成します。ページ名グリッド編集としました。


対話グリッドのページが作成されたら、最初に対話モード・レポートと同様にソースを変更します。同じコードを設定します。


認識された列よりROW_KEYを選択し、タイプ非表示主キーONに変更します。

続いてプロセスの設定を行います。フォームに行った設定と同様ですが、ページ・アイテムではなく列をバインド変数として指定します。プロセスのコードは以下になります。
declare
    l_skill_level pvt_employee_skills.skill_level%type;
begin
    case
    when :APEX$ROW_STATUS in ('C','U') then
        for c in (select id, language_name from pvt_languages)
        loop
            l_skill_level := v(upper(c.language_name));
            if l_skill_level is not null then
                merge into pvt_employee_skills k
                using 
                (
                    select :EMPLOYEE_NAME as employee_name, 
                        c.id as language,
                        l_skill_level as skill_level
                    from dual
                ) e
                on (k.employee_name = e.employee_name and k.language = e.language)
                when matched then
                    update set 
                        skill_level = e.skill_level
                when not matched then
                    insert 
                        (employee_name, language, skill_level)
                    values
                        (e.employee_name, e.language, e.skill_level);
            else
                delete from pvt_employee_skills where employee_name = :EMPLOYEE_NAME and language = c.id;
            end if;
        end loop;
        if :ROW_KEY is null then
            :ROW_KEY := :EMPLOYEE_NAME;
        end if;
    when :APEX$ROW_STATUS = 'D' then
       delete from pvt_employee_skills where employee_name = :EMPLOYEE_NAME;
  end case;
end;
行のロックは以下のコードで行います。
declare
    type pvt_employee_skills_t is table of pvt_employee_skills%rowtype;
    rs pvt_employee_skills_t;
begin
    select * bulk collect into rs from pvt_employee_skills where employee_name = :EMPLOYEE_NAME
    for update nowait;
end;

以上で対話グリッドのページも完成です。動作を確認してみましょう。


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

https://github.com/ujnak/apexapps/blob/master/exports/pivottable.sql

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