30.4. MySQL函数,存储过程,触发器,视图-颤沙的博客-51CTO博客

函数

函数：系统函数和自定义函数<>系统函数:https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html<>自定义函数 (user-defined function UDF)

注自定义函数定义之后保存在mysql.proc（mysql.func)表中 创建UDF<><>必须得有，且只能有一个返回值

创建函数

示例1：无参UDF

查看自定义的函数列表：

示例2：有参数UDF<><>

其中的delimiter代表分隔符，先切换成//（也可以写其他的符号），然后函数定义完了之后再切换回; 这样做用于避免函数的的内容被执行，因为函数体内的命令有分号作为结尾，不先修改分隔符的话会被当做命令来执行。

自定义函数中定义局部变量语法

DECLARE 变量1[,变量2,... ] 变量类型 [DEFAULT 默认值]

说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义

示例3:<><>DECLARE a, b SMALLINT UNSIGNED;

在这里的函数中定义本地变量的时候，要使用declare命令(主义和set区别)，一行中的变量的类型只需要写一次（写在最后）即可，它代表把前面所有的变量都定义为这种类型的 写在begin和and之间进行定义的变量都是局部变量，在这里类似C语言，且变量前面不用加任何符号直接定义和使用即可

为变量赋值语法

SET @parameter_name = value[,parameter_name = value...]<>示例4:<>

存储过程

存储过程优势

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程 提高了运行速度 同时降低网络数据传输量<>存储过程与自定义函数的区别 存储过程实现的过程要复杂一些,而函数的针对性较强 存储过程可以有多个返回值,而自定义函数只有一个返回值 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用

存储过程：存储过程保存在mysql.proc表中

创建存储过程<>说明:当无参时,可以省略"()",当有参数时,不可省略"()” 存储过程修改<>方法就是删除重建 删除存储过程

存储过程示例

创建无参存储过程

delimiter //<>

CALL showTime;

创建含参存储过程：只有一个IN参数

delimiter //<>

call selectById(2);

创建包含有会话级全局变量的存储过程

delimiter //<>

CALL dorepeat(100);

注意这里用到了循环体repeat ... until ... END repeat; 因为是用set命令定义的自定义的变量，所以就算在存储过程外也能够直接使用它。 在这里用了set定义会话级全局变量的方式把过程内的结果传入到外面，更简单的方法就是下面4中的定义一个OUT参数，用它把结果传到外面（不过也得利用外部的会话级自定义全局变量接收）。
创建含参存储过程:包含IN参数和OUT参数

delimiter //<><>SELECT row_count() into num; #注意这里的形参并没有用@，看注意点7

call deleteById(2,@Line);

说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行的数值的用户变量@Line 然后用select @Line;输出被修改后的行的值（这里也就指的是被删除掉了多少行的行数值，删除这些行的判断条件由传入的参数uid指定）。 可用help row_count 查看此函数的解释

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行<>

IF：用来进行条件判断。根据是否满足条件，执行不同语句 CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断 LOOP：重复执行特定的语句，实现一个简单的循环 LEAVE：用于跳出循环控制 （类似break) ITERATE：跳出本次循环，然后直接进入下一次循环 (类似continue) REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句 WHILE：有条件控制的循环语句

trigger触发器(类似ansible中的handler)

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行<>创建触发器<><><>说明：<>BEFORE | AFTER }，表示在事件之前或之后触发<>

查看触发器

SHOW TRIGGERS 查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息:<><>删除触发器

触发器示例

=================先定义一张表：
CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id)
);
CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
INSERT INTO student_count VALUES(0);

================然后创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
CREATE TRIGGER trigger_student_count_insert
AFTER INSERT
ON student_info FOR EACH ROW  ：这里要指定有事件变化的表（info表），别指定成了count表
UPDATE student_count SET student_count=student_count+1;
CREATE TRIGGER trigger_student_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

注意这里的ON TABLE_NAME中的table_name指定的是变化的表和事件,它和trigger_body中的命令并无直接联系
body中的SQL语句可以对任何表进行任何操作，和上面ON后面的表并无直接联系，那个表只适用于判断事件的发生，事件发生后就和body无关了（也就是说并非只能针对ON后面的这个表进行操作，body命令没有限制）

注意点:

自定义函数存储的位置是在mysql.proc表中，这是一个表。 函数和存储过程（本质也是一种函数）的定义可以用交互式方式进行，也可以用非交互式方式先写到文件内，然后导入即可（数据库外直接重定向，连接到数据库内则用source命令）。 注意这里只是定义了函数和存储过程，而并没有调用或者执行它们。 注意，与示例3中函数体中的局部变量相对应的会话级的全局变量可以在mysql的交互式命令行中用set命令来进行定义赋值；set命令不仅可以定义并赋值用户自己定义的变量，还可以用来修改系统自身存在的变量的值。 不过系统自身的变量的值是否能够直接在mysql命令行中修改，以及系统变量生效的范围（global,session也就是这个端口）等等，会在下一章进行详细介绍。 这里需要注意的就是如果是global类型的变量，只有在mysql服务重启的时候，它才会重新恢复到默认值（假设没写入配置文件且用set修改了它的值）;而session级别的，只要退出了mysql的连接，下次客户端重新连接mysql服务的时候它都会恢复到默认值（包括另外的终端上连接的也是）。除非将它写入配置文件中（有相对应的选项）并且重启mysql服务。 另外一点要注意的就是用mysqladmin能够修改的变量会在重启mysql服务后才恢复默认值，而不是客户端mysql连接到服务中后。因为mysqladmin修改的就是服务器端的配置，如果没有写入配置文件，则服务器端重启之后会恢复到默认值。它和mysql客户端命令是没有关系的。 注意用set命令定义和赋值自定义变量的时候要在变量名字前面加上@符号，不然会被当做系统变量。这个变量虽然是全局可用的，但它也是出于会话级别的全局，新开一个会话（终端端口）就不能再用了。 显示这个变量的时候要用select @变量名 ； 注意show variables [like '系统变量名'] 命令只能显示系统定义的变量 还可以用into命令把聚合函数的结果赋值到自定义变量中： select avg(age) form students into @avg_age; select @avg_age; 存储过程就相当于是一部分SQL语句和操作的合集的封装，和函数很类似（其实本质就是一种特殊的function) 函数和存储过程中的变量使用的的时候（不论是定义的时候用的形参，还是说内部定义的本地变量）都不需要在变量或参数前面加上@符号，只有用到了set命令自定义变量赋值或者外部的自定义变量的时候，才需要在参数名前面加上@符号。 注意一点就是表中的字段用set修改赋值的时候也不需要加上@符号，这里的字段虽然也能够进行各种计算和条件判断（where)等,但是字段和变量并不一样。（参考上面trigger的示例中的set命令，由此想起来这一点的）

视图

视图：VIEW,虚表，保存有实表的查询结果

创建方法：<>注意：视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制

视图注意点：

因为视图创建完之后在数据库中就像真的表一般无法区分，（此时当然也不可能用show create view view_name的方式来查看，因为不知道是view 还是 table)