// Here we prepare a CallableStatement using a WebLogic extension // to JDBC that supports binding an Oracle cursor to an output // parameter. Register the output parameter type as OTHER . . . weblogic.jdbc.common.OracleCallableStatement cstmt = (weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN OPEN ? FOR select * from emp; END;"); cstmt.registerOutParameter(1, java.sql.Types.OTHER);
// . . . and execute it. Retrieve the results into a ResultSet, // print it and close it. Then close the CallableStatement. Note // that the getResultSet(int) method is a WebLogic extension for // retrieving the results of a stored procedure. cstmt.execute(); ResultSet rs = cstmt.getResultSet(1); printResultSet(rs); rs.close(); cstmt.close();
// You can also set input parameters, execute the statement, // retrieve the query into a ResultSet, and print it // for each set of input parameters. cstmt2.setInt(2, 1); cstmt2.execute(); rs = cstmt2.getResultSet(1); printResultSet(rs);
// Here we prepare the CallableStatement, // register its output parameters (note the use of type OTHER // to bind the parameter to an Oracle cursor) . . . and execute it. weblogic.jdbc.common.OracleCallableStatement cstmt3 = (weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN multi_cursor(?, ?, ?); END;"); cstmt3.registerOutParameter(1, java.sql.Types.OTHER); cstmt3.registerOutParameter(2, java.sql.Types.OTHER); cstmt3.registerOutParameter(3, java.sql.Types.OTHER);
cstmt3.execute();
// Create a ResultSet for each set of parameters ResultSet rs1 = cstmt3.getResultSet(1); ResultSet rs2 = cstmt3.getResultSet(2); ResultSet rs3 = cstmt3.getResultSet(3);
// Print the ResultSets printResultSet(rs1); printResultSet(rs2); printResultSet(rs3);
// Always close objects like Statements and ResultSets // when you've finished using them. rs1.close(); rs2.close(); rs3.close();
// Close the CallableStatement cstmt3.close();
// Close the connection to the DBMS conn.close(); }
("{call stat_exes 1(输入参数), ?(输出参数)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.execute();
int error_flag = cstmt.getInt(1);
public static void main(String[] argv)
throws Exception
{ Properties props = new Properties();
props.put("user","scott");
props.put("password","tiger");
props.put("server","DEMO");
Driver myDriver = (Driver)
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn = myDriver.connect("jdbc:weblogic:oracle", props);
// Here we prepare a CallableStatement using a WebLogic extension
// to JDBC that supports binding an Oracle cursor to an output
// parameter. Register the output parameter type as OTHER . . .
weblogic.jdbc.common.OracleCallableStatement cstmt =
(weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN OPEN ? FOR select * from emp; END;");
cstmt.registerOutParameter(1, java.sql.Types.OTHER);
// . . . and execute it. Retrieve the results into a ResultSet,
// print it and close it. Then close the CallableStatement. Note
// that the getResultSet(int) method is a WebLogic extension for
// retrieving the results of a stored procedure.
cstmt.execute();
ResultSet rs = cstmt.getResultSet(1);
printResultSet(rs);
rs.close();
cstmt.close();
weblogic.jdbc.common.OracleCallableStatement cstmt2 =
(weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN single_cursor(?, ?); END;");
cstmt2.registerOutParameter(1, java.sql.Types.OTHER);
// You can also set input parameters, execute the statement,
// retrieve the query into a ResultSet, and print it
// for each set of input parameters.
cstmt2.setInt(2, 1);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
cstmt2.setInt(2, 2);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
cstmt2.setInt(2, 3);
cstmt2.execute();
rs = cstmt2.getResultSet(1);
printResultSet(rs);
// Close the CallableStatement
cstmt2.close();
// Here we prepare the CallableStatement,
// register its output parameters (note the use of type OTHER
// to bind the parameter to an Oracle cursor) . . . and execute it.
weblogic.jdbc.common.OracleCallableStatement cstmt3 =
(weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN multi_cursor(?, ?, ?); END;");
cstmt3.registerOutParameter(1, java.sql.Types.OTHER);
cstmt3.registerOutParameter(2, java.sql.Types.OTHER);
cstmt3.registerOutParameter(3, java.sql.Types.OTHER);
cstmt3.execute();
// Create a ResultSet for each set of parameters
ResultSet rs1 = cstmt3.getResultSet(1);
ResultSet rs2 = cstmt3.getResultSet(2);
ResultSet rs3 = cstmt3.getResultSet(3);
// Print the ResultSets
printResultSet(rs1);
printResultSet(rs2);
printResultSet(rs3);
// Always close objects like Statements and ResultSets
// when you've finished using them.
rs1.close();
rs2.close();
rs3.close();
// Close the CallableStatement
cstmt3.close();
// Close the connection to the DBMS
conn.close();
}
static void printResultSet(ResultSet rs)
throws SQLException
{
int rowcount = 0;
while(rs.next()) rowcount++;
System.out.println("Records = " + rowcount );
}
}