一、实验目的
1．掌握事务的概念、性质、定义及使用；
2．掌握游标的概念、组成、创建及使用；
3．掌握存储过程的概念、类型、特点、创建、执行及管理。
4．掌握触发器的概念、创建、管理及使用。
二、实验学时
4学时
三、实验内容
1．进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：
insert into 成本表 values('zy2023006','112202002','y005',
10000,'张三', '07-01-2023' ,'07-04-2023','07-25-2023',
'作业公司作业一队','堵漏',7000,2500,1000,1400,11900,
'李四','07-26-2023',11900,'王五','07-28-2023')
insert into材料消耗表values('zy2023006','wm001',200)
insert into材料消耗表values('zy2023006','wm002',200)
insert into材料消耗表values('zy2023006','wm003',200)
insert into材料消耗表values('zy2023006','wm004',100)
由于workbench无法使用事务，随改用命令行。
Start transaction;
insert into 成本表 values('zy2023006','112202002','y005',
10000,'张三', '07-01-2023' ,'07-04-2023','07-25-2023',
'作业公司作业一队','堵漏',7000,2500,1000,1400,11900,
'李四','07-26-2023',11900,'王五','07-28-2023')；
-第一条插入语句和列数不匹配
insert into 材料消耗表 values('zy2023006','wm001',200);
insert into 材料消耗表 values('zy2023006','wm002',200);
insert into 材料消耗表 values('zy2023006','wm003',200);
insert into 材料消耗表 values('zy2023006','wm004',100);
-下面四条插入语句因为有外码限制操作失败
Rollback;
select * from 材料消耗表;
后面4.（3）还是把外码限制删除了，所以来补一个结果
2．进行如下游标练习：
定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。
表头：单据号 预算单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位 施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账人 入账日期
执行以上所定义的游标，查看是否能正确输出结果。
delimiter //
create procedure w1()
begin
declare i int default 0;
declare 单据号 varchar(20);
declare 预算单位 varchar(20);
declare 井号 varchar(20);
declare 预算金额 varchar(20);
declare 预算人 varchar(20);
declare 预算日期 varchar(20);
declare 开工日期 varchar(20);
declare 完工日期 varchar(20);
declare 施工单位 varchar(20);
declare 施工内容 varchar(20);
declare 材料费 varchar(20);
declare 材料一费用 varchar(20);
declare 材料二费用 varchar(20);
declare 材料三费用 varchar(20);
declare 材料四费用 varchar(20);
declare 人工费 varchar(20);
declare 设备费 varchar(20);
declare 其他费用 varchar(20);
declare 结算金额 varchar(20);
declare 结算人 varchar(20);
declare 结算日期 varchar(20);
declare 入账金额 varchar(20);
declare 入账人 varchar(20);
declare 入账日期 varchar(20);
declare c1 cursor for select * from 成本表;
declare continue handler for not found
set i=1;
open c1;
while i=0 do
fetch c1 into
单据号, 预算单位, 井号,预算金额, 预算人, 预算日期, 开工日期, 完工日期, 施工单位, 施工内容,材料费,
材料一费用, 材料二费用, 材料三费用,材料四费用, 人工费, 设备费, 其他费用,结算金额, 结算人, 结算日期, 入账金额, 入账人, 入账日期 ;
select 单据号, 预算单位, 井号,预算金额, 预算人, 预算日期, 开工日期, 完工日期, 施工单位, 施工内容,材料费, 材料一费用, 材料二费用, 材料三费用,材料四费用, 人工费, 设备费, 其他费用,结算金额, 结算人, 结算日期, 入账金额, 入账人, 入账日期 ;
end while;
end//
delimiter ;
效果如图会五次显示查询出结果，分别在result12345中，利用临时表修改，可以实现最后查询结果在一个表中
Delimiter //
CREATE PROCEDURE w1()
begin
drop temporary table if exists t1;
create temporary table if not exists t1(
单据号 varchar(20),
预算单位 varchar(20),
井号 varchar(20),
预算金额 varchar(20),
预算人 varchar(20),
预算日期 varchar(20),
开工日期 varchar(20),
完工日期 varchar(20),
施工单位 varchar(20),
施工内容 varchar(20),
材料费 varchar(20),
材料一费用 varchar(20),
材料二费用 varchar(20),
材料三费用 varchar(20),
材料四费用 varchar(20),
人工费 varchar(20),
设备费 varchar(20),
其他费用 varchar(20),
结算金额 varchar(20),
结算人 varchar(20),
结算日期 varchar(20),
入账金额 varchar(20),
入账人 varchar(20),
入账日期 varchar(20)
)ENGINE = MEMORY;
begin
declare i int default 0;
declare 单据号 varchar(20);
declare 预算单位 varchar(20);
declare 井号 varchar(20);
declare 预算金额 varchar(20);
declare 预算人 varchar(20);
declare 预算日期 varchar(20);
declare 开工日期 varchar(20);
declare 完工日期 varchar(20);
declare 施工单位 varchar(20);
declare 施工内容 varchar(20);
declare 材料费 varchar(20);
declare 材料一费用 varchar(20);
declare 材料二费用 varchar(20);
declare 材料三费用 varchar(20);
declare 材料四费用 varchar(20);
declare 人工费 varchar(20);
declare 设备费 varchar(20);
declare 其他费用 varchar(20);
declare 结算金额 varchar(20);
declare 结算人 varchar(20);
declare 结算日期 varchar(20);
declare 入账金额 varchar(20);
declare 入账人 varchar(20);
declare 入账日期 varchar(20);
declare c1 cursor for select * from 成本表;
declare continue handler for not found
set i=1;
open c1;
xunhuan:loop
if i=1 then leave xunhuan;
end if;
fetch c1 into 单据号, 预算单位, 井号,预算金额, 预算人, 预算日期, 开工日期, 完工日期, 施工单位, 施工内容,材料费, 材料一费用, 材料二费用, 材料三费用,材料四费用, 人工费, 设备费, 其他费用,结算金额, 结算人, 结算日期, 入账金额, 入账人, 入账日期 ;
if i=1 then leave xunhuan;
end if;
insert into t1 values(
单据号, 预算单位, 井号,预算金额, 预算人, 预算日期, 开工日期, 完工日期, 施工单位, 施工内容,材料费, 材料一费用, 材料二费用, 材料三费用,材料四费用, 人工费, 设备费, 其他费用,结算金额, 结算人, 结算日期, 入账金额, 入账人, 入账日期);
end loop xunhuan;
select * from t1;
end;
truncate table t1;
End //
Delimiter ；
3．定义一个存储过程，要求完成以下功能：
生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。
输出格式 ***单位**时间---**时间成本运行情况
预算金额 结算金额 入账金额 未结算金额 未入账金额
****.** ****.** ****.** ****.** ****.**
其中：未结算金额=预算金额-结算金额
未入账金额=结算金额-入账金额
分三种情况（单位分别为：采油厂、采油矿、采油队）执行以上定义的存储过程，查看执行输出结果。
delimiter //
CREATE PROCEDURE w2(in 单位代码 char(20), in 起始日期 date, in 结束日期 date)
begin
declare 单位名称 char(20);
declare 预算金额 decimal(10,2);
declare 结算金额 decimal(10,2);
declare 入账金额 decimal(10,2);
declare 未结算金额 decimal(10,2);
declare 未入账金额 decimal(10,2);
select 单位表.单位名称 into 单位名称
from 单位表
where 单位表.单位代码 = 单位代码;
select sum(成本表.预算金额) into 预算金额 from 成本表
where 成本表.预算单位 like concat(单位代码, '%') and (成本表.预算日期 between 起始日期 and 结束日期);
select sum(成本表.结算金额) into 结算金额 from 成本表
where 成本表.预算单位 like concat(单位代码, '%') and (成本表.结算日期 between 起始日期 and 结束日期);
select sum(成本表.入账金额) into 入账金额 from 成本表
where 成本表.预算单位 like concat(单位代码, '%') and (成本表.入账日期 between 起始日期 and 结束日期) and (成本表.入账金额 is not null);
set 未结算金额 = 预算金额 - 结算金额;
set 未入账金额 = 结算金额 - 入账金额;
if 预算金额 is null then
set 预算金额 = 0;
end if;
if 结算金额 is null then
set 结算金额 = 0;
end if;
if 入账金额 is null then
set 入账金额 = 0;
end if;
if 未结算金额 is null then
set 未结算金额 = 0;
end if;
if 未入账金额 is null then
set 未入账金额 = 0;
end if;
select concat(单位名称, '单位', 起始日期, '--', 结束日期, '成本运行情况') result;
select 预算金额, 结算金额, 入账金额, 未结算金额, 未入账金额;
End
delimiter ；测试一：
call zhaizihan.w2('1122', '2023-5-1', '2023-5-29');
测试二：
call zhaizihan.w2('112201', '2023-5-1', '2023-5-29');
测试三：
call zhaizihan.w2('112201001', '2023-5-1', '2023-5-29');
4．针对成本表定义三个触发器，分别完成以下功能：
⑴ 对成本表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。
delimiter //
drop trigger if exists w1;
create trigger w1 after insert on 成本表
for each row
begin
declare 结算金额 decimal(10,2);
declare f1 decimal(10,2);
declare f2 decimal(10,2);
declare f3 decimal(10,2);
declare f4 decimal(10,2);
select 材料费, 人工费, 设备费, 其他费用 into f1, f2, f3, f4
from 成本表
where 单据号 = NEW.单据号;
set 结算金额 = f1 + f2 + f3 + f4;
update 成本表
set 结算金额 = 结算金额
where 单据号 = NEW.单据号;
end //
delimiter ;
然后执行时出现错误，在触发器中对同一表操作形成嵌套，随更改为
delimiter //
drop trigger if exists w1;
create trigger w1 before insert on 成本表
for each row
begin
set NEW.结算金额 = NEW.材料费 + NEW.人工费 + NEW.设备费 + NEW.其他费用;
end //
delimiter ;测试：
insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2023000','test',100,200,300,400);触发器执行成功
⑵ 当修改成本表的某行数据时自动修改结算金额字段。
delimiter //
drop trigger if exists w2;
CREATE TRIGGER w2 BEFORE INSERT ON 成本表
FOR EACH ROW
BEGIN
DECLARE 结算金额 DECIMAL(10,2);
DECLARE temp1 DECIMAL(10,2);
DECLARE temp2 DECIMAL(10,2);
DECLARE temp3 DECIMAL(10,2);
DECLARE temp4 DECIMAL(10,2);
SELECT 材料费, 人工费, 设备费, 其他费用 INTO temp1, temp2, temp3, temp4
FROM 成本表
WHERE 单据号 = NEW.单据号;
SET 结算金额 = temp1 + temp2 + temp3 + temp4;
SET NEW.结算金额 = 结算金额;
END //
delimiter ;测试：
insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2023111','test',100,200,300,400);
update 成本表
set 材料费 = 100000
where 单据号 = '2023111';
⑶ 当删除成本表中一行数据时，自动删除材料消耗表中相应明细数据。
delimiter //
drop trigger if exists w3;
CREATE TRIGGER w3 after delete ON 成本表
FOR EACH ROW
BEGIN
delete
from 材料消耗表
where 单据号=old.单据号;
END //
delimiter ;
测试语句
insert into 成本表(单据号,预算人,材料费,人工费,设备费,其他费用)
values('2023110','test',100,200,300,400);
insert into 材料消耗表
values('2023110','wm001'，200);
select 单据号,物码
from
材料消耗表;
delete from 成本表
where 单据号 = '2023110';执行测试语句：
delete from 成本表
where 单据号 = '2023110';由于之前定义的外码 操作失败，所以删除外码
alter table 材料消耗表 drop foreign key 材料消耗表_ibfk_1;
alter table 材料消耗表 drop foreign key 材料消耗表_ibfk_2;
alter table 材料消耗表 drop foreign key 材料消耗表_ibfk_3;再次执行测试语句成功
⑷ 对上述3个触发器用适当的更新语句进行验证，并查看结果是否达到预期结果。
2041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
