createtable testtab (col1 number); createorreplace package demo_pck as type numbers_t istableofnumberindexby binary_integer; end; createorreplaceprocedure procin(p1 in demo_pck.numbers_t) is begin for i in p1.FIRST..p1.LAST loop insertinto testtab values (i); end loop; end; createorreplaceprocedure procout(p1 out demo_pck.numbers_t)is begin p1(1):=4; p1(2):=5; p1(3):=6; end; createorreplaceprocedure procinout(p1 in out demo_pck.numbers_t)is begin for i in p1.FIRST..p1.LAST loop p1(i) := p1(i) +6; end loop; end; createorreplacefunction funcnone return demo_pck.numbers_t is n demo_pck.numbers_t; begin n(1) :=10; n(2) :=11; n(3) :=12; return n; end;
java文件:
/**//* * This sample shows how to access PL/SQL index-by table from JDBC. */ import java.sql.*; import java.math.BigDecimal; import oracle.sql.*; import oracle.jdbc.driver.*; class IndexTableExample ...{ publicstaticvoid main(String args[]) throws SQLException,ClassNotFoundException ...{ // Load the driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url ="jdbc:oracle:oci8:@avex14"; try...{ String url1 = System.getProperty("JDBC_URL"); if (url1 !=null) url = url1; }catch (Exception e) ...{ // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection(url, "scott", "tiger"); func_test(conn ); procin_test(conn ); procinout_test(conn ); procout_test(conn ); // Close the connection. conn.close(); } /** *//** * Utility function to dump the contents of the "testtab" table */ staticvoid dumpTestTable(Connection conn) throws SQLException ...{ Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select * from testtab"); while (rset.next()) System.out.println("testtab Record : "+ rset.getInt(1)); stmt.execute("delete from testtab"); rset.close(); stmt.close(); } /** *//** * Bind a PL/SQL index-by table IN parameter. */ staticvoid procin_test(Connection conn) throws SQLException ...{ System.out.println("procin_test () "); // Prepare the statement OracleCallableStatement procin = (OracleCallableStatement) conn .prepareCall("begin procin (?); end;"); // index-by table bind value int[] values =...{ 1, 2, 3 }; // maximum length of the index-by table bind value. This // value defines the maximum possible "currentLen" for batch // updates. For standalone binds, "maxLen" should be the // same as "currentLen". int maxLen = values.length; // actual size of the index-by table bind value int currentLen = values.length; // index-by table element type int elemSqlType = OracleTypes.NUMBER; // index-by table element length in case the element type // is CHAR, VARCHAR or RAW. This value is ignored for other // types. int elemMaxLen =0; // set the value procin.setPlsqlIndexTable(1, values, maxLen, currentLen, elemSqlType, elemMaxLen); // execute the call procin.execute(); // verify the result dumpTestTable(conn); // close the statement procin.close(); } /** *//** * Bind a PL/SQL index-by table OUT parameter, and accesses the value using * JDBC default mapping. */ staticvoid procout_test(Connection conn) throws SQLException ...{ System.out.println("procout_test () "); OracleCallableStatement procout = (OracleCallableStatement) conn .prepareCall("begin procout (?); end;"); // maximum length of the index-by table bind value. This // value defines the maximum table size in the OUT parameter. int maxLen =10; // index-by table element type int elemSqlType = OracleTypes.NUMBER; // index-by table element length in case the element type // is CHAR, VARCHAR or RAW. This value is ignored for other // types int elemMaxLen =0; // register the OUT parameter procout.registerIndexTableOutParameter(1, maxLen, elemSqlType, elemMaxLen); // execute the call procout.execute(); // access the value using JDBC default mapping BigDecimal[] values = (BigDecimal[]) procout.getPlsqlIndexTable(1); // print the elements for (int i =0; i < values.length; i++) System.out.println(values[i].intValue()); // close the statement procout.close(); } /** *//** * Bind a PL/SQL index-by table IN/OUT parameter, and access the value using * Oracle JDBC mapping (Datum mapping). */ staticvoid procinout_test(Connection conn) throws SQLException ...{ System.out.println("procinout_test () "); OracleCallableStatement procinout = (OracleCallableStatement) conn .prepareCall("begin procinout (?); end;"); // index-by table IN bind value int[] values =...{ 1, 2, 3 }; // maximum length of the index-by table bind value. This // value defines the maximum possible "currentLen" for batch // updates. For standalone binds, "maxLen" should be the // same as "currentLen". int maxLen = values.length; // actual size to bind index-by table int currentLen = values.length; // index-by table element type int elemSqlType = OracleTypes.NUMBER; // index-by table element length in case the element type // is CHAR, VARCHAR or RAW. This value is ignored for other // types. int elemMaxLen =0; // set the IN value procinout.setPlsqlIndexTable(1, values, maxLen, currentLen, elemSqlType, elemMaxLen); // maximum length of the index-by table OUT bind value. This // value defines the maximum table size in the OUT parameter. int maxOutLen =10; // register the OUT parameter procinout.registerIndexTableOutParameter(1, maxOutLen, elemSqlType, elemMaxLen); // execute the call procinout.execute(); // access the value using Oracle JDBC mapping Datum[] outvalues = procinout.getOraclePlsqlIndexTable(1); // print the elements for (int i =0; i < outvalues.length; i++) System.out.println(outvalues[i].intValue()); // close the statement procinout.close(); } /** *//** * Call a function that returns a PL/SQL index-by table, and access the * value as a Java primitive array. */ staticvoid func_test(Connection conn) throws SQLException ...{ System.out.println("func_test () "); OracleCallableStatement funcnone = (OracleCallableStatement) conn .prepareCall("begin ? := funcnone; end;"); // maximum length of the index-by table value. This // value defines the maximum table size to be returned. int maxLen =10; // index-by table element type int elemSqlType = OracleTypes.NUMBER; // index-by table element length in case the element type // is CHAR, VARCHAR or RAW. This value is ignored for other // types int elemMaxLen =0; // register the return value funcnone.registerIndexTableOutParameter(1, maxLen, elemSqlType, elemMaxLen); // execute the call funcnone.execute(); // access the value as a Java primitive array. int[] values = (int[]) funcnone.getPlsqlIndexTable(1, java.lang.Integer.TYPE); // print the elements for (int i =0; i < values.length; i++) System.out.println(values[i]); // close the statement funcnone.close(); } }
在java中处理oracle中的Index-by表类型:注意:这种处理方式必须要在oci连接模式下才能进行!sp文件:create table testtab (col1 number);create or replace package demo_pck as type numbers_t is table of number index by binary_integer;e