存储过程实验
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)
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=2 and l.linenumber=1;
update `lineitem 订单明细表` set quantity=quantity+5
where orderkey=2 and linenumber=1;
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=2 and l.linenumber=1;
(2)
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=9;
INSERT INTO `lineitem 订单明细表` VALUES(9,44686,1232,2,91,6666.00,0.69,0.80,'','',Null,Null,Null,' ',' ',Null)
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=9;
(3)
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=10;
DELETE FROM `lineitem 订单明细表` WHERE orderkey=10 and linenumber=1
select l.partkey2,l.suppkey,l.quantity,ps.availqty
from `lineitem 订单明细表` l,`partsupp 零件供应联系表` ps
where l.partkey2=ps.partkey and l.suppkey=ps.suppkey and l.orderkey=10;