数据库原理实验四（MySQL版）

实验四 其它数据库对象的管理

一、实验目的

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)

2．进行如下游标练习：

定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。

表头：单据号 预算单位 井号 预算金额 预算人 预算日期 开工日期 完工日期 施工单位 施工内容 材料费 人工费 设备费 其它费用 结算金额 结算人 结算日期 入账金额 入账人 入账日期

执行以上所定义的游标，查看是否能正确输出结果。

3．定义一个存储过程，要求完成以下功能：

生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。

输出格式 ***单位**时间---**时间成本运行情况

预算金额 结算金额 入账金额 未结算金额 未入账金额

****.** ****.** ****.** ****.** ****.**

其中：未结算金额=预算金额-结算金额

未入账金额=结算金额-入账金额

分三种情况（单位分别为：采油厂、采油矿、采油队）执行以上定义的存储过程，查看执行输出结果。

4．针对成本表定义三个触发器，分别完成以下功能：

⑴ 对成本表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。

⑵ 当修改成本表的某行数据时自动修改结算金额字段。

⑶ 当删除成本表中一行数据时，自动删除材料消耗表中相应明细数据。

⑷ 对上述3个触发器用适当的更新语句进行验证，并查看结果是否达到预期结果。

四、实验报告

#1．进行如下事务处理练习（把下列五条语句作为一个事务处理，只有五条语句全部成功执行才做提交，
# 并给出成功的提示信息；否则就做回退处理，并给出具体的错误提示信息）：
-- 开始事务
DROP PROCEDURE IF EXISTS process_transaction;
DELIMITER //
CREATE PROCEDURE process_transaction()
BEGIN
    START TRANSACTION;
    BEGIN
		insert into 成本消耗表 values
		('zy2023006','112202002','y005',10000,'张三', '2023-07-01' ,'2023-07-04','2023-07-25','作业公司作业一队','堵漏',7000,null,null,null,null,2500,1000,1400,11900,'李四','2023-07-26',11900,'王五','2023-07-28');
        insert into 材料消耗表 values ('zy2023006', 'wm001', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm002', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm003', 200);
        insert into 材料消耗表 values ('zy2023006', 'wm004', 100);
    END;
    IF (SELECT COUNT(*) FROM 成本消耗表 WHERE 单据号 = 'zy2023006') > 0 THEN
        -- 提交事务
        COMMIT;
        SELECT '事务执行成功' AS result;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SELECT '事务执行失败' AS result;
    END IF;
END //
DELIMITER ;
CALL process_transaction();
 
# 2．进行如下游标练习：
# 定义一个游标，用于存放成本表的全部行数据，并打印以下表头和各行数据。
# -- 定义存储过程
-- 删除之前定义的存储过程
DROP PROCEDURE IF EXISTS print_cost_table;
-- 创建存储过程
-- 定义存储过程
DELIMITER //
CREATE PROCEDURE print_cost_table()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id varchar(20);
    DECLARE cur_budget_unit varchar(20);
    DECLARE cur_well varchar(20);
    DECLARE cur_budget_amount decimal(10,2);
    DECLARE cur_budget_person varchar(20);
    DECLARE cur_budget_date date;
    DECLARE cur_start_date date;
    DECLARE cur_end_date date;
    DECLARE cur_construction_unit varchar(20);
    DECLARE cur_construction_content varchar(20);
    DECLARE cur_material_cost decimal(10,2);
    DECLARE cur_labor_cost decimal(10,2);
    DECLARE cur_equipment_cost decimal(10,2);
    DECLARE cur_other_cost decimal(10,2);
    DECLARE cur_settlement_amount decimal(10,2);
    DECLARE cur_settlement_person varchar(20);
    DECLARE cur_settlement_date date;
    DECLARE cur_accounting_amount decimal(10,2);
    DECLARE cur_accounting_person varchar(20);
    DECLARE cur_accounting_date date;
 
    -- 声明游标
    DECLARE cur_cost_table CURSOR FOR
        SELECT
            单据号, 预算单位, 井号, 预算金额, 预算人, 预算日期,
            开工日期, 完工日期, 施工单位, 施工内容, 材料费,
            人工费, 设备费, 其他费用, 结算金额, 结算人,
            结算日期, 入账金额, 入账人, 入账日期
        FROM 成本消耗表;
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 打印表头
    SELECT
        '单据号', '预算单位', '井号', '预算金额', '预算人', '预算日期',
        '开工日期', '完工日期', '施工单位', '施工内容', '材料费',
        '人工费', '设备费', '其他费用', '结算金额', '结算人',
        '结算日期', '入账金额', '入账人', '入账日期';
	  -- 进入游标
    OPEN cur_cost_table;
    read_loop: LOOP
        FETCH cur_cost_table INTO
            cur_id, cur_budget_unit, cur_well, cur_budget_amount, cur_budget_person,
            cur_budget_date, cur_start_date, cur_end_date, cur_construction_unit,
            cur_construction_content, cur_material_cost, cur_labor_cost,
            cur_equipment_cost, cur_other_cost, cur_settlement_amount,
            cur_settlement_person, cur_settlement_date, cur_accounting_amount,
            cur_accounting_person, cur_accounting_date;
 
        -- 判断是否取到数据
        IF done THEN
            LEAVE read_loop;
        END IF;
 
        -- 打印数据
        SELECT
            cur_id, cur_budget_unit, cur_well, cur_budget_amount, cur_budget_person,
            cur_budget_date, cur_start_date, cur_end_date, cur_construction_unit,
            cur_construction_content, cur_material_cost, cur_labor_cost,
            cur_equipment_cost, cur_other_cost, cur_settlement_amount,
            cur_settlement_person, cur_settlement_date, cur_accounting_amount,
            cur_accounting_person, cur_accounting_date;
    END LOOP;
    -- 关闭游标
    CLOSE cur_cost_table;
END //
DELIMITER ;
-- 调用存储过程
CALL print_cost_table();
 
 
# 3．定义一个存储过程，要求完成以下功能：生成某单位(单位可以是采油厂或采油矿或采油队)某段时间内的成本运行情况（输入参数：单位代码 起始日期 结束日期）。
DELIMITER //
CREATE PROCEDURE generate_cost_report(IN unit_code CHAR(9), IN start_date DATE, IN end_date DATE)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_budget_amount DECIMAL(10,2);
    DECLARE cur_settlement_amount DECIMAL(10,2);
    DECLARE cur_accounting_amount DECIMAL(10,2);
    DECLARE cur_unsettled_amount DECIMAL(10,2);
    DECLARE cur_unaccounted_amount DECIMAL(10,2);
 
    -- 声明游标
    DECLARE cur_cost_table CURSOR FOR
        SELECT
            预算金额, 结算金额, 入账金额
        FROM 成本消耗表
        WHERE 预算单位 IN (SELECT 单位代码 FROM 单位表 WHERE 单位代码 = unit_code OR 单位代码 LIKE CONCAT(unit_code, '%'))
        AND 预算日期 >= start_date AND 预算日期 <= end_date;
 
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 初始化变量
    SET cur_budget_amount = 0;
    SET cur_settlement_amount = 0;
    SET cur_accounting_amount = 0;
    SET cur_unsettled_amount = 0;
    SET cur_unaccounted_amount = 0;
    -- 获取预算金额、结算金额和入账金额
    OPEN cur_cost_table;
    read_loop: LOOP
        FETCH cur_cost_table INTO cur_budget_amount, cur_settlement_amount, cur_accounting_amount;
        -- 判断是否取到数据
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 计算未结算金额和未入账金额
        SET cur_unsettled_amount = cur_budget_amount - cur_settlement_amount;
        SET cur_unaccounted_amount = cur_settlement_amount - cur_accounting_amount;
 
        -- 打印数据
        SELECT
            CONCAT('***', (SELECT 单位名称 FROM 单位表 WHERE 单位代码 = unit_code),
                   '**时间---', start_date, '**时间成本运行情况') AS header,
            '预算金额', '结算金额', '入账金额', '未结算金额', '未入账金额',
            cur_budget_amount, cur_settlement_amount, cur_accounting_amount,
            cur_unsettled_amount, cur_unaccounted_amount;
    END LOOP;
 
    CLOSE cur_cost_table;
END //
DELIMITER ;
 
-- 采油厂
CALL generate_cost_report('1122', '2023-05-01', '2023-05-28');
 
-- 采油矿
CALL generate_cost_report('112201', '2023-05-01', '2023-05-28');
 
-- 采油队
CALL generate_cost_report('112201001', '2023-05-01', '2023-05-28');
 
 
# 4.0针对成本表定义三个触发器，分别完成以下功能：
# 4.1对成本表插入一行数据时，自动计算并插入结算金额字段（结算金额=材料费+人工费+设备费+其它费用）。
DROP TRIGGER IF EXISTS calculate_settlement_amount;
DROP TRIGGER IF EXISTS update_settlement_amount;
DROP TRIGGER IF EXISTS delete_material_consumption;
 
CREATE TRIGGER calculate_settlement_amount
BEFORE INSERT ON 成本消耗表
FOR EACH ROW
SET NEW.结算金额 = NEW.材料费 + NEW.人工费 + NEW.设备费 + NEW.其他费用 ;
# 4.2当修改成本表的某行数据时自动修改结算金额字段。
CREATE TRIGGER update_settlement_amount
BEFORE UPDATE ON 成本消耗表
FOR EACH ROW
SET NEW.结算金额 = NEW.材料费 + NEW.人工费 + NEW.设备费 + NEW.其他费用 ;
# 4.3当删除成本表中一行数据时，自动删除材料消耗表中相应明细数据。
CREATE TRIGGER delete_material_consumption
AFTER DELETE ON 成本消耗表
FOR EACH ROW
DELETE FROM 材料消耗表 WHERE 单据号 = OLD.单据号;
# 4.4对上述3个触发器用适当的更新语句进行验证，并查看结果是否达到预期结果。
# 插入一行数据到 成本表，并观察 结算金额 是否被正确计算和插入
INSERT INTO 成本消耗表 VALUES('zy2023007','112202002','y001',12000,'张三', '2023-07-01' ,'2023-07-04','2023-07-25','作业公司作业一队','堵漏',7000,null,null,null,null,2000,1000,1000,null,'李四','2023-07-26',11900,'王五','2023-07-28');
SELECT * FROM 成本消耗表 WHERE 单据号 = 'zy2023007';
 
# 更新一行数据，观察触发器是否正确更新了 结算金额
UPDATE 成本消耗表 SET 材料费 = 9000 WHERE 单据号 = 'zy2023007';
SELECT * FROM 成本消耗表 WHERE 单据号 = 'zy2023007';
 
# 删除一行数据，观察触发器是否正确删除了相应的材料消耗表中的数据
DELETE FROM 成本消耗表 WHERE 单据号 = 'zy2023007';
SELECT * FROM 材料消耗表 WHERE 单据号 = 'zy2023007';