头歌数据库实验七:函数与触发器

头歌数据库实验七:函数与触发器

yzdev
2023-05-09 / 0 评论 / 462 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2023年05月09日,已超过513天没有更新,若内容或图片失效,请留言反馈。
本文共 256 个字数,平均阅读时长 ≈ 1分钟

第1关:定义、调用参数函数

use demo;

#代码开始
#定义、调用参数函数(func_sqty):查询返回指定供应商的供应零件总数量。
#DROP FUNCTION func_sqty;

#SET GLOBAL log_bin_trust_function_creators = TRUE;
DELIMITER //
CREATE FUNCTION func_sqty (
    p_sno CHAR ( 2 )) RETURNS INT READS SQL DATA BEGIN
    DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = p_sno;
    RETURN sumqty;
    
END//


#代码结束

select func_sqty('S1');
select func_sqty('S2');
select func_sqty('S3');

第2关:触发器应用(1)

use demo;

#定义一个触发器(tr_spj_insert),完成向SPJ表新增数据时,及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_insert AFTER INSERT ON spj FOR EACH ROW
BEGIN
    DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = new.sno;
    UPDATE s 
    SET sqty = sumqty 
    WHERE
        sno = new.sno;
    
END//





#代码结束

#以下代码不要改动或删除,将会对创建的触发器进行测试
insert into spj values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500); 

第3关:触发器应用(2)

use demo;

#定义一个触发器(tr_spj_delete),完成向SPJ表删除数据时,及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_delete AFTER DELETE ON spj FOR EACH ROW
BEGIN
        DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = old.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = old.sno;
    END//
#代码结束

#以下代码不要改动或删除,将会对创建的触发器进行测试
DELETE FROM spj WHERE sno='S1' AND pno='P1' AND jno='J1';

第4关:触发器应用(3)

use demo;

#定义一个触发器(tr_spj_update),完成向SPJ表更新数据时,及时更新所有供应商供应零件总数量。
#代码开始
DELIMITER //
CREATE TRIGGER tr_spj_update AFTER UPDATE ON spj FOR EACH ROW
BEGIN
        DECLARE
        sumqty INT;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = old.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = old.sno;
    SELECT
        sum( qty ) INTO sumqty 
    FROM
        spj 
    WHERE
        sno = new.sno;
    update s 
    SET sqty = sumqty 
    WHERE
        sno = new.sno;
    END//

#代码结束

#以下代码不要改动或删除,将会对创建的触发器进行测试
UPDATE spj SET sno='S1' WHERE sno='S2' AND pno='P3' AND jno='J1';
10

打赏

海报

正在生成.....

评论 (0)

语录
取消