掲載元 2014年5/6月

テクノロジー：PL/SQL

SQLをOracle Application Expressで記述する

Steven Feuerstein著

記述するコードを最小限に抑え、配置場所を慎重に選び再配置して、可能な限りパッケージ化する。

前号のOracle Magazineでは、Oracle Application ExpressアプリケーションのPL/SQLコードのもっともよい記述方法についてアドバイスしました。この記事では、この同じアプリケーションのSQL文を記述する方法、タイミング、場所について説明します。

言語は違っても課題は同じ

重要なので、前回の記事に書いた3つのガイドラインをここに再掲します（その後、SQLのコンテキストについて詳しく説明します）。

• アプリケーション・ビルダー（Oracle Application Expressのアプリケーション構築用UI）で記述すべきSQL文は、レポートおよび表を移入する問合せのみです。その場合も、問合せをできる限りシンプルにするために、ビューを使用したり、一部の複雑な使用例の場合はテーブル・ファンクションを使用したりする必要があります。
• できるだけコードの反復を避けます。このアドバイスはOracle Application Expressに限ったものではなく、高品質のプログラミング全体に該当するもっとも重要なガイドラインの１つです。
• Oracle Application Expressアプリケーション（以降、この記事では単に"アプリケーション"と呼びます）内のコード量を最小限に抑えます。なるべく多くのコードをPL/SQLパッケージに移動します。

この記事では1つ目のガイドラインを中心に説明しますが、このガイドラインは他の2つのガイドラインとも密接に関連し、それらを通じて実現されます。

今回もPL/SQL Challengeアプリケーションを参考にしながら、上記ガイドラインに準拠している例と違反している例の両方を示していきます。

すべてのDMLをPL/SQLプロシージャに移動する

まずは、アプリケーションでのユーザー入力に対する応答として実行する必要があるデータ操作言語（DML）文（具体的にはINSERT、UPDATE、およびDELETE）の実装方法を見てみましょう。

Oracle Application ExpressアプリケーションにINSERT文またはUPDATE文を記述するのは実に簡単です。PL/SQLはSQL文のコンパイルと実行をネイティブにサポートしているので、PL/SQLプロセスを作成して適切なフィールドにDML文を入力するだけです。

ユーザーの操作に応じてデータベース表の内容を変更しないアプリケーションはほとんどないため、アプリケーションでは多くのDML文を実行することが必要になるでしょう。アプリケーション・ビルダー内には決して直接DML文を記述しないことをお勧めします。代わりに、次のようにする必要があります。

• できるだけ多くのDML文をOracle Application Expressで生成すること。たとえば、ユーザーが表に対してDML操作を実行できるページを作成する必要がある場合は、"Form on a Table for View"として領域を定義したあとに、Oracle Application Expressで行の自動処理機能を使用してすべてのDMLを実装することができます。表に列を追加すると領域に新しい項目が追加され、アプリケーションのDML文は、この新しい列が組み込まれるようにOracle Application Expressによって調整されます。
• パッケージ・プロシージャを定義し、プロシージャ内にDML文を配置し、アプリケーションのプロセス内からプロシージャをコールすること。このようにすると、アプリケーションへの影響を最小限に抑えながらDML文を変更するのが容易になります（DML文の追加も容易になりますが、これについては後ほど実例を示します）。

DMLをPL/SQLパッケージに移動する理由としてもっとも説得力があるのは、ものごと（特にトランザクション）は時間の経過とともに複雑になって行く、というプログラミングにつきものの基本的な現実です。今日、表T1への1つの挿入だったものが、次のように徐々に姿を変えて行きます。

そして、1つの場所だけで使用する必要があるものと思っていると、アプリケーションの2つ目、3つ目の場所でも使用する必要があることに気付きます。

そのため、このINSERT INTO T1を複数の場所に記述していると、この単純なトランザクションを3ステップのトランザクションに拡張する時には、拡張したトランザクションをコピーしてそれぞれの場所に貼り付けることになります。最終的には、デバッグ、管理、最適化、機能強化が極めて困難なアプリケーションができあがります。

PL/SQL ChallengeのWebサイトは息子のEliと私がOracle Application Expressで構築して運営しているのですが、私はこのWebサイトでこれとまったく同じ問題にぶつかりました。このWebサイトのおもなアクティビティは、Oracleのさまざまなテクノロジーに関する知識をテストするクイズを出題することです。ユーザーは自分の理解力を確認するために、クイズが終了したあとQuiz Detailsページにアクセスし、自分の成績を確認します。

私は、各クイズの閲覧実績をプレーヤー別にqdb_quiz_visits表に記録します。そのため、Quiz Detailsページに対するOracle Application Expressページ・プロセスの最初の反復処理では、単純に次の文を実行しました。

INSERT INTO qdb_quiz_visits 
           (user_id, quiz_id)
     VALUES (:p659_user_id, 
             :p659_quiz_id);

ところが、このプロセスを実装したあと、プレーヤーがそのクイズの調査結果を確認したときもクイズの閲覧実績を記録する必要があることに気付きました。そこで、別のページに移動し、速やかにコピー/貼付けを実行して（ページ番号を置き換える部分の確認と編集も慎重に行い）、次に示す新しいページ・プロセスを作成しました。

INSERT INTO qdb_quiz_visits 
           (user_id, quiz_id)
     VALUES (:p740_user_id, 
             :p740_quiz_id);

ページ番号以外は何も違いません。まったくもって単純明快です。それなのになぜ、わざわざパッケージの中にプロシージャを作ってそこにINSERTを配置する必要があるのでしょうか。アクセス履歴を記録するプロセスが決して変わらないのであれば、そのようなことをする理由は1つもないでしょう。

しかし実際には、ほぼすべての要件と同様、PL/SQL ChallengeのWebサイトにポイントを追加したとき、このプロセスの変更が必要になりました。学習意欲を刺激するために、クイズを閲覧したプレーヤーには毎回5ポイントを進呈するようにします。

ポイントを付与するコードの複雑さは、qdb_quiz_visits表へのINSERTと大して変わりません。

INSERT INTO qdb_points 
            (user_id,
             activity_date,
             activity_id,
             activity_key_value,
             points)
     VALUES ( :p659_user_id,
             SYSDATE,
             'QUIZVIEW',
             :p659_quiz_id,
             5); 

そして、そこには罠、すなわちとてつもない誘惑が待ち構えています。すでにINSERT文を複製しているのですから、今回も同じ道を進み、この新しいINSERT文を2つの場所に追加すべきでしょうか。

幸いにも、解決策（大混乱を避ける方法）は明白です。すなわち、すべてのトランザクション・ロジックを1つのプロシージャの裏に隠し、すべての項目参照をパラメータに変更し、必要に応じてこのプロシージャをコールします。次に示すのが、閲覧実績とポイントの挿入を隠したrecord_quiz_viewプロシージャです。

PACKAGE BODY qdb_quiz_mgr
IS
   PROCEDURE record_quiz_view (
      user_id_in   IN PLS_INTEGER,
      quiz_id_in   IN PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_quiz_visits 
             (user_id, quiz_id)
           VALUES (user_id_in, quiz_id_in);

      INSERT INTO qdb_points (user_id,
             activity_date,
             activity_id,
             activity_key_value,
             points)
           VALUES (user_id_in,
             SYSDATE,
             'QUIZVIEW',
             quiz_id_in,
             5);
   END;
END;

すると、このプロセス・コードは、次のようにすっきりとしたものになります。

BEGIN
   qdb_quiz_mgr.record_quiz_view (
      user_id_in   => :p659_user_id,
      quiz_id_in   => :p659_quiz_id);
END;

DML文をプロシージャの中に隔離するために、ほんの少し余計に時間をかけることは、退職後の計画のために貯金するようなものです。現時点では少し困った（お金が少し足りない、時間が少し足りない）と感じるかもしれませんが、コードの変更や強化がはるかに容易になるのですから、将来はお金と時間に余裕ができます。

でも、心配ご無用。65歳になるまで待たなくても、"DMLを隠した"効果を感じることができるでしょう。アプリケーションの変化はとても激しいですし、最初から完璧なアプリケーションを開発できる人はほとんどいないのですから、効果はほぼすぐに表れます。

では、PL/SQL Challengeのrecord_quiz_viewプロシージャを見てみましょう。クイズの詳細を閲覧するたびに5ポイントを獲得できるのであれば、同じクイズを何度もクリックして作為的にポイント合計を増やすプレーヤーもいるのではないか、ということに私はすぐ気付きました。そのため、その日初めて閲覧したときだけポイントを付与するように、このプロシージャを修正する必要がありました。

きっと、皆さんもご自分のアプリケーションで似たような経験をされていることでしょう。時間が経つに連れてあらゆることが複雑になります。事前に何もかも想定することなどほとんどありません。誰でも必ずコードを変更しに戻ることになるのです。そして、そのコードにユーザーが起動できるトランザクションが含まれている場合は、どうしてもコードの修正が必要になります。

INSERT文を隠した例はすでに紹介しました。挿入を実行する場合は、常に新しい行が作成されます。しかし、更新と削除を実行する場合は既存の行が変更され、更新内容が失われる可能性が出てきます。ユーザーAとユーザーBが、Webサイト上のあるページの同じデータ行を問合せ、その後2人とも変更を行うとします。ユーザーAが「Submit」ボタンを押すと、変更内容が保存されます。ユーザーBが「Submit」を押すと、ユーザーBの変更が実行され、ユーザーAが保存した変更内容は上書きされます。

特定のページで行の自動処理を利用できず、代わりに独自の更新コードや削除コードを記述する場合は、必ず次の手順に従ってください。

Create Package with Methods on Tablesにアクセスするには、「SQL Workshop」→「Utilities」→「Methods on Tables」の順にクリックします（図1）。そうすると、DML文を隠す目的にも、ユーザーの行った変更が失われないようにする目的にも、これらのサブプログラムを使用できます。

図1：Create Package with Methods on Tablesの場所

複雑な問合せをビューに隠す

ここまでで、挿入、更新、削除への対処が終わりました。では、ほかのDML文、つまりSELECTについて検討しましょう。推奨事項は次の2つです。

プレーヤーがクイズに解答した日付がPL/SQL Challengeのプロセスで必要だとします。アプリケーション・ビルダー内に次のブロックを記述することもできます。

DECLARE
   l_date   DATE;
BEGIN
   SELECT taken_on
     INTO l_date
     FROM qdb_quiz_answers
    WHERE     user_id = :p659_user_id
          AND quiz_id = :p659_quiz_id;

   IF l_date < SYSDATE - 2
   THEN
      ....
   END IF;
END;

代わりに、問合せをファンクションに移動し、プロセス・コードを次のようにまとめる必要があります。

CREATE OR REPLACE PACKAGE BODY 
qdb_quiz_mgr
IS
   FUNCTION quiz_taken_on (
      user_id_in   IN PLS_INTEGER,
      quiz_id_in   IN PLS_INTEGER)
      RETURN DATE
   IS
      l_date   DATE;
   BEGIN
      SELECT taken_on
        INTO l_date
        FROM qdb_quiz_answers
       WHERE     user_id = user_id_in
             AND quiz_id = quiz_id_in;

      RETURN l_date;
   END;
END;
/

DECLARE
   l_date   DATE;
BEGIN
   l_date :=
      qdb_quiz_mgr.quiz_taken_on (
         :p659_user_id,
         :p659_quiz_id);

   IF l_date < SYSDATE - 2   
   THEN
      ...
   END IF;
END;
/

次に、複数の行をフェッチする必要がある場合は、次に示すように、BULK COLLECTを使用して配列を返します。

CREATE OR REPLACE PACKAGE qdb_quiz_mgr
IS
  TYPE answers_t
    IS TABLE OF qdb_quiz_answers%ROWTYPE;

  FUNCTION quizzes_taken_by (
    user_id_in   IN PLS_INTEGER)
  
RETURN answers_t;
END;
/
CREATE OR REPLACE PACKAGE BODY 
qdb_quiz_mgr
IS
   FUNCTION quizzes_taken_by (
      user_id_in   IN PLS_INTEGER)
      RETURN answers_t
   IS
      l_answers   answers_t;
   BEGIN
        SELECT *
          BULK COLLECT INTO l_answers
          FROM qdb_quiz_answers
         WHERE user_id = user_id_in
      ORDER BY taken_on;

      RETURN l_answers;
   END;
END;
/

適切に正規化してリレーショナル表を設計すると、表が大量にできあがり、同じ結合を何度も実行することが必要になります。たとえば、PL/SQL ChallengeのWebサイトでは、クイズごとにトピック（関連するOracle機能）とクイズ形式（選択肢が複数ある、正誤を判定するなど）が決まっています。その結果、気が付くと次のような問合せを大量に記述していました。

SELECT qz.question_text the_question,
       t.title feature,
       qf.text quiz_format
  FROM qdb_quizzes qz,
       qdb_topics t,
       qdb_quiz_formats qf
 WHERE     qz.topic_id = t.topic_id
       AND qz.quiz_format_id =
              qf.quiz_format_id;

私の指はすぐにWHERE句の入力に飽きてしまうし、私の内なる声はしつこく問い続けます。「どうしてまたこれを書いているのだろうか。もっとよいやり方はないのだろうか」と。

ええ、ありますとも。そこで、この3方向の結合を何度も記述する代わりに、次のビューを作成します。

CREATE OR REPLACE VIEW qdb_quizzes_v
AS
   SELECT qz.question_text the_question,
          t.title feature,
          qf.text quiz_format
     FROM qdb_quizzes qz,
          qdb_topics t,
          qdb_quiz_formats qf
    WHERE     qz.topic_id = t.topic_id
          AND qz.quiz_format_id =
                 qf.quiz_format_id;

すると、レポートの問合せは次のようにシンプルになります。

SELECT * FROM qdb_quizzes_v

1週間後に、いずれかの表にある別の列をレポートに含める必要が出てきたとしても、その列をビューに追加すれば、ほら、このとおり。レポートで使用できるようになります。

でも、気を付けてください。結合を含むビューをたくさん作り、それらのビューを相互に結合すると、大量の無駄な作業を実行することになる場合があります。qdb_quizzes_vビューに使用されているトピック表を見てみましょう。トピックは必ずドメイン（SQLやPL/SQLといったテクノロジー領域）に含まれます。また、PL/SQL Challengeでは、Oracleドキュメントへのリンクをはじめ、トピックに関連する一連のリソースを提供しています。

そこで、次のような便利なビューを作成します。

CREATE OR REPLACE VIEW qdb_topic_details_v
AS
   SELECT d.domain_name technology,
          t.title feature,
          r.title doc_title,
          r.url   doc_link
     FROM qdb_topics t,
          qdb_resources r,
          qdb_domains d
    WHERE     t.topic_id = r.topic_id
          AND r.resource_type =
                 'FEATURE_DOC'
          AND t.domain_id = d.domain_id;

そして、これをTopic Detailsレポートで使用します。これはまったく妥当なことです。ところが翌日になって、クイズの情報にドキュメントのURLとドメイン名を結合することが必要になります。次の2つのビューを結合するより簡単な方法があるでしょうか。

SELECT t.technology,
       q.question_text,
       r.doc_link
  FROM qdb_quizzes_v q,
       qdb_topic_details_v t
 WHERE q.topic_id = t.topic_id;

さらにこれを実行すると、qdb_topics表を2回結合するという代償を払うことになります。これによってパフォーマンス上の問題が発生するでしょうか。このような単純な例では、おそらく発生しないでしょう。しかし、問合せおよびビューの複雑さが増すと（10とおりの結合を行うビューというのも珍しくありません）、パフォーマンスは低下するでしょう。それに、未来のプログラマーたちは、どうして必要な問合せを正確に記述しなかったのだろうかと不思議に思うかもしれません。"怠けただけ"という答えでは、普通は納得してもらえません。 

結論を言います。レポートの問合せはビューを使用してできるだけ単純にします。ただし、ビューを使用する際は慎重になり規則を守ってください。必要なことだけを実行する問合せを記述しなくても済むからと言って、SQLエンジンに余計な作業をたくさんさせないようにします。

テーブル・ファンクションを使ってレポートの重複を避ける

Oracle Application Expressの機能でもっとも優れているのが、インタラクティブ・レポート作成機能です。強力なレポートの構築が容易になるだけでなく、レポートの内容と表示方法についてかなりのことをユーザーが決められるようになります。私はPL/SQL ChallengeのWebサイト全体でレポートを使用しますが、特にランキング・レポートには欠かせない機能だということが分かりました。

プレーヤーは次のようなさまざまな基準でランキングを確認したがります。

• プレーヤー別：自分のランキングを知ることができ、PL/SQLクイズで最上位にランクするプレーヤーを見つけることもできます。
• 会社別：同じ会社または同じ開発チームで働く全員の成績を比較できます。
• 団体別：ODTUGのメンバーとDOAG（ドイツのオラクル・ユーザー・グループ）のメンバーのスコアを比較する、などができます。
• 国別：PL/SQL Challengeではナショナリズムが健在です。
• 期間別：週間、月間、四半期、年間、全期間のランキングを確認できます。

異なる期間のすべてでランキングが必要なことから、期間ごとに異なるマテリアライズド・ビューを移入して使用するため、このランキング・レポートは特に複雑になります。たとえば、四半期ランキングを取得するには、mv_qdb_rankings_qビューを問合せる必要があります。

インタラクティブ・レポートでは動的SQL文を使用できないため、ビューが異なるという事実が最初は頭痛の種でした。その結果、図2に示すとおり、2ダース近くの異なるレポートを構築しました。

図2：21件の初期のPL/SQL Challengeインタラクティブ・レポート

これらのレポートを構築している間、自分が特に利口だとか生産的だとは感じませんでしたが、構築は1回だけでそれ以上はいじらないようにしなければならない、ということは少なくとも考えました。

はい、そうです。

私は2か月間この件について考えをめぐらせ続けました。2か月経つ頃には、プレーヤーから要望のあった優先順位の高い機能強化リクエストを6件続けてため込んでいました。そして、21件のインタラクティブ・レポートを1つずつ調べて必要なあらゆる変更を施すという、非常に退屈でミスを犯しやすい作業に立ち向かいました。

もっとよい方法がきっとあるはずです（オラクルがOracle Application Expressを機能強化してインタラクティブ・レポートで動的SQLがサポートされるようになること以外に）。そして、実際にあります。テーブル・ファンクションが使えるのです。これは、TABLE演算子内にラッピングして問合せのFROM句の中からコールできるファンクションです。このファンクションからデータのコレクションが返され、コレクションがTABLE演算子によって行と列に変換され、それがレポート（およびテーブル・ファンクションを使用して問合せを実行するあらゆるもの）で使用されます。

この記事のテーマから外れるため、テーブル・ファンクションについては詳しく説明しませんが、テーブル・ファンクションは非常に便利ですから、記事の最後に掲載されているリンクをたどってこのファンクションを学習することをお勧めします。ここでは、ランキングを表示する大量のインタラクティブ・レポートをわずか2つまで減らすときに辿った、テーブル・ファンクションを使用するための基本手順のみを説明します。

トップダウン・アプローチをとり、レポートで実行できるようにする必要がある問合せから着手します。2013年第4四半期の日次PL/SQLクイズの会社別ランキングを見る必要があるとすると、問合せは次のようになります。

SELECT *
 FROM TABLE (
  qdb_ranking_mgr.ir_rankings_tf (
   category_in     => 'COMPANY',
   period_type_in  => 'QUARTERLY',
   competition_in  => 'DAILY PL/SQL QUIZ',
   period_in       => '2013-4'));

ファンクションに渡す値をハードコーディングしましたが、アプリケーションの場合は、プレーヤーが値を選択します。

とても単純な問合せであると同意してもらえるのではないでしょうか。でもこれは、詳細をすべてファンクションに隠してしまったからに過ぎません。

では、このテーブル・ファンクションに隠れている部分を見てみましょう。テーブル・ファンクションからはコレクションを返す必要があるため、コレクション型を定義する必要があります。今回は複数の情報（会社名、ランキング、正解率、その他）を返そうとしているため、オブジェクト型のコレクションを作成する必要があります。

CREATE OR REPLACE TYPE 
ir_ranking_ot IS OBJECT
(
   period VARCHAR2 (500),
   type_name VARCHAR2 (300),
   score INTEGER,
   pct_correct_answers NUMBER,
   answer_time VARCHAR2 (500),
   overall_rank INTEGER
);
/

CREATE OR REPLACE TYPE ir_rankings_nt
   IS TABLE OF ir_ranking_ot
/

これで、ファンクションのヘッダーは次のようになります。

PACKAGE qdb_ranking_mgr
IS	
   FUNCTION ir_rankings_tf (
      category_in      IN VARCHAR2,
      period_type_in   IN VARCHAR2,
      competition_in   IN INTEGER,
      period_in        IN VARCHAR2)
      RETURN ir_rankings_nt;

テーブル・ファンクションの実装はアプリケーションに大きく依存します。ir_rankings_tfファンクションには、パラメータ値に基づいて動的問合せを構成する230行のコードが含まれます。必要なマテリアライズド・ビューはperiod_typeで決まるため、これがもっとも重要なパラメータです。次に示すのは、このファンクションを大幅に簡略化したバージョンで、ローカル・コレクションの宣言、非常に動的な問合せに対するEXECUTE IMMEDIATE - BULK COLLECTによるコレクションの移入、ファンクションからコレクションを返す処理の部分が含まれています。

   l_report_data ir_rankings_nt;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT ir_ranking_ot('
      || c_select_list
      || ') FROM mv_qdb_rankings_'
      || period_type_in
      || ' mv,'
      || c_detail_tables
      || ' WHERE '
      || c_where_clause
      || ' GROUP BY '
      || c_period_qualifier_column
      BULK COLLECT INTO l_report_data;

   RETURN l_report_data;
END;

テーブル・ファンクションはかなり複雑になる可能性がありますが、その複雑さはファンクション・インタフェースの裏に隠されます。テーブル・ファンクションから返されたデータが必要な開発者は、わざわざ詳細を調べなくても、データを取得するための単純な問合せを記述できます。

とはいえ、Oracle Application Express内でテーブル・ファンクションを使用する最大のメリットだと思ったことは、保守が必要なインタラクティブ・レポートの数が劇的に減ることです。

SQLを制する者はアプリケーションを制す

Oracle Databaseを基盤とするどのアプリケーションでも、もっとも重要なパーツはSQL文です。また、パフォーマンスの問題の多くを引き起こし、もっとも頻繁に変わるアプリケーションのパーツもSQL文です。したがって、いつどこにどのようにSQL文を記述するかをガイドラインに定め、それに従うことが極めて重要です。

Oracle Application ExpressでSQL文を記述する場合は、次のガイドラインに従うことをお勧めします。

• 複雑な問合せロジックはビューの裏に隠し、アプリケーション・ビルダーでレポートおよび表を定義する場合は、このビューに対する単純なSELECT文を記述すること。
• DML文（挿入、更新、削除）はすべてパッケージ・プロシージャの裏に隠し、項目をパラメータとして渡して、アプリケーション・ビルダーからこのプロシージャをコールすること。
• プロセス内で実行する問合せはすべて、所定のデータを（スカラー値、レコードまたはコレクションの形で）返すパッケージ・ファンクションの裏に隠すこと。
• レポートおよび表のエンジンとなる問合せの場合は、複雑な部分をなるべくビューの裏に隠すこと。ただし、ビューを乱用してI/Oが過度になることがないように注意すること。
• 無駄なインタラクティブ・レポートやその他のUI要素を作成せずに済むように、Oracle Application Expressの問合せでのテーブル・ファンクションの活用方法を検討すること。

これらのガイドラインに従えば、知らないうちにアプリケーションの保守にかかる時間が減り、代わりに、ユーザーがわくわくするような新しい機能について考え、設計、実装する時間が増えるでしょう。

CREATE TABLE plch_employees
(
   employee_id   INTEGER,
   last_name     VARCHAR2 (100)
)
/

CREATE TABLE plch_departments
(
   department_id     INTEGER,
   department_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Shubin');

   INSERT INTO plch_employees
        VALUES (200, 'Gould');

   INSERT INTO plch_employees
        VALUES (300, 'Dawkins');

   INSERT INTO plch_departments
        VALUES (10, 'Analysis');

   INSERT INTO plch_departments
        VALUES (20, 'Discovery');

   COMMIT;
END;
/

BEGIN
   RETURN    'SELECT '
    || CASE :p1000_report_type
       WHEN 'D'
       THEN
          'employee_id, last_name 
      FROM plch_employees'
       WHEN 'E'
       THEN
          'department_id, department_name 
      FROM plch_departments'
   END;
END;

CREATE OR REPLACE TYPE plch_report_data_ot
   IS OBJECT
(
   report_id INTEGER,
   report_text VARCHAR2 (100)
)
/

CREATE OR REPLACE TYPE plch_report_data_nt
   IS TABLE OF plch_report_data_ot
/

CREATE OR REPLACE PACKAGE plch_pkg
IS
   FUNCTION id_and_name (
      type_in   IN VARCHAR2)
      RETURN plch_report_data_nt;
END;
/

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   FUNCTION id_and_name (
      type_in   IN VARCHAR2)
      RETURN plch_report_data_nt
   IS
      l_return   plch_report_data_nt;
   BEGIN
      CASE type_in
         WHEN 'E'
         THEN
            SELECT plch_report_data_ot (
                      employee_id,
                      last_name)
              BULK COLLECT INTO l_return
              FROM plch_employees;
         WHEN 'D'
         THEN
            SELECT plch_report_data_ot (
                      department_id,
                      department_name)
              BULK COLLECT INTO l_return
              FROM plch_departments;
      END CASE;

      RETURN l_return;
   END;
END;
/

SELECT *
  FROM TABLE (
          plch_pkg.id_and_name (
             :p1000_report_type))
/

 
SELECT employee_id report_id,
       last_name report_text
  FROM plch_employees
 WHERE :p1000_report_type = 'E'
UNION
SELECT department_id, department_name
  FROM plch_departments
 WHERE :p1000_report_type = 'D';

Steven Feuersteinの略歴および彼がOracle Magazineに投稿したPL/SQLに関する他の記事へのリンク

▲ ページTOPに戻る

記事一覧へ戻る