Mac De Oracle : PL/SQL de Collection #1 Tweet

最近のOracle Master試験は、PL/SQLは試験範囲に含まれていないわけですが、Oracleの開発や運用管理でもPL/SQLを利用する場面は少なく無いと思います。
（Mac De OracleでもPL/SQL関連ネタは多いです。）

Oracleデータベースありきですけが、Webアプリケーションからバッチ処理、さらに、運用管理、移行時など、PL/SQLは利用範囲が意外と広いのです。

ところで、
ブログの検索キーワードで、ORA-20000〜ORA-20999に次いで多いのが、実は、PL/SQLネタ。
Oracle Master試験の対象外になったことが影響しているんでしょうかねぇ？
Oracle Masterだからといっても、PL/SQLを使いこなせるとは限らない？　という状況になりつつあるなんて話もちらほら。　少々心配です。

ということで、PL/SQLの　Collectionについて少し書いてみるか！　と思った次第。

では、早速。。。

PL/SQLで扱えるコレクションは、

・Associative Array
・Nested Table
・Varray

の３種。

・Associative Array

結合配列（索引付き配列と呼ばれたり、以前はPL/SQL表とも呼ばれていました。）これだけ日本語で書いてもイメージできないですよね。おそらく。
Javaをやっている方には、HashTableみたいな〜〜。とか、Perlをやっている方には連想配列みたいな〜。とか説明すること、「あ〜〜〜！　なるほど。」とイメージできるようですね。（〜みたいな〜〜、なんですけどね。）

・Nested Table

Oracleが、Object Relational Database (ORDB)へと進化した際(Oracle8だったか？！）に登場したのコレクションの一つが、このNested Table（ネスト表）。このNested Tableは、Associate Arrayとは異なり、データベースへ格納することも可能。
また、PL/SQLで利用する場合、Associative Arrayとは異なり、事前に初期化しておく必要がある。
初期化って何？　ということになりますが、要するにインスタンスを作成しておいてね。ってことになりますね。（詳細は簡単な例も含めて別エントリにて。）
＃マニュアルには「初期化」と書いてあるので、そのまま使っています。

・Varray

Oracleが、Object Relational Database (ORDB)へと進化した際に登場したもう一つのコレクションがこのVarray。
事前に要素数が分かっている場合に利用することが多い。データベースに格納することができるが、更新する際にはコレクション全体を更新する必要がある。
PL/SQLで利用するには、Nested Table同様、事前に初期化しておく必要がある。

メモリを食らいつくすくらい巨大なコレクションを作る方も居たりして。。。。
限度はあるよ何事も。。。。。　(^^;;;

PL/SQLのコレクションに関するマニュアルは以下
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19257-01/collections.html#740

ということで、今回は準備だけ。

Object Relationalな部分にもちょっとだけ足を踏み込む次いでに、Collectionに格納する要素もオブジェクト型にしておきましょう。

以下のようにempType型を定義し、TO_STRING()　メンバーファンクション（Java等では、メソッド相当）を定義しておく。
（見ての通り、 empTypeの値をCSV形式の文字列にするファンクション。）

ちなみに、デフォルトコンストラクタをオーバーライドしたり、コンストラクタを追加することもできますよ。（簡単な例は別エントリにて）

create or replace TYPE empType AS OBJECT
(
    empno     NUMBER(4),
    ename     VARCHAR2(10, BYTE),
    job       VARCHAR2(9, BYTE),
    hiredate  DATE,
    sal       NUMBER(7,2),
    deptno    NUMBER(2),
    MEMBER FUNCTION TO_STRING RETURN VARCHAR2
);
/

create or replace TYPE BODY empType AS
  MEMBER FUNCTION TO_STRING
  RETURN VARCHAR2 IS
  BEGIN
    RETURN
      TO_CHAR(SELF.empno)                   ||','||
      SELF.ename                            ||','||
      SELF.job                              ||','||
      TO_CHAR(SELF.hiredate, 'YYYY/MM/DD')  ||','||
      TO_CHAR(SELF.sal, '99999.99')         ||','||
      TO_CHAR(SELF.deptno);
  END;
END;
/

今日はここまで。