use demo;#代码开始#定义、调用参数函数(func_sqty):查询返回指定供应商的供应零件总数量。delimiter//createfunction func_sqty(p_sno char(2))returnsintreadssqldatabegindeclare sumqty int;selectsum(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//createtrigger tr_spj_insert afterinserton spj for each rowbegindeclare sumqty int;selectsum(qty)into sumqty from spj where sno =new.sno;update s set sqty =sumqty where sno=new.sno;end//#代码结束#以下代码不要改动或删除,将会对创建的触发器进行测试insertinto 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//CREATETRIGGER tr_spj_delete AFTERDELETEON spj FOR EACH ROWBEGINDECLARE
sumqty INT;SELECTsum( qty )INTO sumqty
FROM
spj
WHERE
sno = old.sno;update s
SET sqty = sumqty
WHERE
sno = old.sno;END//#代码结束#以下代码不要改动或删除,将会对创建的触发器进行测试DELETEFROM spj WHERE sno='S1'AND pno='P1'AND jno='J1';
第4关:触发器应用(3)
use demo;#定义一个触发器(tr_spj_update),完成向SPJ表更新数据时,及时更新所有供应商供应零件总数量。#代码开始DELIMITER//CREATETRIGGER tr_spj_update AFTERUPDATEON spj FOR EACH ROWBEGINDECLARE
sumqty INT;SELECTsum( qty )INTO sumqty
FROM
spj
WHERE
sno = old.sno;update s
SET sqty = sumqty
WHERE
sno = old.sno;SELECTsum( 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';