Oracle集合部类输出参数的PL/SQL存储过程及其Java调用

Oracle集合类型输出参数的PL/SQL存储过程及其Java调用

? ??????????stmt.registerOutParameter( 1, OracleTypes.ARRAY," testArray" );
? ??????????stmt.executeUpdate();
?? ?????????// 得到 ARRAY 对象
?? ?????????ARRAY simpleArray = stmt.getARRAY(1);
//转换为java数组
? ??????????String[ ] values = (String[])simpleArray.getArray();
//输出数组内容
?? ?????????for( int i = 0; i < values.length; i++ )
??? ?????????System.out.println( "row " + i + " = '" + values[i] +"'" );
}
2.3 ?嵌套表作为输出参数
存 储过程中使用嵌套表，并不是直接将嵌套表作为输出参数，而是对嵌套表"造型"后以游标形式输出。嵌套表的基本功能与索引表相同，但嵌套表可以使用SQL进 行操作，其内容可通过SELECT 语句查询并"造型"后以游标形式返回。在大多数的查询统计中，常常需要返回结果集，这时使用嵌套表就尤其方便。笔者在开发过程中深刻体会到使用对象嵌套表 可以解决绝大多数的查询统计问题。下面着重介绍如何在存储过程里利用对象类型的嵌套表。
对于任意的统计分析表格，我们可以将其简化成下面的输出形式：
??
????? 统计项目1
统计项目2
统计项目3
统计项目4
名称1
?
?
?
?
名称2
?
?
?
?


这样我们把每一行看作是一个对象实例，该行的每一列则可以看作是该对象的一个属性，下面通过构造对象，然后对包含对象的嵌套表进行造型，返回游标得到结果集。
?2.3.1 存储过程的编写
? 首先构造统计对象如下：
????? create or replace type TestObj as object
(
?vname? varchar2(20),??? --名称
?item1?? number,??????? --统计项目1
?item2?? number,??????? --统计项目2
?item3?? number,??????? --统计项目3
item4?? number??????? --统计项目4
);
构造包含对象类型的嵌套表：
?create or replace type TestNestTable as table of TestObj;
定义对索引表"造型"后的输出的游标类型：
create or replace package out_param is
type out_cur is ref cursor;????????
下面是嵌套表作为输出参数的存储过程：
create or replace procedure testPro2(o_cur out out_param.out_cur ) is
---- 包含对象的嵌套表变量的声明
v_objTable? TestNestTable：= TestNestTable ();
begin
???????????????????? --嵌套表变量的使用
v_objTabl.extend;
v_objTable(1):= TestObj(‘张三',12，123，123，34);
v_objTabl.extend;
v_objTable(2):= TestObj(‘李四,22，223，223，234);
--对嵌套表进行"造型"返回游标
open? o_cur? for? select * from Table(cast (v_objTable as TestNestTable) );
end ?testPro2;
2.3.2? Java程序的编写
??? //从游标返回结果集
public ResultSet getCursor()
{
try
{
......
stmt =(CallableStatement )conn.prepareCall("call testPro2(?)");
//注册游标对象类型
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
//返回结果集
ResultSet ?Rs=(ResultSet)stmt.getObject(1);
}
catch(Exception e)
{
}
return Rs;
}
3? 结束语
使用索引表和可变数组，可将返回的集合映射成Java数组。由于索引表会自动分配空间，在声明时不需要指定其大小，而且不需要初始化，使用起来比较方便。但是索引表作为输出参数只能使用oci驱动（返回游标时，可以用瘦客户驱动也可以用oci驱动），所需要的动态连接库文件（ocijdbc9.dll）要在环境变量里进行设置（例如：path=D:\oracle\ora90\BIN），在不同的环境下OCI驱动还可能出现类装载异常，所以返回索引表尽管方便，但偶尔会出现意想不到的错误。可变数组映射成Java数组简单，对于返回小数据量的结果，也是不错的选择，但使用可变数组作为输出参数，声明时必须限定该数组的大小上限，并且需使用构造器初始化。
使用嵌套表，可以对嵌套表进行SQL操作，其内容能通过对标准的SELECT 语句造型后可转化为游标输出。而且嵌套表的内容相当于session变量，当断开连接后即释放内存，但同样存在需要初始化和扩展的问题。
综 上所述，究竟采用索引表、嵌套表和可变数组中哪一种作为存储过程的输出要看具体的要求和开发环境。有一点我们需要注意，如果返回的数据量较大，以数组形式 返回，则需一次性取回所有结果，在PL/SQL里为所有结果分配空间并复制，然后将这些数据通过网络发送到客户端，客户端也同样需要分配空间接受这些数 据；而采用游标形式，只要返回一个指针，然后分批返回结果（可自定义每次返回记录的条数），而不是一次性返回所有结果，因此在客户端不需分配大块的空间存 放所有结果。可见，对于大数据量的应用程序，返回游标程序运行效率会更高。

?

二、测试示例JAVA

public static void testPro2() {
??? ??? Connection conn = DBUtil.getConnection();
??? ??? CallableStatement stmt = null;
??? ??? ResultSet rs = null;
??? ??? try {
??? ??? ??? stmt =(CallableStatement)conn.prepareCall("call testPro2(?)");
??? ??? ??? //注册游标对象类型
??? ??? ??? stmt.registerOutParameter(1, OracleTypes.CURSOR);
??? ??? ??? stmt.execute();
??? ??? ??? //返回结果集
??? ??? ??? rs =(ResultSet)stmt.getObject(1);
??? ??? ??? while (rs.next()) {
??? ??? ??? ??? //
??? ??? ??? }
??? ??? } catch (Exception e) {
??? ??? ??? e.printStackTrace();
??? ??? } finally {
??? ??? ??? try {
??? ??? ??? ??? if (rs != null)
??? ??? ??? ??? ??? rs.close();
??? ??? ??? ??? if (stmt != null)
??? ??? ??? ??? ??? stmt.close();
??? ??? ??? } catch (Exception se) {
??? ??? ??? }
??? ??? ??? DBUtil.closeConnection(conn);
??? ??? }
??? }

?

三、SQL代码

create or replace package out_param is
? type out_cur is ref cursor;
end out_param;

?

create or replace type TestNestTable as table of TestObj

?

create or replace procedure testPro2(o_cur out out_param.out_cur) is
? ---- 包含对象的嵌套表变量的声明
? v_objTable TestNestTable := TestNestTable();
begin
? --嵌套表变量的使用
? v_objTable.extend;
? v_objTable(1) := TestObj('张三', 12, 123, 123, 34);
? v_objTable.extend;
? v_objTable(2) := TestObj('李四', 22, 223, 223, 234);
? --对嵌套表进行"造型"返回游标
? open o_cur for
??? select * from Table(cast(v_objTable as TestNestTable));
end testPro2;

?