MySQL stored functions detailed introduction

MySQL stored functions detailed introduction

1. Create a stored function

Syntax format:

CREATE FUNCTION function name (parameter name parameter type, ...) 
RETURNS Return value type BEGIN
	Function body#The function body must contain a RETURN statement END

illustrate:

Parameter list:

FUNCTION always默認為IN參數

The statement after RETURNS indicates the type of data returned by the function;

The RETURNS clause can only be specified for FUNCTION and is強制for functions. It is used to specify the return type of the function, and the function body must contain a RETURN value statement.

The function body can also use BEGIN…END to indicate the beginning and end of the SQL code.

If the function body contains only one statement,可以省略BEGIN…END .

2. Calling stored functions

In MySQL, the usage of stored functions is the same as that of MySQL internal functions. In other words, user-defined stored functions are of the same nature as MySQL internal functions. The difference is that stored functions are用戶自己定義, while internal functions are開發者定義.

SELECT function name (argument list)

3. Delete stored functions

Grammatical structure:

DROP FUNCTION [IF EXISTS] stored function name

4. View stored procedures

1. Use the SHOW CREATE statement to view the creation information of stored procedures and functions

Grammatical structure:

SHOW CREATE FUNCTION stored function name

2. Use the SHOW STATUS statement to view the status information of the stored function

Grammatical structure:

SHOW FUNCTION STATUS [LIKE 'pattern']

This statement returns the characteristics of the subroutine, such as database, name, type, creator, and creation and modification dates.

[LIKE 'pattern'] matches the name of the stored function, which can be omitted. If omitted, information about all stored functions in the MySQL database will be listed.

Example:

#Query the stored functions whose names begin with selectmysql> SHOW FUNCTION STATUS LIKE 'SELECT%';
*************************** 1. row ***************************
                  Db: test_db
                Name: SelectAllData
                Type: FUNCTION 
             Definer: root@localhost
            Modified: 2021-10-16 15:55:07
             Created: 2021-10-16 15:55:07
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci

3. View the information of stored functions from the information_schema.Routines table

The information of stored functions in MySQL is stored in the Routines table under the information_schema database. You can query the information of stored functions by querying the records in this table.

Grammatical structure:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = 'Stored function name' [AND ROUTINE_TYPE = 'FUNCTION '];

Note: If存在存儲過程和函數名稱相同的情況in the MySQL database, it is best to specify ROUTINE_TYPE query condition to indicate whether the query is for a stored procedure or a function.

5. Modify the storage function

Modifying a storage function does not affect the function's functionality, but only modifies related features. This is done using the ALTER statement.

ALTER FUNCTION stored function name;

6. Comparison between stored functions and stored procedures

Keywords Calling Syntax Return Value Application Scenario
Stored Procedures PROCEDURE CALL stored procedure() Understood as 0 or more Generally used for updates
Stored Functions FUNCTION SELECT function() Can only be one Generally used when the query result is a value and is returned

In addition, stored functions can be used in query statements, but stored procedures cannot . On the contrary, stored procedures are more powerful, including the ability to perform table operations (such as creating tables, deleting tables, etc.) and transaction operations, which are not available in stored functions.

7. Strengthen the exercises

#Prerequisites CREATE TABLE employees
AS
SELECT * FROM atguigudb.`employees`; 

CREATE TABLE 
departments AS
SELECT * FROM atguigudb.`departments`; 
#1. Create a function get_count() to return the number of employees in the company. #with parameters and returns SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
	RETURN (SELECT COUNT(*) FROM employees);
END $
DELIMITER ;

Notice:

When creating a stored you might want to use the less safe log_bin_trust_function_creators variable variable". Then I checked my notes and found that one way to solve this problem is to execute the SQL statement SET GLOBAL log_bin_trust_function_creators = 1;

#2. Create a function ename_salary() to return the salary of an employee according to his/her id. #Query the table structure to see the type of data to be returned. DESC employees;

DELIMITER $
CREATE FUNCTION ename_salary(id INT)
RETURNS DOUBLE(8,2)
BEGIN
	RETURN (SELECT salary FROM employees WHERE employee_id = id);
END $
DELIMITER ;

#Query results SELECT ename_salary(100);

Love at the end: You should learn: 1. Use the basic syntax for creating stored functions; 2. Learn to call stored functions; 3. Know the similarities and differences between stored functions and stored procedures

This is the end of this article about the detailed introduction of MySQL stored functions. For more relevant MySQL stored function content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Thoroughly understand MySQL stored procedures and functions
  • Analysis of the difference between MySQL stored functions and stored procedures
  • Introduction to commonly used functions in MYSQL database
  • MySQL batch inserts data through function stored procedures
  • Detailed explanation of creating stored procedures and functions in mysql
  • MySQL detailed summary of commonly used functions
  • Comprehensive summary of mysql functions
  • How to use MYSQL functions

<<:  Solutions to problems related to software package dependency reporting during installation in Linux

>>:  What we have to say about CSS absolute and relative

Recommend

Timeline implementation method based on ccs3

In web projects we often use the timeline control...

Detailed explanation of this reference in React

Table of contents cause: go through: 1. Construct...

HTML realizes real-time monitoring function of Hikvision camera

Recently the company has arranged to do some CCFA...

Specific steps for Vue browser to return monitoring

Preface When sharing a page, you hope to click th...

Navicat for MySQL 11 Registration Code\Activation Code Summary

Recommended reading: Navicat12.1 series cracking ...

8 essential JavaScript code snippets for your project

Table of contents 1. Get the file extension 2. Co...

Linux system to view CPU, machine model, memory and other information

During system maintenance, you may need to check ...

MySQL 5.7.23 installation and configuration graphic tutorial

This article records the detailed installation pr...

Basic installation process of mysql5.7.19 under winx64 (details)

1. Download https://dev.mysql.com/downloads/mysql...

Detailed explanation of keywords and reserved words in MySQL 5.7

Preface The keywords of MySQL and Oracle are not ...

Solutions to browser interpretation differences in size and width and height in CSS

Let’s look at an example first Copy code The code ...

Detailed explanation of MySQL database (based on Ubuntu 14.0.4 LTS 64 bit)

1. Composition and related concepts of MySQL data...

Vue implements multi-column layout drag

This article shares the specific code of Vue to i...