Write in front: follow Master Kang of station B to learn the notes summary and self summary of mysql.

1. Create storage function

Syntax format:

CREATE FUNCTION Function name(Parameter name parameter type,...) 
RETURNS return type
BEGIN
	Function body   #There must be a RETURN statement in the function body
END

explain:

• Parameter list: FUNCTION always defaults to the IN parameter.
• The statement after RETURNS indicates the type of data returned by the function;
• The RETURNS clause can only specify FUNCTION, which is mandatory 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 represent the beginning and END of SQL code. If the function body has only one statement, BEGIN... END can also be omitted.

2. Call storage function

In mysql, the use of storage functions is the same as that of MySQL internal functions. In other words, user-defined storage functions and MySQL internal functions are of the same nature. The difference is that the storage function is defined by the user, while the internal function is defined by the MySQL developer.

SELECT Function name(Argument list)

3. Delete storage function

Syntax structure:

DROP FUNCTION [IF EXISTS] Store function name

4. Viewing stored procedures

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

Syntax structure:

SHOW CREATE FUNCTION Store function name

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

Syntax 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 storage function and can be omitted. When not writing is omitted, the information of all storage functions existing in MySQL database will be listed.

give an example:

#Query the storage function whose name starts with select
mysql> 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. From information_ schema. View the information of the stored function in the routes table

The function information stored in MySQL is stored in information_ In the routes table under the schema database. You can query the information of the storage function by querying the records of the table.

Syntax structure:

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

Note: if the stored procedure and function names are the same in MySQL database, it is better to specify route_ Type query criteria to indicate whether the query is a stored procedure or a function.

5. Modify storage function

Modifying the storage function does not affect the function of the storage function, but only modifies the relevant characteristics. Use the ALTER statement.

ALTER FUNCTION Store function name;

6. Compare stored functions and stored procedures

	keyword	Call syntax	Return value	Application scenario
stored procedure	PROCEDURE	CALL stored procedure ()	Understood as 0 or more	Generally used for updates
Storage function	FUNCTION	SELECT function ()	It can only be one	It is generally used when the query result is a value and is returned

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

7. Exercise strengthening

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

CREATE TABLE 
departments AS
SELECT * FROM atguigudb.`departments`; 

#1. Create function get_count(),Returns the number of employees in the company #There are 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 ;

be careful:

You may want to use the less safe log_bin_trust_function_creators variable when you create a storage function. After checking the notes, you find that one way to solve this problem is to execute SET GLOBAL log_bin_trust_function_creators = 1;SQL statement

#2. Create function ename_salary(), returns its salary according to the employee id 
#Query 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. Be able to use the basic syntax for creating storage functions; 2. Learn to call storage functions; 3. Know the similarities and differences between stored functions and stored procedures