MySQL触发器、存储过程

一、使用数据库

mysql> use mydb16_trigger;
Database changed

存储过程的题使用以下库：

mysql> use mydb7_openlab;
Database changed

二、建表

1、表goods

mysql> create table goods( gid char(8) primary key,
    -> name varchar(10),
    -> price decimal(8,2),
    -> num int);

mysql> insert into goods values('A0001','橡皮',2.5,100),('B0001','小楷本',2.8,210),
    -> ('C0001','铅笔',1.2,120), ('D0001','计算器',28,20);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

2、表orders

mysql> create table orders( oid int primary key auto_increment,
    -> gid  char(10) not null,
    -> name varchar(10),
    -> price decimal(8,2),
    -> onum int,
    -> otime date);

三、建立触发器

1、建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试

查询语句：

mysql> create trigger orders_after_insert_trigger
    -> after insert on orders for each row
    -> update goods set num=num-new.onum
    -> where gid=new.gid;

测试：

插入订单前：

插入订单后：

2、建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量

查询语句：

mysql> create trigger orders_after_delete_trigger
    -> after delete on orders for each row
    -> update goods set num=num+old.onum
    -> where gid=old.gid;

测试：

删除订单前：

删除订单后：

3、建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新

查询语句：

mysql> create trigger orders_after_update_trigger
    -> after update on orders for each row
    -> update goods set num=num+(old.onum-new.onum)
    -> where gid=old.gid;

测试：

订单修改前：

订单修改后：

四、存储过程

1、使用mydb7_openlab库

mysql> use mydb7_openlab;
Database changed

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

mysql> delimiter //
mysql>  create procedure s1()
    ->  begin
    ->      select name,incoming from emp_new;
    ->  end //

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

mysql>  delimiter //
mysql> create procedure s2(in in_name varchar(11), out out_age int )
    -> begin
    ->     select age into out_age from emp_new where name=in_name;
    -> end //

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

mysql> delimiter //
mysql> create procedure  s3(in in_dept int ,out avg_sal float)
    ->     select   avg(incoming) into avg_sal  from  emp_new where  dept2=in_dept;
    -> end //