DROP PROCEDURE IF EXISTS `world`.`DYNAMIC_DEMO_RETURN` $$
CREATE DEFINER=`tusa`@`%` PROCEDURE `DYNAMIC_DEMO_RETURN`(districtName VARCHAR(35), OUT maxPop INT )
BEGIN
SELECT CountryCode, District,SUM(Population) AS POP FROM City
GROUP BY District, CountryCode ORDER BY POP DESC LIMIT 1;
SELECT * FROM City WHERE District = districtName ORDER BY POPULATION DESC;
SELECT MAX(POPULATION) INTO maxPop FROM City WHERE District = districtName ORDER BY POPULATION DESC;
END $$
/ JAVA /
CallableStatement callableStmt =myConnection.prepareCall("{CALL sp_test_inout_rs2(?,?)}");
callableStatementInstance.registerOutParameter(2,Types.INTEGER);
callableStmt.setString(1, ‘Liguria’);
callableStmt.execute( );
// JAVA //
private void executeProcedure(Connection connection, String sqlText)
throws SQLException {
CallableStatement cs = connection.prepareCall("{CALL " + sqlText + "}");
boolean moreResultSets = cs.execute( );
while (moreResultSets) {
ResultSet rs = cs.getResultSet( );
ResultSetMetaData rsmd = rs.getMetaData( );
StringBuffer buffer = new StringBuffer( );
for (int i = 1; i <= rsmd.getColumnCount( ); i++)
buffer.append(rsmd.getColumnName(i)).append("\t");
System.out.println(buffer.toString( ));
while (rs.next( )) {
buffer.setLength(0);
for (int i = 1; i <= rsmd.getColumnCount( ); i++)
buffer.append(rs.getString(i)).append("\t");
System.out.println(buffer.toString( ));
}
moreResultSets = cs.getMoreResults( );
}
}
To retrieve the OUT PARAMETER System.out.println("Out parameter = " + callableStmt.getInt(2));