JAVA中调用存储过程和函数

参考网址:http://www.exampledepot.com/egs/java.sql/CallProcedure.html

Calling a Stored Procedure in a Database

This example demonstrates how to call stored procedures with IN, OUT, and IN/OUT parameters.

COPY

CallableStatement cs;
try {
  // Call a procedure with no parameters
    cs = connection.prepareCall("{call myproc}");
    cs.execute();

  // Call a procedure with one IN parameter
    cs = connection.prepareCall("{call myprocin(?)}");

    // Set the value for the IN parameter
    cs.setString(1, "a string");

    // Execute the stored procedure
    cs.execute();

  // Call a procedure with one OUT parameter
    cs = connection.prepareCall("{call myprocout(?)}");

    // Register the type of the OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);

    // Execute the stored procedure and retrieve the OUT value
    cs.execute();
    String outParam = cs.getString(1);     // OUT parameter

  // Call a procedure with one IN/OUT parameter
    cs = connection.prepareCall("{call myprocinout(?)}");

    // Register the type of the IN/OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);

    // Set the value for the IN/OUT parameter
    cs.setString(1, "a string");

    // Execute the stored procedure and retrieve the IN/OUT value
    cs.execute();
    outParam = cs.getString(1);            // OUT parameter
} catch (SQLException e) {
}

参考网址：http://www.exampledepot.com/egs/java.sql/CallFunction.html

Calling a Function in a Database

A function is essentially a stored procedure that returns a result. This example demonstrates how to call functions with IN, OUT, and IN/OUT parameters.

COPY

CallableStatement cs;
try {
  // Call a function with no parameters; the function returns a VARCHAR
    // Prepare the callable statement
    cs = connection.prepareCall("{? = call myfunc}");

    // Register the type of the return value
    cs.registerOutParameter(1, i);

    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);

  // Call a function with one IN parameter; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncin(?)}");

    // Register the type of the return value
    cs.registerOutParameter(1, Types.VARCHAR);

    // Set the value for the IN parameter
    cs.setString(2, "a string");

    // Execute and retrieve the returned value
    cs.execute();
    retValue = cs.getString(1);

  // Call a function with one OUT parameter; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Execute and retrieve the returned values
    cs.execute();
    retValue = cs.getString(1);           // return value
    String outParam = cs.getString(2);    // OUT parameter

  // Call a function with one IN/OUT parameter; the function returns a VARCHAR
    cs = connection.prepareCall("{? = call myfuncinout(?)}");

    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);

    // Set the value for the IN/OUT parameter
    cs.setString(2, "a string");

    // Execute and retrieve the returned values
    cs.execute();
    retValue = cs.getString(1);           // return value
    outParam = cs.getString(2);           // IN/OUT parameter
} catch (SQLException e) {
}

CALL CURSOR:

For Oracle stored procedure returns CURSOR parameter, you can

1、Registered via JDBC CallableStatement.registerOutParameter(index,OracleTypes.CURSOR).

2、Get it back via callableStatement.getObject(index).

CODE:



//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "mkyong");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
 
// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();
 
// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);
 
// loop it like normal
while (rs.next()) {
    String userid = rs.getString("USER_ID");
    String userName = rs.getString("USERNAME");
}