mysql初学之触发器和存储过程

触发器是数据库系统中的一种机制，可以根据某一张表的增、删、改操作时作出一些特定的功能处理。

触发器创建在表的基础上，类型有三种，增、删、改，也就是说，一个表最多只能创建三个触发器。

同时，触发器是可以联动的。比如说销售记录插入一条销售信息，则销售记录表的insert触发器触发，针对商品表的库存进行修改。如果商品表创建了update触发器，则商品表的update触发器也会紧跟着触发。

语法 ：create trigger  t_xiaoshou after  insert/update/delete on 表名

              for each row

              begin

                       //业务处理

                      //业务处理

              end;

在触发中new表示新添加的数据行   old 表示删除的数据行。

Insert 时只有new 数据行

Delete时只有 old 数据行

Update时 两个数据行都有,更改数据时，先把这一行数据删除，数据行存在old里面，然后再插入修改后的数据行，则插入的数据行保存在new 里面。

案例：

使用update 触发器实现QQ的在线时长更改，则重新计算QQ等级

DELIMITER$$

CREATE TRIGGER t_qqtime BEFORE UPDATE ON TIME/*自表更新用Before*/

FOR EACH ROW

BEGIN

 /*判断今天登录时长有没有大于120分钟*/

 IF old.time!=new.time AND new.time>=old.time+120 THEN

 /*大于120分钟则加一天*/

   SET new.day=old.day+1;

 END IF;

 /*10级以下的判断条件*/

 IF old.grade<10 THEN

/*10级以下，每5天升一级*/

   IF old.day!=new.day AND new.day%5=0 THEN

  SET new.grade=old.grade+1;

   END IF;

  END IF;

  

 IF old.grade<20 THEN

/*10-20级，每8天升一级*/

    IF old.day!=new.day AND new.day%8=0 THEN

  SET new.grade=old.grade+1;

    END IF;

  END IF;

END$$;

存储过程类似于Java中的方法，把一些复杂的业务进行封装，并进行了预编译，以后可以调用过程名，就可以执行过程中的业务代码，就像Java中的方法调用。过程可以给参数。

优点：速度快、安全、减少网络流量

案例1：无參存储过程的演示

1. 创建无参的存储过程，查询各年级的科目信息

DELIMITER$$

CREATE PROCEDURE p_student4()/*创建存储过程*/

BEGIN

/*执行多条语句*/

 SELECT *FROM SUBJECT JOIN grade ON  subject.`gradeID`=grade.`id`;

END

$$;

/*调用无參存储过程*/

CALL p_student4();

案例2：有參的存储过程演示

1. 用输入和输出参数实现指定年级的课程数量。

DELIMITER$$/*创建有參存储函数，in是传入参数，out是传出参数*/

CREATE PROCEDURE p_total(IN grade VARCHAR(20),OUT num1 INT,OUT num2 INT)

BEGIN/*执行多条查询命令*/

/*查询出输入的grade的id 通过获取到的年级id查询出课程数并赋值给num1*/

SELECT COUNT(*) INTO num1 FROM SUBJECT WHERE gradeid IN

(SELECT id FROM grade WHERE gradename=grade);

/*查询出输入的grade的id 通过获取到的年级id查询出课程总时间并赋值给num2*/

SELECT SUM(classhour) INTO num2 FROM SUBJECT WHERE gradeid IN

(SELECT id FROM grade WHERE gradename=grade);

/*查询出该年级的课程详细信息*/

SELECT * FROM SUBJECT WHERE gradeid IN

(SELECT id FROM grade WHERE gradename=grade);

END

$$;

/*设置接收变量 必须要有@*/

SET @n1=0;SET @n2=0;

/*传入相应的参数*/

CALL `p_total`('一年级',@n1,@n2);

/*查询出结果，通过下图可以看出一年级有2门课，总课时168*/

SELECT @n1'课程数',@n2'课程总时间';