39
PL/SQLを含むレポートの作成
この章では、PL/SQLを含むレポートについて説明します。この章の手順に従うと、図39-1に示すようなレポート出力を生成できます。
図39-1 PL/SQLレポートの出力
画像の説明
概要
- PL/SQLをレポートに取り込むには、様々な方法があります。これまでに、簡単なPL/SQL式を使用して値を計算する式列を作成したり、PL/SQLを使用して条件に応じてメール・ラベルの書式を決めるフォーマット・トリガーを作成しています。ここでは、外部ライブラリ、ローカル・ファンクションおよびプロシージャを作成します。
- 外部PL/SQLライブラリは、名前付きPL/SQLファンクションとプロシージャを含むモジュールです。これらはデータベースまたはファイルに格納でき、任意のレポートから参照できるだけでなく他のOracle製品からも参照できます。外部ライブラリを使用することで、レポート、フォーム、グラフなどで、頻繁に使用するPL/SQL構文を何度も入力する必要がなくなります。このため、同じPL/SQLコードを複数保持するという問題も回避できます。
- ローカルPL/SQLは、名前付きPL/SQLファンクションとプロシージャで構成され、レポート定義に格納されます。ローカルPL/SQLを参照できるのは、レポート内のオブジェクト（グループ・フィルタ、式列、フォーマット・トリガーなど）のみです。ただし、1つの場所にPL/SQLを保持できるという点では、利便性は変わりません。
データ・リレーションシップ
このレポートでは、1つの問合せを使用します。また、外部ライブラリに格納されているファンクション、レポートレベルのファンクション、2つの式列、および空白を挿入する前に表示するレコード数を決めるパラメータを追加します。
レイアウト
このレポートでは、表形式のレイアウト・スタイルを少し修正したものを使用します。
使用例
この例では、外部PL/SQLライブラリとPL/SQLをレポートで使用して、フォーマットを変更してレコード間に間隔を追加したり、各従業員の報酬合計を計算します。データ・モデル・ビューで問合せを手動で作成し、ペーパー・レイアウト・ビューでレポートのレイアウトを変更します。式列、サマリー列、およびサマリー列とユーザー・パラメータを使用するフォーマット・トリガーを作成します。
このサンプル・レポートの作成過程では、次を行います。
PL/SQLレポートのサンプルを表示するには、サンプル・フォルダplsqlを開き、Oracle Reportsのサンプルplsql.rdfを開きます。このファイルの開き方の詳細は、「はじめに」の「サンプル・レポートへのアクセス」を参照してください。
39.1 この例の前提条件
この章の例を作成するには、Oracle Database付属のHuman Resourcesサンプル・スキーマ（HR）に対するアクセス権が必要です。このサンプル・スキーマに対するアクセス権の有無が不明のときは、データベース管理者に問い合せてください。
39.2 新しいPL/SQLライブラリの作成
この項の手順では、新しいPL/SQLライブラリを作成し、次にライブラリに含まれるファンクションを作成する方法について説明します。
ライブラリを作成するには:
- Reports Builderを起動します。すでに起動している場合は、「ファイル」→「新規」→「レポート」を選択します。
- 「ようこそ」ダイアログ・ボックスまたは「新規レポート」ダイアログ・ボックスで、「新規レポートを手動で作成」を選択して「OK」をクリックします。
- オブジェクト・ナビゲータで、「ファイル」→「新規」→「PL/SQLライブラリ」を選択します。
オブジェクト・ナビゲータのレポート名の下の「PL/SQLライブラリ」ノードの下に、新しいライブラリが表示されます。
- 「PL/SQLライブラリ」ノードが開かれていない場合は、新しいライブラリのノードを開いて、「プログラム・ユニット」と「連結ライブラリ」という2つのサブノードを表示します。
- 「プログラム・ユニット」ノードをクリックし、「編集」→「作成」を選択します。
- 「新規プログラム・ユニット」ダイアログ・ボックスで、「名前」フィールドに
BONUS_PAYと入力します。
- 「ファンクション」を選択し、「OK」をクリックしてPL/SQLエディタを表示します。
- PL/SQLエディタで、テンプレートを使用して次のPL/SQLコードを入力します。
FUNCTION BONUS_PAY(JOB_ID IN CHAR, SAL IN NUMBER, COMM_PCT IN NUMBER) RETURN NUMBER
IS
BEGIN
IF JOB_ID != 'SA_REP' THEN
RETURN (SAL * 0.15);
ELSE
IF SAL * COMM_PCT >= 500 THEN
RETURN ((SAL + SAL * COMM_PCT) * 0.15);
ELSE
RETURN ((SAL + SAL * COMM_PCT) * 0.10);
END IF;
END IF;
END;
注意
提供されているテキスト・ファイルplsql_code.txtから、このコードをコピーして貼り付けると、コードを入力できます。
|
- 「コンパイル」をクリックします。
- コンパイル・エラーが発生した場合は、作成したコードがサンプルのRDFファイルまたはこの章のコードと同じになるように修正して、再度コンパイルします。
- コンパイル・エラーがなくなったら、「閉じる」をクリックします。
新しいファンクションがオブジェクト・ナビゲータに表示されます。
- 「ファイル」→「保存」を選択して、新しいファンクションを保存します。
- 「ライブラリ保存」ダイアログ・ボックスで、
bonus.pllと入力して「ファイル・システム」が選択されていることを確認し、「OK」をクリックします。
- オブジェクト・ナビゲータで、作成した「MODULE1」レポートの下にある「連結ライブラリ」ノードをクリックします。必ずこのノードを選択してください。「PL/SQLライブラリ」ノードの下にあるノードではありません。
- 「編集」→「作成」を選択します。
- 「連結ライブラリ」ダイアログ・ボックスで、「ライブラリ」フィールドに
bonus.pllと入力します。
注意
bonus.pllを別のディレクトリに保存した場合は、「参照」をクリックして、ファイル・システム上の保存場所を見つけます。参照する前に「ファイル・システム」が選択されていることを確認します。
|
- ライブラリ名が「library」フィールドに表示されたら、「連結」をクリックしてライブラリを連結します。
注意
名前にパスが含まれるライブラリを連結すると、パス名は移植できないことが通知され、パスを削除するオプションを選択できます。パス指定をそのまま使用した場合、その特定の場所でのみライブラリが検索されます。そのため、ライブラリを移動すると、Reports Builderではライブラリが見つからなくなります。パスを削除した場合は、ライブラリを移動しても、標準の検索パスを使用してライブラリが検索されます。
|
この時点で、オブジェクト・ナビゲータのオブジェクト構造は次のようになっています。
図39-2 オブジェクト・ナビゲータ
画像の説明
- レポートを
plsqlreport_<自分のイニシャル>.rdfという名前で保存します。
39.3 レポート定義の作成
この項の手順では、レポートを定義する問合せと式列を作成し、作成したbonus.pll外部PL/SQLライブラリのコードをコールする方法について説明します。
ファンクションに依存する式列などもあるため、説明されている順序に従ってオブジェクトを作成することをお薦めします。
39.3.1 問合せの作成
この項の手順では、レポートに必要なデータを取得する問合せを作成する方法について説明します。
問合せを作成するには:
- オブジェクト・ナビゲータで、「データ・モデル」ノードの横のビュー・アイコンをダブルクリックして、データ・モデル・ビューを表示します。
- データ・モデル・ビューで、「挿入」→「問合せ」を選択してデータ・ウィザードを表示します。
- 「ようこそ」ページが表示されたら、「次へ」をクリックします。
- 「問合せ」ページで、問合せ名はデフォルトのままにして、「次へ」をクリックします。
- 「データ・ソース」ページで、「SQL問合せ」を選択し、「次へ」をクリックします。
- 「データ」ページの「データ・ソース定義」フィールドに次の
SELECT文を入力します。
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY,COMMISSION_PCT
FROM EMPLOYEES
ORDER BY LAST_NAME
注意
この問合せは、次のいずれかの方法で入力できます。
- 提供されているテキスト・ファイル
plsql_code.txtからコードをコピーして、「データ・ソース定義」フィールドに貼り付けます。
- 「クエリー・ビルダー」をクリックして、コードを入力せずに問合せを作成します。
- 「データ・ソース定義」フィールドにコードを入力します。
|
- 「次へ」をクリックします。
注意
データベースにまだ接続していない場合は、「クエリー・ビルダー」または「次へ」をクリックしたときに、データベースに接続するよう求められます。この例に該当するスキーマが含まれるデータベースに接続してください。第39.1項「この例の前提条件」に、この例のサンプル・スキーマの要件に関する説明があります。
|
- 「グループ」ページで「次へ」をクリックします。
- 「終了」をクリックして、データ・モデル・ビューに最初の問合せを表示します。表示は次のようになります。
図39-3 PL/SQLレポートのデータ・モデル・ビュー
画像の説明
- レポートを保存します。
39.3.2 ボーナスを計算する式列の作成
この項の手順では、PL/SQLファンクションを使用して、各従業員のボーナスを計算する式列を作成する方法について説明します。
BONUS式列を作成するには:
- データ・モデル・ビューで、グループ「G_FIRST_NAME」をクリックします。次に、グループに列を追加できるように、下部にあるサイズ変更ハンドルをクリックして下方向にドラッグします。次の例のようになります。
図39-4 G_FIRST_NAMEが拡張されたデータ・モデル
画像の説明
- ツール・パレットの「式列」ツールをクリックし、「G_FIRST_NAME」グループ内でクリックして式列を作成します。
図39-5 名前付けされていない式列を含むデータ・モデル
画像の説明
- 新しい式列オブジェクト（CF_1）をダブルクリックしてプロパティ・インスペクタを表示し、次のプロパティを設定します。
- 「一般情報」で、「名前」プロパティを「BONUS」に設定します。
- 「プレースホルダ/式」で、「PL/SQL式」プロパティ・フィールドをクリックしてPL/SQLエディタを表示します。
- PL/SQLエディタで、テンプレートを使用して次のPL/SQLコードを入力します。
function BONUSFormula return Number is
begin
return BONUS_PAY(:JOB_ID, :SALARY, :COMMISSION_PCT);
end;
注意
提供されているテキスト・ファイルplsql_code.txtから、このコードをコピーして貼り付けると、コードを入力できます。このコードはBonus式列用です。
|
- 「コンパイル」をクリックします。
注意
コンパイル・エラーが発生した場合は、入力したコードと提供されているコードを比較してください。
|
- コンパイル・エラーがない場合は、「閉じる」をクリックして、データ・モデル・ビューにレポートのデータ・モデルを表示します。表示は次のようになります。
図39-6 BONUS式列作成後のデータ・モデル
画像の説明
- レポートを保存します。
39.3.3 報酬合計を計算するレポートレベルのファンクションの作成
この項の手順では、営業担当者の報酬合計（列SALARYの値にCOMMとBONUSを加算）と残りの従業員の報酬合計（SALARYにBONUSを加算）を返すファンクションを記述する方法について説明します。
- オブジェクト・ナビゲータで、「プログラム・ユニット」ノードをクリックし、「編集」→「作成」を選択します。
- 「新規プログラム・ユニット」ダイアログ・ボックスで、「名前」フィールドに
FINAL_CALCと入力します。
- 「ファンクション」を選択し、「OK」をクリックします。
- PL/SQLエディタで、テンプレートを使用して次のPL/SQLコードを入力します。
FUNCTION FINAL_CALC RETURN NUMBER IS
BEGIN
IF :JOB_ID = 'SA_REP' THEN
RETURN (:BONUS + :SALARY + :COMMISSION_PCT * :SALARY);
ELSE
RETURN (:BONUS + :SALARY);
END IF;
END;
注意
提供されているテキスト・ファイルplsql_code.txtから、このコードをコピーして貼り付けると、コードを入力できます。このコードはFinal Calc用です。
|
- 「コンパイル」をクリックします。
- エラーが発生せずにコードがコンパイルされたら、「閉じる」をクリックします。
新しいファンクション「FINAL_CALC」は、オブジェクト・ナビゲータで次のように表示されます。
図39-7 FINAL_CALCファンクション作成後のオブジェクト・ナビゲータ
画像の説明
- レポートを保存します。
39.3.4 報酬合計用の第2の式列の作成
この項の手順では、報酬合計を計算する別の式列を作成する方法について説明します。レポートレベルのファンクションFINAL_CALCによって計算される値が、列TOTAL_COMPに割り当てられます。式列の作成方法について不明な点がある場合は、第39.3.2項「ボーナスを計算する式列の作成」を参照してください。
TOTAL_COMP式列を作成するには:
- データ・モデル・ビューで、前述の手順に従って、「BONUS」式列の下に第2の式列を作成します。
- 新しい式列オブジェクト（CF_1）をダブルクリックしてプロパティ・インスペクタを表示し、次のプロパティを設定します。
- 「一般情報」で、「名前」プロパティを「TOTAL_COMP」に設定します。
- 「プレースホルダ/式」で、「PL/SQL式」プロパティ・フィールドをクリックしてPL/SQLエディタを表示します。
- PL/SQLエディタで、テンプレートを使用して次のPL/SQLコードを入力します。
function TOTAL_COMPFormula return Number is
begin
return FINAL_CALC;
end;
注意
提供されているテキスト・ファイルplsql_code.txtから、このコードをコピーして貼り付けると、コードを入力できます。このコードはTotal Comp式列用です。
|
- 「コンパイル」をクリックします。
- エラーが発生せずにコードがコンパイルされたら、「閉じる」をクリックして、データ・モデル・ビューにレポートのデータ・モデルを表示します。表示は次のようになります。
図39-8 BONUS式列とTOTAL_COMP式列作成後のデータ・モデル
画像の説明
- レポートを保存します。
39.4 レポート・ブロック・ウィザードを使用したレポート・レイアウトの作成
必要な式列とファンクションの作成が終了したら、レポートのレイアウトを作成できます。
レポート・レイアウトを作成するには:
- ツールバーの「ペーパー・レイアウト」ボタンをクリックして、ペーパー・レイアウト・ビューを表示します。
- ペーパー・レイアウト・ビューで、「挿入」→「レポート・ブロック」を選択します。
- レポート・ブロック・ウィザードの「スタイル」ページで、「表形式」を選択して「次へ」をクリックします。
- 「グループ」ページで、「使用可能グループ」リストの「G_FIRST_NAME」をクリックし、「下」をクリックして印刷方向を指定し、「表示グループ」リストに移動して「次へ」をクリックします。
- 「フィールド」ページで、「>>」ボタンをクリックして、すべてのフィールドを「表示フィールド」リストに移動します。「表示フィールド」リストの内容は次のようになります。
図39-9 「表示フィールド」リスト
画像の説明
注意
フィールドが正しい順序で表示されていない場合は、該当のフィールド名をクリックしてリスト内の適切な位置にドラッグします。
|
- 「次へ」をクリックします。
- 「ラベル」ページで、ラベルを次のように変更し、「次へ」をクリックします。
- 「テンプレート」ページで「終了」をクリックして、ペーパー・レイアウト・ビューにレポート・レイアウトを表示します。表示は次のようになります。
図39-10 PL/SQLサンプル・レポートのペーパー・レイアウト・ビュー
画像の説明
- ツールバーの「ペーパー・レイアウトを実行」ボタンをクリックしてレポートを実行し、ペーパー・デザイン・ビューに表示します。
- ペーパー・デザイン・ビューで、ツールバーの「フレックス・オフ」ボタンをクリックします。
- 「Last Name」ラベルを削除します。
- 「First Name」ラベルのテキストを「Name」に変更します。
- 新しい「Name」ラベルの幅を姓名用の両列にまたがるように調整します。
- 姓名用の両列の間に1文字分の空白が表示されるように、両列のサイズを調整します。
- オブジェクト・ナビゲータで、「F_FIRST_NAME」フィールドの横のプロパティ・アイコンをダブルクリックしてプロパティ・インスペクタを表示し、次のプロパティを設定します。
- 「標準レイアウト」で、「垂直拡張度」プロパティを「固定」に設定し、「水平拡張度」プロパティを「可変」に設定します。
- 「F_LAST_NAME」フィールドに対して、同じ手順を繰り返します。
- ツールバーの「ペーパー・レイアウト」ボタンをクリックして、ペーパー・レイアウト・ビューを表示します。
- ペーパー・レイアウト・ビューで、ツール・パレットの「枠」ツールをクリックします。
- 「F_FIRST_NAME」フィールドと「F_LAST_NAME」フィールドを囲む枠を描画します。
- 枠を選択した状態で、「ツール」→「プロパティ・インスペクタ」を選択し、プロパティ・インスペクタを表示して次のプロパティを設定します。
- 「一般情報」で、「名前」プロパティを「M_NAME」に設定します。
- ペーパー・レイアウト・ビューで、ツールバーの「フレックス・オフ」が選択されていることを確認します。
- 枠を選択し、その枠が「F_FIRST_NAME」フィールドと「F_LAST_NAME」フィールドの両方を囲むまで、「レイアウト」→「背面へ」を選択します。
|
ヒント
オブジェクト・ナビゲータでフィールドを確認しながら、「レイアウト」→「背面へ」を選択できます。2つのフィールドが「M_NAME」のサブノードになったら、操作を停止します。
|
操作が完了すると、オブジェクト・ナビゲータは次のようになります。
図39-11 M_NAME繰返し枠作成後のオブジェクト・ナビゲータ
画像の説明
- ツールバーの「ペーパー・デザイン」ボタンをクリックして、ペーパー・デザイン・ビューにレポートを表示します。
- ペーパー・デザイン・ビューで、次の列の下の値を[Shift]キーを押しながらクリックします。
- Salary
- Commission
- Bonus
- Total Compensation
- ツールバーの「通貨」ボタンをクリックして、数値に"$"（日本語環境では"¥"）を追加します。
- 「10進桁を追加」ボタンを2回クリックして、数値に小数点以下2桁を追加します。
- [Shift]キーを押しながら、「Salary」、「Commission」、「Bonus」および「Total Compensation」の列ヘッダーをクリックし、それらを選択します。
- 「右揃え」ボタンをクリックして、列を右揃えにします。
レポートの表示は次のようになります。
図39-12 変更したレポートのペーパー・デザイン・ビュー
画像の説明
- レポートを保存します。
39.5 レコード間への垂直間隔の追加
レポートをより見やすくするために、特定数のレコード間に空白を追加できます。このためには、まず、空白を挿入する前に表示するレコード数を決定するパラメータを作成します。次に、レコード数をカウントするサマリー列をデータ・モデルに作成します。さらに、垂直拡張度が可変になるように、レポートのペーパー・レイアウトを変更します。最後に、ユーザー指定のレコード数の間に空白を挿入するフォーマット・トリガーを作成します。
39.5.1 ユーザー・パラメータの作成
この項で作成するパラメータによって、空白を挿入する前に表示するレコード数が決定されます。このパラメータはユーザー・パラメータであるため、実行時にその値を変更できます。
ユーザー・パラメータを作成するには:
- オブジェクト・ナビゲータの「データ・モデル」ノードで、「ユーザー・パラメータ」ノードをクリックします。
- 「編集」→「作成」を選択して、「ユーザー・パラメータ」ノードの下に新しいユーザー・パラメータを作成します。
- プロパティ・インスペクタが表示されていない場合は、新しいユーザー・パラメータ（P_1）を右クリックして「プロパティ・インスペクタ」を選択し、プロパティ・インスペクタを表示してプロパティを設定します。
ユーザー・パラメータがオブジェクト・ナビゲータに表示されます。
図39-13 オブジェクト・ナビゲータのユーザー・パラメータ
画像の説明
- レポートを保存します。
39.5.2 レコード数をカウントするサマリー列の作成
この項では、従業員レコードの数をカウントするサマリー列をデータ・モデルに作成します。この情報は、空白を挿入する場所を決定するためのフォーマット・トリガーで使用されます。
サマリー列を作成するには:
- オブジェクト・ナビゲータで、「データ・モデル」ノードの横のビュー・アイコンをダブルクリックして、データ・モデル・ビューを表示します。
- データ・モデル・ビューで、ツール・パレットの「サマリー列」ツールをクリックし、G_FIRST_NAMEグループのTOTAL_COMP式列の下をクリックして、新しいサマリー列を作成します。
図39-14 新しいサマリー列作成後のデータ・モデル
画像の説明
- 新しいサマリー列オブジェクト（CS_1）をダブルクリックしてプロパティ・インスペクタを表示し、次のプロパティを設定します。
- 「一般情報」で、「名前」プロパティを「CNT_COLUMN」に設定します。
- 「サマリー」で、「ファンクション」プロパティを「カウント」、「ソース」プロパティを「FIRST_NAME」に設定します。
- レポートを保存します。
39.5.3 レイアウトの変更
Reports Builderで垂直間隔を挿入できるようにするには、レポートのレイアウトを変更する必要があります。
垂直拡張度を追加するには:
- ツールバーの「ペーパー・レイアウト」ボタンをクリックして、ペーパー・レイアウト・ビューを表示します。
- ツールバーの「フレックス・オン」ボタンをクリックします。
- ペーパー・レイアウト・ビューで、「G_FIRST_NAME」に関連付けられている繰返し枠をクリックします。
注意
ペーパー・レイアウト・ビューで繰返し枠が見つからない場合は、オブジェクト・ナビゲータで「R_G_FIRST_NAME」をクリックします。関連付けられている繰返し枠が、ペーパー・レイアウト・ビューで選択されます。
|
- 枠の中央ハンドルをクリックして枠を下方向にドラッグし、追加の空白を作成します。この追加の空白は、レコード間に挿入される空白よりも少し大きくします。
- 繰返し枠を選択した状態で、「ツール」→「プロパティ・インスペクタ」を選択し、プロパティ・インスペクタを表示して次のプロパティを設定します。
- 「標準レイアウト」で、「垂直拡張度」プロパティを「可変」に設定します。
- ペーパー・レイアウト・ビューで、ツール・パレットの「四角形」ツールを選択し、新しく作成した空白でフィールドの下に四角形を描画します。
- 新しい四角形には塗りつぶしも線も選択せず、見えないようにしてください。
次のイメージは、見えない四角形を作成した新しいレイアウトを示します。
図39-15 垂直間隔を追加したレイアウト
画像の説明
- レポートを保存します。
39.5.4 フォーマット・トリガーの作成
レイアウトを調整したら、前の項で作成した新しいボイラープレート四角形を基にフォーマット・トリガーを作成できます。このフォーマット・トリガーによって、ユーザー・パラメータで指定されたレコード数ごとに、その後に空白が表示されます。
ボイラープレート四角形にフォーマット・トリガーを作成するには:
- ペーパー・レイアウト・ビューで四角形を選択した状態でキーボードの[F11]を押して（または、「ツール」→「PL/SQLエディタ」を選択して）、PL/SQLエディタを表示します。
- PL/SQLエディタで、テンプレートを使用して次のPL/SQLコードを入力します。
function B_1FormatTrigger return boolean is
begin
If :CNT_COLUMN mod :SPACE = 0 then
return(TRUE);
else
return(FALSE);
end if;
end;
注意
提供されているテキスト・ファイルplsql_code.txtから、このコードをコピーして貼り付けると、コードを入力できます。このコードはフォーマット・トリガー用です。
|
- 「コンパイル」をクリックします。
- エラーが発生せずにコードがコンパイルされたら、「閉じる」をクリックします。
- レポートを保存します。
39.6 ペーパーを対象としたレポートの実行
空白を挿入してフォーマット・トリガーを作成したら、レポートには5レコードごとに（またはパラメータ・フォームで指定した任意のレコード数ごとに）空白が挿入されます。
レポートを実行するには:
- ツールバーの「ペーパー・レイアウトを実行」ボタンをクリックします。パラメータ・フォームが表示されたら、ツールバーの「実行」ボタンをクリックします。
次のようなレポートがペーパー・デザイン・ビューに表示されます。
図39-16 最終的なPL/SQLサンプル・レポート
画像の説明
39.7 まとめ
これで、すべての作業が完了しました。外部PL/SQLライブラリを使用して従業員のボーナスを計算するレポートは、正常に作成されました。この外部PL/SQLライブラリは、それを参照するだけで他のレポートでも使用できます。この章で学習した内容は次のとおりです。
- 外部PL/SQLライブラリを作成し、使用する。
- レポート・ウィザードでデフォルト・レイアウトを作成する。
- ユーザー指定のレコード数の間に垂直間隔を追加する。
この例で使用されているウィザード、ビュー、プロパティの詳細は、Oracle Reportsオンライン・ヘルプを参照してください。このヘルプは、第3.1.1項「Oracle Reportsオンライン・ヘルプの使用」で説明しているとおり、Reports BuilderまたはOracle Technology Network（OTN）から表示できます。
