触发器、函数、存储过程、视图

触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

create trigger trigger_name
after/before insert/update/delete on 表名
for each row
begin
sql语句：（触发的语句一句或多句）
end

DELIMITER $$
create trigger trigger_name
after insert on teacher
for each row
begin
insert into course(cname,teacher_id) values('数学',new.tid);
end$$
DELIMITER ;

*MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

 

 函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数：

创建：CREATE FUNCTION 函数名称(参数列表) 　
　　   RETURNS 返回值类型 　
         函数体

调用：SELECT 函数名称(参数列表)

修改： ALTER FUNCTION 函数名称 [characteristic ...]
删除：DROP FUNCTION [IF EXISTS] 函数名称

-- 1. 创建函数
create function fun_add(a int,b int)
returns int return a + b;
-- 2. 执行函数
select fun_add(1,1);

DELIMITER $$
CREATE FUNCTION rsum(n int) RETURNS int
    begin
        declare a int default 0;
        declare s int default 0;
        while a<n do
            set a = a+1;
            set s = s+a;
        end while;
        return s;
    end$$
DELIMITER ;

 

出错信息：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
原因：

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方法：
SQL code
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF  |
+---------------------------------+-------+
mysql> set global log_bin_trust_function_creators=1;
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON  |
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1

 

 

视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影

create view view_name as select 语句

*视图只是一条预编译的SQL语句，并不保存实际数据 

mysql> select * from teacher,course where course.teacher_id = teacher.tid;
+-----+-----------------+-----+--------+------------+
| tid | tname           | cid | cname  | teacher_id |
+-----+-----------------+-----+--------+------------+
|   1 | 张磊老师        |   1 | 生物   |          1 |
|   2 | 李平老师        |   2 | 物理   |          2 |
|   3 | 刘海燕老师      |   3 | 体育   |          3 |
|   2 | 李平老师        |   4 | 美术   |          2 |
+-----+-----------------+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> create view view_name as select * from teacher,course where course.teacher_id = teacher.tid;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_homework |
+--------------------+
| class              |
| course             |
| score              |
| student            |
| teacher            |
| view_name          |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from view_name;
+-----+-----------------+-----+--------+------------+
| tid | tname           | cid | cname  | teacher_id |
+-----+-----------------+-----+--------+------------+
|   1 | 张磊老师        |   1 | 生物   |          1 |
|   2 | 李平老师        |   2 | 物理   |          2 |
|   3 | 刘海燕老师      |   3 | 体育   |          3 |
|   2 | 李平老师        |   4 | 美术   |          2 |
+-----+-----------------+-----+--------+------------+

　

存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）

create procedure 存储过程名(参数,参数,…)
　　 begin
　　 //代码
　　 end

call 存储过程名(参数...)

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<30)do
        insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

存储过程可以有多个in,out,inout参数，而函数只有输入参数类型，而且不能带in.
存储过程实现的功能要复杂一些；而函数的单一功能性（针对性）更强。
存储过程可以返回多个值；存储函数只能有一个返回值。
存储过程一般独立的来执行；而存储函数可以作为其它sql语句的组成部分来出现。
存储过程可以调用存储函数。函数不能调用存储过程。

作者：唯老
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。