Neoview Guide to Stored Procedures in Java (R2.5)

public static void projectTeam(int projectCode,
ResultSet[] members)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getMembers = conn.prepareStatement(
" SELECT E.empnum, E.first_name, E.last_name, " +
" D.location " +
" FROM persnl.employee E, persnl.dept D, persnl.project P " +
" WHERE P.projcode = ? " +
" AND P.empnum = E.empnum " +
" AND E.deptnum = D.deptnum ");
getMembers.setInt(1, projectCode);
members[0] = getMembers.executeQuery();
} // See the “PROJECTTEAM Procedure” (page 89).
public static void topSalesReps(int whichQuarter,
ResultSet[] topReps)
throws SQLException
{
if (whichQuarter < 1 || whichQuarter > 4)
{
throw new SQLException ("Invalid value for quarter. " +
"Retry the CALL statement " +
"using a number from 1 to 4 " +
"to represent the quarter.", "38001" );
}
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getTopReps = conn.prepareStatement(
" SELECT [first 5] E.empnum, E.first_name, " +
" E.last_name, TOTALS.total " +
" FROM persnl.employee E, " +
" ( SELECT O.salesrep, " +
" sum(OD.unit_price * OD.qty_ordered) as total " +
" FROM sales.orders O, sales.odetail OD " +
" WHERE O.ordernum = OD.ordernum " +
" AND quarter(O.order_date) = ? " +
" GROUP BY O.salesrep " +
" ) TOTALS " +
" WHERE E.empnum = TOTALS.salesrep " +
" ORDER BY TOTALS.total descending ");
getTopReps.setInt(1, whichQuarter);
topReps[0] = getTopReps.executeQuery();
} // See the “TOPSALESREPS Procedure” (page 90).
}
See the following sections for more information about each SPJ method.
84 Sample SPJs