oracle odcivarchar2list,如何加载大量的string来匹配oracle数据库？

使用集合

VARIABLE cursor REFCURSOR; DECLARE your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN your_collection.EXTEND( 10000 ); FOR i IN 1 .. 10000 LOOP -- Populate the collection. your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 ); END LOOP; OPEN :cursor FOR SELECT t.* FROM your_table t INNER JOIN TABLE( your_collection ) c ON t.id = c.COLUMN_VALUE; END; / PRINT cursor;

或者通过java来做同样的事情：

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import oracle.jdbc.OraclePreparedStatement; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; public class TestDatabase2 { public static void main(String args[]){ try{ Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password"); String[] ids = { "1", "2", "3" }; ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con); PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( :your_collection ) c ON t.id = c.COLUMN_VALUE"); // Passing an array to the procedure - ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) ); ResultSet cursor = st.executeQuery(); while ( cursor.next() ) { int id = cursor.getInt(1); double column1 = cursor.getDouble(2); double column2 = cursor.getDouble(3); System.out.println( String.format( "Id: %5d", id ) ); System.out.println( String.format( " Column1: %s", column1 ) ); System.out.println( String.format( " Column2: %s", column2 ) ); } } catch(ClassNotFoundException | SQLException e) { System.out.println(e); } } }