数据库实验七：存储过程实验

233333，实验八的时候总感觉好像实验七计算的公式不太对，懒得改了就这样吧2333

定义一个存储过程proc2，更新给定订单的(含税折扣价)的总价，执行这个存储过程。

含税折扣总价=extendedprice*(税率+1）*折扣

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.orderkey=18 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
CREATE PROCEDURE proc2()
BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey = 18;
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem WHERE orderkey = 18 GROUP BY orderkey;
 UPDATE orders SET totalprice = sum_price WHERE orderkey = 18;
END;
 $$
DELIMITER ;
CALL proc2();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.orderkey=18 && orders.orderkey=lineitem.orderkey;

定义一个存储过程proc3，更新某个顾客的所有订单的（含税折扣价）总价，执行这个存储过程。

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=320728 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
CREATE PROCEDURE proc3()
BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey IN (SELECT orderkey FROM orders WHERE custkey = 320728);
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem,orders WHERE orders.custkey = 320728 && lineitem.orderkey=orders.orderkey GROUP BY orders.custkey;
 UPDATE orders SET totalprice = sum_price WHERE custkey = 320728;
END;
 $$
DELIMITER ;
CALL proc3();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=320728 && orders.orderkey=lineitem.orderkey;

定义一个存储过程proc4，更新某个顾客的所有订单的（含税折扣价）总价并输出该总价，执行这个存储过程。

SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=518445 && orders.orderkey=lineitem.orderkey;
DELIMITER $$
 CREATE PROCEDURE proc4()
 BEGIN
 DECLARE sum_price numeric(10,2);
 UPDATE lineitem SET extendedprice = extendedprice * discount  * (1+tax) WHERE orderkey IN (SELECT orderkey FROM orders WHERE custkey = 518445);
 SELECT SUM(lineitem.extendedprice) INTO sum_price FROM lineitem,orders WHERE orders.custkey = 518445 && lineitem.orderkey=orders.orderkey GROUP BY orders.custkey;
 UPDATE orders SET totalprice = sum_price WHERE custkey = 518445;
 SELECT sum_price;
END;
 $$
DELIMITER ;
CALL proc4();
SELECT lineitem.extendedprice,lineitem.orderkey,lineitem.tax,lineitem.discount,orders.totalprice  FROM lineitem,orders WHERE orders.custkey=518445 && orders.orderkey=lineitem.orderkey;

删除存储过程proc4。

DROP PROCEDURE proc4;

定义一个存储过程proc1，更新所有订单(含税折扣价)的总价，执行这个存储过程。

先使用游标逐行更新lineitem表所有extendedprice，再使用更新后的extendedprice更新totalprice

lineitem更新前

orders更新前

DELIMITER $$
CREATE PROCEDURE proc1()
BEGIN
 DECLARE totalprice_y numeric(10,2);
 DECLARE totalprice_x numeric(10,2);	
 DECLARE count INT; #统计一共多少行
 DECLARE orderkey_y INT;/*orderkey 和 linenumber联合做主键，这里取这俩*/
 DECLARE linenumber_y INT;
 DECLARE extendedprice_y DECIMAL(15,2);
 DECLARE discount_y DECIMAL(3,2);
 DECLARE tax_y DECIMAL(3,2);
 DECLARE count_y INT DEfAULT 0;
 DECLARE done2 INT DEFAULT 0;
 DECLARE orderkey_u INT;
 DECLARE count2 INT;
 DECLARE count3 INT;
 
 DECLARE done BOOLEAN DEFAULT FALSE; 
 
 DECLARE orderkey_x CURSOR FOR SELECT
	orderkey,linenumber,extendedprice,discount,tax FROM lineitem;
 DECLARE orderkey_z CURSOR FOR SELECT
  orderkey,totalprice FROM orders;
	
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;#找不到的时候done置TRUE 
 
 SELECT COUNT(DISTINCT orderkey) INTO count FROM lineitem; 
 SELECT COUNT(DISTINCT orderkey) INTO count2 FROM orders;
 SET count3 = count2;
 
#更新lineitem的所有extendedprice
 OPEN orderkey_x;
 FETCH orderkey_x INTO orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y;
REPEAT

 SET extendedprice_y = extendedprice_y * discount_y * (1.00 + tax_y);
 UPDATE lineitem SET extendedprice = extendedprice_y  WHERE orderkey = orderkey_y AND linenumber = linenumber_y;
 SELECT orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y,count_y;
 SET count_y = count_y + 1;
 FETCH orderkey_x INTO orderkey_y,linenumber_y,extendedprice_y,discount_y,tax_y;

UNTIL done END REPEAT;
CLOSE orderkey_x;





#更新orders的所有totalprice
 OPEN orderkey_z;
 FETCH orderkey_z INTO orderkey_y,totalprice_y;

WHILE count2 > 0 DO

#orderkey_y取自orders,如果在lineitem中也存在，将orderkey_u=orderkey_y;
SELECT orderkey INTO orderkey_u FROM lineitem
 WHERE orderkey = orderkey_y GROUP BY orderkey;
#如果orderkey_u = orderkey_y; 计算该orderkey下的订单总和，否则done2置1
IF orderkey_u = orderkey_y THEN 
SELECT SUM(extendedprice) INTO totalprice_x FROM lineitem
 WHERE orderkey = orderkey_u GROUP BY orderkey ;
SET done2 = 0;
ELSE
SET done2 = 1;
END IF;

IF done2 = 0 THEN
UPDATE orders SET totalprice = totalprice_x WHERE orderkey = orderkey_y;
SELECT orderkey_y,totalprice_y as old_price, totalprice_x as new_price;
ELSE
SELECT orderkey_y,"该orderkey不在lineitem中";
END IF;

SET done2 = 0;
FETCH orderkey_z INTO orderkey_y,totalprice_y;
SET count2 = count2 - 1;
END WHILE;

CLOSE orderkey_z;

select count_y as orderkey_linenumber_count_in_lineitem , count as distinct_orderkey_count_in_lineitem,count3 as orderkey_count_in_orders;
END
 $$
DELIMITER ;
CALL proc1();

更新exetendedprice的循环一的执行

使用更新后的extendedprice更新totalprice的循环二的执行

更新后的lineitem和orders

主要问题所在：DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 这个问题很大，如果游标只是遍历一个表，而且操作也只在这个表，那么用这个语句标志循环结束，可能一般情况都没有问题，比如循环一就可以实现，但是的但是：在循环二中要更新orders中的totalprice，游标设置在orders表上，但是有些orderkey并不存在于lineitem表的orderkey中，那么如果依然用这个done做结束标志的话：在循环里orderkey一旦出现在lineitem中select找不到的情况，done直接置TRUE，然后执行完后面语句到until那里一判断就跳出循环了，而实际上我们想的是在lineitem里面的没有orders中的某个orderkey的时候，直接去获取orders中的下一个orderkey看看在lineitem中有没有，如果有那么就使用lineitem的内容计算所有这种orderkey行的extendprice的总和，把这个值赋值给orders的totalprice就行，所以不能再使用这种循环结束方式，然后我计算了一下orders中的行数，用这个行数循环orders就行了