mysql存储过程和自定义函数

目录

• 存储过程
  • 概念
  • 准备测试数据
  • 创建存储过程
  • 调用存储过程
  • 删除存储过程
  • 修改存储过程
  • 查看存储过程
  • 示例
    • 示例1：空的参数列表
    • 示例2：带in参数
    • 示例3：带out参数
    • 示例4：带inout参数的存储过程
    • 存储过程的查询
• 自定义函数
  • 创建函数
  • 调用函数
  • 查看函数
  • 删除函数
  • 示例
    • 示例1：有参函数
    • 示例2：有参函数
• 存储过程和函数的区别

存储过程

官方文档

概念

在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。而不是针对一个表或几个表的单条sql语句。

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

准备测试数据

-- 建test库
create database if not exists test;

use test

-- 建t_user表
drop table if exists t_user;
create table t_user (
	id int not null primary key comment '编号',
  age smallint unsigned not null comment '年龄',
  name varchar(16) not null comment '姓名'
) comment '用户表';

创建存储过程

官网语法参考：http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/create-procedure.com.coder114.cn.html

create procedure 存储过程名([参数模式] 参数名 参数类型) 
begin
	存储过程体 
end

参数模式有3种：

in：该参数可以作为输入，也就是该参数需要调用方传入值。

out：该参数可以作为输出，也就是说该参数可以作为返回值。

inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。

参数模式默认为IN。

一个存储过程可以有多个输入、多个输出、多个输入输出参数。

调用存储过程

call 存储过程名称(参数列表);

删除存储过程

drop procedure [if exists] 存储过程名称;

mysql中不能批量删除存储过程或函数，只能一次删除一个存储过程或函数，删除存储过程或者函数需要有该过程或者函数的alter routine权限

修改存储过程

存储过程不能修改，若涉及到修改的，可以先删除，然后重建。

查看存储过程

select `name` from mysql.proc where db = '库名' and `type` = 'PROCEDURE' 

show create procedure 存储过程名称;

 show procedure status;

示例

示例1：空的参数列表

创建存储过程

/*设置结束符为$*/ 
DELIMITER $ 
/*如果存储过程存在则删除*/ 
DROP PROCEDURE IF EXISTS proc1;
/*创建存储过程proc1*/ 
CREATE PROCEDURE proc1() 
  BEGIN 
    INSERT INTO t_user VALUES (1,30,'张三'); 
    INSERT INTO t_user VALUES (2,50,'李四'); 
  END $ 


/*将结束符置为;*/ 
DELIMITER ;

调用存储函数

CALL proc1();

查看结果：

mysql> select * from t_user;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  30 | 张三   |
|  2 |  50 | 李四   |
+----+-----+--------+
2 rows in set (0.00 sec)

mysql> call proc1;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t_user;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  30 | 张三   |
|  2 |  50 | 李四   |
+----+-----+--------+
2 rows in set (0.00 sec)

存储过程调用成功，表中成功插入了两条数据

示例2：带in参数

创建存储过程

/*设置结束符为$*/ 
DELIMITER $ 
/*如果存储过程存在则删除*/ 
DROP PROCEDURE IF EXISTS proc2;
/*创建存储过程proc2*/ 
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
  BEGIN 
    INSERT INTO t_user VALUES (id,age,name);
  END $


/*将结束符置为;*/ 
DELIMITER ;

调用存储过程

/*创建了3个自定义变量*/ 
SELECT @id:=3,@age:=60,@name:='王五';

/*调用存储过程*/ 
CALL proc2(@id,@age,@name);

查看效果

mysql> select * from t_user;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  30 | 张三   |
|  2 |  50 | 李四   |
|  3 |  60 | 王五   |
+----+-----+--------+
3 rows in set (0.00 sec)

示例3：带out参数

创建存储过程

/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $

/*创建存储过程proc3*/
CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out user_count int,out max_id INT)
  BEGIN
    INSERT INTO t_user VALUES (id,age,name); 
    /*查询出t_user表的记录，放入user_count中,max_id用来存储t_user中最小的id*/
    SELECT COUNT(*),max(id) into user_count,max_id from t_user;
  END $
  
/*将结束符置为;*/ 
DELIMITER ;

proc3中前两个参数id和name没有指定模式，默认为in

调用存储过程

/*创建了3个自定义变量*/ 
SELECT @id:=4,@age:=33,@name:='赵六'; 

/*调用存储过程*/ 
CALL proc3(@id,@age,@name,@user_count,@max_id);

查看效果

mysql> select @user_count,@max_id;
+-------------+---------+
| @user_count | @max_id |
+-------------+---------+
|           4 |       4 |
+-------------+---------+
1 row in set (0.00 sec)

mysql> select * from t_user;
+----+-----+--------+
| id | age | name   |
+----+-----+--------+
|  1 |  30 | 张三   |
|  2 |  50 | 李四   |
|  3 |  60 | 王五   |
|  4 |  33 | 赵六   |
+----+-----+--------+
4 rows in set (0.00 sec)

示例4：带inout参数的存储过程

创建存储过程

/*如果存储过程存在则删除*/ 
DROP PROCEDURE IF EXISTS proc4; 
/*设置结束符为$*/ 
DELIMITER $ 

/*创建存储过程proc4*/ 
CREATE PROCEDURE proc4(INOUT a int,INOUT b int) 
  BEGIN 
    SET a = a*2; 
    select b*2 into b; 
  END $
  
/*将结束符置为;*/
DELIMITER ;

调用存储过程

/*创建了2个自定义变量*/ 
set @a=10,@b:=20; 
/*调用存储过程*/ 
CALL proc4(@a,@b);

查看结果：

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

上面的两个自定义变量@a、@b作为入参，然后在存储过程内部进行了修改，又作为了返回值。

存储过程的查询

mysql> select `name` from mysql.proc where db = 'test' and `type` = 'PROCEDURE';
+-------+
| name  |
+-------+
| proc1 |
| proc2 |
| proc3 |
| proc4 |
+-------+
4 rows in set (0.00 sec)

mysql> show create procedure proc1\G
*************************** 1. row ***************************
           Procedure: proc1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`()
BEGIN
    INSERT INTO t_user VALUES (1,30,'张三');
    INSERT INTO t_user VALUES (2,50,'李四');
  END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

自定义函数

函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。

【但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】

函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。

创建函数

create function 函数名([参数名称 参数类型],[...]) 
returns 返回值类型 
begin
    函数体
end

函数体由sql语句和return值组成

参数是可选的

返回值是必须的

调用函数

• 直接使用函数名()就可以调用【虽然这么说，但返回的是一个结果，sql中不使用select的话任何结果都无法显示出来（所以单纯调用会报错），】
• 如果想要传入参数可以使用函数名(参数)

查看函数

-- 查看数据库中的函数
select `name` from mysql.proc where db = '数据库名' and `type` = 'FUNCTION';

-- 查看函数的创建过程
show create function 函数名;

-- 或是
show function status;

删除函数

drop function [if exists] 函数名;

示例

示例1：有参函数

创建函数

/*删除fun1*/ 
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/ 
DELIMITER $ 
/*创建函数*/ 
CREATE FUNCTION fun1() 
  returns INT 
  BEGIN 
    DECLARE max_id int DEFAULT 0; 
    SELECT max(id) INTO max_id FROM t_user;
    return max_id; 
  END $ 
/*设置结束符为;*/ 
DELIMITER ;

declear定义局部变量，在存储过程和函数中通过declare定义变量在begin...end中

declear的作用域仅仅在定义他的begin end中有效

对于开启了bin-log的mysql会出现如下的错误：

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)

也就是Mysql配置了复制，复制功能也就意味着Master数据的变化，会同步到Slave。对于函数，Mysql要求函数不能修改数据。也不要想着去跳过检查，否则将导致后期数据不一致。

这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

所以，在上面的函数中指定READS SQL DATA，表明这个函数不会去修改数据
我这里图方便直接加上READS SQL DATA
或者SET GLOBAL log_bin_trust_function_creators = 1;
要永久生效，配置文件添加log-bin-trust-function-creators=1，重启服务

重新执行

CREATE FUNCTION fun1() 
  returns INT READS SQL DATA
  BEGIN 
    DECLARE max_id int DEFAULT 0; 
    SELECT max(id) INTO max_id FROM t_user;
    return max_id; 
  END $ 

DELIMITER ;

查看效果：

mysql> select fun1();
+--------+
| fun1() |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

示例2：有参函数

创建函数;

/*删除函数*/ 
DROP FUNCTION IF EXISTS get_user_id; 
/*设置结束符为$*/ 
DELIMITER $ 
/*创建函数*/ 
CREATE FUNCTION get_user_id(v_name VARCHAR(16)) 
  returns INT READS SQL DATA
  BEGIN 
    DECLARE r_id int; 
    SELECT id INTO r_id FROM t_user WHERE name = v_name; 
    return r_id; 
  END $ 
/*设置结束符为;*/ 
DELIMITER ;

运行效果;

mysql> select get_user_id('李四');
+-----------------------+
| get_user_id('李四')   |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select id from t_user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.01 sec)

存储过程和函数的区别

存储过程的关键字为procedure，返回值可以有多个，调用时用call，一般用于执行比较复杂的的过程体、更新、创建等语句。

函数的关键字为function，返回值必须有一个，调用用select，一般用于查询单个值并返回

	存储过程	函数
返回值	可以有0个或者多个	必须有一个
关键字	procedure	function
调用方式	call	select