实验五 存储过程和触发器

一.实验目的

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

二. 实验内容

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

1.创建一个InsertS的存储过程，向S中插入一条记录，新记录的值由参数提供。并验证该存储过程的执行情况。

实验过程：

• 创建存储过程：

DROP PROCEDURE IF EXISTS InsertS;
DELIMITER $$
CREATE PROCEDURE InsertS(in S_SNO CHAR(2),in S_SNAME CHAR(3),in S_STATUS CHAR(2),in C_CITY CHAR(2))
BEGIN
	insert into s VALUES(S_SNO,S_SNAME,S_STATUS,C_CITY);
END
$$
DELIMITER;

• 调用InsertS

call InsertS('S8','精益',50,'北京');

2.创建一个QuerySPJ的存储过程，根据用户提供的工程项目代码查询该工程项目的使用零件情况，并返回使用零件的总数量。

实验过程：

• 创建存储过程：

DROP PROCEDURE IF EXISTS QuerySPJ;
DELIMITER $$
CREATE PROCEDURE QuerySPJ(in J_JNO CHAR(2),OUT QTYcount INT)
BEGIN
	SELECT jno,pno,QTY FROM spj WHERE JNO=J_JNO;
	SELECT sum(QTY) into QTYcount
	FROM spj
	GROUP BY JNO
	HAVING JNO=J_JNO;
END
$$
DELIMITER;

• 调用QuerySPJ，查询J4：

SET @count=0;
call QuerySPJ('J4',@count);
SELECT @count;

3.创建一个触发器，使得在S表中删除某条记录时，SPJ表中的数据也进行相应的删除操作。（创建触发器之前先去掉表的外键联系，这个触发器只是模拟一下级联删除操作）

实验过程：

• 删除外键：

Sql语句方式：

Alter table spj drop foreign key spj_ibfk_1;
Alter table spj drop foreign key spj_ibfk_2;
Alter table spj drop foreign key spj_ibfk_3;

可视化界面：

右键设计表，把外键删掉就可以了

• 创建触发器：

CREATE TRIGGER delete_spj
AFTER
DELETE on s
for each ROW
BEGIN
DELETE FROM spj WHERE SNO=OLD.SNO;
END;

• 再运行删除语句：

DELETE from s where SNO='S3';

4.在S表中增加一列属性，属性名为AvgQty，表示供应商的平均供应数量，创建一个触发器，使得当向SPJ表中插入数据时，AvgQty也做相应修改。

实验过程：

• 添加新的属性列：

alter table s add avgQty DOUBLE (10,0);

• 创建触发器：

CREATE TRIGGER spj_AvgQty
AFTER
INSERT on spj
for EACH ROW
BEGIN
DECLARE qtycount DOUBLE(10,0);
DECLARE count INT;
SET qtycount=0;
set count=0;
SELECT SUM(QTY) INTO qtycount FROM spj GROUP BY sno HAVING sno=new.sno;
SELECT count(*) INTO count FROM spj GROUP BY sno HAVING sno=new.sno;
set qtycount=qtycount/count;
UPDATE s SET AvgQty=qtycount WHERE sno=new.sno;
END;

• 测试：

INSERT INTO spj VALUES('S3','P1','J6',200);

5.创建一个供应历史表hspj(sno,pno,jno,qty,username,modifydate)，记录供应变化情况。实现这样的约束控制：如果spj表中的某条供应记录发生改变，就在hspj表中插入一行历史记录，其中username为操作的用户名，modifydate为操作的时间。

实验过程：

• 创建一个hspj表：

DROP TABLE  if EXISTS hspj;
CREATE TABLE hspj(
sno char(2),
pno char(2),
jno char(7),
qty INT,
username CHAR(20),
modifydate TIMESTAMP,
PRIMARY KEY(sno,pno,jno)
);

• 创建触发器：

CREATE TRIGGER spj_hspj
AFTER
INSERT on spj
FOR EACH ROW
BEGIN
INSERT into hspj() VALUES(new.sno,new.pno,new.jno,new.qty,CURRENT_USER,CURRENT_TIMESTAMP);
end;

• 测试：

INSERT INTO spj() VALUES('S7','P1','J2',200);