jdbc CallableStatement 例子

记录，备忘。

conn = getConnection();

CallableStatement call = conn.prepareCall("BEGIN update sequence_tab set seq_col = seq_col + 1 where pk_col=xxx returning seq_col into ?; END;");
call.registerOutParameter(1, Types.INTEGER);
call.execute();
int currentSeq = call.getInt(1);

    PreparedStatement pst = conn.prepareStatement( "select memo from audit_process where id in (select * from table(?))");
    Array arr = getOracleConnection(conn).createARRAY("SYS.ODCINUMBERLIST", new int[]{1,2,3,4,5} );
    pst.setArray(1, arr);
    ResultSet rs = pst.executeQuery();
    while(rs.next())  out.println( rs.getString(1) + " , ");
    pst.close();

    CallableStatement call = conn.prepareCall( "DECLARE seqs SYS.ODCINUMBERLIST; names SYS.ODCIVARCHAR2LIST; BEGIN update SEQUENCES set seq=seq+0 where 1=1 returning seq, name BULK COLLECT into seqs, names;  open ? for select * from table(seqs); open ? for select * from table(names); END;");
    call.registerOutParameter(1, OracleTypes.CURSOR);
    call.registerOutParameter(2, OracleTypes.CURSOR);

    call.execute();
    ResultSet rs = (ResultSet)call.getObject(1);
    while(rs.next())  out.println( rs.getInt(1) + " , ");
    rs.close();
    rs = (ResultSet)call.getObject(2);
    while(rs.next())  out.println( rs.getString(1) + " , ");
    rs.close();
    call.close();