Oracle复合类型参数的存储过程以及JDBC调用

原文：http://blog.csdn.net/fk_baker/article/details/2451365

  Oracle中PL/SQL的功能非常强大，可以实现很多功能，所以会将很多业务逻辑通过存储过程实现，
但是，存储过程的参数在JDBC调用时需要进行类型之间的对应，对于简单类型，就比较简单，本文主要是
阐述PL/SQL的复合类型参数在JDBC中如何调用的.

 

1、nest表组合成结果集，然后以游标变量的形式返回

--创建类型

create or replace package mytest is

  -- Author  : ADMINISTRATOR
  -- Created : 2008-5-13 11:19:28
  -- Purpose : test
 
TYPE record_type IS RECORD(
      code   VARCHAR2(18),
      p_name VARCHAR2(16)); 

TYPE ref_cur_type IS REF CURSOR;
end mytest;

CREATE OR REPLACE TYPE table_type IS TABLE OF record_type;

--创建过程

CREATE OR REPLACE PROCEDURE testproc( v_ref_cur out mytest.ref_cur_type) IS

   v_record record_type;

   v_table table_type := table_type();

BEGIN
   FOR i IN 1 .. 9 LOOP
      v_record := NEW record_type('45212319830810435' || i, '侯廷文' || i);
      v_table.EXTEND;
      v_table(i) := v_record;
   END LOOP;

   OPEN v_ref_cur FOR
      SELECT * FROM TABLE(CAST(v_table AS table_type));

END testproc;

--java代码：

 

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testproc(?)} " );
        cstmt.registerOutParameter( 1 , OracleTypes.CURSOR);
        cstmt.execute();
        ResultSet rs  =  (ResultSet) cstmt.getObject( 1 );
         while  (rs.next())  {
            System.out.print(rs.getString(1) + "");
            System.out.print(rs.getString(2) + "");
            System.out.println();
        }

 

2、PL/SQL varray数组类型的形式返回结果

--创建类型

CREATE OR REPLACE TYPE myarr IS VARRAY(200) OF VARCHAR2(256);

--创建过程

CREATE OR REPLACE PROCEDURE testvarray(thearr OUT myarr) IS

BEGIN
   thearr := myarr();
   FOR i IN 1 .. 100 LOOP
      thearr.EXTEND;
      thearr(i) := '我是侯廷文'||i;
   END LOOP;
END testvarray;

--java代码：

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testvarray(?)} " );
        cstmt.registerOutParameter( 1 , OracleTypes.ARRAY,  " MYARR " );

        cstmt.execute();
        ARRAY array  =  cstmt.getARRAY( 1 );
        Datum[] datas  =  array.getOracleArray();

         for  ( int  i  =   0 ; i  <  datas.length; i ++ )  {
            System.out.println(i + ":" + new String(datas[i].getBytes()));
        }

3、PL/SQL 的Object类型返回，对应jdbc的Struct

--创建类型

CREATE OR REPLACE TYPE record_type as object(code VARCHAR2(18),
                                             p_name VARCHAR2(16));

--创建过程

create or replace procedure testStruct(theObj OUT record_type) is
begin
  theObj:=record_type('452134198308104351','侯廷文');
end testStruct;

--java代码：

 

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testStruct(?)} " );
        cstmt.registerOutParameter( 1 , OracleTypes.STRUCT,  " RECORD_TYPE " );
        cstmt.execute();
        STRUCT struct  =  cstmt.getSTRUCT( 1 );
        Datum[] datas  =  struct.getOracleAttributes();
         for  ( int  i  =   0 ; i  <  datas.length; i ++ )  {
            System.out.print(i + ":" + new String(datas[i].shareBytes()));
            System.out.println();
        }

4、PL/SQL 的table类型返回，对应jdbc的ARRAY

--创建类型

create or replace type nest_table_type IS TABLE OF VARCHAR2(200);

--创建过程

CREATE OR REPLACE PROCEDURE testtable(thetable OUT nest_table_type) IS
BEGIN
   thetable := nest_table_type();
   FOR i IN 1 .. 100 LOOP
      thetable.EXTEND;
      thetable(i) := dbms_random.STRING('X', 3) || '侯廷文' || i;
   END LOOP;
END testtable;

--java代码：

 

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
         .prepareCall( " {call testtable(?)} " );
         cstmt.registerOutParameter( 1 , OracleTypes.ARRAY,  " NEST_TABLE_TYPE " );
         cstmt.execute();
         ARRAY array  =  cstmt.getARRAY( 1 );
         Datum[] datas = array.getOracleArray();
          for  ( int  i  =   0 ; i  <  datas.length; i ++ )  {
         System.out.print(i + ":" + new String(datas[i].shareBytes()));
         System.out.println();
         }

 

5、PL/SQL 的Index table类型,注意该类型只能在
   oci8方式连接下使用,所需要的动态连接库文件（ocijdbc9.dll）要在环境变量里进行设置
   （例如：path=D:/oracle/ora90/BIN），在不同的环境下OCI驱动还可能出现类装载异常，
   所以返回索引表尽管方便，但偶尔会出现意想不到的错误

--创建类型

CREATE OR REPLACE PACKAGE p_type IS
TYPE index_table_type IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
END;

--创建过程

create or replace procedure testIndexTable(v_iTable OUT p_type.index_table_type) is
begin
  FOR i IN 1 .. 100 LOOP
  v_iTable(i):=dbms_random.string('X',5)||'侯廷文'||i;
  END LOOP;
end testIndexTable;

--java代码：

 OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testindextable(?)} " );
        cstmt.registerIndexTableOutParameter( 1 ,  200 , OracleTypes.VARCHAR,  256 );
        cstmt.execute();
        String[] strs  =  (String[]) cstmt.getPlsqlIndexTable( 1 );
         for  ( int  i  =   0 ; i  <  strs.length; i ++ )  {
            System.out.println(i + ":" + strs[i]);
        }

 

 

citiplanner 用的上6

6、nestTable里装的是Person Object

--创建类型

CREATE OR REPLACE TYPE Person AS OBJECT
(
       p_code VARCHAR2(32),
       p_name VARCHAR2(16),
       p_age NUMBER,
       p_birthday DATE
);

CREATE OR REPLACE TYPE person_table_type IS TABLE OF Person;

--创建过程

CREATE OR REPLACE PROCEDURE testtableofobject(v_table OUT person_table_type) IS
BEGIN
   v_table := person_table_type();
   FOR i IN 1 .. 5 LOOP
      v_table.EXTEND;
      v_table(i) := NEW person('45212319880810435' || i,
                               '侯廷文' || i,
                               20 + i,
                               to_date('1985-08-1' || i, 'YYYY-MM-DD'));
   END LOOP;
END testtableofobject;

--java代码：

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testtableofobject(?)} " );
        cstmt.registerOutParameter( 1 , OracleTypes.ARRAY,  " person_table_type "
                .toUpperCase());
        cstmt.execute();
        ARRAY array  =  cstmt.getARRAY( 1 );
        Datum[] datas  =  array.getOracleArray();
         for  ( int  i  =   0 ; i  <  datas.length; i ++ )  {
            System.out.print(i + ":");
            Datum[] personAttributes = ((STRUCT) datas[i])
                    .getOracleAttributes();
            System.out.print("p_code:"
                    + new String(personAttributes[0].getBytes()) + "");
            System.out.print("p_name:"
                    + new String(personAttributes[1].getBytes()) + "");
            System.out.print("p_age:" + personAttributes[2].intValue() + "");
            System.out.print("p_birthday:" + personAttributes[3].dateValue()
                    + "");
            System.out.println();
        }

 

7、输入参数为nestTable里装的是Person Object
   注意要将nls_charset12.jar放入ClassPath中，否则报SQLException:
   Non supported character set: oracle-character-set-852

--创建类型

CREATE OR REPLACE TYPE Person AS OBJECT
(
       p_code VARCHAR2(32),
       p_name VARCHAR2(16),
       p_age NUMBER,
       p_birthday DATE
);

CREATE OR REPLACE TYPE person_table_type IS TABLE OF Person;

CREATE TABLE personTable OF person;

--创建过程

CREATE OR REPLACE PROCEDURE testInPersons(persons IN person_table_type) IS
BEGIN
FORALL i IN persons.first .. persons.count
INSERT INTO personTable VALUES(persons(i));
COMMIT;
END testInPersons;

--java代码：

OracleCallableStatement cstmt  =  (OracleCallableStatement) con
                .prepareCall( " {call testInPersons(?)} " );
        StructDescriptor sd  =   new  StructDescriptor( " PERSON " , con);
        Object[] personAttrs1  =   new  Object[]  { "This is code11", "侯廷文11",
                new NUMBER(32), new Date(new java.util.Date().getTime()) } ;
        STRUCT person1  =   new  STRUCT(sd, con, personAttrs1);

        Object[] personAttrs2  =   new  Object[]  { "This is code22", "侯廷文22",
                new NUMBER(33), new Date(new java.util.Date().getTime()) } ;
        STRUCT person2  =   new  STRUCT(sd, con, personAttrs2);

        ArrayDescriptor ad  =   new  ArrayDescriptor( " person_table_type "
                .toUpperCase(), con);
        ARRAY persons  =   new  ARRAY(ad, con,  new  STRUCT[]  { person1, person2 } );
        cstmt.setARRAY( 1 , persons);
        cstmt.execute();
    }