实验5 存储过程和触发器

实验目的

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

实验内容

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

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

DELIMITER $$
CREATE PROCEDURE InsertS(in SNO0 CHAR(9),IN SNAME0 CHAR(9),IN STATUS0 CHAR(9),IN CITY0 CHAR(9))
    BEGIN 
    INSERT INTO s
    VALUES(SNO0,SNAME0,STATUS0,CITY0);
    END$$
DELIMITER

call InsertS('XX','XX','XX','XX')

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

DELIMITER $$
CREATE PROCEDURE QuerySPJ(in JNO0 char(9))
    BEGIN
    SELECT * FROM spj WHERE JNO=JNO0;
    SELECT sum(QTY) as SUM_QTY FROM spj WHERE JNO=JNO0;
    
    END$$
DELIMITER

CALL QuerySPJ("j2")

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

CREATE TRIGGER DropS
BEFORE DELETE ON s FOR EACH ROW
BEGIN 
    DELETE FROM spj WHERE sno = old.sno;
END

DELETE FROM s WHERE sno = "S?"

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

        需要在s表上新加一列，列名为A（自定义）

CREATE TRIGGER A
AFTER insert on spj for each ROW
BEGIN
    DECLARE A1 INT;
    SELECT SUM(QTY) FROM spj WHERE sno = new.sno INTO A1;
    UPDATE s SET A = A1 WHERE sno = new.sno;
END

INSERT INTO spj
VALUES('XX','XX','XX','XX');

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