1, 前置条件
MySQL 数据库中存在表 user_info, 其结构和数据如下:
- MySQL> desc user_info;
- +-----------+----------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-----------+----------+------+-----+---------+-------+
- | id | int(10) | NO | PRI | NULL | |
- | name | char(20) | NO | | NULL | |
- | passwd | char(40) | NO | | NULL | |
- | email | char(20) | NO | | NULL | |
- | phone | char(20) | NO | | NULL | |
- | role | char(10) | NO | | NULL | |
- | sex | char(10) | NO | | NULL | |
- | status | int(10) | NO | | NULL | |
- | createAt | datetime | NO | | NULL | |
- | exprAt | datetime | NO | | NULL | |
- | validDays | int(10) | NO | | NULL | |
- | delAt | datetime | YES | | NULL | |
- +-----------+----------+------+-----+---------+-------+
- 12 rows in set (0.10 sec)
- MySQL> select * from user_info;
- +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
- | id | name | passwd | email | phone | role | sex | status | createAt | exprAt | validDays | delAt |
- +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
- | 1 | StephenWang7 | py123456 | 123@qq.com | 15103887470 | admin | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
- | 2 | StephenWang8 | 123456 | 123@qq.com | 15103887470 | viewer | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
- +----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
- 2 rows in set (0.00 sec)
2, 自定义函数
函数: 可以完成特定功能的一段 SQL 集合. MySQL 支持自定义函数来完成特定的业务功能.
创建自定义函数 (User Defined Function 简称 UDF) 的语法如下:
create function <函数名称> ([参数 1] [类型 1], [参数 N] [类型 N])
returns <类型>
- return
- <函数主体>
调用 UDF 的语法如下:
select <函数名称> ([参数])
创建无参的 UDF
示例 1: 查询 user_info 表中有多少条记录
- # 定义函数
- MySQL> create function user_info_count()
- -> returns int(10)
- -> return
- -> (select count(*) from user_info);
调用函数 user_info_count()
- MySQL> select user_info_count();
- +-------------------+
- | user_info_count() |
- +-------------------+
- | 2 |
- +-------------------+
- 1 row in set (0.00 sec)
创建有参 UDF
示例 2: 根据 id 查询用户 name.
- # 定义函数
- MySQL> create function queryNameById(uid int(10))
- -> returns char(20)
- -> return
- -> (select name from user_info where id=uid);
- Query OK, 0 rows affected (0.01 sec)
调用函数, 查询 id 为 1 的用户名称.
- MySQL> select queryNameById(1);
- +------------------+
- | queryNameById(1) |
- +------------------+
- | StephenWang7 |
- +------------------+
- 1 row in set (0.00 sec)
查看 UDF
查询系统中所有的 UDF
show function status;
查询指定的 UDF
#
show create function 函数名称;
- MySQL> show function queryNameById;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById' at line 1
- MySQL> show function queryNameById();
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById()' at line 1
- MySQL> show create function queryNameById();
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
- MySQL> show create function queryNameById;
- +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- | Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |
- +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- | queryNameById | 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 DEFINER=`root`@`localhost` FUNCTION `queryNameById`(uid int(10)) RETURNS char(20) CHARSET latin1
- return (select name from user_info where id=uid) | utf8 | utf8_general_ci | latin1_swedish_ci |
- +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
- 1 row in set (0.00 sec
修改 UDF
如果想要修改函数的内容, 先删除后再重新创建.
删除 UDF
删除 UDF 语法如下:
drop function <函数名称>;
示例 3: 删除函数 queryNameId 后再次调用并观察现象.
- MySQL> drop function queryNameById;
- Query OK, 0 rows affected (0.45 sec)
- MySQL> select queryNameById(1);
- ERROR 1305 (42000): FUNCTION rms.queryNameById does not exist
- MySQL>
3, 存储过程
存储功能和自定义函数相似, 也是一组完成特定功能的 SQL 语句集合. 把复杂或频繁调用的 SQL 提前写好并指定一个名称. 待到要使用时, 直接调用即可.
定义存储过程的语法如下:
CREATE PROCEDURE <过程名> ( [过程参数[,...] ] ) <过程体>
[过程参数[,...] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
# 语法定义来自: http://c.biancheng.net/view/2593.html
创建无参的存储过程
示例 4: 查询用户 name.
- MySQL> DELIMITER //
- MySQL> craete procedure queryName()
- -> begin
- -> select name from user_info;
- -> end //
关于 DELIMITER 命令, 修改 MySQL 结束命令的字符. 默认的结束命令字符为分号, 当存储过程中包含多条语句时, 遇到第一个分号会作为存储过程结束的标志. 这样不符合预期, 因此需要修改默认结束命令字符. DELIMITER // 就是将结束命令字符修改为 //. 调用存储过程的命令为: call 存储过程名称.
- # 此时的命令的结束符号为 // 不是;
- MySQL> call queryName()//
- +--------------+
- | name |
- +--------------+
- | StephenWang7 |
- | StephenWang8 |
- +--------------+
- 2 rows in set (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
创建带参数的存储过程
示例 5: 根据 id 查询 name.
- MySQL> create procedure queryNameById
- -> (In uid int(15))
- -> begin
- -> select name from user_info where id=uid;
- -> end
- -> //
- Query OK, 0 rows affected (0.03 sec)
调用存储过程 queryNameById
- MySQL> call queryNameById(1);
- -> //
- +--------------+
- | name |
- +--------------+
- | StephenWang7 |
- +--------------+
- 1 row in set (0.03 sec)
- Query OK, 0 rows affected (0.04 sec)
修改存储过程
如果想创建存储过程的内容可以先删除再重新创建存储过程.
查看存储过程
show create procedure <过程名称>
MySQL> show create procedure queryNameById; -> // +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | queryNameById | 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 DEFINER=`root`@`localhost` PROCEDURE `queryNameById`(In uid int(15)) begin select name from user_info where id=uid; end | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.04 sec)
删除存储过程
drop procedure <过程名称>
删除存储过程 queryNameById
- MySQL> drop procedure queryNameById//
- Query OK, 0 rows affected (0.02 sec)
- MySQL> call queryNameById(1)//
- ERROR 1305 (42000): PROCEDURE rms.queryNameById does not exist
4, 总结
自定义函数和存储过程都是完成特定功能的 SQL 集合, 那么他们有什么不同呢?
a, 调用方式不同
# 自定义函数
select <函数名>
# 存储过程
call <存储过程名>
b, 自定义函数不能有输出参数, 而存储过程可以.
c, 自定义函数必须要包含 return 语句, 而存储过则不需要.
来源: http://www.linuxidc.com/Linux/2019-04/158100.htm