程序如下:
use test1;
delimiter //
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
) comment ' obtain order total, optionally adding tax'
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity)
from orderitems
where order_num=onumber
into total;
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end //
delimiter ;
delimiter //
create procedure processorderers()
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
declare ordernumbers cursor
for
select order_num from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals(order_num int,total decimal(8,2));
open ordernumbers;
REPEAT
fetch ordernumbers into o;
call ordertotal(o,1,t);
insert into ordertotals(drder_num,total)
values(o,t);
until done end repeat;
close ordernumbers;
end //
delimiter ;在输入select * from ordertotals;后,一开始说在这个库test1下无ordertotals表,后来改改就可以输出了,但是ordertotals表里是空的,无数据,这是什么原因,请各位帮帮小弟,谢谢啦!!!
use test1;
delimiter //
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
) comment ' obtain order total, optionally adding tax'
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity)
from orderitems
where order_num=onumber
into total;
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end //
delimiter ;
delimiter //
create procedure processorderers()
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
declare ordernumbers cursor
for
select order_num from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals(order_num int,total decimal(8,2));
open ordernumbers;
REPEAT
fetch ordernumbers into o;
call ordertotal(o,1,t);
insert into ordertotals(drder_num,total)
values(o,t);
until done end repeat;
close ordernumbers;
end //
delimiter ;在输入select * from ordertotals;后,一开始说在这个库test1下无ordertotals表,后来改改就可以输出了,但是ordertotals表里是空的,无数据,这是什么原因,请各位帮帮小弟,谢谢啦!!!
解决方案 »
- Eclipse+Mysql下开发,如何根据数据库中的一张表的内容生成数据库中的另一张表,求大虾帮帮忙,万分感谢!!!
- 这句话是什么意思
- 关于Mysql 时间的问题
- 求一条SQL语句,更改两个表中的同一字段。两个表用相同ID关联的
- 有什么办法,可以在WINXP上把LINUX里的MYSQL数据库完全备份到WINXP机器??或者是导出到WINXP机器?
- 如何写mysql的备份批处理?
- 求助!安装MYSQL5.0后无法运行
- 请教高手,在线等!
- mysql有没有办法记录查询错误、警告日志?
- 【求助】order by , group by 后 怎么设置一组数据内的序号
- 创建外键时,如何可阻止innodb自动创建索引
- 关于 Mysql MMM
call ordertotal(o,1,t);
SET DONE=0;
...
call ordertotal(o,1,t);
SELECT DONE;检查DONE的值