delimiter //
create procedure processorders()
begin
-- declare local variable
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
-- declare the cursor
declare ordernumbers cursor for
select order_num from orders;
-- declare continue handler
declare continue handler for sqlstate '02000' set done=1;
-- create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals (
order_num INT,
total DECIMAL(8 , 2 )
);
-- open the cursor
open ordernumbers;
-- 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//
delimiter //
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
) comment 'obtain order total , optionally adding tax'
begin
-- declare variable for total
declare total decimal(8,2);
-- declare tax percentage
declare taxrate int default 6;
-- get the order total
SELECT
SUM(item_price * quantity)
FROM
orderitems
WHERE
order_num = onumber INTO total;
-- is this taxable?
if taxable then
-- yes ,so add taxrate to the total
select total+(total/100*taxrate) into total;
end if;
-- and finally, save to out variable
SELECT total INTO ototal;
end //
運行如下：
drop table ordertotals;
call processorders;
select * from ordertotals
輸出結果：
20005 158.86
20009 40.78
20006 58.30
20007 1060.00
20008 132.50
20008 132.50
請問為什么會在最后插入兩個20008呀？這些代碼就是《MYSQL必知必會》上面的，書上的結果就沒有多插一條20008。謝謝
uj5u.com熱心網友回復：
高手在哪里？uj5u.com熱心網友回復：
在存盤程序里插入一些select 變數 來列印出來一些引數 看是不是你想想中的轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109467.html
標籤：MySQL