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

Calling the Procedure: LOWERPRICE
To invoke the LOWERPRICE procedure in Neoview Script:
SQL>call sales.lowerprice();
--- SQL operation complete.
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 sales.parts p
LEFT OUTER JOIN 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
3201 6 525.00
255 38 4000.00
5101 6 200.00
2002 46 1500.00
7102 18 275.00
3103 40 4200.00
... ... ...
--- 17 row(s) selected.
The invocation of LOWERPRICE lowers the price of this item from 4200.00 to 3780.00:
PARTNUM QTYORDERED PRICE
------- -------------------- ----------
6500 40 85.50
5504 23 148.50
2002 46 1350.00
3201 6 472.50
7102 18 247.50
3103 40 3780.00
... ... ...
--- 17 row(s) selected.
Procedures in the SALES Schema 77