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-14
LOWERPRICE Stored Procedure
CREATE PROCEDURE Statement
CREATE PROCEDURE samdbcat.sales.lowerprice()
EXTERNAL NAME 'Sales.lowerPrice'
EXTERNAL PATH '/usr/mydir/myclasses'
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA;
CALL Statement to Invoke the SPJ
To invoke the LOWERPRICE procedure in MXCI:
CALL samdbcat.sales.lowerprice();
To view the prices and quantities of items in the database with 50 or fewer orders,
issue this query before and after calling the LOWERPRICE procedure:
SELECT *
FROM
(SELECT p.partnum, SUM(qty_ordered) AS qtyOrdered, p.price
FROM samdbcat.sales.parts p
LEFT OUTER JOIN samdbcat.sales.odetail o
ON p.partnum = o.partnum
GROUP BY p.partnum, p.price) AS allparts
WHERE qtyOrdered < 51;
The LOWERPRICE procedure lowers the price of items with 50 or fewer orders by 10
percent in the database. For example, part number 3103, the LASER PRINTER, X1,
has 40 orders and a price of 4200.00:
PARTNUM QTYORDERED PRICE
------- -------------------- ------------
212 20 2500.00
244 47 3000.00
255 38 4000.00
2002 46 1500.00
405 18 795.00
3103 40 4200.00
3201 6 525.00
3205 38 625.00
3210 7 715.00
...
The invocation of LOWERPRICE lowers the price of this item from 4200.00 to 3780.00:
PARTNUM QTYORDERED PRICE
------- -------------------- ------------
212 20 2250.00
244 47 2700.00
255 38 3600.00
2002 46 1350.00
2405 18 715.50
3103 40 3780.00
3201 6 472.50
3205 38 562.50
3210 7 643.50
...