实验五 存储过程和触发器

实验五 存储过程和触发器

• 实验目的

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

二. 实验内容

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

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

实验过程：

1. 创建存储过程：

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;

1. 调用InsertS

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

实验结果：

1. 创建存储过程：

1. 调用InsertS

结果分析：

该存储过程创建成功，可以实现用户直接调用进行数据的插入

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

实验过程：

1. 创建存储过程：

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;

1. 调用QuerySPJ，查询J4：

SET @count=0;

call QuerySPJ('J4',@count);

SELECT @count;

实验结果：

1. 创建存储过程：

1. 调用QuerySPJ，查询J4：

 

结果分析：

      

可以看到J4的零件使用情况确实如结果所示。J4的零件使用情况见结果1，总零件数见结果2，J4使用零件总数800个。

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

实验过程：

1. 删除外键：

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;

可视化界面：

1. 创建触发器：

CREATE TRIGGER delete_spj

AFTER

DELETE on s

for each ROW

BEGIN

DELETE FROM spj WHERE SNO=OLD.SNO;

END;

1. 再运行删除语句：

DELETE from s where SNO='S3';

实验结果：

1. 删除外键：

1. 创建触发器：

1. 再运行删除语句：

结果分析：

在无外键的情况下，s表与spj表没有关联，但是通过触发器是可以实现级联删除的。需要注意的是，弄清楚删的顺序，删除s表中数据的时候，spj表中的数据也相应删除。

 

 

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

实验过程：

1. 加新的属性列：

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

1. 创建触发器：

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;

1. 测试：

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

实验结果：

1. 添加新的属性列：

1. 创建触发器：

1. 测试:

结果分析：

插入了一条('S3','P1','J6',200);s表中的AvgQty数据列中的数据发生相应变化。

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

实验过程：

1. 创建一个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)

);

1. 创建触发器：

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;

1. 测试：

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

实验结果：

1. 创建一个hspj表：

 

 (2)    创建触发器：

 (3)    测试：

结果分析：

        插入一条数据('S7','P1','J2',200);的时候，hspj表会插入一条操作的信息。