数据库实验5 存储过程和触发器

一．实验目的

1. 加深对存储过程和触发器的理解
2. 掌握存储过程和触发器的创建和使用，理解它们的执行方式的不同
3. 理解并体会存储过程和触发器的区别和联系

二. 实验内容

1.题目要求

在已建好的各表基础上，根据需要创建相关的存储过程或触发器，完成一定的功能。

1. 创建一个InsertS的存储过程，向S中插入一条记录，新记录的值由参数提供。并验证该存储过程的执行情况。
    2. 创建一个QuerySPJ的存储过程，根据用户提供的工程项目代码查询该工程项目的使用零件情况，并返回使用零件的总数量。
   3. 创建一个触发器，使得在S表中删除某条记录时，SPJ表中的数据也进行相应的删除操作。（创建触发器之前先去掉表的外键联系，这个触发器只是模拟一下级联删除操作）
   4. 在S表中增加一列属性，属性名为AvgQty，表示供应商的平均供应数量，创建一个触发器，使得当向SPJ表中插入数据时，AvgQty也做相应修改。
   5．创建一个供应历史表hspj(sno,pno,jno,qty,username,modifydate)，记录供应变化情况。实现这样的约束控制：如果spj表中的某条供应记录发生改变，就在hspj表中插入一行历史记录，其中username为操作的用户名，modifydate为操作的时间。

2.实验结果

代码

-- SET  sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-- 1. 创建一个InsertS的存储过程，向S中插入一条记录，新记录的值由参数提供。并验证该存储过程的执行情况。
DELIMITER $$
CREATE PROCEDURE InsertS(in isno char(20),in isname char(20),in isstatus char(20),in icity char(20))
BEGIN
	INSERT
	INTO S
	VALUES(isno,isname,isstatus,icity);
END
$$
DELIMITER;

call InsertS('S9','snamedata','99','citydata');

-- 恢复数据 

DROP PROCEDURE InsertS;

DELETE
FROM s
where sno='s9';

-- 2. 创建一个QuerySPJ的存储过程，根据用户提供的工程项目代码查询该工程项目的使用零件情况，并返回使用零件的总数量。
DELIMITER $$
CREATE PROCEDURE QuerySPJ(in qjno char(20),out num int)
BEGIN
	SELECT sum(QTY)
	FROM SPJ
	WHERE JNO=qjno
	INTO num;
END
$$
DELIMITER;

call QuerySPJ('j1',@num);
SELECT @num;

-- 恢复数据
DROP PROCEDURE QuerySPJ;

-- 3. 创建一个触发器，使得在S表中删除某条记录时，SPJ表中的数据也进行相应的删除操作。（创建触发器之前先去掉表的外键联系，
-- 这个触发器只是模拟一下级联删除操作）
CREATE TRIGGER delete_s
AFTER DELETE ON S
FOR EACH ROW
BEGIN
DELETE
FROM SPJ
WHERE SNO=old.sno;
END

DELETE
FROM S
WHERE SNO = 'S1'; 

-- 恢复数据
DROP TRIGGER delete_s;

-- 4. 在S表中增加一列属性，属性名为AvgQty，表示供应商的平均供应数量，创建一个触发器，使得当向SPJ表中插入数据时，
-- AvgQty也做相应修改。
alter TABLE S
ADD AvgQty int;
UPDATE S
SET AvgQty=(
SELECT AVG(Qty)
FROM SPJ
WHERE s.sno=spj.sno);

CREATE TRIGGER avgqty_update
AFTER INSERT ON spj
FOR EACH ROW
BEGIN
UPDATE S
SET AvgQty=(
SELECT AVG(Qty)
FROM SPJ
WHERE sno=new.sno)
WHERE s.sno=new.sno;
END

INSERT
into spj
VALUES('S1','p1','j5',200);

-- 恢复数据

alter TABLE S
DROP AvgQty;

DROP TRIGGER avgqty_update

DELETE
FROM spj
WHERE sno='s1' and pno='p1' and jno='j5';

-- 5．创建一个供应历史表hspj(sno,pno,jno,qty,username,modifydate)，记录供应变化情况。实现这样的约束控制：
-- 如果spj表中的某条供应记录发生改变，就在hspj表中插入一行历史记录，其中username为操作的用户名，modifydate为操作的时间。
CREATE TABLE hspj(
SNO CHAR(20) references s(sno),
PNO CHAR(20) references p(pno),
JNO CHAR(20) references j(jno),
qty int,
username CHAR(20),
modifydate DATETIME
)

CREATE TRIGGER hspj_update
AFTER UPDATE ON spj
FOR EACH ROW
BEGIN
	INSERT INTO hspj
	VALUES(old.sno,old.pno,old.jno,old.qty,CURRENT_USER,CURRENT_TIMESTAMP);
END

UPDATE spj
SET qty=500
WHERE sno='S1' AND pno='P2' AND jno='J2';

-- 恢复数据
DROP TRIGGER hspj_update;

实验结果截图

1.

2.

3.

外键删除

删除前

删除后

4.

插入前

插入后

5.建表

更新SPJ表后