基于MySQL的触发器和存储过程操作

1.触发器

首先创建goods商品表和orders订单表，

mysql> desc goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid   | char(8)      | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| price | decimal(8,2) | YES  |     | NULL    |       |
| num   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> desc orders;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| oid   | int          | NO   | PRI | NULL    | auto_increment |
| gid   | char(10)     | NO   |     | NULL    |                |
| name  | varchar(10)  | YES  |     | NULL    |                |
| price | decimal(8,2) | YES  |     | NULL    |                |
| onum  | int          | YES  |     | NULL    |                |
| otime | date         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

然后在商品表中插入一些商品记录：

mysql> select * from goods;
+-------+--------+-------+------+
| gid   | name   | price | num  |
+-------+--------+-------+------+
| a0001 | 橡皮   |  2.50 |  100 |
| b0001 | 小楷本 |  2.80 |  210 |
| c0001 | 铅笔   |  1.20 |  120 |
| d0001 | 计算器 | 28.20 |   20 |
+-------+--------+-------+------+
4 rows in set (0.01 sec)

最后创建三个触发器 ：

订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量：

mysql> delimiter //
mysql> create trigger update_goods_num_after_insert
    -> after insert on orders
    -> for each row
    -> begin
    ->     update goods
    ->     set num = num - new.onum
    ->     where gid = new.gid;
    -> end //
Query OK, 0 rows affected (0.01 sec)

客户取消订单，恢复商品表对应商品的数量：

mysql> delimiter //
mysql> create trigger restore_goods_num_after_delete
    -> after delete on orders
    -> for each row
    -> begin
    ->     update goods
    ->     set num = num + old.onum
    ->     where gid = old.gid;
    -> end //
Query OK, 0 rows affected (0.01 sec)

客户修改订单，商品表对应商品数量同步更新：

mysql> delimiter //
mysql> create trigger update_goods_num_after_update
    -> after update on orders
    -> for each row
    -> begin
    ->     declare quantity_change int;
    ->     set quantity_change = new.onum - old.onum;
    ->     update goods
    ->     set num = num - quantity_change
    ->     where gid = new.gid;
    -> end //
Query OK, 0 rows affected (0.02 sec)

 2.存储过程

 首先使用已存在的库和表，对其进行操作。

创建提取emp_new表所有员工姓名和工资的存储过程s1

mysql> delimiter //
mysql> create procedure s1()
    -> begin
    ->     select name, incoming from emp_new;
    -> end //
Query OK, 0 rows affected (0.02 sec)

创建存储过程s2，实现输入员工姓名后返回员工的年龄

mysql> delimiter //
mysql> create procedure s2(in p_name varchar(11), out p_age int)
    -> begin
    ->     select age into p_age from emp_new where name = p_name;
    -> end //
Query OK, 0 rows affected (0.01 sec)

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

mysql> delimiter //
mysql> create procedure s3(in p_dept2 int, out p_avg_salary decimal(10,2))
    -> begin
    ->     select avg(incoming) into p_avg_salary from emp_new where dept2 = p_dept2;
    -> end //
Query OK, 0 rows affected (0.01 sec)

           在触发器部分，通过创建商品表和订单表，深刻理解了表结构设计对数据完整性和一致性的重要性。建立不同功能的触发器，如订单数量变动时商品数量同步调整、取消订单时恢复商品数量、修改订单时更新商品数量，不仅熟悉了触发器的语法和触发机制，还体会到其在业务逻辑实现中的关键作用，它能自动响应数据操作，保证数据的准确性和业务的连贯性。
            存储过程的实践同样具有挑战性和价值。从创建提取员工姓名和工资的存储过程，到输入员工姓名返回年龄、传入部门号返回平均工资的存储过程，逐步掌握了存储过程的参数传递、逻辑处理和结果返回等操作。这不仅提高了数据库操作的效率，还增强了代码的可复用性和可维护性。
            通过本次作业，我对数据库的高级功能有了更深入的理解和应用能力，同时也意识到在实际开发中，合理运用触发器和存储过程能有效提升系统性能和数据处理的准确性。