Explanation of MySQL Custom Functions and Stored Procedures Examples

Preface

This article mainly introduces the related contents about MySQL custom functions and stored procedures, and shares them for your reference and study. The following words are not much to say, let's take a look at the detailed introduction

1. Preconditions

The table user_info exists in the MySQL database with the following structure and data:

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 functions

Function: A 1-segment SQL collection that can complete specific functions. MySQL supports custom functions to perform specific business functions.

The syntax for creating a custom function (User Defined Function for short 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 UDF is as follows:

select < Function name > ([Parameter])

Creating a Parameterless UDF

Example 1: Query how many records are in the user_info table

# Definition function 
mysql> create function user_info_count()
 -> returns int(10)
 -> return
 -> (select count(*) from user_info);

Call function user_info_count ()

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

Create a parameterized UDF

Example 2: Query user name according to id.

# Definition 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 of 1.

mysql> select queryNameById(1);
+------------------+
| queryNameById(1) |
+------------------+
| StephenWang7 |
+------------------+
1 row in set (0.00 sec)

View UDF

Query all UDF 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

Modify UDF

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

Delete UDF

Delete the UDF syntax as follows:

drop function < Function name > ;

Example 3: Delete the function queryNameId and 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 procedures

The storage function is similar to the custom function, and is also a set of SQL statements that perform specific functions. Write complex or frequently called SQL in advance and specify a name. When you want to use it, you can call it directly.

The syntax for defining stored procedures is as follows:

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

Creating a Parameterless Stored Procedure

Example 4: Query user name.

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

For the DELIMITER command, modify the characters of the MySQL end command. The default end command character is a semicolon. When a stored procedure contains multiple statements, the first semicolon is encountered as the end flag of the stored procedure. This is not expected, so the default end command character needs to be modified. DELIMITER//is to change the end command character to//. The command to call the stored procedure is: call stored procedure name.

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

Query OK, 0 rows affected (0.00 sec)

Creating stored procedures with parameters

Example 5: Query name against id.

# Definition function 
mysql> create function user_info_count()
 -> returns int(10)
 -> return
 -> (select count(*) from user_info);

0

Call the stored procedure queryNameById

# Definition function 
mysql> create function user_info_count()
 -> returns int(10)
 -> return
 -> (select count(*) from user_info);

1

Modify stored procedures

If you want to create the contents of a stored procedure, you can delete it first and then recreate it.

Viewing Stored Procedures

# Definition function 
mysql> create function user_info_count()
 -> returns int(10)
 -> return
 -> (select count(*) from user_info);

2

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 procedure

drop procedure < Procedure name >

Delete stored procedure queryNameById

# Definition function 
mysql> create function user_info_count()
 -> returns int(10)
 -> return
 -> (select count(*) from user_info);

4

4. Summary

Custom functions and stored procedures are SQL collections that perform specific functions, so what's the difference between them?

a, called in different ways

# Custom functions
select < Function name >
# Stored procedure
call < Stored procedure name >

b, custom functions cannot have output parameters, while stored procedures can.

c, custom functions must contain return statements, while stored procedures do not.

Summarize