The following sections show how to call a stored procedure with both IN and OUT parameters.
A stored procedure for Oracle database written in PL/SQL language is listed as follows.
There are four parameters in the procedure and the last three of them are OUT parameters which means data will be passed out from those parameters.
CREATE OR REPLACE PROCEDURE getPERSONByUserId( p_userid IN PERSON.USER_ID%TYPE, o_username OUT PERSON.USERNAME%TYPE, o_createdby OUT PERSON.CREATED_BY%TYPE, o_date OUT PERSON.CREATED_DATE%TYPE) IS BEGIN SELECT USERNAME , CREATED_BY, CREATED_DATE INTO o_username, o_createdby, o_date FROM PERSON WHERE USER_ID = p_userid; END; /
The Java code to call a stored procedure is listed as follows and the OUT parameters
is used in getXXX() method from the CallableStatement
.
import java.sql.CallableStatement; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; // w w w.ja v a 2 s . c om public class Main { private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase"; private static final String DB_USER = "user"; private static final String DB_PASSWORD = "password"; public static void main(String[] argv) throws Exception { Class.forName(DB_DRIVER); Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD); CallableStatement callableStatement = null; String getPERSONByUserIdSql = "{call getPERSONByUserId(?,?,?,?)}"; callableStatement = dbConnection.prepareCall(getPERSONByUserIdSql); callableStatement.setInt(1, 10); callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR); callableStatement.registerOutParameter(4, java.sql.Types.DATE); callableStatement.executeUpdate(); String userName = callableStatement.getString(2); String createdBy = callableStatement.getString(3); Date createdDate = callableStatement.getDate(4); System.out.println("UserName : " + userName); System.out.println("CreatedBy : " + createdBy); System.out.println("CreatedDate : " + createdDate); callableStatement.close(); dbConnection.close(); } }