数据库实验七 综合实验-水果商店进阶二

---

一、实验目的与要求：

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

二、实验内容：

设计并完成以下实验，要求附上源码（非截图），测试效果截图

1.在客户表customers中添加“VIP”字段，默认值0，根据已购买的累计金额自动更新等级，如累计100_{199，一星VIP折扣9.9，200}499二星VIP折扣9.7，500_{999三星VIP折扣9.5，1000}1999，四星VIP折扣9，后8.5折。

①　根据已购买情况修改客户表的VIP信息
源码：

先把vip字段添加，默认值为0
ALTER table customers
add VIP INT(11) not NULL DEFAULT(0);
再设计游标，更新等级
create PROCEDURE C_starts()
BEGIN
     DECLARE cc_id INT(11);
     DECLARE csum_price decimal(10,2);
     DECLARE done TINYINT DEFAULT 0;
     DECLARE C_starts cursor for select c_id,sum(original_price) FROM orders group by c_id;
     DECLARE CONTINUE HANDLER FOR NOT found set done=1;
open C_starts;
read_cur:LOOP
      FETCH C_starts INTO cc_id,csum_price;
      IF done then LEAVE read_cur;
      END IF;
      UPDATE customers_copy set VIP=
      CASE
       WHEN csum_price BETWEEN 0 AND 100 THEN 0
       WHEN csum_price BETWEEN 100 AND 199 THEN 1
       WHEN csum_price BETWEEN 200 AND 499 THEN 2
       WHEN csum_price BETWEEN 500 AND 999 THEN 3
       WHEN csum_price BETWEEN 1000 AND 1999 THEN 4
       WHEN csum_price>1999 THEN 5
      END
     WHERE c_id=cc_id;
      END loop read_cur;
CLOSE C_starts;
END

运行测试结果截图:


②　在订单表orders中插入新订单时自动计算并插入当前客户的折扣信息，注不修改原订单的折扣
源码：

CREATE
    TRIGGER `new_discount` BEFORE INSERT ON `orders` 
    FOR EACH ROW BEGIN
     DECLARE Oc_vip INT;
     SET Oc_vip=(SELECT vip FROM customers WHERE c_id=new.c_id);
     SET new.discount=(
     CASE 
       WHEN  Oc_vip=0 THEN 1
       WHEN  Oc_vip=1 THEN 0.99
       WHEN  Oc_vip=2 THEN 0.97
       WHEN  Oc_vip=3 THEN 0.95
       WHEN  Oc_vip=4 THEN 0.9
       WHEN  Oc_vip=5 THEN 0.85
     END);
END;

运行测试结果截图:
现在orders中插入了新的o_num30011和30012
后来又在orderitems中插入数据

2. 查询指定客户的“总优惠金额”，即累计每次购买时为客户优惠金额。

源码：

SET GLOBAL log_bin_trust_function_creators=1;
CREATE FUNCTION All_discount(cid INT) 
RETURNS DECIMAL(8,2)
RETURN (
	SELECT SUM(original_price-pay)
	FROM 
	orders 
	where c_id=cid
  GROUP BY c_id
	)

运行测试结果截图:

三、实验小结

1．实验中遇到的问题及解决过程
在“在订单表orders中插入新订单时自动计算并插入当前客户的折扣信息，注不修改原订单的折扣 ”最后测试的时候刚开始就直接在orders里面插入。后来觉得为了数据的统一性还是在orderitems中插入，然后就也可以用到上次实验的触发器。

2．实验中产生的错误及原因分析
暂无

3．实验体会和收获。
我想收回我上次实验中说的对触发器和游标有了深入的认识这句话，因为在这次实验中，其实有些方法中的细节刷新了我对触发器和游标的认识，我觉得吧，自己最多能说句，能用触发器和游标来解决问题，但是不断地学习总有不断地学习知识点的刷新，加油吧。