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));

Latest conferences

We have 57 guests and no members online

oracle_ace