Database Reference
In-Depth Information
-- Loop through all rows
REPEAT
-- Get order number
FETCH ordernumbers INTO o;
-- Get the total for this order
CALL ordertotal(o, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
-- End of loop
UNTIL done END REPEAT;
-- Close the cursor
CLOSE ordernumbers;
END;
▼
Analysis
In this example, we added another variable named
t
(this stores the total for
each order). The stored procedure also creates a new table on the fly (if it does
not exist) named
ordertotals
. This table stores the results generated by the
stored procedure.
FETCH
fetches each
order_num
as it did before, and then
uses
CALL
to execute another stored procedure (the one we created in the pre-
vious chapter) to calculate the total with tax for each order (the result of which
is stored in
t
). And then finally,
INSERT
is used to save the order number and
total for each order.
To try this example, simple
CALL
it:
▼
Input
CALL processorders();
This stored procedure returns no data, but it does create and populate another
table that can then be viewed using a simple
SELECT
statement:
▼
Input
SELECT *
FROM ordertotals;
