Detailed introduction to MySQL custom functions and stored procedures (with code)-Mysql Tutorial-php.cn

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)

Copy after login

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 
<函数主体>

Copy after login

The syntax for calling UDF is as follows:

select  <函数名称> ([参数])

Copy after login

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);

Copy after login

Call the function user_info_count()

mysql> select  user_info_count();
+-------------------+
| user_info_count() |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.00 sec)

Copy after login

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)

Copy after login

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)

Copy after login

View UDF

Query all UDFs in the system

show function status;

Copy after login

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 &#39;queryNameById&#39; 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 &#39;queryNameById()&#39; 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 &#39;()&#39; 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

Copy after login

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  <函数名称>;

Copy after login

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>

Copy after login

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

Copy after login

Create a stored procedure without parameters

Example 4: Query user name.

mysql> DELIMITER //
mysql> craete procedure queryName()
    -> begin
    -> select  name from user_info;
    -> end //

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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 <过程名称>

Copy after login

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)

Copy after login

Delete stored procedures

drop procedure <过程名称>

Copy after login

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

Copy after login

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 <存储过程名>

Copy after login

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!