0


实验五 存储过程和触发器

实验五 存储过程和触发器

  • 实验目的
  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表会插入一条操作的信息。

标签: 数据库 sql mysql

本文转载自: https://blog.csdn.net/weixin_41957626/article/details/129740157
版权归原作者 简单点了 所有, 如有侵权,请联系我们删除。

“实验五 存储过程和触发器”的评论:

还没有评论