delimiter//
Create procedure processorders ()
The begin
- declare a local variable
Declare the done Boolean default 0;
Declare int o;
Declare t a decimal (8, 2);
- declare the cursor
Declare ordernumbers cursor for
The select order_num from the orders;
- declare the continue handler
Declare the continue handler for sqlstate '02000' set done=1;
- create a table to store the results
The CREATE TABLE IF NOT EXISTS ordertotals (
Order_num INT,
Total DECIMAL (8, 2)
);
- open the cursor
The open ordernumbers.
- loop through all rows
Repeat
- get the order number
The fetch ordernumbers into o;
- get the total for this order
Call ordertotal (o, 1, t);
- insert the order and the total into ordertotals
Insert into ordertotals (order_num, total) values (o, t);
- the end of the loop
Repeat until done end;
- close the cursor
The close ordernumbers.
End//
delimiter//
Create procedure ordertotal (
In onumber int,
In taxable Boolean,
The out ototal decimal (8, 2)
) the comment 'obtain order total, optionally adding tax'
The begin
- declare variable for total
Declare the total decimal (8, 2);
- declare tax percentage with
Declare taxrate int the default 6;
- get the order total
SELECT
The SUM (item_price * quantity)
The FROM
The orderitems
WHERE
Order_num=onumber INTO total;
- is this taxable?
If taxable then
-- yes, so add taxrate to the total
Select the total + (total/100 * taxrate) into the total;
End the if;
- and, finally, save to the out variable
SELECT the total INTO ototal;
End//
Run the following:
drop table ordertotals;
Call processorders;
Select * from ordertotals
Output:
20005
158.8620009
40.7820006
58.3020007
1060.0020008
132.5020008
132.50Excuse me why at the last insert two 20008? The code is "MYSQL will know will be" above, the result of the book is no more than a 20008, thank you
CodePudding user response:
Where is the master?CodePudding user response:
In the stored procedure insert some select variables to see if you think the print out some parameters