ORACLE覚書 階層データを引数として渡す

サマリ1
　|– 明細1-1
　|– 明細1-2
サマリ2
　|– 明細2-1

のような、階層のあるデータを引数で渡したいと思って、調べてみたらオブジェクト型なるものを発見。

で、実際にどうやって実装すればいいのか、また調べて実行。

＜手順＞
1, まずはCREATE TYPEでもって、任意のオブジェクト型を作ってみる。

・明細データの型を作る。(Javaでいうと、データクラスみたいなもの)

CREATE TYPE DTL_TYPE AS OBJECT (
   DTL_ID   VARCHAR2(5)
   DTL_NAME VARCHAR2(10)
);

・次に作った明細(DTL_TYPE)のテーブルを型にする。(JavaでいうとList<明細>みたいなもの)

CREATE TYPE DTLTAB_TYPE AS TABLE OF DTL_TYPE;

・明細が済んだら次はサマリも同じように型を作る。

CREATE TYPE SUM_TYPE AS OBJECT (
  SUM_ID   VARCHAR2(5)
 ,SUM_NAME VARCHAR2(20)
 ,DTL      DTLTAB_TYPE
);

CREATE TYPE SUMTAB_TYPE AS TABLE OF SUM_TYPE;

2, 試しに作ったオブジェクト型を引数に、関数を作ってみる

---------------------------------------
-- 明細データをカンマ区切りで取得
---------------------------------------
CREATE OR REPLACE FUNCTION OTAMESHI_FUNC(
  PARAM IN SUMTAB_TYPE 
) 
RETURN VARCHAR
IS
  TEMP    VARCHAR(1000);
BEGIN
  -- サマリをぐるぐる回す
  DECLARE
  CURSOR CURSUM IS SELECT SUM_ID, SUM_NAME, DTL FROM TABLE(CAST(PARAM AS SUMTAB_TYPE));
  CURSUM_ROW CURSUM%ROWTYPE;
  BEGIN
    OPEN CURSUM;
    LOOP
      FETCH CURSUM INTO CURSUM_ROW;
      EXIT WHEN CURSUM%NOTFOUND;

      TEMP := TEMP || ',<<' || CUR_ROW2.SUM_ID || ':' || CUR_ROW2.SUM_ID || '>>';
      
      -- 明細をぐるぐる回す
      DECLARE
      CURSOR CUR IS SELECT DTL_ID, DTL_NAME  FROM TABLE(CAST(CURSUM_ROW.DTL AS DTLTAB_TYPE));
      CUR_ROW CUR%ROWTYPE;
      BEGIN
        OPEN CUR;
        LOOP
          FETCH CUR INTO CUR_ROW;
          EXIT WHEN CUR%NOTFOUND;

          TEMP := TEMP || ',' || CUR.DTL_ID || ':' || CUR.DTL_ID;

        END LOOP;
      END;


    END LOOP;
  END;

  -- TEMPに入れたデータを返す
  RETURN TEMP;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'エラーです.';
END;

3, 作った関数を呼び出してみる

SELECT
  OTAMESHI_FUNC(
      SUMTAB_TYPE(
        SUM_TYPE('01'
               , 'まさか'
               , DTLTAB_TYPE(
                             DTL_TYPE('A1','やさか')
                            ) 
               )
       ,SUM_TYPE('02'
               , 'どうか'
               , DTLTAB_TYPE(
                             DTL_TYPE('D1','ぎんか')
                            ,DTL_TYPE('B1','きんか')
                            ) 
               )
     )
  )
FROM DUAL

できるのは解ったのだが、う～ん、どうなんだろう。