頭歌資料庫實驗七：函式與觸發器

Cloudservice發表於2024-05-29
資料庫函式觸發器

第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';

相關文章