Detailed introduction to MySQL custom functions and stored procedures (with code)
- 不言forward
- 2019-04-15 11:48:392102browse
This article brings you a detailed introduction to MySQL custom functions and stored procedures (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
1. Preconditions
The table user_info exists in the MySQL database. Its structure and data are as follows:
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. Custom function
Function: A collection of SQL that can complete a specific function. MySQL supports custom functions to complete specific business functions.
The syntax for creating a custom function (User Defined Function, referred to as UDF) is as follows:
create function <函数名称> （[参数1] [类型1], [参数N] [类型N]） returns <类型> return <函数主体>
The syntax for calling UDF is as follows:
select <函数名称> ([参数])
Create a UDF without parameters
Example 1: Query how many records there are in the user_info table
#定义函数
mysql> create function user_info_count()
-> returns int(10)
-> return
-> (select count(*) from user_info);Call the function user_info_count()
mysql> select user_info_count(); +-------------------+ | user_info_count() | +-------------------+ | 2 | +-------------------+ 1 row in set (0.00 sec)
Create a UDF with parameters
Example 2: Query the user name based on id .
#定义函数
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)Call the function to query the user name with id 1.
mysql> select queryNameById(1); +------------------+ | queryNameById(1) | +------------------+ | StephenWang7 | +------------------+ 1 row in set (0.00 sec)
View UDF
Query all UDFs in the system
show function status;
Query the specified 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
Modify UDF
If you want Modify the content of the function, delete it and then recreate it.
Delete UDF
The syntax for deleting UDF is as follows:
drop function <函数名称>;
Example 3: After deleting the function queryNameId, call it again and observe the phenomenon.
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. Stored procedure
The storage function is similar to the custom function. It is also a set of SQL statements that complete specific functions. Write complex or frequently called SQL in advance and specify a name. When you want to use it, just call it directly.
The syntax for defining a stored procedure is as follows:
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 [ IN | OUT | INOUT ] <参数名> <类型> #语法定义来自：http://c.biancheng.net/view/2593.html
Create a stored procedure without parameters
Example 4: Query user name.
mysql> DELIMITER //
mysql> craete procedure queryName()
-> begin
-> select name from user_info;
-> end //Regarding the DELIMITER command, modify the characters that end the MySQL command. The default end command character is a semicolon. When the stored procedure contains multiple statements, the first semicolon encountered will be used as a sign of the end of the stored procedure. This is not as expected, so the default end command character needs to be modified. DELIMITER //Just change the end command character to //. The command to call a stored procedure is: call stored procedure name.
#此时的命令的结束符号为// 不是; mysql> call queryName()// +--------------+ | name | +--------------+ | StephenWang7 | | StephenWang8 | +--------------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Create a stored procedure with parameters
Example 5: Query name based on id.
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)Call the stored procedure queryNameById
mysql> call queryNameById(1);
-> //
+--------------+
| name |
+--------------+
| StephenWang7 |
+--------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.04 sec)Modify the stored procedure
If you want to create the content of the stored procedure, you can delete and then re-create the stored procedure.
View stored procedures
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)
Delete stored procedures
drop procedure <过程名称>
Delete stored procedures 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. Summary
Custom functions and Stored procedures are all SQL collections that perform specific functions, so what are their differences?
a. Different calling methods
#自定义函数 select <函数名> #存储过程 call <存储过程名>
b. Custom functions cannot have output parameters, but stored procedures can.
c. Custom functions must contain return statements, but stored procedures do not.
[Related recommendations: MySQL Tutorial]
The above is the detailed content of Detailed introduction to MySQL custom functions and stored procedures (with code). For more information, please follow other related articles on the PHP Chinese website!