数据库存储器

最新推荐文章于 2022-12-30 10:49:21 发布

m0_59603897

最新推荐文章于 2022-12-30 10:49:21 发布

阅读量986

点赞数 2

文章标签：数据库 database

本文链接：https://blog.csdn.net/m0_59603897/article/details/121512528

版权

存储过程实验

1.实验目的

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

2.实验内容和要求

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

1. 实验步骤

(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 proc2(did INT)

BEGIN

UPDATE `orders 订单表` SET totalprice=(SELECT SUM(extendedprice*(1-discount)*(1+tax)) FROM `lineitem 订单明细表` WHERE `orders 订单表`.orderkey=`lineitem 订单明细表`.orderkey AND `lineitem 订单明细表`.orderkey=did);

END

CALL proc2(100)

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

这个存储过程。

CREATE PROCEDURE proc3(cname char(20))

BEGIN

DECLARE cnumber INTEGER;

SELECT custkey INTO cnumber

FROM `customer 顾客表`

WHERE `name` = cname;

UPDATE `orders 订单表`,

(SELECT orderkey,SUM(extendedprice*(1 - discount)*(1 + tax)) AS sum_account

FROM `lineitem 订单明细表`

GROUP BY orderkey) AS temp

SET `orders 订单表`.totalprice = sum_account

WHERE `orders 订单表`.orderkey = temp.orderkey AND `orders 订单表`.custkey = cnumber;

END;

执行：

call proc3(阿宝宝)

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

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

CREATE PROCEDURE proc4(cname char(20),OUT account INTEGER)

BEGIN

DECLARE cnumber INTEGER;

SELECT custkey INTO cnumber

FROM `customer 顾客表`

WHERE `name`= cname;

UPDATE `orders 订单表`,

(SELECT orderkey,SUM(extendedprice*(1 - discount)*(1 + tax)) AS sum_account

FROM `lineitem 订单明细表`

GROUP BY orderkey) AS temp

SET `orders 订单表`.totalprice = sum_account

WHERE `orders 订单表`.orderkey = temp.orderkey AND `orders 订单表`.custkey = cnumber;

SELECT SUM(`orders 订单表`.totalprice) INTO account

FROM `orders 订单表`

GROUP BY custkey

HAVING `orders 订单表`.custkey=cnumber;

END;

执行：

CALL proc4('阿宝宝',@account);

查看存储结果：SELECT @account;

（5）删除存储过程 proc4。

Drop PROCEDURE proc4

触发器实验

2. 实验目的

掌握数据库触发器的设计和使用方法

2.实验内容和要求

定义 BEFORE 触发器和 AFTER 触发器。能够理解不同类型触发器的作用和执行原理，验

证触发器的有效性。

3. 实验步骤

（1）AFTER 触发器

①在 Lineitem 表上定义一个 UPDATE 触发器，当修改订单明细（即修改订单明细价格

extendeprice、折扣 discount、税率 tax）时，自动修改订单 Orders 的 TotalPrice，以保

持数据一致性。

create trigger AFTER_update

after update on `lineitem 订单明细表`

for each row

begin

update `orders 订单表`

set totalprice=totalprice+(new.extendedprice*(1-new.discount)*(1+new.tax)

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

where orderkey=new.orderkey;

end

②在 Lineitem 表上定义一个 INSERT 触发器，当增加一项订单明细时，自动修改订单

Orders 的 TotalPrice，以保持数据的一致性。

create trigger AFTER_insert

after insert on `lineitem 订单明细表`

for each row

begin

update `orders 订单表` set totalprice

=totalprice+NEW.extendedprice*(1-NEW.discount)*(1+NEW.tax)

where orderkey=NEW.orderkey;

end

③在 Lineitem 表上定义一个 DELETE 触发器，当删除一项订单明细时，自动修改订单

Orders 的 TotalPrice，以保持数据一致性。

create trigger ALTER_delete

after delete on `lineitem 订单明细表`

for each row

begin

update `orders 订单表` set totalprice

=totalprice-OLD.extendedprice*(1-OLD.discount)*(1+OLD.tax)

where orderkey=orderkey;

End

④验证上面的三个触发器是否起作用。

（1）

SELECT totalprice FROM `orders 订单表` WHERE orderkey=18

update `lineitem 订单明细表` set tax=tax+0.05 where orderkey=18 and linenumber=1;

SELECT totalprice FROM `orders 订单表` WHERE orderkey=18

（2）

SELECT totalprice FROM `orders 订单表` WHERE orderkey=18

INSERT INTO `lineitem 订单明细表` VALUES(18,20000,533,2,50,8888.00,0.82,0.62,'','',Null,Null,Null,' ',' ',Null)

SELECT totalprice FROM `orders 订单表` WHERE orderkey=18

（3）

SELECT totalprice FROM `orders 订单表` WHERE orderkey=1

DELETE FROM `lineitem 订单明细表` WHERE orderkey=1 and linenumber=1

SELECT totalprice FROM `orders 订单表` WHERE orderkey=1

（2）BEFORE 触发器

①在 Lineitem 表上定义一个 BEFORE UPDATE 触发器，当修改订单明细中的数量

（quantity）时，先检查供应表 PartSupp 中的可用数量 availqty 是否足够。

create trigger BEFORE_update

before update on `lineitem 订单明细表`

for each row

begin

select availqty into @L_availqty from `partsupp 零件供应联系表`

where partkey=new.partkey2 and suppkey=new.suppkey;

if(@L_availqty-(new.quantity-old.quantity)>=0) then

begin

update `partsupp 零件供应联系表` set availqty=availqty-(new.quantity)

where partkey=new.partkey2 and suppkey=new.suppkey ;

end;

end if;

End

②在 Lineitem 表上定义一个 BEFORE INSERT 触发器，当插入订单明细时，先检查供应

表 PartSupp 中的可用数量 availqty 是否足够。

create trigger BEFORE_insert

before insert on `lineitem 订单明细表`

for each row

begin

select availqty into @L_availqty from `partsupp 零件供应联系表`

where partkey=new.partkey2 and suppkey=new.suppkey;

if(@L_availqty-(new.quantity)>=0) then

begin

update `partsupp 零件供应联系表` set availqty=availqty-(new.quantity)

where partkey=new.partkey2 and suppkey=new.suppkey ;

end;

end if;

End；

③在 Lineitem 表上定义一个 BEFORE DELETE 触发器，当删除订单明细时，该订单明细

项订购的数量要归还于对应的零件供应记录。

create trigger BEFORE_delete

before

delete on `lineitem 订单明细表`

for each row

begin

update `partsupp 零件供应联系表` set availqty=availqty-(old.quantity)

where partkey=old.partkey2 and suppkey=old.suppkey;

end;

④验证上面的三个触发器是否起作用。

（1）