MySQL之Procedure（存储过程）和Function（函数）

骑猪看日落 2023-07-03 08:23 14阅读 0赞

## 一组预先编译好的SQL语句的集合，相当于批处理语句 ##

### 存储过程 ###

特点：提高代码的重用性、简化操作、减少编译次数和数据库的连接次数，提高了效率

**①创建**  
CREATE PROCEDURE 存储过程（参数列表）  
BEGIN  
存储过程体（一组合法的sql语句）  
END  
参数列表：参数模式 参数名 参数类型  
参数模式： IN（该参数作为输入） OUT（相当于返回值） INOUT（相当于引用，既传参又返回）  
在给OUT模式赋值时：SELECT 表名.列名, … INTO 参数名, …  
PS：

*  若存储过程仅仅一句话，BEGIN END可省略
 *  存储过程体的每条SQL语句结尾必须添加分号

**存储过程的结束符需使用 DELIMITER 进行设置，否则由于存储过程体中存在分号，mysql会判断语句结束，导致报错。  
语法：DELIMITER 结束标记（在结束标记更改后，要一直使用使用更改后的标记）**  
例：

CREATE PROCEDURE inset(IN a INT,INOUT b INT)
    BEGIN 
    	 SET b = a+b;
    END $

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxODkxODA1_size_16_color_FFFFFF_t_70]  
**②调用**  
CALL 存储过程名（参数列表）  
TIPS：出现乱码问题时，可使用 SET NAMES ‘字符集’ \#可以改变与cilent端相关的编码字符集  
出现表中列名和参数名相同时，为就近原则，常用表名.列名来区分  
对于OUT和INOUT类型参数，直接在参数列表中命名，用SELECT查看

**③删除**  
DROP PROCEDURE 存储过程名 （一次只能删除一个）  
**④查看存储过程的信息**

SHOW CREATE PROCEDURE 存储过程名

### 函数 ###

存储过程可有0个或多个返回，适合操作批量更新  
函数只能有一个返回，适合数据处理后返回一个结果

**①创建**  
REATE FUNCTION 函数名（参数列表）RETURNS 返回类型  
BEGIN  
函数体  
END  
参数列表： 参数名 参数类型  
PS：若存储过程仅仅一句话，BEGIN END可省略  
一定包含RETURN语句，若RETURN语句没位于函数体最后也不报错，但不建议

**②调用**  
SELECT 函数名（参数列表）

**③查看**  
SHOW CREATE FUNCTION  
PS：所有存储过程和函数都在mysql库proc表中

**④删除**  
DROP FUNCTION 函数名

**TIPS：有关建立函数时遇到的问题：**  
**原因** ：由于主从复制的关系，自定义函数可能导致两边数据不一致，所以mysql不允许用户使用不安全自定义函数。

**原理**  
在开启binlog（二进制日志）的情况下，log\_bin\_trust\_function\_creators参数默认设为0。

**解决方法**  
创建函数时至少要指定 DETERMINISTIC（确定的）, NO SQL（无SQL语句） , READS SQL DATA（只是读取数据）这几个参数中的一个，它们告知服务器，该函数不会修改数据（位于函数声明后 BEGIN前）  
或把log\_bin\_trust\_function\_creators变量设置为1  
例：

CREATE FUNCTION inset() RETURNS INT
    DETERMINISTIC
    BEGIN
       DECLARE num INT DEFAULT 0;
       SELECT COUNT(*) INTO num
       FROM EMPLOYEE
       WHERE gender = 1;
       RETURN num;
    END $$

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxODkxODA1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200207103540271.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxODkxODA1,size_16,color_FFFFFF,t_70