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 =