Java调用存储过程(2)

create function snuffed_it_when (VARCHAR) returns integer 'declare 
poet_id NUMBER; 
poet_age NUMBER; 
begin 
--first get the id associated with the poet. 
SELECT id INTO poet_id FROM poets WHERE name = $1; 
--get and return the age. 
SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id; 
return age; 
end;' language 'pl/pgsql'; 

connection.setAutoCommit(false); 
CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }"); 
proc.registerOutParameter(1, Types.INTEGER); 
proc.setString(2, poetName); 
cs.execute(); 
int age = proc.getInt(2); 

create procedure list_early_deaths () return refcursor as 'declare 
toesup refcursor; 
begin 
open toesup for SELECT poets.name, deaths.age FROM poets, deaths 
-- all entries in deaths are for poets. -- but the table might become generic. 
WHERE poets.id = deaths.mort_id AND deaths.age < 60; 
return toesup; 
end;' language 'plpgsql'; 

PrintWriter: 
static void sendEarlyDeaths(PrintWriter out){ 
Connection con = null; 
CallableStatement toesUp = null; 
try { 
con = ConnectionPool.getConnection(); 
// PostgreSQL needs a transaction to do this... con. 
setAutoCommit(false); // Setup the call. 
CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); 
toesUp.registerOutParameter(1, Types.OTHER); 
toesUp.execute(); 
ResultSet rs = (ResultSet) toesUp.getObject(1); 
while (rs.next()) { 
String name = rs.getString(1); 
int age = rs.getInt(2); 
out.println(name + " was " + age + " years old."); 
} 
rs.close(); 
} 
catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close(); 
} 
} 

public class ProcessPoetDeaths{ 
public abstract void sendDeath(String name, int age); 
} 
static void mapEarlyDeaths(ProcessPoetDeaths mapper){ 
Connection con = null; 
CallableStatement toesUp = null; 
try { 
con = ConnectionPool.getConnection(); 
con.setAutoCommit(false); 
CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); 
toesUp.registerOutParameter(1, Types.OTHER); 
toesUp.execute(); 
ResultSet rs = (ResultSet) toesUp.getObject(1); 
while (rs.next()) { 
String name = rs.getString(1); 
int age = rs.getInt(2); 
mapper.sendDeath(name, age); 
} 
rs.close(); 
} catch (SQLException e) { // We should protect these calls. toesUp.close(); 
con.close(); 
} 
} 

static void sendEarlyDeaths(final PrintWriter out){ 
ProcessPoetDeaths myMapper = new ProcessPoetDeaths() { 
public void sendDeath(String name, int age) { 
out.println(name + " was " + age + " years old."); 
} 
}; 
mapEarlyDeaths(myMapper); 
}