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

---

Marcus Miller - Silver Rain - It'll Come Back to You

---

ちょいと間が空いたが、PL/SQLのコレクションでのお遊び。その５回目。

検索ワードで結構目にするのが、"Ｎ次元　結合配列" というキーワード。

ということで、今回は、多次元のコレクションの例を。

Ｎ次元のコレクションってどうやって作るの？　ってことで情報を検索しているのだと思いますが、しっかりマニュアルにも記載されているので、そちらもしっかり読んでおくことをおすすめしますよ！。

Associative Array(以下、結合配列）、VARRAYやNested Table(以下、ネスト表）どれでも、考え方は同じ。
コレクション自体を別コレクションの要素にすればできるんですよ。　コレクションを入れ子にする　と言えばイメージしやすい？　ですかね。

以下のコードで赤太字で示しているところが多次元コレクション定義とアクセスのポイント。
まず、empType型を要素とするNestedTableType型(ネスト表）を定義し、次にNestedTalbType型を要素とするStrAssociativeArrayType型（結合配列）を定義している。
以下の例は多少、ひねくれた例？　として結合配列とネスト表という異なるコレクションを利用して多次元コレクションを定義している。その影響でコレクションの操作は多少面倒になるのだが．．

尚、empType型は、事前に作成しておいたオブジェクト型。

create or replace
PROCEDURE multiLevelCollections
AS
   TYPE NestedTableType IS TABLE OF empType;
   TYPE StrAssociativeArrayType IS TABLE OF NestedTableType INDEX BY VARCHAR2(50);

   myNestedTable NestedTableType := NestedTableType();
   myStrAssociativeArray StrAssociativeArrayType;
  
  claerMyStrAssociateArray StrAssociativeArrayType;
  
  myEmp empType;
  j dept.dname%TYPE;
  vDeptName dept.dname%TYPE := NULL;
  
  CURSOR csrEmp IS
    SELECT
      emp.job,
      emp.deptno,
      dept.dname,
      emp.empno,
      emp.ename,
      emp.sal,
      emp.hiredate
    FROM
      emp JOIN dept
      ON emp.deptno = dept.deptno
    ORDER BY
      emp.deptno,
      emp.ename;

  PROCEDURE printArray
  IS
  BEGIN
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('**** コレクションに要素が存在している状態 ****');
    DBMS_OUTPUT.PUT_LINE(
      'myStrAssociativeArray(' 
      || myStrAssociativeArray.COUNT 
      || ')'
    );
    
    j := myStrAssociativeArray.FIRST;
    WHILE j IS NOT NULL LOOP
      DBMS_OUTPUT.NEW_LINE;
      DBMS_OUTPUT.PUT_LINE(
        'myStrAssociativeArray(' 
          || j  || ')(' || myStrAssociativeArray(j).COUNT || ')' 
      );
    
      FOR k IN 
        myStrAssociativeArray(j).FIRST..myStrAssociativeArray(j).LAST 
      LOOP
        DBMS_OUTPUT.PUT_LINE(
          'myStrAssociativeArray('  || j          || ').' 
          || 'myNestedTable('       || TO_CHAR(k) || ') = '
          || myStrAssociativeArray(j)(k).TO_STRING()
        );
      END LOOP;
      j := myStrAssociativeArray.NEXT(j);
    END LOOP;
  END;
  
BEGIN
  FOR emp_rec IN csrEmp LOOP
    IF csrEmp%ROWCOUNT = 1 THEN
      vDeptName := emp_rec.dname;
    END IF;
    
    myEmp :=  empType (
                emp_rec.empno,
                emp_rec.ename,
                emp_rec.job,
                emp_rec.hiredate,
                emp_rec.sal,
                emp_rec.deptno
              );
                  
    IF vDeptName <> emp_rec.dname THEN
      myStrAssociativeArray(vDeptName) := myNestedTable;
      vDeptName := emp_rec.dname;
      myNestedTable := NestedTableType();
    END IF;

    myNestedTable.EXTEND(1);
    myNestedTable(myNestedTable.COUNT) := myEmp;
  END LOOP;
  
  IF myNestedTable.COUNT > 0 THEN
    myStrAssociativeArray(vDeptName) := myNestedTable;
  END IF;
    
  printArray();
  
  -- 結合配列を空にする！
  myStrAssociativeArray := claerMyStrAssociateArray;
  
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('**** コレクションが空の状態 ****');
    DBMS_OUTPUT.PUT_LINE(
    'myStrAssociativeArray(' 
    || myStrAssociativeArray.COUNT 
    || ')'
  );
 
     
END;
/

SCOTT> l
  1  select
  2      emp.deptno,
  3      dept.dname,
  4      emp.empno,
  5      emp.ename,
  6      emp.sal,
  7      emp.hiredate
  8    from
  9      emp join dept
 10      on emp.deptno = dept.deptno
 11    order by
 12      emp.deptno,
 13*     emp.ename
SCOTT> /

    DEPTNO DNAME               EMPNO ENAME             SAL HIREDATE
---------- -------------- ---------- ---------- ---------- --------
        10 ACCOUNTING           7782 CLARK            2450 81-06-09
        10 ACCOUNTING           7839 KING             5000 81-11-17
        20 RESEARCH             7876 ADAMS            1100 87-05-23
        20 RESEARCH             7566 JONES            2975 81-04-02
        20 RESEARCH             7788 SCOTT            3000 87-04-19
        20 RESEARCH             7369 SMITH             800 80-12-17
        30 SALES                7499 ALLEN            1600 81-02-20
        30 SALES                7698 BLAKE            2850 81-05-01
        30 SALES                7900 JAMES             950 81-12-03
        30 SALES                7654 MARTIN           1250 81-09-28
        30 SALES                7844 TURNER           1500 81-09-08
        30 SALES                7521 WARD             1250 81-02-22

12行が選択されました。

SCOTT> 

実行結果は次のようになる。(Oracle SQL Developer 1.1 for MacOSX）　
1.2がリリースされたようなので後でダウンロードしとくか・・> TODO

ということで、今日はここまで。

Have a good Week end!