Hi!
In employee examples, there this kind of stored procedures:
-------
CREATE PROCEDURE ship_order (po_num CHAR(8))
AS
DECLARE VARIABLE ord_stat CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no INTEGER;
DECLARE VARIABLE any_po CHAR(8);
BEGIN
SELECT s.order_status, c.on_hold, c.cust_no
FROM sales s, customer c
WHERE po_number = :po_num
AND s.cust_no = c.cust_no
INTO :ord_stat, :hold_stat, :cust_no;
/* This purchase order has been already shipped. */
IF (ord_stat = 'shipped') THEN
BEGIN
EXCEPTION order_already_shipped;
END
/* Customer is on hold. */
ELSE IF (hold_stat = '*') THEN
BEGIN
EXCEPTION customer_on_hold;
END
/*
* If there is an unpaid balance on orders shipped over 2 months ago,
* put the customer on hold.
*/
FOR SELECT po_number
FROM sales
WHERE cust_no = :cust_no
AND order_status = 'shipped'
AND paid = 'n'
AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
INTO :any_po
DO
BEGIN
EXCEPTION customer_check;
UPDATE customer
SET on_hold = '*'
WHERE cust_no = :cust_no;
SUSPEND;
END
/*
* Ship the order.
*/
UPDATE sales
SET order_status = 'shipped', ship_date = 'NOW'
WHERE po_number = :po_num;
SUSPEND;
END !!
-------
It raises exception and do update after it.
How could this not be just garbage commands?
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
