程序媛

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

触发器:触发器是一个特殊的存储过程,它是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语句的组成部分来出现。
存储过程可以调用存储函数。函数不能调用存储过程。

作者:唯老
链接:https://www.jianshu.com/p/ffa593181ead
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
函数和存储过程的区别

 

更多内容

https://blog.csdn.net/babycan5/article/details/82789099

posted @ 2019-11-07 11:20  Eva_J  阅读(1472)  评论(1编辑  收藏  举报