本文已参与「新人创作礼」活动，一起开启掘金创作之路。

【MySQL】数据库综合应用-水果商店

一、目的与要求：

综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、操作内容：

1.修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。

源码：

CREATE PROCEDURE priceOF()
BEGIN

DECLARE number INT;
DECLARE item_price decimal(8,2); 
DECLARE priceOrdFru CURSOR FOR 
SELECT f_price from fruits;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN priceOrdFru;
FETCH next FROM priceOrdFru INTO item_price;

WHILE (number=0) DO
UPDATE orderitems 
SET item_price=(SELECT f_price FROM fruits
WHERE fruits.f_id = orderitems.f_id);
FETCH next FROM priceOrdFru INTO item_price;
END WHILE;
CLOSE priceOrdFru;

END

CALL priceOF();
SELECT* FROM orderitems

2.在订单详情表orderitems插入新订单时自动获得水果价格。

CREATE TRIGGER updateOrd_Fru BEFORE INSERT 
ON orderitems FOR EACH ROW
BEGIN
DECLARE price decimal(8,2);
SELECT f_price INTO price 
FROM fruits WHERE f_id=new.f_id;
SET new.item_price=price;
END

INSERT 
INTO orderitems(o_num,o_item,f_id,quantity)
VALUES(30006,5,'a0',52);
SELECT* FROM orderitems

3.在总的订单表orders中新增“原价格”、“折扣”“应付款”三个属性，三个属性要求如下：

①　属性名分别为original_price、discount、pay，数据类型都是decimal(10,2)；

②　“原价格”是自动统计“订单详情”表orderitems中同一订单的总金额，该属性要求非空，初值0

③　“折扣”是当前折扣信息（后续根据VIP等级自动更新，本题用初值1计算），该属性要求非空，初值1； “应付款”是打折后的价格，该属性要求非空，初值0。

ALTER TABLE orders
ADD  original_price decimal(10,2) not null
DEFAULT 0;
ALTER TABLE orders
ADD discount decimal(10,2) not null
DEFAULT 1;
ALTER TABLE orders
ADD pay decimal(10,2) not null
DEFAULT 0;

SELECT* FROM orders

设计实验完成以下三项功能

①　对总订单表orders修改已销售总订单

CREATE PROCEDURE updateOrd_Fru()
BEGIN
DECLARE number INT;
DECLARE original_price decimal(10,2); 
DECLARE updateOrdFru CURSOR FOR 
SELECT sum(quantity * item_price) AS SumSI
from orderitems;
DECLARE CONTINUE HANDLER FOR 
NOT FOUND SET number=1;
SET number=0;
OPEN updateOrdFru;
FETCH next FROM updateOrdFru INTO original_price;
WHILE (number=0) DO

UPDATE orders 
SET original_price =(SELECT sum(quantity * item_price)
AS SumSI
FROM orderitems
WHERE orderitems.o_num = orders.o_num);

FETCH next FROM updateOrdFru INTO original_price ;
END WHILE;
CLOSE updateOrdFru;
END
CALL updateOrd_Fru();
UPDATE orders SET pay = original_price * discount;
SELECT* FROM orders

②　在订单详情表orderitems新增订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  insertOrd_Ord1  
AFTER INSERT ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + new.quantity * new.item_price
WHERE orders.o_num = new.o_num;
END

CREATE TRIGGER  insertOrd_Ord1  
AFTER INSERT ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + new.quantity * new.item_price
WHERE orders.o_num = new.o_num;
END

INSERT 
INTO orderitems(o_num,o_item,f_id,quantity)
VALUES(30005,5,'a1',5);
SELECT* FROM orders

③　在订单详情表orderitems删除订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  deleteOrd_Ord1  
AFTER DELETE ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price - old.quantity * old.item_price
WHERE orders.o_num = old.o_num;
END

DELETE FROM orderitems
WHERE o_num = 30005 AND f_id = 'a1';
SELECT* FROM orders

④　在订单详情表orderitems修改订单项时同步修改总订单表orders相关属性值

CREATE TRIGGER  updateOrd_Ord0
AFTER UPDATE ON orderitems 
FOR EACH ROW 
BEGIN
UPDATE orders 
SET original_price = original_price + 
(new.quantity-old.quantity) * old.item_price
WHERE orders.o_num = new.o_num;
END

UPDATE orderitems
SET quantity = 15
WHERE o_num = 30005 AND f_id = 'a2';
SELECT* FROM orders

小结

1．遇到的问题及解决过程

1.数据无法正确的逐个插入问题，通过游标工具正确的解决

2.在修改价格时，pay值未正确改变，通过再次创建触发器解决了该问题。

2．产生的错误及原因分析

1.[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE orderitems SET item_price=(SELECT f_price FROM fruits WHERE fruits.f_i' at line 7

原因：系统中为设置空值默认输入，需要输入语句：

SET @@GLOBAL.sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

2.[Err] 1442 - Can't update table 'orderitems' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

原因：在一个表的触发器中不能同时进行insert/update/delete操作，需要改为set操作