HiIt really should be rewritten/updated.1. First select should use new type
join kind not comma join kind.2. Field po_number should be prefixed with alias
of the table.3. Order of exception and update should be reverted, sussped
removed, also maybe this update should be run in autonomous transaction but
this is not nescessary as someone can commit after this error.Regards,Karol
Bieniaszewski
-------- Oryginalna wiadomość --------Od: Adriano dos Santos Fernandes
<adrian...@gmail.com> Data: 03.02.2020 16:16 (GMT+01:00) Do: For discussion
among Firebird Developers <firebird-devel@lists.sourceforge.net> Temat:
[Firebird-devel] Stored procedures example 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?AdrianoFirebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-develFirebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
