Linux九阴真经之九阴白骨爪残卷8（存储函数、存储过程、触发器）
存储函数
说明： 参数可以有多个,也可以没有参数，必须有且只有一个返回值。
1、系统函数
参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
2、自定义函数(user-defined function：UDF)
自定义函数保存在mysql.proc表中
-
查看UDF列表 SHOW FUNCTIOIN STATUS;
-
查看UDF定义 SHOW CREATE FUNCTION function_name
-
删除UDF DROP FUNCTION function_name
- 调用自定义函数语法 SELECT function_name(parameter_value,...)
- 为变量赋值 SET parameter_name = value[,parameter_name = value...]
-
查看变量 SELECT INTO parameter_name
MariaDB [testdb]> DELIMITER // #修改结束符为// MariaDB [testdb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) -> RETURNS SMALLINT -> BEGIN -> DECLARE a, b SMALLINT UNSIGNED DEFAULT 10; -> SET a = x, b = y; -> RETURN a+b; -> END// Query OK, 0 rows affected (0.01 sec) MariaDB [testdb]> DELIMITER ; #定义完函数后再修改回来 MariaDB [testdb]> SELECT addTwoNumber(8,9); #调用UDF求和 +-------------------+ | addTwoNumber(8,9) | +-------------------+ | 17 | +-------------------+
三、存储过程
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程。提高了运行速度同时降低网络数据传输量
存储过程：存储过程保存在mysql.proc表中
创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型
查看存储过程列表 SHOW PROCEDURE STATUS
查看存储过程定义 SHOW CREATE PROCEDURE sp_name
调用存储过程: CALL sp_name ([ proc_parameter [,proc_parameter ...]])
说明:当无参时,可以省略"()",当有参数时,不可省略"()”
删除存储过程: DROP PROCEDURE [IF EXISTS] sp_name
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建
流程控制
存储过程和函数中可以使用流程控制来控制语句的执行
- IF：用来进行条件判断。根据是否满足条件，执行不同语句
- CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
- LOOP：重复执行特定的语句，实现一个简单的循环
- LEAVE：用于跳出循环控制
- ITERATE：跳出本次循环，然后直接进入下一次循环
- REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
- WHILE：有条件控制的循环语句
触发器
触发器的执行不是有程序调用，也不是由手工启动，而是有时间来触发和激活从而实现执行。
创建触发器
mysql CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名
查看触发器 SHOW TRIGGERS;
删除触发器 DROP TRIGGER trigger_name；
示例：创建触发器，在想学生表 INSERT 数据时，学生数量增加，删除学生信息时，学生数量减少。
MariaDB [laobai]> CREATE TABLE students_info (id TINYINT(2) NOT NULL AUTO_INCREMENT,name VARCHAR(30) DEFAULT NULL,PRIMARY KEY(id)); #创建一张学生信息表 Query OK, 0 rows affected (0.00 sec) MariaDB [laobai]> create table students_count (stu_count tinyint(2) default 0); #创建一张学生数量表 Query OK, 0 rows affected (0.00 sec) MariaDB [laobai]> insert into students_count values(0); #给个初始值为0 Query OK, 1 row affected (0.00 sec) MariaDB [laobai]> create trigger trigger_students_count_insert -> after insert -> on students_info for each row -> update students_count set stu_count=stu_count+1; Query OK, 0 rows affected (0.01 sec) MariaDB [laobai]> create trigger trigger_students_count_delete -> after delete -> on students_info for each row -> update students_count set stu_count=stu_count-1; Query OK, 0 rows affected (0.01 sec)
MariaDB [laobai]> insert students_info(id,name) values (1,'tom'),(2,'maria'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [laobai]> select * from students_info; +----+-------+ | id | name | +----+-------+ | 1 | tom | | 2 | maria | +----+-------+ 2 rows in set (0.00 sec) MariaDB [laobai]> select * from students_count; #插入记录，触发事件，数量增加为2 +-----------+ | stu_count | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) MariaDB [laobai]> delete from students_info where id=1; Query OK, 1 row affected (0.01 sec) MariaDB [laobai]> select * from students_info; +----+-------+ | id | name | +----+-------+ | 2 | maria | +----+-------+ 1 row in set (0.00 sec) MariaDB [laobai]> select * from students_count; #删除记录，数量减1 +-----------+ | stu_count | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
