2020-03-26
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
できるのは解ったのだが、う~ん、どうなんだろう。