在java中处理oracle中的对象数组(嵌套表):
sp文件:
------------------------------- Create Object Type ----------------------------------
create or replace type o_tmp as object (
userid varchar2 ( 10 ),
username varchar2 ( 20 )
);
/
--------------------------- Create Nested Table Type On Base Object Type------------------------
create or replace type tmp_array is table of o_tmp;
/
---------------------------- Create a Package File -----------------------------------------------------------
create or replace package body test_object
as
procedure getObject(obj_arr out tmp_array)
is
tmp_obj o_tmp;
test_arr tmp_array : = tmp_array();
n number : = 1 ;
begin
loop
exit when n > 5 ;
tmp_obj : = o_tmp( ' test1 ' || n, ' fuyue ' || n);
test_arr.extend;
test_arr(n) : = tmp_obj;
n : = n + 1 ;
end loop;
obj_arr : = test_arr;
for n in obj_arr.first .. obj_arr.last loop
tmp_obj : = obj_arr(n);
dbms_output.put_line(tmp_obj.userid || ' --- ' || tmp_obj.username);
end loop;
end getObject;
end test_object;
/
create or replace type o_tmp as object (
userid varchar2 ( 10 ),
username varchar2 ( 20 )
);
/
--------------------------- Create Nested Table Type On Base Object Type------------------------
create or replace type tmp_array is table of o_tmp;
/
---------------------------- Create a Package File -----------------------------------------------------------
create or replace package body test_object
as
procedure getObject(obj_arr out tmp_array)
is
tmp_obj o_tmp;
test_arr tmp_array : = tmp_array();
n number : = 1 ;
begin
loop
exit when n > 5 ;
tmp_obj : = o_tmp( ' test1 ' || n, ' fuyue ' || n);
test_arr.extend;
test_arr(n) : = tmp_obj;
n : = n + 1 ;
end loop;
obj_arr : = test_arr;
for n in obj_arr.first .. obj_arr.last loop
tmp_obj : = obj_arr(n);
dbms_output.put_line(tmp_obj.userid || ' --- ' || tmp_obj.username);
end loop;
end getObject;
end test_object;
/
java文件:
import
java.sql.Array;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
public class ObjArray ... {
private static String strDriver = "oracle.jdbc.driver.OracleDriver";
private static String strUrl = "jdbc:oracle:thin:@192.160.10.14:1521:avex";
/** *//**
* @param args
*/
public static void main(String[] args) ...{
// TODO Auto-generated method stub
test();
}
private static void test() ...{
try ...{
Class.forName(strDriver);
OracleConnection con = (OracleConnection) DriverManager
.getConnection(strUrl, "avex", "avex");
CallableStatement stmt = null;
stmt = con.prepareCall("{call test_object.getObject(?)}");
stmt.registerOutParameter(1, OracleTypes.ARRAY, "TMP_ARRAY");
stmt.execute();
Array outparam = stmt.getArray(1);
Object[] obj = (Object[]) outparam.getArray();
for (int i = 0; i < obj.length; i++) ...{
STRUCT struct = (STRUCT) obj[i];
Datum[] dt = struct.getOracleAttributes();
for (int n = 0; n < dt.length; n++) ...{
System.out.println(dt[n]);
}
}
} catch (ClassNotFoundException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
public class ObjArray ... {
private static String strDriver = "oracle.jdbc.driver.OracleDriver";
private static String strUrl = "jdbc:oracle:thin:@192.160.10.14:1521:avex";
/** *//**
* @param args
*/
public static void main(String[] args) ...{
// TODO Auto-generated method stub
test();
}
private static void test() ...{
try ...{
Class.forName(strDriver);
OracleConnection con = (OracleConnection) DriverManager
.getConnection(strUrl, "avex", "avex");
CallableStatement stmt = null;
stmt = con.prepareCall("{call test_object.getObject(?)}");
stmt.registerOutParameter(1, OracleTypes.ARRAY, "TMP_ARRAY");
stmt.execute();
Array outparam = stmt.getArray(1);
Object[] obj = (Object[]) outparam.getArray();
for (int i = 0; i < obj.length; i++) ...{
STRUCT struct = (STRUCT) obj[i];
Datum[] dt = struct.getOracleAttributes();
for (int n = 0; n < dt.length; n++) ...{
System.out.println(dt[n]);
}
}
} catch (ClassNotFoundException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}