import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class ArrayDemo2
{
public static void passArray() throws SQLException
{
Connection conn =
new
OracleDriver().defaultConnection();
long start = System.currentTimeMillis();
String strArray[] = { "4444444","455554","4466644" };
String deArray[] = { "4444411","455511","4466611" };
String coArray[] = { "哈哈","西西","往往" };
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "TESTSMLIST", conn );
ARRAY array_to_pass =
new ARRAY( descriptor, conn, strArray );
ARRAY array_to_pass2 =
new ARRAY( descriptor, conn, deArray );
ARRAY array_to_pass3 =
new ARRAY( descriptor, conn, coArray );
OraclePreparedStatement ps =
(OraclePreparedStatement)conn.prepareStatement
( "begin testsm(:x,:y,:z); end;" );
ps.setARRAY( 1, array_to_pass );
ps.setARRAY( 2, array_to_pass2 );
ps.setARRAY( 3, array_to_pass3 );
long startinit = System.currentTimeMillis();
int total = 3;
ps.execute();
long end = System.currentTimeMillis();
long costtime = end - start;
int ctimesec = (int)(costtime / 1000);
if(ctimesec == 0) ctimesec = 1;
System.out.println("End test,see follow result:");
System.out.println("init cost "+ (startinit - start) +" minsec");
System.out.println("total "+total+",cost time "+ctimesec +" sec("+costtime+" in minsec)");
System.out.println("insert oracle speed "+ (total / ctimesec) +" num/s");
}
}
create or replace type TESTSMLIST as table of varchar2(200);//注意这里定义的类型名要大写,我因为这个问题浪费了好多
时间.
create or replace procedure testsm(src testsmlist,dest testsmlist,cnt testsmlist)
is
ymd char(8);
s varchar2(1000);
cnt2 varchar2(450);
i number;
begin
i:=1;
while i <src.count loop
cnt2:=replace(cnt(i),'''','‘');
s:='insert into TB_LOG_ALL_inserttest(src_number,dest_number,content) values('''||src(i)||''','''||dest(i)||''','''||cnt2||''')';
execute im
mediate s;
commit;
i:=i+1;
end loop;
end testsm;
create or replace
procedure show_java_calling_testsm
as language java
name 'ArrayDemo2.passArray()';
set
serveroutput on
exec show_java_calling_testsm
------------------------------------------------------------------------
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/8731/showart_63985.html
-------------------------------------------------
java调用oracle存储过程的自定义类型(可变数组)。
2010-05-14 10:06
在使用存储过程中，我们有时需要传递可变数组，存在两种情况，存储过程有输入或输出参数为自定义可变数组的。在java代码中，如何正确调用oracle存储过程的自定义可变数组类型，在这里做一下示例说明. java调用oracle存储过程的自定义类型: plsql定义字符串和数值型可变数组： 一。定义全局类型： CREATE OR REPLACE TYPE USERSEQID_ARRAY IS VARRAY(50000) OF NUMBER(9) CREATE OR REPLACE TYPE USERNAME_ARRAY AS VARRAY(32) of varchar(32) CREATE OR REPLACE TYPE USERPWD_ARRAY AS VARRAY(50000) of varchar(60) 二。java调用输出参数为自定义数组的存储过程： 2.1 输出参数为自定义数组的存储过程make_logincard_pro： procedure make_logincard_pro (
p_cardsuitcode in varchar,
p_userseqidArr out USERSEQID_ARRAY ,
p_usernameArr out USERNAME_ARRAY
)
IS
v_addedtime date:= sysdate; BEGIN FOR ii IN 1 .. 10 LOOP IF p_userseqidArr IS NULL THEN
p_userseqidArr := USERSEQID_ARRAY(ii);
ELSE
p_userseqidArr.EXTEND; --超过数组定义大小(50000)将抛出异常
p_userseqidArr(ii) := ii;
END IF; IF p_usernameArr IS NULL THEN
p_usernameArr := USERSEQID_ARRAY(ii || 'TT');
ELSE
p_usernameArr.EXTEND; --超过数组定义大小(32)将抛出异常
p_usernameArr(ii) := ii || 'TT';
END IF;
END LOOP
END make_logincard_pro ; 2.2JAVA调用存储过程make_logincard_pro：
//代码片段
Connection con = session.connection();
java.sql.CallableStatement cst = con
prepareCall("call CNBT.test_pro(?,?,?)");
cst.setString(1, cardSuitCode);
cst.registerOutParameter(2, OracleTypes.ARRAY,"USERSEQID_ARRAY");
cst.registerOutParameter(3, OracleTypes.ARRAY,"USERNAME_ARRAY");
java.sql.Array userSeqIdArr = cst.getArray(2);
java.sql.Array userNameArr = cst.getArray(3); if ( userSeqIdArr != null ) ...{
BigDecimal userSeqIdList[] = (BigDecimal[])userSeqIdArr.getArray();//数据库的number映射为BigDecimal
//。。。。。。
}
if ( userNameArr != null ) ...{
String userNameList[] = (String[])userNameArr.getArray();
//。。。。。。
}
--------------------------------------------------------------------------------
三. java调用输入参数为自定义数组的存储过程： 3.1 输入参数为自定义数组的存储过程update_logincard_pwd： /**//**********************************************
* update_logincard_pwd *
*功能描述：更新密码存储过程 *
*输入参数： *
*输出参数： *
*作者：hanjiong *
***********************************************/
procedure update_logincard_pwd (
p_userSeqIdList in USERSEQID_ARRAY,
p_userPwdList in USERPWD_ARRAY,
p_resultcode out number
);3.2 java调用存储过程update_logincard_pwd： //代码片段
..........................
Connection con = session.connection();//使用的weblogic数据源
oracle.jdbc.OracleCallableStatement cst2 = (oracle.jdbc.OracleCallableStatement)con
.prepareCall(
"call CNBT.update_logincard_pwd(?,?,?)");
weblogic.jdbc.wrapper.Connection weblogicConn = (weblogic.jdbc.wrapper.Connection)con;
oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection)weblogicConn.getVendorConnection();//转化connection
oracle.sql.ArrayDescriptor des_USERSEQID_ARRAY =
oracle.sql.ArrayDescriptor.createDescriptor("USERSEQID_ARRAY",oracleConn);
oracle.sql.ArrayDescriptor des_USERPWD_ARRAY =
oracle.sql.ArrayDescriptor.createDescriptor("USERPWD_ARRAY",oracleConn);
oracle.sql.ARRAY ora_array1 = new oracle.sql.ARRAY(des_USERSEQID_ARRAY, oracleConn, userAccSeqIdArr);
oracle.sql.ARRAY ora_array2 = new oracle.sql.ARRAY(des_USERPWD_ARRAY, oracleConn, userPwdList);
cst2.setArray(1, ora_array1);
cst2.setArray(2, ora_array2);
cst2.registerOutParameter(3, java.sql.Types.INTEGER);
cst2.execute();
updateCode = cst2.getInt(3);
|
