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:
The syntax for calling a UDF is as follows:
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:
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:
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
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
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:
|
<<: vue.js downloads pictures according to picture url
>>: Tutorial on installing rabbitmq using yum on centos8
Recommend
Semantics: Is Html/Xhtml really standards-compliant?
<br />Original text: http://jorux.com/archiv...
jQuery to achieve the barrage effect case
This article shares the specific code of jQuery t...
js uses the reduce method to make your code more elegant
Preface In actual projects, the most common proce...
How to reset the root password in mysql8.0.12
After installing the database, if you accidentall...
A quick review of CSS3 pseudo-class selectors
Preface If CSS is the basic skill of front-end de...
Implementation of Nginx operation response header information
Prerequisite: You need to compile the ngx_http_he...
MySQL 5.7.16 installation and configuration method graphic tutorial (Ubuntu 16.04)
How to install MySQL 5.7 in Ubuntu 16.04? Install...
MySQL 5.7.18 installation and configuration tutorial under Windows
This article shares the installation and configur...
React implements the expansion and collapse function of complex search forms
Give time time and let the past go. In the previo...
Building FastDFS file system in Docker (multi-image tutorial)
Table of contents About FastDFS 1. Search for ima...
A brief discussion on four solutions for Vue single page SEO
Table of contents 1.Nuxt server-side rendering ap...
SQL Practice Exercise: Online Mall Database Product Category Data Operation
Online shopping mall database-product category da...
A Brief Analysis on the Time Carrying Problem of MySQL
The default time type (datetime and timestamp) in...
JavaScript array reduce() method syntax and example analysis
Preface The reduce() method receives a function a...
Why is UTF-8 not recommended in MySQL?
I recently encountered a bug where I was trying t...