PLSQL コレクション（配列） ～結合配列～

PLSQLでコレクション（配列）を使用してみます。

コレクション（配列）の種類は3種類あるようです。

• 結合配列（PL/SQL表、索引付表　ともいう）
• ネストした表（PL/SQL表 ともいう）
• 可変配列(VARRAYともいう)

結合配列以外のネストした表、可変配列は、なにをするためのものなのか、なんのためにあるのか、
今の私には難しすぎて理解できないです。
いつか理解できたときに、残りのふたつの記事を書くことにして、今回は結合配列について書いていきます。

結合配列（PL/SQL表、索引付表 ともいう）

まずは結合配列を使ってみます。
結合配列とはHashやMapといった連想配列みたいなものです。
Shift the Oracle PL/SQL コレクション型に結合配列の特徴がまとまってます。

結合配列の特徴の一部

• データベースに格納できない
• 初期化していなくてもNULLではない
• 実行開始時には既にNULLではなくNULLにできない。（エラーになる）
• PL/SQL ブロック内だけで有効 (CREATE TYPE できない)

結合配列の使い方

1. まずは配列の「データ型」を宣言します。

TYPE 配列データ型名 IS TABLE OF 格納する値の型 INDEX BY キーのデータ型

たとえば
キーが数値で値が50バイトの文字列の場合のコレクションのデータ型の宣言
TYPE ary_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

キーが10バイトの文字列で値が50バイトの文字列の場合のコレクションのデータ型の宣言
TYPE ary_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(10);

2. 変数を宣言 『型』を作っただけでは使用できないので、作った型をデータ型とした変数を宣言します。

3. 変数を使い終わったらnullを代入してメモリ解放できないので、Deleteメソッドを使用してメモリを解放します。

配列データ型名.DELETE;

下記のコードはMemberテーブルより全データを取得し、
配列のキーにMemberId列の値、配列の値にMemberName列の値を設定して配列を作成します。

DECLARE
    --配列の型を宣言
    TYPE aryName_type IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(4);
    --配列変数を宣言
    aryName aryName_type;
    --カーソルを宣言
    CURSOR member_cur IS
        SELECT MemberId, MemberName FROM Member;
    --カーソルデータを格納するレコード変数   
    member_rec member_cur%Rowtype;
BEGIN
    
    OPEN member_cur;
    
    LOOP
        FETCH member_cur INTO member_rec;        
        EXIT WHEN member_cur%NOTFOUND;
        --MemberIdをキーにMemberNameを配列に格納
        aryName(member_rec.MemberId) := member_rec.MemberName;      
    END LOOP;
    
    CLOSE member_cur;
    
    --配列よりMemberId='2'のMemberNameを出力する
    SYS.DBMS_OUTPUT.PUT_LINE( aryName('2') );

    --使い終わったらDELETEメソッドでメモリ解放する
    aryName.DELETE;
END;

配列の値にレコードを設定することもできます。
以下のコードは、配列のキーにレコードのインデックス番号、配列の値にレコードを設定して配列を作成します。

DECLARE
    --カーソル宣言
    CURSOR member_crs IS 
        SELECT MemberId, MemberName FROM Member;
    --カーソルデータを代入するレコード変数
    member_rec member_crs%Rowtype;
    --キーにインデックス番号、値にレコードを持つ配列型を宣言
    TYPE ary_type IS TABLE OF member_crs%Rowtype INDEX BY Binary_Integer;
    --配列型の変数
    ary ary_type;
    --カウンタ変数
    counter NUMBER(4);
  
BEGIN
    
    OPEN member_crs;
    
    counter := 0;
    LOOP
        FETCH member_crs INTO member_rec;
        EXIT WHEN member_crs%NOTFOUND;
        --インデックス番号をキーにレコードを配列に格納
        ary(counter) := member_rec;
        counter := counter + 1;
    END LOOP;
        
    CLOSE member_crs;

    --配列のインデックスの2番目の要素を取り出す
    member_rec := ary(2);
    SYS.DBMS_OUTPUT.PUT_LINE(member_rec.MemberId || ' ' || member_rec.MemberName);
    --使い終わったらDELETEメソッドでメモリ解放する
    ary.DELETE;
END;

配列の値に配列を設定するこで多次元配列にできます。
以下のコードは、二次元配列で九九表のサンプルです。

DECLARE
    TYPE col_type IS TABLE OF NUMBER(2) INDEX BY BINARY_INTEGER;
    TYPE row_type IS TABLE OF col_type INDEX BY BINARY_INTEGER;
    --配列型の変数
    kukuTable row_type;
  
BEGIN
  
    FOR row in 1 .. 9 LOOP
        FOR col in 1..9 LOOP
        kukuTable(row)(col) := row * col;
        END LOOP;
    END LOOP;

    SYS.DBMS_OUTPUT.PUT_LINE( '7 × 9 = '|| kukuTable(7)(9) );
    
END;