Shulou(Shulou.com)05/31 Report--

This article is about how to customize functions and stored procedures in MySQL. I think it is very practical, so I share it with you. I hope you can get something after reading this article.

1. Pre-conditions

Table user_info exists 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 | 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 | 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 | 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 perform specific functions. MySQL supports custom functions to perform specific business functions.

The syntax for creating a custom function (User Defined Function referred to as UDF) is as follows:

Create function ([parameter 1] [type 1], [parameter N] [type N])

Returns

Return

The syntax for calling UDF is as follows:

Select ([parameters])

Create a UDF without parameters

Example 1: query how many records are 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)

Create a parametric UDF

Example 2: query the user name based on id.

# define functions 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 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 1mysql > 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 1mysql > 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 1mysql > 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 latin1return (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 the function, delete it and then recreate it.

Delete UDF

The syntax for deleting a UDF is as follows:

Drop function

Example 3: call again after deleting the function queryNameId 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 existmysql >

3. Stored procedure

The storage function is similar to a custom function in that it is a collection 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, just call it directly.

The syntax for defining a stored procedure is as follows:

CREATE PROCEDURE ([process parameters [,...] ])

[process parameters [,...] ] format

[IN | OUT | INOUT]

# Syntax definition from: 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 / /

About the DELIMITER command, modify the character of the MySQL end command. The default end command character is a semicolon, and when a stored procedure contains multiple statements, the first semicolon is encountered as a sign of the end of the stored procedure. This is not as expected, so you need to modify the default end command character. DELIMITER / / is to change the end command character to / /. The command to call the stored procedure is: call stored procedure name.

# the command ends with / / no; 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 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 stored procedure

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

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 procedure

Drop procedure

Delete 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 SQL collections that perform specific functions, so what's the difference between them?

A, the calling mode is different

# Custom function

Select

# stored procedure

Call

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

C. Custom functions must contain return statements, but stored procedures do not.

The above is how to customize functions and stored procedures in MySQL. Xiaobian believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.