SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Sample SPJs
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
A-13
Examples of the Sample SPJs
Examples of the Sample SPJs
These examples show each SPJ method, the CREATE PROCEDURE statement that
registers the SPJ, the CALL statement that invokes the SPJ, and the output in MXCI:
•
LOWERPRICE Stored Procedure on page A-13
•
DAILYORDERS Stored Procedure on page A-15
•
MONTHLYORDERS Stored Procedure on page A-16
•
TOTALPRICE Stored Procedure on page A-17
•
ADJUSTSALARY Stored Procedure on page A-18
•
EMPLOYEEJOB Stored Procedure on page A-19
•
SUPPLIERINFO Stored Procedure on page A-20
•
SUPPLYNUMBERS Stored Procedure on page A-21
LOWERPRICE Stored Procedure
Java Method
public static void lowerPrice()
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement getParts =
conn.prepareStatement("SELECT p.partnum, " +
" SUM(qty_ordered) AS qtyOrdered " +
"FROM samdbcat.sales.parts p " +
"LEFT JOIN samdbcat.sales.odetail o " +
" ON p.partnum = o.partnum " +
"GROUP BY p.partnum");
PreparedStatement updateParts =
conn.prepareStatement("UPDATE samdbcat.sales.parts " +
"SET price = price * 0.9 " +
"WHERE partnum = ?");
ResultSet rs = getParts.executeQuery();
while (rs.next())
{
BigDecimal qtyOrdered = rs.getBigDecimal(2);
if ((qtyOrdered == null) || (qtyOrdered.intValue() < 50))
{
BigDecimal partnum = rs.getBigDecimal(1);
updateParts.setBigDecimal(1, partnum);
updateParts.executeUpdate();
}
}
rs.close();
conn.close();
}