MySQL之存储函数详细介绍

吾爱主题阅读：153 2024-04-02 18:53:45 评论：0

1、创建存储函数

语法格式：

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

说明：

参数列表：

FUNCTION中总是默认为IN参数

RETURNS 后的语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

函数体也可以用BEGIN…END来表示SQL代码的开始和结束。

如果函数体只有一条语句，也可以省略BEGIN…END。

2 、调用存储函数

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

1	`SELECT` `函数名(实参列表)`

3、删除存储函数

语法结构：

1	`DROP` `FUNCTION` `[IF EXISTS] 存储函数名`

4、查看存储过程

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

语法结构：

1	`SHOW` `CREATE` `FUNCTION` `存储函数名`

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

语法结构:

1	`SHOW` `FUNCTION` `STATUS [` `LIKE` `'pattern'` `]`

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

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

举例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 #查询名称以 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表中。可以通过查询该表的记录来查询存储函数的信息。

语法结构：

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

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

5、修改存储函数

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

1	`ALTER` `FUNCTION` `存储函数名;`

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

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

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

7、练习题加强

1 2 3 4 5 6 7 8 #前提准备 CREATE TABLE employees AS SELECT * FROM atguigudb.`employees`; CREATE TABLE departments AS SELECT * FROM atguigudb.`departments`;

1 2 3 4 5 6 7 8 9 10 #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语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 #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);