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

PARTDATA Procedure
The PARTDATA procedure accepts a part number and returns this information about the part:
• Part description, price, and quantity available as output parameters
• A result set that contains rows from the ORDERS table about where this part was ordered
• A result set that contains rows from the PARTLOC table, listing locations that have this part
in stock and the quantity they have on hand
• A result set that contains rows from the PARTSUPP table for suppliers who carry this part
• A result set that contains rows from the EMPLOYEE table for sales reps who have sold this
part
Java Method: partData()
Example A-6 partData() Method
public static void partData(int partNum,
String[] partDescription,
BigDecimal[] unitPrice,
int[] qtyAvailable,
ResultSet[] orders,
ResultSet[] locations,
ResultSet[] suppliers,
ResultSet[] reps)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
// Retrieve detail about this part into the output parameters
PreparedStatement getPartInfo = conn.prepareStatement(
" SELECT P.partdesc, P.price, P.qty_available " +
" FROM sales.parts P " +
" WHERE partnum = ? ");
getPartInfo.setInt(1, partNum);
ResultSet rs = getPartInfo.executeQuery();
rs.next();
partDescription[0] = rs.getString(1);
unitPrice[0] = rs.getBigDecimal(2);
qtyAvailable[0] = rs.getInt(3);
rs.close();
// Return a result set of rows from the ORDERS table listing orders
// that included this part. Each ORDERS row is augmented with the
// quantity of this part that was ordered.
PreparedStatement getOrders = conn.prepareStatement(
" SELECT O.*, QTY.QTY_ORDERED " +
" FROM sales.orders O, " +
" ( select ordernum, sum(qty_ordered) as QTY_ORDERED " +
" from sales.odetail " +
" where partnum = ? " +
" group by ordernum) QTY " +
" WHERE O.ordernum = QTY.ordernum " +
" ORDER BY O.ordernum ");
getOrders.setInt(1, partNum);
orders[0] = getOrders.executeQuery();
Procedures in the SALES Schema 77