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

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!