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

1.实验目的

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

2.实验内容和要求

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

3.实验步骤和实验结果

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

```sqlCREATEPROCEDURE proc1 ()BEGINUPDATE orders 
    SET totalprice =(SELECTSUM(
                extendedprice *(1+tax 
                    )*(1-discount 
                ))FROM
            lineitem 
        WHERE
            orders.orderkey = lineitem.orderkey 
        );END;

调用存储过程：
CALL proc1;

查看：
实验前数据：

实验后数据：

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

CREATEPROCEDURE proc2 (IN okey INT)BEGINUPDATE orders 
    SET totalprice =(SELECTSUM( extendedprice *(1+tax )*(1-discount ))FROM
            lineitem 
        WHERE
            orders.orderkey = lineitem.orderkey 
        );END;

调用存储过程：
CALL proc2(20);

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

CREATEPROCEDURE proc3 (IN cname INT)BEGINDECLARE
        ckey INT;SELECT
        custkey INTO ckey 
    FROM
        customer 
    WHERE
        customer.NAME = cname;UPDATE orders 
    SET totalprice =(SELECTSUM(
                extendedprice *(1+tax 
                    )*(1-discount 
                ))FROM
            lineitem,
            orders 
        WHERE
            lineitem.orderkey = orders.orderkey 
            AND orders.custkey = ckey 
        );END;

调用：
首先根据orderkey找到一个顾客名用做调用存储过程的参数值：
CALL proc3(‘孔珍’);

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

CREATEPROCEDURE proc4 (IN cname CHAR(25),OUT tprice DECIMAL(10,2))BEGINDECLARE
        ckey INT;SELECT
        custkey INTO ckey 
    FROM
        customer 
    WHERE
        NAME = cname;UPDATE orders 
    SET totalprice =(SELECTSUM(
                extendedprice *(1+tax 
                    )*(1-discount 
                ))FROM
            lineitem 
        WHERE
            lineitem.orderkey = orders.orderkey 
        );SELECTSUM( totalprice )INTO tprice 
    FROM
        orders 
    WHERE
        orders.custkey = ckey 
    GROUPBY
    orderkey;END;

调用：

查看：

（5）删除存储过程 proc4：
DROP PROCEDURE proc4;

查看：

（6）定义一个存储过程 proc5，更新某年订单(含税折扣价)的总价，执行这个存储过程。（要求使用游标完成）：
创建存储过程：

CREATEPROCEDURE proc5 ()BEGINDECLARE
        cursor_price DECIMAL(10,2);#记录某一个totalpriceDECLARE
        cursor_extendedprice DECIMAL(8,2);DECLARE
        cursor_discount DECIMAL(3,2);DECLARE
        cursor_tax DECIMAL(3,2);DECLARE
        cursor1 CURSORFORSELECT
        extendedprice 
    FROM
        lineitem,
        orders 
    WHERE
        orders.orderkey = lineitem.orderkey;DECLARE
        cursor2 CURSORFORSELECT
        discount 
    FROM
        lineitem,
        orders 
    WHERE
        orders.orderkey = lineitem.orderkey;DECLARE
        cursor3 CURSORFORSELECT
        tax 
    FROM
        lineitem,
        orders 
    WHERE
        orders.orderkey = lineitem.orderkey;OPEN cursor1;OPEN cursor2;OPEN cursor3;FETCH cursor1 INTO cursor_extendedprice;FETCH cursor2 INTO cursor_discount;FETCH cursor3 INTO cursor_tax;SET cursor_price = cursor_extendedprice *(1+cursor_tax 
        )*(1-cursor_discount 
    );CLOSE cursor1;CLOSE cursor2;CLOSE cursor3;SELECTSUM( totalprice )FROM
        orders 
    WHEREYEAR( orderdate )=2022;END;

查看：

（7）定义一个存储过程 proc6，能够根据某年(年份作为输入参数，整数)各个客户的下订单购买情况，把该年客户订单总金额超过某个阈值（该阈值为输入参数，为整数）的客户表（customer）中的备注（comment）字段更新为重要客户（“SVIP”）（要求使用游标完成）：
创建存储过程：

CREATEPROCEDURE proc6 (INYEARINT,IN threshold INT)BEGINDECLARE
        price DECIMAL(10,2);DECLARE
        cursor_comment VARCHAR(100);DECLARE
        cursor1 CURSORFORSELECTCOMMENTFROM
        customer 
    WHERE
        custkey IN(SELECT custkey FROM orders );DECLARE
        cursor2 CURSORFORSELECT
        totalprice 
    FROM
        orders 
    WHEREYEAR( orderdate )=YEARAND custkey IN(SELECT custkey FROM customer );OPEN cursor1;OPEN cursor2;FETCH cursor1 INTO cursor_comment;FETCH cursor2 INTO price;IF
        price >= threshold THENSET cursor_comment ='SVIP';ENDIF;CLOSE cursor1;END;

查看：