/*
编写一段程序,将每种玩具的价格提高¥0.5,
直到玩具的平均价格接近$24.5为止。此外,任何玩具的最大价格不应超过$53。
*/
create procedure rise_price()
begin
declare average float(4,2);
declare maxV float(4,2);
select avg(mtoyrate) into average from toys;
select max(mtoyrate) into maxV from toys;
when average <= 24.5 && maxV <= 53 do
update toys set mtoyrate = mtoyrate + 0.5;
select avg(mtoyrate) into average from toys;
select max(mtoyrate) into maxV from toys;
end while;
if average > 24.5 || maxV > 53 then
update toys set mtoyrate = mtoyrate - 0.5;
end;
--执行时
call rise_price();
/*创建一个称为prcCharges的存储过程,它返回某个定单号的装运费用和包装费用。
*/
--表格
mysql> select corderno,mshippingcharges,mgiftwrapcharges from orders;
+----------+------------------+------------------+
| corderno | mshippingcharges | mgiftwrapcharges |
+----------+------------------+------------------+
| 000001 | 6 | 1.25 |
| 000002 | 8 | 2 |
| 000003 | 12 | 0 |
+----------+------------------+------------------+
9 rows in set
create procedure prccharges(in orderno char(6),
out shippingCharges float(4,2),out wrapCharges float(4,2))
begin
select mshippingcharges into shippingCharges from
orders where corderno = orderno;
select mgiftwrapcharges into wrapcharges from orders
where corderno = orderno;
end;
--执行时命令
call prccharges('000001',@pp,@qq);
select @pp,@qq;
--执行结果
mysql> select @pp,@qq;
+-----+------+
| @pp | @qq |
+-----+------+
| 6 | 1.25 |
+-----+------+
1 row in set
/*创建一个称为prcHandlingCharges的过程,它接收定单号并显示经营费用。
提示:经营费用=装运费+礼品包装费
*/
create procedure prcHandLingCharges(in orderno char(6),
out handlingCharges float(4,2))
begin
declare ppp float(4,2);
declare qqq float(4,2);
call prccharges(orderno,ppp,qqq);
set handlingCharges = ppp + qqq;
end;
--执行时命令
call prcHandLingCharges('000001',@qqqq);
select @qqqq;
--执行结果
mysql> select @qqqq;
+-------+
| @qqqq |
+-------+
| 7.25 |
+-------+
1 row in set
编写一段程序,将每种玩具的价格提高¥0.5,
直到玩具的平均价格接近$24.5为止。此外,任何玩具的最大价格不应超过$53。
*/
create procedure rise_price()
begin
declare average float(4,2);
declare maxV float(4,2);
select avg(mtoyrate) into average from toys;
select max(mtoyrate) into maxV from toys;
when average <= 24.5 && maxV <= 53 do
update toys set mtoyrate = mtoyrate + 0.5;
select avg(mtoyrate) into average from toys;
select max(mtoyrate) into maxV from toys;
end while;
if average > 24.5 || maxV > 53 then
update toys set mtoyrate = mtoyrate - 0.5;
end;
--执行时
call rise_price();
/*创建一个称为prcCharges的存储过程,它返回某个定单号的装运费用和包装费用。
*/
--表格
mysql> select corderno,mshippingcharges,mgiftwrapcharges from orders;
+----------+------------------+------------------+
| corderno | mshippingcharges | mgiftwrapcharges |
+----------+------------------+------------------+
| 000001 | 6 | 1.25 |
| 000002 | 8 | 2 |
| 000003 | 12 | 0 |
+----------+------------------+------------------+
9 rows in set
create procedure prccharges(in orderno char(6),
out shippingCharges float(4,2),out wrapCharges float(4,2))
begin
select mshippingcharges into shippingCharges from
orders where corderno = orderno;
select mgiftwrapcharges into wrapcharges from orders
where corderno = orderno;
end;
--执行时命令
call prccharges('000001',@pp,@qq);
select @pp,@qq;
--执行结果
mysql> select @pp,@qq;
+-----+------+
| @pp | @qq |
+-----+------+
| 6 | 1.25 |
+-----+------+
1 row in set
/*创建一个称为prcHandlingCharges的过程,它接收定单号并显示经营费用。
提示:经营费用=装运费+礼品包装费
*/
create procedure prcHandLingCharges(in orderno char(6),
out handlingCharges float(4,2))
begin
declare ppp float(4,2);
declare qqq float(4,2);
call prccharges(orderno,ppp,qqq);
set handlingCharges = ppp + qqq;
end;
--执行时命令
call prcHandLingCharges('000001',@qqqq);
select @qqqq;
--执行结果
mysql> select @qqqq;
+-------+
| @qqqq |
+-------+
| 7.25 |
+-------+
1 row in set