MySQL存储函数，值得一看

秒速五厘米 2023-10-01 10:05 22阅读 0赞

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

> 写在前面： 跟随B站的康师傅学习mysql的笔记摘要和自我总结。

#### 1、创建存储函数 ####

**语法格式：**

CREATE FUNCTION 函数名(参数名 参数类型,...) 
    RETURNS 返回值类型
    BEGIN
    	函数体   #函数体中肯定有 RETURN 语句
    END

**说明：**

*  参数列表： FUNCTION中总是`默认为IN参数`。
 *  `RETURNS` 后的语句表示函数返回数据的类型；
 *  RETURNS子句只能对FUNCTION做指定，对函数而言这是`强制`的。它用来指定函数的返回类型，而且函数体必须包含一个`RETURN value`语句。
 *  函数体也可以用`BEGIN…END`来表示SQL代码的开始和结束。如果函数体只有一条语句，也`可以省略BEGIN…END`。

#### 2 、调用存储函数 ####

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是`用户自己定义`的，而内部函数是MySQL的`开发者定义`的。

SELECT 函数名(实参列表)

#### 3、删除存储函数 ####

**语法结构：**

DROP FUNCTION [IF EXISTS] 存储函数名

#### 4、查看存储过程 ####

`1. 使用SHOW CREATE语句查看存储过程和函数的创建信息`

**语法结构：**

SHOW CREATE FUNCTION 存储函数名

`2. 使用SHOW STATUS语句查看存储函数的状态信息`

**语法结构:**

SHOW FUNCTION STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

`[LIKE 'pattern']`匹配存储函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储函数的信息。

**举例：**

#查询名称以select开头的存储函数
    mysql> SHOW FUNCTION STATUS LIKE 'SELECT%';
    *************************** 1. row ***************************
                      Db: test_db
                    Name: SelectAllData
                    Type: FUNCTION 
                 Definer: root@localhost
                Modified: 2021-10-16 15:55:07
                 Created: 2021-10-16 15:55:07
           Security_type: DEFINER
                 Comment: 
    character_set_client: utf8mb4
    collation_connection: utf8mb4_general_ci
      Database Collation: utf8mb4_general_ci

`3. 从information_schema.Routines表中查看存储函数的信息`

MySQL中存储函数的信息存储在information\_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储函数的信息。

**语法结构：**

SELECT * FROM information_schema.Routines
    WHERE ROUTINE_NAME='存储函数名' [AND ROUTINE_TYPE = 'FUNCTION '];

**说明：** 如果在MySQL数据库中`存在存储过程和函数名称相同的情况`，最好指定`ROUTINE_TYPE`查询条件来指明查询的是存储过程还是函数。

#### 5、修改存储函数 ####

修改存储函数，不影响存储函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER FUNCTION 存储函数名;

#### 6、对比存储函数和存储过程 ####

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>关键字</th> 
   <th>调用语法</th> 
   <th>返回值</th> 
   <th>应用场景</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储过程</td> 
   <td>PROCEDURE</td> 
   <td>CALL 存储过程()</td> 
   <td>理解为有0个或多个</td> 
   <td>一般用于更新</td> 
  </tr> 
  <tr> 
   <td>存储函数</td> 
   <td>FUNCTION</td> 
   <td>SELECT 函数()</td> 
   <td>只能是一个</td> 
   <td>一般用于查询结果为一个值并返回时</td> 
  </tr> 
 </tbody> 
</table>

此外，**存储函数可以放在查询语句中使用，存储过程不行**。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

#### 7、练习题加强 ####

#前提准备
    CREATE TABLE employees
    AS
    SELECT * FROM atguigudb.`employees`; 
    
    CREATE TABLE 
    departments AS
    SELECT * FROM atguigudb.`departments`;

#1. 创建函数get_count(),返回公司的员工个数 #有参有返回 
    SET GLOBAL log_bin_trust_function_creators = 1;
    
    DELIMITER $
    CREATE FUNCTION get_count()
    RETURNS INT
    BEGIN
    	RETURN (SELECT COUNT(*) FROM employees);
    END $
    DELIMITER ;

**注意：**

自己在创建存储函数中报错“`you might want to use the less safe log_bin_trust_function_creators variable`”，然后查看笔记发现解决此问题的一种方式就是执行`SET GLOBAL log_bin_trust_function_creators = 1;`SQL语句

#2. 创建函数ename_salary(),根据员工id，返回它的工资 
    #查询表结构来看要返回数据的类型
    DESC employees;
    
    DELIMITER $
    CREATE FUNCTION ename_salary(id INT)
    RETURNS DOUBLE(8,2)
    BEGIN
    	RETURN (SELECT salary FROM employees WHERE employee_id = id);
    END $
    DELIMITER ;
    
    #查询结果
    SELECT ename_salary(100);

> 爱在结尾：自己应该学会：1、会使用创建存储函数的基本语法 ； 2、学会调用存储函数； 3、知道存储函数与存储过程的相同和不同点

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/fe2d20fa1eda4136878d0d4339d75de1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center