使用集合
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);
}
}}
