90类型输出参数的PLSQL存储过程及其Java调用

Oracle集合类型输出参数的PL/SQL存储过程及其Java调用现在的java的数据库-关系映射技术似乎不提倡用存储过程，其实存储过程更能发挥数据库的效率。

1 引言存储过程因其执行效率高、与事务处理的结合、运行更安全等优点，在数据库应用程序中被广泛采用。

PL/SQL是用于从各种环境中访问Oracle数据库的一种编程语言，它与数据库服务器集成在一起，PL/SQL编写的存储过程编译效率高，网络系统开销小，同时PL/SQL 直观性好，是大多数人的选择。

以Number、Varchar等基本标量类型为输出参数的PL/SQL存储过程，每个输出参数只能返回一个对应值。

而在实际数据库应用程序中，尤其是在进行系统综合查询统计时，往往需要返回二维数组或结果集，这时应考虑在存储过程中使用集合这种数据结构。

对于集合，我们可以一次把许多元素作为一个整体进行操作，也可以对集合中的单个元素进行操作，使用方便、灵活。

2 PL/SQL存储过程及Java程序的编写2.1 索引表作为输出参数索引表是无约束的，其大小的唯一限制（除可用内存外）就是它的关键字BINARY_INTEGER类型所能表示数值的约束（-2147483647...+2147483647），其元素不需要按任何特定顺序排列。

在声明时，我们不需要指定其大小，而且对索引表的元素可以直接赋值，不用初始化，可见使用索引表极其方便。

2.1.1存储过程的编写我们可以在PL/SQL语句块中定义索引表，但作为输出参数的索引表，必须要在包（package）里定义，方法如下：create or replace package out_param is---- 定义了元素是varchar2类型的一个索引表类型type out_index_table_typ is table of varchar2(50) index by binary_integer;end out_param;接下来就可以在pl/sql存储过程里引用在包里定义的索引表类型：create or replaceprocedure testPro1(in_param in varchar2, o_table out out_param. out_index_table_typ ) is begin-------这里略去程序体end testPro1;其中，返回的索引表类型前必须用包名加上句点来引用out_param. out_index_table_typ 2.1.2 Java程序的编写索引表与数据库表很形似，有key和value两列，但它不是真正的数据库表，不可以存储到数据库中。

因此索引表不能使用SQL进行操作，这样它的内容不能通过一个标准的SELECT语句返回游标得到。

这一点与嵌套表有很大不同。

由存储过程返回的索引表可以映射成java数组类型、JDBC Datatypes的BigDecimal[]数组类型和oracle的Datum[]数组。

有一点要注意，尽管索引表中的元素不一定要按任何特定顺序排列，其元素可以借助于任意有效关键字而插入，但对映射数组元素的引用应该从1开始，而且要连续，否则映射成数组时会出现null元素。

下面示例为将索引表映射成java数组类型。

import oracle.jdbc.*;import oracle.sql.*;import java.sql.*;public class ReturnIndexTable{ Connection ociconn=null;OracleCallableStatement stmt =null;public String[] getTable(String in_param){ String[] reAry=null;try{ OracleDriver S_Driver=null;if(S_Driver==null)S_Driver=new oracle.jdbc.OracleDriver();DriverManager.registerDriver(S_Driver);String url="jdbc:oracle:oci8:@test";String user="user";String password=" password";ociconn= DriverManager.getConnection(url,user,password);stmt =(OracleCallableStatement)ociconn.prepareCall("begin testPro1(?,?); end;");// 返回的索引表最大长度（可以大于索引表实际长度）int maxLen =31;// 索引表元素类型int elemSqlType = OracleTypes.VARCHAR;//索引表元素长度（CHAR, VARCHAR or RAW），其它元素类型可忽略该项值，但该参数仍须定义int elemMaxLen=50;stmt.setString(1,in_param);// 注册返回参数stmt.registerIndexTableOutParameter(2,maxLen,elemSqlType,elemMaxLen);stmt.execute();// 返回数组类型reAry=(String[])stmt.getPlsqlIndexTable(2);}catch (Exception e){e.printStackTrace();}finally{return reAry;}}//关闭连接.............}2.2 可变数组作为输出参数可变数组和另外两种集合类型不同，其元素在内存中是连续存储的，且在大小方面有一个固定的上界。

声明时需要指定该数组中元素的最大数目（可变数组的大小可以用EXTEND 方法来增加，但不能被扩展超过所声明的极限大小）。

可变数组的元素被赋值之前，必须使用构造器进行初始化。

元素插入数组时应从索引1开始，连续插入。

2.2.1 存储过程的编写可变数组的定义方法如下：create or replace type testArray is varray(5) of number(3)PL/SQL存储过程里调用可变数组作为输出参数：create or replace function getTestArray return testArrayaso_data testArray:= testArray ();beginfor v_count in 1..5 loopo_data.extend;o_data(v_count):= v_count;end loop;return o_data;end;2.2.2 Java程序的编写由存储过程返回的可变数组同样可以映射成java数组类型。

但Java程序调用存储过程返回的可变数组方式和索引表方式却不相同，这一点应注意，具体方法如下：public static void main( ){......//调用存储过程OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall ( "begin ? := getTestArray; end;" );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 语句查询并"造型"后以游标形式返回。

在大多数的查询统计中，常常需要返回结果集，这时使用嵌套表就尤其方便。

笔者在开发过程中深刻体会到使用对象嵌套表可以解决绝大多数的查询统计问题。

下面着重介绍如何在存储过程里利用对象类型的嵌套表。

对于任意的统计分析表格，我们可以将其简化成下面的输出形式：这样我们把每一行看作是一个对象实例，该行的每一列则可以看作是该对象的一个属性，下面通过构造对象，然后对包含对象的嵌套表进行造型，返回游标得到结果集。

2.3.1 存储过程的编写首先构造统计对象如下：create or replace type TestObj as object(vname varchar2(20), --名称item1 number, --统计项目1item2 number, --统计项目2item3 number, --统计项目3item4 number --统计项目4);构造包含对象类型的嵌套表：create or replace type TestNestTable as table of TestObj;定义对索引表"造型"后的输出的游标类型：create or replace package out_param istype 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;}2.4 嵌套表作为输入参数2.4.1 存储过程的编写造统计对象和嵌套表通2.3.1。