MySQL数据库-实验七 存储过程实验

一、实验目的

掌握数据库存储过程的设计和使用方法。

二、实验内容和要求

存储过程的定义，存储过程运行，存储过程更名，存储过程删除。

三、实验步骤

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

create procedure proc1 ()

begin

    update orders

        set totalprice = (

            select

                sum(

                    extendedprice * (1-discount) * (1+tax)

                    )

            from lineitem

            where orders.orderkey = lineitem.orderkey

            );

end;

call proc1();

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

程。

create procedure proc1 ()

begin

    update orders

        set totalprice = (

            select

                sum(

                    extendedprice * (1-discount) * (1+tax)

                    )

            from lineitem

            where orders.orderkey = lineitem.orderkey

            );

end;

call proc1();

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

这个存储过程。

create procedure proc3(in cname char(30))

begin

declare tag_custkey int;

select custkey into tag_custkey from customer where name = cname;

update orders  set totalprice = ( select  sum(extendedprice * (1-discount)*(1+tax))from lineitem

        where orders.orderkey = lineitem.orderkey and orders.custkey = tag_custkey);

end;

call proc3 ('白毛');

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

该总价，执行这个存储过程。

create procedure proc4(

    in cname char(30),

    out totalprice numeric(10,2)

    )

begin

    declare tag_custkey int;

    select  custkey into tag_custkey from customer where name = cname ;

    update  orders set totalprice = (

        select  sum(extendedprice * (1-discount )*(1+tax))from lineitem where orders.orderkey = lineitem.orderkey and orders.custkey = tag_custkey

        );

    select  sum(totalprice) into totalprice from orders where custkey = tag_custkey group by  custkey;

end;

call proc4('白毛',@test);

SELECT @test ;

（5）删除存储过程 proc4。

drop procedure proc4 ;

（6）定义一个存储过程 proc5，更新某年订单(含税折扣价)的总价，执行这个存储过程。

（要求使用游标完成）。

CREATE PROCEDURE proc5(IN p_year INT)

BEGIN

    DECLARE done INT DEFAULT FALSE;

    declare order_id int;

    declare total_price decimal(10,2) ;

    DECLARE cur_order CURSOR FOR SELECT  orders.orderkey,SUM(extendedprice * (1 - discount) * (1 + tax))

        AS total_price FROM lineitem,orders WHERE lineitem.orderkey= orders.orderkey

            and  YEAR(orderdate) = p_year GROUP BY orderkey;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_order;

    read_loop: LOOP

        FETCH cur_order INTO order_id, total_price;

        IF done THEN

            LEAVE read_loop;

        END IF;

        UPDATE orders SET totalprice = total_price WHERE orders.orderkey = order_id;

    END LOOP;

    CLOSE cur_order;

END //

call proc5 (2023);

（7）定义一个存储过程 proc6，能够根据某年(年份作为输入参数，整数)各个客户的下

订单购买情况，把该年客户订单总金额超过某个阈值（该阈值为输入参数，为整数）的客户表（customer）中的备注（comment）字段更新为重要客户（“SVIP”）（要求使用游标完成）。

DELIMITER //

CREATE PROCEDURE proc6(IN p_year INT, IN p_threshold INT)

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE v_customer_id INT;

    DECLARE v_total_amount DECIMAL(10, 2);

    DECLARE cur_customer CURSOR FOR SELECT custkey, SUM(extendedprice * (1 - discount) * (1 + tax)) AS total_amount FROM lineitem,orders WHERE lineitem.orderkey = orders.orderkey and  YEAR(orders.orderdate) = p_year GROUP BY custkey;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



    OPEN cur_customer;



    read_loop: LOOP

        FETCH cur_customer INTO v_customer_id, v_total_amount;

        IF done THEN

            LEAVE read_loop;

        END IF;



        IF v_total_amount > p_threshold THEN

            UPDATE customer SET comment = 'SVIP' WHERE custkey = v_customer_id;

        END IF;

    END LOOP;

    CLOSE cur_customer;

END //

DELIMITER ;



call proc6(2023,500000);

select  * from customer where comment = 'SVIP';

查找一下设置的信息。