Oracleの配列・構造体パラメータ
配列や構造体のパラメータ
複雑な構造や実現難度の高さから機能制限がありながらも、限定的にサポートされているプロシージャのパラメータの型として、TABLE型(PL/SQL表) や OBJECT型 があります(@since 0.9.7.6)。 (VARRAY型は、DBFluteでの扱いとしてはTABLE型と変わりはないので、TABLE型の説明に集約されます)
Oracleのプロシージャの特殊な型に関する補足ををこのページにまとめています。"DBMSごとの取扱い" の Oracle のページからの続き的な内容です。
OBJECT型対応Entityの自動生成
OBJECT型に対応する Entity (CustomizeEntity) がSql2Entityタスクにて自動生成されます。 ParameterBean では対応する Entity 型プロパティが定義されますので、INパラメータ、OUTパラメータ共に、その Entity 経由でデータのやり取りができます。
OBJECT型の属性の構成を変更(属性の名前変更や削除など)すれば、Sql2Entityタスクの再実行で影響範囲をコンパイルエラーとして特定できます。
Oracle依存処理の自動生成
通常、TABLE型やOBJECT型のパラメータをJDBC経由で扱うとき、OracleパッケージのJDBCドライバのクラスを利用しなければなりません(oracle.sql.STRUCTなど)。 DBFluteでは、Oracle依存の処理自体が自動生成されます。(Oracle、かつ、オプションで有効にした場合)
TABLE型、OBJECT型のコラボ
TABLE型の要素としてOBJECT型を、もしくは、OBJECT型の属性としてTABLE型を、どちらも利用できます。 プロパティの型なども自動解決されます。また、階層レベルも基本的に無限階層で利用できます(ただし、状況により制限あり)。
事前の設定(dfpropなど)
これらを利用するためには、Oracle のJDBCドライバのクラスにコンパイルレベルで依存しなければなりません。 DBFluteはデフォルトでは、DBMS依存のJDBCドライバのクラスにコンパイルレベル依存しないようになっていますので、 オプションで依存するように設定します。(具体的には、oracle.sql.ARRAY や oracle.sql.StructDescriptor などに依存します)
また、実現に Oracle の物理コネクションが必要(具体的には、oracle.sql.STRUCT の生成時などに必要)となるため、物理コネクションを取得するためのコールバック処理を DBFluteConfig にて設定します。DBFlute 自身はデータベースコネクションの管理はしておらず、アプリ(DIコンテナ)から渡されたデータソース(DataSource)から取得できる論理的なコネクションを利用しています。 そのため、論理コネクションから物理コネクションを発掘するための処理をアプリから貰わなければなりません。(但し、S2Container の DBCP や Commons DBCP を利用している場合は、デフォルトでその発掘作業が登録されていますので設定は不要です)
TABLE型(PL/SQL表) (別名：ARRAY型)
要素型が、データ型マッピングでサポートされている型(一部除く)、および、(サポートされている範囲の)TABLE型、OBJECT型の場合に限り利用することができます。 プロパティは List 型となります(ELEMENTの型はデータ型マッピングの通り)。
VARRAY型も含めて配列の形をとる型を総称の意味合いも含めて、ARRAY型と呼ぶことがあります。 JDBCドライバでは、実際に Types.ARRAY に対応しており、また、DBFluteの内部での実装でも Array という表現で扱っています。
e.g. TABLE型の引数を持ったプロシージャの作成 (ReplaceSchemaで実行) @SQL
create type VARCHAR2_TABLE is table of VARCHAR2(100);
-- #df:begin#
create or replace procedure SP_TABLE_MANUAL_PARAMETER(
v_in_varchar_array in VARCHAR2_TABLE
, v_out_varchar_array out VARCHAR2_TABLE
) as
begin
v_out_varchar_array := v_in_varchar_array;
end SP_TABLE_MANUAL_PARAMETER;
/
-- #df:end#
e.g. TABLE型の引数を持ったプロシージャの呼び出し (IN, OUT) @Java
SpTableManualParameterPmb pmb = new SpTableManualParameterPmb();
pmb.setVInVarcharArray(Arrays.asList("foo", "bar"));
vendorCheckBhv.outsideSql().call(pmb);
List<String> outList = pmb.getVOutVarcharArray();
TABLE型の定義スキーマ
メインスキーマ、追加スキーマのいずれかで定義されているTABLE型のみ利用可能です。 それ意外のスキーマに定義されているTABLE型を利用する場合は、そのスキーマを追加スキーマとして扱う必要があります。 (追加スキーマのプロシージャやTABLE型に対する、ユーザ権限の付与忘れにご注意ください)
TABLE型のサポートされない利用
- TABLE型やVARRAY型のシノニム
- TABLE型やVARRAY型を参照するシノニムを型とするパラメータはサポートされません。 ※実現コストが高いため、実装を見送っています
- TABLE型やVARRAY型のネスト
- 要素型が(ARRAY型である)TABLE型やVARRAY型、つまり、List<List<String>> となるような形はサポートされません。 ※oracle.sql.ARRAY型での取扱い方がわかっていません
- CLOB (BLOBも含む)
- CLOB や BLOB などの型はサポートされません。 ※oracle.sql.ARRAY型での取扱い方がわかっていません
- DBMS_SQL.NUMBER_TABLEなど
- DBMS_SQLのようなパッケージ内に定義された型を利用することはできません。 ※oracle.sql.ArrayDescriptor型での取扱い方がわかっていません
- サポートされないOBJECT型の要素
- サポートされないOBJECT型は、同じくARRAY型の要素としても利用することはできません。
- ARRAYの要素型の認識ができない場合
- ARRAYの要素型の認識ができない場合、要素型が Object 型にマッピングされ動作はサポートされません。 (ただ、ARRAYの要素型の認識されないケースはそもそも少ないと想定されています)
- ただし、Object 型のプロパティが定義されていても、そのプロパティだけを利用しなければ(null のみの利用であれば)動作する可能性はあります。 また、スカラ値の要素の配列の場合も、型のつじつまが合えば動く可能性はあります。
その他、細かい制限の可能性
その他、細かい点(把握しき仕切れていないもの)で制限がありますので、必ずアプリ環境での動作確認をしてから利用するようにして下さい。
ARRAYの要素型の認識
ARRAYの要素型がメタデータから判明しない場合は、List<Object> にマッピングされ動作はサポートされません。ARRAYの要素型が認識される条件を満たしている必要があります。
ALL_COLL_TYPES が利用できる場合
ALL_COLL_TYPES が利用できる(Oracleの)バージョン、ALL_COLL_TYPES が利用できるユーザ環境においては、 全てのARRAYの要素型が認識されます。
ALL_ARGUMENTS, ALL_TYPES の場合
ALL_COLL_TYPES が利用できない場合は、代わりに ALL_ARGUMENTS と ALL_TYPES を利用します。この場合、以下のような "ある一定の条件" を満たした場合のみ要素型が認識されます。
- プロシージャパラメータ自体の型で利用
- ARRAY型パラメータの要素型として利用(ネスト)
つまり、OBJECT型の属性の型でしか利用されていないARRAY型 は、その要素型が認識できず Object 型になってしまいます。 その場合、実質的に動作はサポートされません。対応する型の話に限らず、OBJECT型の Entity の自動生成対象の条件にも影響がでますので注意です。
OBJECT型 (別名：STRUCT型)
プロシージャのパラメータとして定義されているOBJECT型(ARRAY型の要素での利用も含む)、 および、そのOBJECT型の属性としてネストされているOBJECT型は、対応する CustomizeEntity が自動生成され、ParameterBean のプロパティ型として利用することができます。 プロパティの型は自動生成された CustomizeEntity となります(ARRAY型の要素の場合は、Entity要素のList型)。
STRUCT型と呼ぶことがあります。JDBCドライバでは、実際に Types.STRUCT に対応しており、また、DBFluteの内部での実装でも Struct という表現で扱っています。
e.g. OBJECT型の引数を持ったプロシージャの作成 (ReplaceSchemaで実行) @SQL
create type FOO_BEAN as object (
FOO_ID NUMBER(8),
FOO_NAME VARCHAR2(20),
FOO_DECIMAL NUMBER(5, 3),
FOO_DATE DATE,
FOO_TIMESTAMP TIMESTAMP(3),
FOO_CLOB CLOB -- CLOB is unsupported
);
-- #df:begin#
create or replace procedure SP_STRUCT_BASIC_PARAMETER(
v_in_foo_bean in FOO_BEAN
, v_out_foo_bean out FOO_BEAN
) as
begin
v_out_foo_bean := v_in_foo_bean;
end SP_STRUCT_BASIC_PARAMETER;
/
-- #df:end#
e.g. OBJECT型の引数を持ったプロシージャの呼び出し @Java
// ## Arrange ##
SpStructBasicParameterPmb pmb = new SpStructBasicParameterPmb();
FooBean fooBean = new FooBean(); // this class is auto-generated
fooBean.setFooId(1);
fooBean.setFooName("foo");
fooBean.setFooDecimal(new BigDecimal("3.4"));
fooBean.setFooDate(DfTypeUtil.toDate("2010-11-20 12:34:56"));
fooBean.setFooTimestamp(DfTypeUtil.toTimestamp("2010-11-21 02:13:45.852"));
// CLOB is unsupported
//fooBean.setFooClob("fooooo");
pmb.setVInFooBean(fooBean);
assertNull(pmb.getVOutFooBean());
// ## Act ##
vendorCheckBhv.outsideSql().call(pmb);
// ## Assert ##
assertEquals(fooBean.toString(), pmb.getVInFooBean().toString());
FooBean outBean = pmb.getVOutFooBean();
assertEquals(fooBean.getFooId(), outBean.getFooId());
assertEquals(fooBean.getFooName(), outBean.getFooName());
assertEquals(fooBean.getFooDecimal(), outBean.getFooDecimal());
assertEquals(fooBean.getFooDate(), outBean.getFooDate());
assertEquals(fooBean.getFooTimestamp(), outBean.getFooTimestamp());
assertEquals(fooBean.getFooClob(), outBean.getFooClob());
自動生成対象となるOBJECT型
OBJECT型に対応した CustomizeEntity が自動生成される条件は プロシージャパラメータから辿れる ことです。例えば、以下のような状況が当てはまります。
- プロシージャパラメータ自体の型で利用 e.g. SP_FOO(v_in_foo_bean in FOO_BEAN) as ...
- ARRAY型パラメータの要素型として利用 e.g. List<FooBean>
- OBJECT型パラメータの属性の型として利用(ネスト構造) e.g. fooBean.getBarBean()
つまり、パラメータとして定義されているARRAY型、もしくは、OBJECT型から(複数階層も含めて)参照して辿れるOBJECT型が自動生成対象となります。 上記以外の使い方をしているOBJECT型であったとしても、複数箇所で利用しているうちのどれか一つが上記に当てはまれば自動生成されます。
ただし例外として、OBJECT型をARRAY型の要素型(List<FooBean>)として利用している場合で、そのARRAYの要素型が認識できない状況においては、そのOBJECT型の Entity は自動生成されません。
OBJECT型の定義スキーマ
メインスキーマ、追加スキーマのいずれかで定義されているOBJECT型のみ利用可能です。 それ意外のスキーマに定義されているOBJECT型を利用する場合は、そのスキーマを追加スキーマとして扱う必要があります。 (追加スキーマのプロシージャやTABLE型に対する、ユーザ権限の付与忘れにご注意ください)
OBJECT型のサポートされない利用
- OBJECT型のシノニム
- OBJECT型を参照するシノニムを型とするパラメータはサポートされません。 ※実現コストが高いため、実装を見送っています
- CLOB (BLOBも含む)
- CLOB や BLOB などの型はサポートされません。定義自体はできますが、実際に値を入れて実行すると例外になります。 (テーブル上のカラムにおいてはサポートされています) ※oracle.sql.STRUCT型での取扱い方がわかっていません
- サポートされないARRAY型の属性
- サポートされないARRAY型は、同じくOBJECT型の属性としても利用することはできません。
- ARRAYの要素型の認識ができない場合
- ARRAYの要素型での利用のときに、ARRAYの要素型の認識ができない場合、Object 型にマッピングされ動作はサポートされません。 (ただ、ARRAYの要素型の認識されないケースはそもそも少ないと想定されています)
- ただし、Object 型のプロパティが定義されていても、そのプロパティだけを利用しなければ(null のみの利用であれば)動作する可能性はあります。 また、スカラ値の要素の配列の場合も、型のつじつまが合えば動く可能性はあります。
その他、細かい制限の可能性
その他、(把握仕切れていないものも含め)細かい点で制限がありますので、必ずアプリ環境での動作確認をしてから利用するようにして下さい。 また、dbflute-oracle-example に様々なパターンのExample がありますので、参考にして下さい。内部的な仕組みを確認する場合は、DBFluteランタイムの OracleArrayType, OracleStructType がポイントとなります。
さらに広い世界
プロシージャコールのページで "フィードバックの重要性" というコラムを書いていますが、このARRAY型やSTRUCT型はそれ以上に未到達の多い領域です。 事前に十分アプリでの適合を検証してから利用するようにお願いします。
Exampleコード
以下のコード例は、実際の Example の抜粋です。
e.g. 複雑なネスト構造を持った型を引数に持ったプロシージャの作成 (ReplaceSchemaで実行) @SQL
create type FOO_BEAN as object (
FOO_ID NUMBER(8),
FOO_NAME VARCHAR2(20),
FOO_DECIMAL NUMBER(5, 3),
FOO_DATE DATE,
FOO_TIMESTAMP TIMESTAMP(3),
FOO_CLOB CLOB
);
create type BAR_BEAN as object (
BAR_ID NUMBER(8),
BAR_NAME VARCHAR2(20),
BAR_DECIMAL NUMBER(5, 3),
BAR_DATE DATE,
BAR_TIMESTAMP TIMESTAMP(3),
BAR_CLOB CLOB
);
create type CORGE_BEAN as object (
CORGE_ID NUMBER(8),
CORGE_NAME VARCHAR2(20),
CORGE_DECIMAL NUMBER(5, 3),
CORGE_DATE DATE,
CORGE_TIMESTAMP TIMESTAMP(3),
CORGE_CLOB CLOB
);
-- #df:begin#
create or replace procedure SP_STRUCT_BASIC_PARAMETER(
v_in_foo_bean in FOO_BEAN
, v_out_foo_bean out FOO_BEAN
) as
begin
v_out_foo_bean := v_in_foo_bean;
end SP_STRUCT_BASIC_PARAMETER;
/
-- #df:end#
create type FOO_TABLE is table of FOO_BEAN;
-- #df:begin#
create or replace procedure SP_STRUCT_LIST_PARAMETER(
v_in_foo_list in FOO_TABLE
, v_out_foo_list out FOO_TABLE
) as
begin
v_out_foo_list := v_in_foo_list;
end SP_STRUCT_LIST_PARAMETER;
/
-- #df:end#
create type BAR_TABLE is table of BAR_BEAN;
create type BAZ_TABLE is table of BAR_TABLE;
create type QUX_TABLE is table of NUMBER(12);
create type QUUX_TABLE is table of NUMBER(12);
create type CORGE_TABLE is table of CORGE_BEAN;
create type NESTED_BAZ_BEAN as object (
BAZ_ID NUMBER(8),
BAZ_NAME VARCHAR2(20),
BAZ_DATE DATE,
BAZ_LIST BAZ_TABLE
-- could not define self reference
--,BAZ_SELF NESTED_BAZ_BEAN
);
create type NESTED_BAR_BEAN as object (
BAR_ID NUMBER(8),
BAR_NAME VARCHAR2(20),
BAR_DATE DATE,
BAR_LIST BAR_TABLE,
BAZ_BEAN1 NESTED_BAZ_BEAN,
BAZ_BEAN2 NESTED_BAZ_BEAN,
QUX_LIST QUX_TABLE,
QUUX_LIST QUUX_TABLE
);
create type NESTED_FOO_BEAN as object (
FOO_ID NUMBER(8),
FOO_NAME VARCHAR2(20),
FOO_DATE DATE,
BAR_BEAN NESTED_BAR_BEAN,
QUX_LIST QUX_TABLE,
QUUX_LIST QUUX_TABLE,
CORGE_LIST CORGE_TABLE
);
-- #df:begin#
create or replace procedure SP_STRUCT_NESTED_PARAMETER(
v_in_foo_bean in NESTED_FOO_BEAN
, v_out_foo_bean out NESTED_FOO_BEAN
) as
begin
v_out_foo_bean := v_in_foo_bean;
end SP_STRUCT_NESTED_PARAMETER;
/
-- #df:end#
e.g. 複雑なネスト構造を持った型を引数に持ったプロシージャの呼び出し (IN, OUT) @Java
// = = = = = = = = = = = = = = = = = = = = = =
// NESTED_FOO_BEAN (STRUCT)
// |-FOO_ID
// |-FOO_NAME
// |-FOO_DATE
// |-BAR_BEAN : NESTED_BAR_BEAN
// | |-BAR_ID
// | |-BAR_NAME
// | |-BAR_DATE
// | |-BAR_LIST : BAR_TABLE<BAR_BEAN>
// | |-BAZ_BEAN1 : NESTED_BAZ_BEAN
// | | |-BAZ_ID
// | | |-BAZ_NAME
// | | |-BAZ_DATE
// | | |-BAZ_LIST : BAZ_TABLE<BAR_TABLE<BAR_BEAN>>
// | |-BAZ_BEAN2 : NESTED_BAZ_BEAN
// | |-QUX_LIST : QUX_TABLE<NUMBER(12)>
// | |-QUUX_LIST : QUUX_TABLE<NUMBER(12)>
// |-QUX_LIST : QUX_TABLE<NUMBER(12)>
// |-QUUX_LIST : QUUX_TABLE<NUMBER(12)>
// |-CORGE_LIST : CORGE_TABLE<CORGE_BEAN>
// = = = = = = = = = =/
// ## Arrange ##
SpStructNestedParameterPmb pmb = new SpStructNestedParameterPmb();
{
// struct in struct as IN parameter
NestedFooBean fooBean = new NestedFooBean();
fooBean.setFooId(1);
fooBean.setFooName("foo");
fooBean.setFooDate(DfTypeUtil.toDate("2010-11-20 12:34:56"));
{
// in struct in struct
NestedBarBean barBean = new NestedBarBean();
barBean.setBarId(2);
barBean.setBarName("bar");
barBean.setBarDate(DfTypeUtil.toDate("2010-11-21 09:51:23"));
// array<struct> in struct in struct
List<BarBean> barList = DfCollectionUtil.newArrayList();
{
BarBean elementBean = new BarBean();
elementBean.setBarId(71);
elementBean.setBarName("bar1");
barList.add(elementBean);
}
{
BarBean elementBean = new BarBean();
elementBean.setBarId(72);
elementBean.setBarName("bar2");
barList.add(elementBean);
}
barBean.setBarList(barList);
// struct in struct in struct
NestedBazBean bazBean1 = new NestedBazBean();
bazBean1.setBazId(4321);
bazBean1.setBazName("baz1");
bazBean1.setBazDate(DfTypeUtil.toDate("2010-11-22 19:21:33"));
// array<array> *unsupported
//List<List<BarBean>> bazList = DfCollectionUtil.newArrayList();
//bazList.add(barList);
//bazBean1.setBazList(bazList);
barBean.setBazBean1(bazBean1);
// struct in struct in struct (no value)
barBean.setBazBean1(bazBean1);
// array in struct in struct
List<BigDecimal> quxList = DfCollectionUtil.newArrayList();
quxList.add(new BigDecimal(1));
quxList.add(new BigDecimal(2));
barBean.setQuxList(quxList);
// array in struct in struct (no value)
//barBean(quuxList);
fooBean.setBarBean(barBean);
}
// array in struct as IN parameter
List<BigDecimal> quuxList = DfCollectionUtil.newArrayList();
quuxList.add(new BigDecimal(3));
quuxList.add(new BigDecimal(4));
quuxList.add(new BigDecimal(5));
fooBean.setQuxList(quxList);
// array in struct as IN parameter (no value)
//fooBean.setQuuxList(quuxList);
// array<struct> in struct as IN parameter
List<CorgeBean> corgeList = DfCollectionUtil.newArrayList();
CorgeBean corgeBean = new CorgeBean();
corgeBean.setCorgeId(123);
corgeBean.setCorgeName("corge");
corgeList.add(corgeBean);
fooBean.setCorgeList(corgeList);
pmb.setVInFooBean(fooBean);
}
assertNull(pmb.getVOutFooBean());
// ## Act ##
vendorCheckBhv.outsideSql().call(pmb);
// ## Assert ##
NestedFooBean inBean = pmb.getVInFooBean();
NestedFooBean outBean = pmb.getVOutFooBean();
assertEquals(inBean.getFooId(), outBean.getFooId());
assertEquals(inBean.getFooName(), outBean.getFooName());
assertEquals(inBean.getFooDate(), outBean.getFooDate());
{
// struct in struct as OUT parameter
NestedBarBean barBean = outBean.getBarBean();
log("fooBean.barBean = " + barBean);
assertEquals(inBean.getBarBean().getBarId(), barBean.getBarId());
assertEquals(inBean.getBarBean().getBarName(), barBean.getBarName());
assertEquals(inBean.getBarBean().getBarDate(), barBean.getBarDate());
{
// array<struct> in struct in struct
List<BarBean> barList = barBean.getBarList();
log("fooBean.barBean.barList = " + barList);
assertEquals(2, barList.size());
log("fooBean.barBean.barList(0)=" + barList.get(0));
log("fooBean.barBean.barList(1)=" + barList.get(1));
assertEquals("71", barList.get(0).getBarId().toString());
assertEquals("bar1", barList.get(0).getBarName());
assertEquals("72", barList.get(1).getBarId().toString());
assertEquals("bar2", barList.get(1).getBarName());
}
{
// struct in struct in struct
NestedBazBean bazBean1 = barBean.getBazBean1();
log("fooBean.barBean.bazBean1 = " + bazBean1);
assertNotNull(bazBean1);
NestedBazBean inBazBean1 = inBean.getBarBean().getBazBean1();
assertEquals(inBazBean1.getBazId(), bazBean1.getBazId());
assertEquals(inBazBean1.getBazName(), bazBean1.getBazName());
assertEquals(inBazBean1.getBazDate(), bazBean1.getBazDate());
}
{
// struct in struct in struct (no value)
NestedBazBean bazBean2 = barBean.getBazBean2();
log("fooBean.barBean.bazBean2 = " + bazBean2);
assertNull(bazBean2);
}
{
// array in struct in struct
List<BigDecimal> quxList = barBean.getQuxList();
log("fooBean.barBean.quxList = " + quxList);
assertFalse(quxList.isEmpty());
assertEquals(inBean.getBarBean().getQuxList().size(), quxList.size());
}
{
// array in struct in struct (no value)
List<BigDecimal> quuxList = barBean.getQuuxList();
log("fooBean.barBean.quuxList = " + quuxList);
assertTrue(quuxList.isEmpty());
}
// array in struct as OUT parameter
List<BigDecimal> quxList = outBean.getQuxList();
log("fooBean.quxList = " + quxList);
assertFalse(quxList.isEmpty());
assertEquals(inBean.getQuxList().size(), quxList.size());
// array in struct as OUT parameter (no value)
List<Object> quuxList = outBean.getQuuxList();
log("fooBean.quuxList = " + quuxList);
assertTrue(quuxList.isEmpty());
// array<struct> in struct as OUT parameter
List<CorgeBean> corgeList = outBean.getCorgeList();
log("fooBean.corgeList = " + corgeList);
assertFalse(corgeList.isEmpty());
assertEquals(1, corgeList.size());
assertEquals("123", corgeList.get(0).getCorgeId().toString());
assertEquals("corge", corgeList.get(0).getCorgeName());
assertNull(corgeList.get(0).getCorgeDate());
}
