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

一、实验目的

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

二、实验内容

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

1.在客户表customers中添加“VIP”字段

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

①根据已购买情况自动修改客户表中所有客户的VIP信息

源码：

ALTER TABLE customers ADD VIP INT NOT NULL DEFAULT(0);

#游标
CREATE PROCEDURE c_vip()
BEGIN
	DECLARE vip_price DECIMAL(6,2);
	DECLARE vip_id INT(10);
	DECLARE done TINYINT DEFAULT 0;
	DECLARE c_vip 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_vip;
read_loop:LOOP
	FETCH c_vip INTO vip_id,vip_price;
	IF done THEN
		LEAVE read_loop;
	END IF;
	UPDATE customers SET VIP=CASE
		WHEN vip_price BETWEEN 0 AND 100 
			THEN 0
		WHEN vip_price BETWEEN 100 AND 199 
			THEN 1
		WHEN vip_price BETWEEN 200 AND 499 
			THEN 2
		WHEN vip_price BETWEEN 500 AND 999 
			THEN 3
		WHEN vip_price BETWEEN 1000 AND 1999 
			THEN 4
		WHEN vip_price > 1999 
			THEN 5
		END
		WHERE c_id=vip_id;
	
	END LOOP read_loop;

CLOSE c_vip;
END

运行测试结果截图：

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

源码：

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

运行测试结果截图：

插入“30009”一行：

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

源码：SELECT SUM(original_price-pay) "总优惠金额" FROM orders WHERE c_id = 10001;
运行测试结果截图：

3.f_id中t字母开头的是特价产品，限制每一位客户每次够买特价产品不能超过50元

源码：

CREATE TRIGGER t_fruits BEFORE INSERT ON orderitems FOR EACH ROW 
	BEGIN
		DECLARE t_price DECIMAL(8,2);
		DECLARE t_fruits CURSOR FOR SELECT new.quantity*new.item_price FROM orderitems WHERE new.f_id LIKE 't%';
		
		OPEN t_fruits;
		FETCH t_fruits INTO t_price;
		IF
			t_price > 50
		THEN
			SIGNAL SQLSTATE 'HY000' SET message_text='购买特价商品不得超过50元！';
		END IF;
		
		CLOSE t_fruits;
	END

运行测试（购买正常及错误的）结果截图：
正常：

错误：

三、实验小结

1.实验中遇到的问题及解决过程

第一题中设计游标时报错：

原因是在更新等级的循环后面少了一次“end”：

2.实验中产生的错误及原因分析

产生错误：添加完触发器后增加记录时报错

原因分析：
查找字符’t’忘记在其后加上“%”