Oracle-存储过程-参数为数组-返回值为集合，java-调用存储过程

记录以备后用:

sql:

--step 1:create object type
CREATE or REPLACE TYPE db_arch_type AS OBJECT (
ID_LICENCEID nVARCHAR2  (50),
ID_ITEM_ARCHIVES_CODE nVARCHAR2  (50)
);
--step 2: create object type array
CREATE TYPE db_arch_array AS TABLE OF db_arch_type;

--step 3:define procedure

CREATE OR REPLACE PACKAGE objecttype AS
   TYPE ARCH_CURSOR IS REF CURSOR; --cursor?
  PROCEDURE db_mining_arch_object (d in db_arch_array,P_CURSOR OUT objecttype.ARCH_CURSOR);
END objecttype;



CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE db_mining_arch_object(d in db_arch_array,P_CURSOR OUT objecttype.ARCH_CURSOR)
AS

v_select_clause varchar2(4000);
v_where_clause clob;
v_where_clause_sub varchar2(400);

BEGIN
v_select_clause:='select * from t_user where 1<>1 ';
FOR i IN d.FIRST..d.LAST
LOOP
 v_where_clause_sub:= '(';
 v_where_clause_sub:= v_where_clause_sub||' ID_LICENCEID='||''''||d(i).ID_LICENCEID||'''' ;
 v_where_clause_sub:= v_where_clause_sub|| ' or  ID_ITEM_ARCHIVES_CODE='||''''||d(i).ID_ITEM_ARCHIVES_CODE||'''';
v_where_clause_sub:=v_where_clause_sub||')';
v_where_clause:= v_where_clause||' or ' ||v_where_clause_sub;
v_where_clause_sub:='';
END LOOP;
OPEN P_CURSOR FOR v_select_clause || v_where_clause;
END db_mining_arch_object;
END objecttype;

java 调用：

 public static void sendStruct()
	    {
			ResultSet rs = null;
	        try{   
	            Object[] so1 = {"4503009940011","130100"};
	            Object[] so2 = {"4308020810006","130110"};
	            CallableStatement callStatement = null;
	            StructDescriptor st = new StructDescriptor("DB_ARCH_TYPE",CONN);
	            STRUCT s1 = new STRUCT(st,CONN,so1);
	            STRUCT s2 = new STRUCT(st,CONN,so2);
	            STRUCT[] deptArray = {s1,s2};
	            ArrayDescriptor arrayDept = ArrayDescriptor.createDescriptor("DB_ARCH_ARRAY", CONN);
	            ARRAY deptArrayObject = new ARRAY(arrayDept, CONN, deptArray);
	            callStatement = (OracleCallableStatement)CONN.prepareCall("{call objecttype.db_arch_object(?,?)}");
	            callStatement.setArray(1, deptArrayObject);
	            callStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
	            callStatement.execute();
	            CONN.commit();
	            rs = (ResultSet) callStatement.getObject(2);
				while (rs.next()) {
					System.out.println("" + rs.getString("CK_GUID"));
					//System.out.println("" + rs.getString(3));
				}
	            callStatement.close();
	        }
	        catch(Exception e){
	            System.out.println(e.toString());
	        }
	}

下载地址：http://download.csdn.net/detail/handaoyaosi/8666707