제11장 사용자 정의 데이터 타입

본 장에서는 tbJDBC에서 사용자 정의 데이터 타입을 처리하는 방법을 설명한다.

11.1. 개요

JDBC 표준에서는 사용자 정의 데이터 타입(User Defined Types) 지원을 위해서 java.sql.Array, java.sql.Struct 인터페이스를 제공하고 있다.

Array 타입은 동일한 타입의 원소들을 모아서 저장하기 위한 타입이다.
Struct 타입은 여러 가지 타입의 원소들을 저장하기 위한 타입이다.

Tibero에서는 사용자 정의 타입에 대한 처리를 PSM을 사용하는 방법으로 제공하고 있다.

11.2. Array 타입

tbJDBC는 java.sql.Array 인터페이스를 구현한 com.tmax.tibero.jdbc.TbArray 클래스를 제공한다.

11.2.1. Array 데이터 타입 선언

사용자는 다음 같이 package 선언을 통해서 Array 타입을 정의할 수 있다.

create or replace package type_pkg 
as
    /* varray */
    type t_varr         is varray(16)   of varchar2(128);
    /* varray of varray */
    type t_varr_varr    is varray(8)    of t_varr;

    /* table */
    type t_tbl          is table of     varchar2(128);
    /* table of table */
    type t_tbl_tbl      is table of     t_tbl;
end;

11.2.2. Array 데이터 타입 IN

Array를 객체를 만들고 PSM 프러시저의 IN 인자로 전달하기 위해서 다음의 과정으로 처리한다.

Connection 객체로부터 Array를 생성한다.

public Array Connection.createArrayOf(String typeName, Object[] elements)
throws SQLException;

프러시저에 Array 객체를 바인딩한다.

CallabaleStatement의 setObject()를 통해서 Array 객체를 PSM 프러시저에 넘길 수 있다.

다음은 Array를 객체를 만들고 PSM 프러시저의 IN 인자로 전달하는 예제이다.

StringBuffer sbProc = new StringBuffer();
sbProc.append("begin ");
sbProc.append("    print_varr(?); ");
sbProc.append("end; ");

Object[] attributes = new Object[16];
for (int i = 0; i < 16; i++) {
     	attributes[i] = "abc" + i;
}

Array arr = conn.createArrayOf("TIBERO.TYPE_PKG.T_VARR", attributes);

CallableStatement cstmt = conn.prepareCall(sbProc.toString());
cstmt.setObject(1, arr);
cstmt.execute();

int status = 0;
int i = 0;
while (status == 0) {
     	cstmt = conn.prepareCall("{call sys.dbms_output.get_line(?, ?)}");
     	cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
     	cstmt.registerOutParameter(2, java.sql.Types.NUMERIC);
     	cstmt.execute();
     	status = cstmt.getInt(2);
     	if (status != 0)
     	     	break;
     	String retStr = cstmt.getString(1);
     	String ret[] = retStr.split(":");
     	System.out.println(ret[1]);
}

11.2.3. Array 데이터 타입 OUT

프러시저로부터 OUT 인자 형태로 Array 객체를 받아오기 위해서는 다음의 단계로 처리한다.

OUT 바인딩을 통한 프러시저를 호출하면 프러시저는 Array 타입 객체를 돌려준다.
CallableStatement.getArray() 함수를 통해 Array 객체를 받아온다.
Array 객체로부터 값을 받아온다.

다음은 프러시저로부터 OUT 인자형태로 Array 객체를 받아오는 예제이다. p_out_t_varr 프러시저는 16개의 문자열 배열을 돌려주도록 작성되어 있다.

sbProc.append("begin ");
sbProc.append("    p_out_t_varr(?, ?); ");
sbProc.append("end; ");

CallableStatement cstmt = conn.prepareCall(sbProc.toString());
cstmt.registerOutParameter(1, Types.ARRAY, "TIBERO.TYPE_PKG.T_VARR");
cstmt.setString(2, "abc");
cstmt.execute();

TbArray arr = (TbArray) cstmt.getArray(1);

Object obj = arr.getArray();
String[] bdArr = (String[]) obj;
assertEquals(16, bdArr.length);
for (int i = 0; i < bdArr.length; i++) {
    System.out.println(bdArr[i]);
}

11.3. Struct 타입

build-in 타입의 collection을 처리하는 Array와 달리 Struct는 여러 종류의 타입을 묶어서 나타내는 경우 사용한다. tbJDBC는 java.sql.Struct 인터페이스를 구현한 com.tmax.tibero.jdbc.Tb Struct 클래스를 제공한다.

11.3.1. Struct 데이터 타입 선언

다음과 같이 package를 통해서 record 타입을 선언할 수 있다.

create or replace package type_pkg 
as
    /*  record */
    type t_rec          is record   (rec_id number, name varchar(128), d date);
    /* record of record */
    type t_rec_rec      is record   (rec_id number, sub_rec1 t_rec, sub_rec2 t_rec);
end;

11.3.2. Struct 데이터 타입 IN

Struct 객체를 IN 인자로 전달하기 위해서는 다음의 단계로 처리한다.

Connection 객체로부터 Struct를 생성한다

public Struct Connection.createStruct(String typeName, Object[] attributes)

프러시저에 Struct 객체를 바인딩한다.

CallabaleStatement의 setObject()를 통해서 Struct 객체를 PSM 프러시저에 넘길 수 있다.

다음은 Struct 객체를 IN 인자로 전달하는 예제이다. print_rec 프러시저는 Struct 객체를 인자로 받아서 처리하는 기능을 담당한다.

StringBuffer sbProc = new StringBuffer();

sbProc.append("begin ");
sbProc.append("    print_rec(?); ");
sbProc.append("end; ");

Object[] attributes = new Object[3];
attributes[0] = 1;
attributes[1] = "abc1";
Calendar cal = Calendar.getInstance();
cal.set(2012, 11, 21); // 2012. 12. 21
attributes[2] = new Date(cal.getTimeInMillis());

TbStruct struct = conn.createStruct("TIBERO.TYPE_PKG.T_REC", attributes);

CallableStatement cstmt = conn.prepareCall(sbProc.toString());
cstmt.setObject(1, struct);
int row = cstmt.executeUpdate();

11.3.3. Struct 데이터 타입 OUT

프러시저로부터 Struct 객체를 OUT 인자 형태로 받아오기 위해서는 다음의 단계로 처리하면 된다.

OUT 바인딩을 통한 프러시저를 호출한다. 프러시저는 Struct 타입의 객체를 돌려준다.
CallableStatement.getStruct() 함수를 통해 Struct 객체를 받아온다.
Struct 객체로부터 값을 받아온다.

다음은 프러시저로부터 Struct 객체를 OUT 인자형태로 받아오는 예제이다. p_out_t_rec 프러시저는 NUMBER, VARCHAR, DATE 형태의 record 객체를 돌려준다.

StringBuffer sbProc = new StringBuffer();
        
sbProc.append("begin ");
sbProc.append("    p_out_t_rec(?); ");
sbProc.append("end; ");

TbCallableStatement cstmt = (TbCallableStatement)conn.prepareCall(sbProc.toString());
cstmt.registerOutParameter(1, Types.STRUCT, "TIBERO.TYPE_PKG.T_REC");
cstmt.execute();

Struct struct = cstmt.getStruct(1);
Object[] structArr = struct.getAttributes();

System.out.println(structArr.length);

System.out.println("number : " + structArr[0]);
System.out.println("varchar : " + structArr[1]);
System.out.println("date : " + structArr[2]);

11.4. Array, Struct 타입 테스트 코드

다음은 위의 Array, Struct 타입 생성 및 테스트를 위한 프로시져 코드이다.

create or replace package type_pkg 
as
    /*  record */
    type t_rec          is record       (rec_id number, name varchar(128), d date);
    /* record of record */
    type t_rec_rec      is record       (rec_id number, sub_rec1 t_rec,
                                         sub_rec2 t_rec);
    /* varray */
    type t_varr         is varray(16)   of varchar2(128);
    /* varray of varray */
    type t_varr_varr    is varray(8)    of t_varr;

    /* table */
    type t_tbl          is table of     varchar2(128);
    /* table of table */
    type t_tbl_tbl      is table of     t_tbl;
    /* index by table */
    type t_ibt          is table of     varchar2(128)   index by pls_integer;
    /* index by table of index by table */
    type t_ibt_ibt      is table of     t_ibt           index by pls_integer;

    /* record of collection (varray, table, index by table) */
    type t_rec_coll     is record       (rec_id number, c1 t_varr, c2 t_tbl,
                                         c3 t_ibt);

    /* varray of record */
    type t_varr_rec     is varray(4) of t_rec;
    /* table of record */
    type t_tbl_rec      is table of     t_rec;
    /* index by table of record */
    type t_ibt_rec      is table of     t_rec index by pls_integer;

    function get_id_seed return number;
    function get_date return date;
end;

create or replace package body type_pkg 
as
    id_seed number := 1;

    function get_id_seed return number 
    as
        id number;
    begin 
        id := id_seed;
        id_seed := id_seed + 1; 
        return id;
    end; 

    function get_date return date
    as
        d date;
    begin 
        d :=    to_date('1900-01-01', 'YYYY-MM-DD') + id_seed;
        return d;
    end;
end;

create or replace function f_out_t_rec return type_pkg.t_rec
as
  o_val type_pkg.t_rec;
begin
    o_val.rec_id := type_pkg.get_id_seed;
    o_val.name := 'NAME' || to_char(o_val.rec_id);
    o_val.d := type_pkg.get_date;
    return o_val;
end;

create or replace function f_out_t_rec_rec return type_pkg.t_rec_rec
as
    o_val type_pkg.t_rec_rec;
    sub_o_val1 type_pkg.t_rec;
    sub_o_val2 type_pkg.t_rec; 
begin
    o_val.rec_id := type_pkg.get_id_seed;    
    o_val.sub_rec1 := f_out_t_rec;
    o_val.sub_rec2 := f_out_t_rec;
    return o_val;
end;

create or replace function f_out_t_varr (str varchar2) return type_pkg.t_varr
as
    o_val type_pkg.t_varr;
begin
    o_val := type_pkg.t_varr();

    for i in  1 .. 16 loop
        o_val.extend;
        o_val(i) :=  str || to_char(i);
    end loop;
    return o_val;
end;

create or replace function f_out_t_varr_varr return type_pkg.t_varr_varr
as
  o_val type_pkg.t_varr_varr;
begin
    o_val := type_pkg.t_varr_varr();

    for i in 1 .. 8 loop
        o_val.extend;
        o_val(i) := f_out_t_varr('elem' || to_char(i));
    end loop;
    return o_val;
end;

create or replace function f_out_t_tbl(str varchar2) return type_pkg.t_tbl
as
    o_val type_pkg.t_tbl;
begin
    o_val := type_pkg.t_tbl();

    for i in  1 .. 4 loop
        o_val.extend;
        o_val(i) :=  str || to_char(i);
    end loop;
    return o_val;
end;

create or replace function f_out_t_tbl_tbl return type_pkg.t_tbl_tbl
as
  o_val type_pkg.t_tbl_tbl;
begin
    o_val := type_pkg.t_tbl_tbl();
    for i in 1 .. 2 loop
        o_val.extend;
        o_val(i) := f_out_t_tbl('elem' || to_char(i));
    end loop;
    return o_val;
end;

create or replace function f_out_t_ibt(str varchar2) return type_pkg.t_ibt
as
    o_val type_pkg.t_ibt;
begin
    for i in  1 .. 4 loop
        o_val(i) :=  str || to_char(i);
    end loop;
    return o_val;
end;

create or replace function f_out_t_ibt_ibt return type_pkg.t_ibt_ibt
as
  o_val type_pkg.t_ibt_ibt;
begin
    for i in 1 .. 2 loop
        o_val(i) := f_out_t_ibt( 'elem' || to_char(i));
    end loop;
    return o_val;
end;

create or replace function f_out_t_rec_coll return type_pkg.t_rec_coll
as
  o_val type_pkg.t_rec_coll;
begin
    o_val.rec_id := type_pkg.get_id_seed; 
    o_val.c1 := f_out_t_varr('rec_fld_varr');
    o_val.c2 := f_out_t_tbl('rec_fld_tbl');
    o_val.c3 := f_out_t_ibt('rec_fld_idt');
    return o_val;
end;

create or replace function f_out_t_varr_rec return type_pkg.t_varr_rec
as
  o_val type_pkg.t_varr_rec;
begin
    o_val := type_pkg.t_varr_rec();
    for i in 1 .. 4 loop
        o_val.extend;
        o_val(i) := f_out_t_rec();
    end loop;
    return o_val;
end;

create or replace function f_out_t_tbl_rec return type_pkg.t_tbl_rec
as
  o_val type_pkg.t_tbl_rec;
begin
    o_val := type_pkg.t_tbl_rec();
    for i in 1 .. 6 loop
        o_val.extend;
        o_val(i) := f_out_t_rec();
    end loop;
    return o_val;
end;

create or replace function f_out_t_ibt_rec return type_pkg.t_ibt_rec
as
  o_val type_pkg.t_ibt_rec;
begin
    for i in 1 .. 4 loop
        o_val(i) := f_out_t_rec();
    end loop;
    return o_val;
end;

create or replace procedure p_out_t_rec(o out type_pkg.t_rec)
as
  o_val type_pkg.t_rec;
begin
    o_val.rec_id := type_pkg.get_id_seed;
    o_val.name := 'NAME' || to_char(o_val.rec_id);
    o_val.d := type_pkg.get_date;

    o := o_val;
end;

create or replace procedure p_out_t_rec_rec(o out type_pkg.t_rec_rec)
as
    sub_o_val1 type_pkg.t_rec;
    sub_o_val2 type_pkg.t_rec;    
begin
    o.rec_id := type_pkg.get_id_seed;    
    p_out_t_rec(o.sub_rec1);
    p_out_t_rec(o.sub_rec2);
end;

create or replace procedure p_out_t_varr(o out type_pkg.t_varr, str varchar2)
as
    o_val type_pkg.t_varr;
begin
    o_val := type_pkg.t_varr();

    for i in  1 .. 16 loop
        o_val.extend;
        o_val(i) :=  str || to_char(i);
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_varr_varr(o out type_pkg.t_varr_varr)
as
  o_val type_pkg.t_varr_varr;
begin
    o_val := type_pkg.t_varr_varr();

    for i in 1 .. 8 loop
        o_val.extend;
        p_out_t_varr(o_val(i),  'elem' || to_char(i));
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_tbl(o out type_pkg.t_tbl, str varchar2)
as
    o_val type_pkg.t_tbl;
begin
    o_val := type_pkg.t_tbl();

    for i in  1 .. 4 loop
        o_val.extend;
        o_val(i) :=  str || to_char(i);
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_tbl_tbl(o out type_pkg.t_tbl_tbl)
as
  o_val type_pkg.t_tbl_tbl;
begin
    o_val := type_pkg.t_tbl_tbl();

    for i in 1 .. 2 loop
        o_val.extend;
        p_out_t_tbl(o_val(i),  'elem' || to_char(i));
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_ibt(o out type_pkg.t_ibt, str varchar2)
as
    o_val type_pkg.t_ibt;
begin
    for i in  1 .. 4 loop
        o_val(i) :=  str || to_char(i);
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_ibt_ibt(o out type_pkg.t_ibt_ibt)
as
  o_val type_pkg.t_ibt_ibt;
begin
    for i in 1 .. 2 loop
        p_out_t_ibt(o_val(i),  'elem' || to_char(i));
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_rec_coll(o out type_pkg.t_rec_coll)
as
  o_val type_pkg.t_rec_coll;
begin
    o_val.rec_id := type_pkg.get_id_seed; 
    p_out_t_varr(o_val.c1, 'rec_fld_varr');
    p_out_t_tbl(o_val.c2, 'rec_fld_tbl');
    p_out_t_ibt(o_val.c3, 'rec_fld_idt');
    o := o_val;
end;

create or replace procedure p_out_t_varr_rec(o out type_pkg.t_varr_rec)
as
  o_val type_pkg.t_varr_rec;
begin
    o_val := type_pkg.t_varr_rec();
    for i in 1 .. 4 loop
        o_val.extend;
        p_out_t_rec(o_val(i));
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_tbl_rec(o out type_pkg.t_tbl_rec)
as
  o_val type_pkg.t_tbl_rec;
begin
    o_val := type_pkg.t_tbl_rec();
    for i in 1 .. 6 loop
        o_val.extend;
        p_out_t_rec(o_val(i));
    end loop;
    o := o_val;
end;

create or replace procedure p_out_t_ibt_rec(o out type_pkg.t_ibt_rec)
as
  o_val type_pkg.t_ibt_rec;
begin
    for i in 1 .. 4 loop
        p_out_t_rec(o_val(i));
    end loop;
    o := o_val;
end;

create or replace procedure print_rec(p in type_pkg.t_rec)
as
begin
    dbms_output.put_line('rec_id:' || p.rec_id);
    dbms_output.put_line('name  :' || p.name);
    dbms_output.put_line('d     :' || p.d);
end;

create or replace procedure print_rec_rec(p in type_pkg.t_rec_rec)
as
begin
    dbms_output.put_line('rec_id:' || p.rec_id);
    print_rec(p.sub_rec1);
    print_rec(p.sub_rec2);
end;

create or replace procedure print_varr(p in type_pkg.t_varr)
as
begin
    for i in  1 .. 16 loop
        dbms_output.put_line(to_char(i) || ':' ||  p(i));
    end loop;
end;

create or replace procedure print_varr_varr(p in type_pkg.t_varr_varr)
as
begin
    for i in  1 .. 8 loop
        print_varr(p(i));
    end loop;
end;

create or replace procedure print_tbl(p in type_pkg.t_tbl)
as
begin
    for i in  1 .. 4 loop
        dbms_output.put_line(to_char(i) || ':' ||  p(i));
    end loop;
end;

create or replace procedure print_tbl_tbl(p in type_pkg.t_tbl_tbl)
as
begin
    for i in  1 .. 2 loop
        print_tbl(p(i));
    end loop;
end;

create or replace procedure print_ibt(p in type_pkg.t_ibt)
as
begin
    for i in  1 .. 4 loop
        dbms_output.put_line(to_char(i) || ':' ||  p(i));
    end loop;
end;

create or replace procedure print_ibt_ibt(p in type_pkg.t_ibt_ibt)
as
begin
    for i in  1 .. 2 loop
        print_ibt( p(i));
    end loop;
end;

create or replace procedure print_rec_coll(p in type_pkg.t_rec_coll)
as
begin
    dbms_output.put_line('rec_id:' || p.rec_id);
    print_varr(p.c1); 
    print_tbl(p.c2);
    print_ibt(p.c3); 
end;

create or replace procedure print_varr_rec(p in type_pkg.t_varr_rec)
as
begin
    for i in 1 .. 4 loop
        print_rec(p(i));
    end loop;    
end;

create or replace procedure print_tbl_rec(p in type_pkg.t_tbl_rec)
as
begin
    for i in 1 .. 6 loop
        print_rec(p(i));
    end loop;
end;

create or replace procedure print_ibt_rec(p in type_pkg.t_ibt_rec)
as
begin
    for i in 1 .. 4 loop
        print_rec(p(i));
    end loop;
end;

이전		다음
제10장 SSL	처음으로	색인