Java调用oracle函数返回oracle类（类似）集合

1          Oracle索引表类型作为返回值

1.1         创建索引表

使用Java调用oracle函数返回索引表类型的集合，索引表的创建必须在oracle包中声明，下面是例子

CREATE OR REPLACE package plsql_ret_type

is

    /*

     add by HJ

     创建一个返回Pl/sql索引表类型的集合，用于java调用返回使用

   */

    type sqlindext istable of varchar2(20) index by binary_integer;

end plsql_ret_type;

1.2         创建一个oracle函数使得返回索引表类型

CREATE OR REPLACE function funtry_ret_index

return plsql_ret_type.sqlindext is

    /*

        add by HJ 测试Java调用数据库返回数据库返回索引表类型的集合

   */

    array plsql_ret_type.sqlindext;

begin

    fori in1..10 loop

        array(i):='array'||i;

    end loop;

    For in1..Array.Count Loop

        dbms_output.put_line(Array(i));

    End Loop;

    Return Array;

end funtry_ret_index;

1.3         java调用oracle函数返回索引表类型

使用该java类时必须加入包ojdbc.jar和nls_charset12.jar，笔者运行时使用的ojdbc14.jar，

这两个包在oracle安装目录下 .. oracle\product\10.2.0\db_1\jdbc\lib

package testp;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import oracle.sql.ARRAY;

public class TestJdbcTest {

    public final static String driver="oracle.jdbc.driver.OracleDriver";

    public final static String url="jdbc:oracle:thin:@192.168.161.801:1521:oracle";

    public final static String userName="ef";

    public final static String passWord="ef";

    public static Connection getConnect() throws Exception{

        Class.forName(driver);

        return DriverManager.getConnection(url,userName,passWord);

    }

    public static StringselDb(String ss){

        /*

         * 数据库返回变长数组 beg

         * */

        String sql1="begin ? :=funtry_ret_array; end;" ;

        OracleCallableStatement stmt = null;

        Connection con=null;

        try {

            con=getConnect();

            stmt=(OracleCallableStatement) con.prepareCall(sql1);

            stmt.registerOutParameter(1,OracleTypes.ARRAY,"SQLARRAY");

            stmt.executeUpdate();

            ARRAY  array=stmt.getARRAY(1);

            String[] stray=(String[]) array.getArray();

            System.out.println(stray.length);

            for(int i=0;i<stray.length;i++){

                System.out.println(stray[i]);

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            try {

                stmt.close();

                con.close();

            } catch (SQLException e) {

                // TODO Auto-generatedcatch block

                e.printStackTrace();

            }

        }

        /*

         * 数据库返回变长数组 end

         * */

        return"********";

    }

    public static void main(String[] args) {

        // TODO Auto-generatedmethod stub

        System.out.println(new TestJdbcTest().selDb("01000000"));

    }

}

2          Oracle可变数组类型作为返回值

2.1         创建可变数组

可变数组不要求必须声明在oracle包中，可直接运行

DROP TYPESQLARRAY;

 

CREATE OR REPLACE TYPE sqlarray is array(100) of varchar2(30)

2.2         创建一个oracle函数使得返回可变数组类型

CREATE OR REPLACE function funtry_ret_array

return sqlarray is

/*

   add by HJ 测试java调用函数返回值为oracle自定义可变数组

   注意：慎用可变数组为返回值，可变数组的最大长度是定义该数组时自定义的，

          很多时候很难确认返回值的大小，所以这个长度可能会太小，也可能会太大

*/

    retarraysqlarray:=sqlarray();

    /*

   cursor cur_bank is

        select *

            from c_bank  where rownum <5;

   */

begin

    /*

   for cbank in cur_bank loop

        retarray.extend;

        retarray(retarray.count):=cbank.BANK_ID;

   end loop;

   */

    For in1..10 Loop

        retarray.extend;

        retarray(retarray.count):='retarray'||i;

    End Loop;

    /*

   For i In 1..retarray.Count loop

        dbms_output.put_line(retarray(i));

   end loop;

   */

    return retarray;

end funtry_ret_array;

2.3         java调用oracle函数返回可变数组类型

使用该java类时必须加入包ojdbc.jar和nls_charset12.jar，笔者运行时使用的ojdbc14.jar

这两个包在oracle安装目录下 .. oracle\product\10.2.0\db_1\jdbc\lib

package testp;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import oracle.sql.ARRAY;

public class TestJdbcTest {

    public final static String driver="oracle.jdbc.driver.OracleDriver";

    public final static String url="jdbc:oracle:thin:@192.168.161.801:1521:oracle";

    public final static String userName="ef";

    public final static String passWord="ef";

    public static Connection getConnect() throws Exception{

        Class.forName(driver);

        return DriverManager.getConnection(url,userName,passWord);

    }

    public static String selDb(String ss){

       

        /*

         * 数据库返回索引表类型集合 beg

         * */

        String sql2="begin ? :=funtry_ret_index; end;" ;

        OracleCallableStatement stmt1 = null;

        Connection con1=null;

        try {

            con1=getConnect();

            stmt1=(OracleCallableStatement) con1.prepareCall(sql2);

            // 返回的索引表最大长度（可以大于索引表实际长度）

            //根据实际情况该值可设置一个相对极限

            int maxLen =10;

            // 索引表元素类型

            int elemSqlType = OracleTypes.VARCHAR;

            //索引表元素长度（CHAR, VARCHAR or RAW），其它元素类型可忽略该项值，但该参数仍须定义 

            //最好和数据库中设置的长度一致

            int elemMaxLen=20;

            // 注册返回参数

            stmt1.registerIndexTableOutParameter(1,maxLen,elemSqlType,elemMaxLen);

            stmt1.execute();

            String[] stray=(String[])stmt1.getPlsqlIndexTable(1);

            System.out.println(stray.length);

            for(int i=0;i<stray.length;i++){

                System.out.println(stray[i]);

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            try {

                stmt1.close();

                con1.close();

            } catch (SQLException e) {

                // TODO Auto-generatedcatch block

                e.printStackTrace();

            }

        }

       

        /*

         * 数据库返回索引表类型集合 end

         * */

        return"**************";

    }

    public static void main(String[] args) {

        // TODO Auto-generatedmethod stub

        System.out.println(new TestJdbcTest().selDb("01000000"));

    }

}

3          Oracle嵌套表类型作为返回值

3.1         声明一个oracle对象

create orreplace  type  sqlobject asobject(

    name varchar2(20),

    age  varchar2(10)

);

3.2         构造一个包含对象类型的嵌套表

create orreplacetypetrysqlobjastableofsqlobject;

3.3         定义一个作为输出参数的游标类型

该游标类型必须声明于包声明中，实例如下：

CREATE  OR REPLACE package plsql_ret_type

is

    /*

     add by HJ

     创建一个返回Pl/sql索引表类型的集合，用于java调用返回使用

   */

    type sqlindext is table of varchar2(20) index by binary_integer;

    /*

     add by HJ

     创建一个返回Pl/sql游标类型，用户Java调用返回

   */

    type sqlcursor is ref  cursor;

end plsql_ret_type;

3.4         java调用oracle函数返回游标类型

使用该java类时必须加入包ojdbc.jar和nls_charset12.jar，笔者运行时使用的ojdbc14.jar，

这两个包在oracle安装目录下 .. oracle\product\10.2.0\db_1\jdbc\lib

package testp;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import oracle.sql.ARRAY;

public class TestJdbcTest {

    public final static String driver="oracle.jdbc.driver.OracleDriver";

    public final static String url="jdbc:oracle:thin:@192.168.161.801:1521:oracle";

    public final static String userName="ef";

    public final static String passWord="ef";

    public static Connection getConnect() throws Exception{

        Class.forName(driver);

        return DriverManager.getConnection(url,userName,passWord);

    }

    public static String selDb(String ss){

   

        /*

         * 数据库返回游标类型的集合 beg

         * */

        String sql3="{?=callFUNTRY_RET_CURSOR()}";

        CallableStatement cstmstatement = null;

        Connection con2=null;

        try {

            con2=getConnect();

            cstmstatement=con2.prepareCall(sql3);

            cstmstatement.registerOutParameter(1,OracleTypes.CURSOR);

            cstmstatement.execute();

            ResultSet rs=(ResultSet)cstmstatement.getObject(1);

            if(rs!=null){

                while(rs.next()){

                    System.out.print(rs.getString(1)+" , "+rs.getString(2));

                    System.out.println();

                }

            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            try {

                cstmstatement.close();

                con2.close();

            } catch (SQLException e) {

                // TODO Auto-generatedcatch block

                e.printStackTrace();

            }

        }

        /*

         * 数据库返回游标类型的集合 end

         * */

        return"**************";

    }

    public static void main(String[] args) {

        // TODO Auto-generatedmethod stub

        System.out.println(new TestJdbcTest().selDb("01000000"));

    }

}

 

4          注释

笔者在例子中均把oracle集合以function返回值的形式返回，在实际开发中也可以作为函数或者过程的out参数传出。

4.1         笔者建议

使用索引表和可变数组，可将返回的集合映射成Java数组。由于索引表会自动分配空间，在声明时不需要指定其大小，而且不需要初始化，使用起来比较方便。但是索引表作为输出参数只能使用oci驱动（返回游标时，可以用瘦客户驱动也可以用oci驱动），所需要的动态连接库文件（ocijdbc9.dll）要在环境变量里进行设置（例如：path=D:\oracle\ora90\BIN），在不同的环境下OCI驱动还可能出现类装载异常，所以返回索引表尽管方便，但偶尔会出现意想不到的错误。可变数组映射成Java数组简单，对于返回小数据量的结果，也是不错的选择，但使用可变数组作为输出参数，声明时必须限定该数组的大小上限，并且需使用构造器初始化。

使用嵌套表，可以对嵌套表进行SQL操作，其内容能通过对标准的SELECT 语句造型后可转化为游标输出。而且嵌套表的内容相当于session变量，当断开连接后即释放内存，但同样存在需要初始化和扩展的问题。
综上所述，究竟采用索引表、嵌套表和可变数组中哪一种作为存储过程的输出要看具体的要求和开发环境。有一点我们需要注意，如果返回的数据量较大，以数组形式返回，则需一次性取回所有结果，在PL/SQL里为所有结果分配空间并复制，然后将这些数据通过网络发送到客户端，客户端也同样需要分配空间接受这些数据；而采用游标形式，只要返回一个指针，然后分批返回结果（可自定义每次返回记录的条数），而不是一次性返回所有结果，因此在客户端不需分配大块的空间存放所有结果。可见，对于大数据量的应用程序，返回游标程序运行效率会更高。（该段文字摘自百度空间）