Stored procedures and functions in MySQL (super detailed, with code analysis)

Posted by MDanz on Sat, 12 Feb 2022 07:04:03 +0100

Stored procedures and functions

MySQL supports stored procedures and functions from version 5.0. Stored procedures and functions can encapsulate complex SQL logic. Applications do not need to pay attention to the complex SQL logic inside stored procedures and functions, but simply call stored procedures and functions.

1. Overview of stored procedures

1.1 understanding

Meaning: Stored Procedure is Stored Procedure in English. His idea is very simple, that is, a group of precompiled SQL statements are encapsulated.

Execution procedure: the stored procedure is pre stored on the MySQL server. When it needs to be executed, the client only needs to send the command to call the stored procedure to the server, and the server can execute all the pre stored series of SQL statements.


  1. It simplifies the statements of programmers and improves the reusability of sql.
  2. Reduce mistakes in operation and improve efficiency
  3. Reduce the amount of network transmission (the client does not need to send all SQL statements to the server through the network)
  4. It reduces the risk of SQL statement exposure on the Internet and improves the security of data query.

Comparison with views and functions:

It has the same advantages as view, clarity and security, and can also reduce the amount of network transmission. However, it is different from the view. The view is a virtual table and usually does not operate directly on the underlying data table, while the stored procedure is programmed SQL. It can directly operate the underlying data table and realize some more complex data processing compared with the set oriented operation mode.

Once the stored procedure is created, using it is as simple as using a function. We can call the stored procedure name directly. Compared with functions, stored procedures have no return value.

1.2 classification

The parameter types of stored procedures can be IN, OUT, and INOUT. According to this point, it is classified as follows:

  1. No parameters (no return)
  2. Only with IN type (with parameters and no return)
  3. Only with OUT type (no parameter and return)
  4. Both IN and OUT (with parameters and return)
  5. With INOUT (return with parameters)

Note: IN, OUT and INOUT can all carry multiple IN one stored procedure.

2. Create stored procedure

2.1 syntax analysis

CREATE PROCEDURE Stored procedure name(IN | OUT | INOUT Parameter name parameter type,...)
	Stored procedure body

1. The meaning of the symbol in front of the parameter:

  • IN: the current parameter is an input parameter, that is, an input parameter. The stored procedure just reads the value of this parameter. If the parameter type is not defined, the default is IN, which indicates the input parameter
  • OUT: the current parameter is an output parameter, that is, it represents the output parameter. After execution, the client or application that calls the old city can read the value returned by this parameter.
  • INOUT: the current parameter can be either an input parameter or an output parameter

2. The formal parameter type can be any type in MySQL database

3. There can be multiple SQL statements in the stored procedure body. If there is only one SQL statement, beign and END can be omitted. Writing a stored procedure is not a simple thing. Complex SQL statements may be required in the stored procedure.

1, BEGIN...END:BEGIN...END The middle contains multiple statements, each with(;)The number is the terminator
2, DECLARE:DECLARE It is used to declare variables in BEGIN...END You need to use intermediate variables before other statements.
3,SET: Copy statement for assigning values to variables
4,SELECT...INTO: Store the query results from the data table into variables, that is, assign values to variables.

4. A new end tag needs to be set

DELIMITER New end tag

Because MySQL's default statement ending symbol is semicolon ';'. In order to avoid conflict with the SQL statement end symbol in the stored procedure, you need to use DELIMITER to change the end symbol of the stored procedure.

# Example 1: create stored procedure select_all_data() to view all the data in the emps table
CREATE PROCEDURE select_all_data()
	SELECT * FROM employees;

# Call of stored procedure
CALL select_all_data();

# Example 2: create stored procedure avg_employee_salary() to view the average salary of all employees
CREATE PROCEDURE avg_employee_salary()
	SELECT AVG(salary) FROM employees;
CALL avg_employee_salary();

# Example 3: create stored procedure show_max_salary() is used to view the maximum salary in the 'emps' table
CREATE PROCEDURE show_max_salary()
	SELECT MAX(salary) FROM employees;
CALL show_max_salary();

# Type 2: with OUT
# Example 4: create stored procedure show_min_salary() to view the minimum salary value in the 'emps' table. And output the minimum salary through the out parameter 'ms'
DESC employees;
	SELECT MIN(salary) INTO ms FROM employees;

CALL show_min_salary(@ms);
# View variable values

# Type 3: with IN
# Example 5: create stored procedure show_someone_salary(), view the salary of an employee IN the "employees" table, and count the employee name with the IN parameter empname
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(25))
	SELECT salary FROM employees WHERE last_name = empname;
END //
CALL show_someone_salary('Abel');

# Type 4: with IN and OUT
# Example 6: create stored procedure show_someone_salary2() view the salary of an employee IN the "employees" table, count the employee name with the IN parameter empname, and output the employee salary with the Out parameter empsalary
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(25), OUT empsalary DOUBLE)
	SELECT salary INTO empsalary FROM employees WHERE last_name = empname;
END //
CALL show_someone_salary2('Abel',@empsalary);
SELECT @empsalary;

# Type 5 with INOUT
# Example 7: create stored procedure show_mgr_name(), query the name of an employee leader, and use the INOUT parameter 'empname' to output the employee name and the name of the employee leader
DESC employees;
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
	SELECT last_name INTO empname FROM employees WHERE employee_id = (SELECT manager_id FROM employees WHERE last_name = empname);
END //
SET @empname = 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname;

Call stored procedure

3.1 calling format

Stored procedures have a variety of calling methods. The stored procedure must be called with CALL statement, and the stored procedure is related to the database. If you want to execute the stored procedure in other databases, you need to specify the database name, such as CALL dbname procname;

CALL Stored procedure name(Argument list)


1. Parameters for calling IN mode

CALL sp1('value')

2. Parameters for calling OUT mode

SET @name;
CALL sp1(@name)
SELECT @name;

3. Parameters for invoking inout mode

SET @name = value;
CALL sp1(@name);
SELECT @name;

4. Use of storage functions

4.1 syntax analysis

Learned functions: LENGTH, SUBSTR, CONCAT, etc

Syntax format:

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


  1. Parameter list: the specified parameter IN, OUT or INOUT is only legal for PROCEDURE, and FUNCTION always defaults to IN parameter
  2. The RETURNS type statement indicates the type of data returned by a FUNCTION. The RETURNS clause can only specify FUNCTION, which is mandatory for functions. It is used to specify the RETURN value type of the FUNCTION, and the FUNCTION body must contain a RETURN value statement.
  3. The function body can also use BEGIN... END to represent the beginning and END of SQL code. If there is only one statement in the function body, BEGIN... END can also be omitted.

4.2 calling storage function

SELECT Function name(Argument list)

4.3 code examples

be careful:

If the error "you might want to use the less safe log_bin_trust_function_creators variable" is reported in the creation of storage function, there are two processing methods:

  • Method 1: add the necessary function features "[NOT] DETERMINISTIC" and "{contains SQL | no SQL | reads SQL data | modifications SQL data}"

  • Mode 2:

    mysql>SET GLOBAL log_bin_trust_function_creators = 1;
# Storage function
# Example 1: create a storage function named email_by_name(), the parameter definition is null, the function queries Abel's email and returns the number type as string.
CREATE FUNCTION email_by_name()
	RETURN(SELECT email FROM employees WHERE last_name = 'Abel');
END //
SELECT email_by_name();

# Example 2: create a storage function named email_by_id(), parameter passed in emp_id, this function queries emp_id email and return it. The data type is character type
CREATE FUNCTION email_by_id(emp_id INT)
	RETURN(SELECT email FROM employees WHERE employee_id = emp_id);
END //

SELECT email_by_id(100);

# Example 3: create a storage function count_by_id(), parameter passed in dept_id, this function queries dept_id is the number of employees in the Department, and the data type is integer
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE FUNCTION count_by_id(dept_id INT)
	RETURN(SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
SELECT count_by_id(60);

4.3 comparison of stored functions and stored procedures

keywordCall syntaxReturn valueApplication scenario
stored procedurePROCEDURECALL stored procedure ()Understood as 0 or moreGenerally used for updating
Storage functionFUNCTIONSELECT function ()Only 1It 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 operations on tables (such as creating tables, deleting tables, etc.) and transaction operations, which are not available in stored functions.

5. View, modify and delete stored procedures and functions

5.1 viewing

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

SHOW CREATE {PROCEDURE | FUNCTION}Stored procedure name or function name

2. Use the SHOW STATUS statement to view stored procedures and status information


5.2 modification

Modifying a stored procedure or function does not affect the function of the stored procedure or function, but only modifies the relevant characteristics.

ALTER {PROCEDURE | FUNCTION} The name of the stored procedure or function [characteristic ...]

5.3 deletion

DROP {PROCEDURE | FUNCTION}[IF EXISTS] The name of the stored procedure or function

6. Disputes over stored procedures

6.1 advantages

  1. Stored procedures can be compiled and used more than once
  2. It can reduce the development workload
  3. The security of stored procedure is strong
  4. It can reduce the amount of network transmission
  5. Good encapsulation

6.2 disadvantages

  1. Portability check.
  2. Debugging is difficult.
  3. Versioning of calling procedures is difficult.
  4. He is not suitable for high concurrency scenarios.

Topics: MySQL SQL server