Oracle集合类型输出参数的PLSQL存储过程及其Java调用
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
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 replace procedure 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 J ava程序的编写
索引表与数据库表很形似,有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.V ARCHAR;
//索引表元素长度(CHAR, V ARCHAR 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;
}