以前にも表のピボットについては記事を書きました。似たような相談を受けたのですが、結果が少し違った実装になったので作業を記録します。
それぞれの従業員についてコンピュータ言語の習得レベルを確認したい、というのが目的です。それで、確認しやすいように、以下の形式にできないか、というのが要件です。
それぞれの言語が列となり、横並びになっています。表示上は見やすいです。しかし、このまま表にする前提で、クイック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の３つの言語を登録します。
従業員の言語スキルのレベルも登録します。以下の画面ではオラクル太郎さんは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プロシージャを呼び出して、ページ・アイテムに言語の名前を設定する処理を１行だけ追加しています。
以上で汎用列を使う設定は完了です。ピボット・レポートを表示させて結果を確認します。
カラム名が言語の名前になっています。
言語に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の例ですが、SAVEとCREATEについても、アイテムとして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のアプリケーション作成の一助になれば幸いです。
完
