Detailed explanation of MySQL custom functions and stored procedures

Preface

This article mainly introduces the relevant content about MySQL custom functions and stored procedures, and shares it for your reference and learning. Let’s take a look at the detailed introduction.

1. Prerequisites

There is a table user_info in the MySQL database, and 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 | [email protected] | 15103887470 | admin | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
| 2 | StephenWang8 | 123456 | [email protected] | 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 functions

Function: A set of SQL statements that can perform specific functions. MySQL supports custom functions to complete specific business functions.

The syntax for creating a user defined function (UDF) is as follows:

create function <function name> ([parameter 1] [type 1], [parameter N] [type N])
returns <type>
return
<Function body>

The syntax for calling a UDF is as follows:

select <function name> ([parameters])

Creating a UDF with no parameters

Example 1: Query the number of records in the user_info table

#Define function 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)

Creating a UDF with parameters

Example 2: Query user name based on id.

#Define function 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 function name;
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

Modifying UDF

If you want to modify the contents of a function, delete it and then recreate it.

Deleting a UDF

The syntax for deleting a UDF is as follows:

drop function <function name>;

Example 3: Delete the queryNameId function and call it again to 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 Procedures

A stored function is similar to a custom function, and is also a set of SQL statements that perform specific functions. Write complex or frequently called SQL in advance and assign a name to it. When you want to use it, just call it.

The syntax for defining a stored procedure is as follows:

CREATE PROCEDURE <procedure name> ( [procedure parameters[,…] ] ) <procedure body>
[procedure parameters[,…]] format
[ IN | OUT | INOUT ] <parameter name> <type>
#Syntax definition from: http://c.biancheng.net/view/2593.html

Creating 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 character that ends the MySQL command. The default end command character is a semicolon. When a stored procedure contains multiple statements, the first semicolon will be used as a sign of the end of the stored procedure. This is not as expected, so the default end-of-command character needs to be modified. DELIMITER // is to change the end command character to //. The command to call a stored procedure is: call stored procedure name.

#The end symbol of the command at this time is // not;
mysql> call queryName() //
+--------------+
| name |
+--------------+
|StephenWang7|
|StephenWang8|
+--------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Creating 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 contents of a stored procedure, you can delete it and then re-create it.

View stored procedures

show create procedure <procedure name>

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)

Deleting a stored procedure

drop procedure <procedure name>

Delete the stored 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. Summary

Custom functions and stored procedures are both SQL sets that perform specific functions, so what are the differences between them?

a. Different calling methods

#Custom functions
select <function name>
#Stored Procedure
call <stored procedure name>

b. Custom functions cannot have output parameters, but stored procedures can.

c. A custom function must contain a return statement, but a stored procedure does not.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in: