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.

benefit

It simplifies the statements of programmers and improves the reusability of sql.
Reduce mistakes in operation and improve efficiency
Reduce the amount of network transmission (the client does not need to send all SQL statements to the server through the network)
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:

No parameters (no return)
Only with IN type (with parameters and no return)
Only with OUT type (no parameter and return)
Both IN and OUT (with parameters and return)
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,...)
[characteristics]
BEGIN
	Stored procedure body
	
END

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
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $
DELIMITER ;

# Call of stored procedure
CALL select_all_data();

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

# Example 3: create stored procedure show_max_salary() is used to view the maximum salary in the 'emps' table
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN
	SELECT MAX(salary) FROM employees;
END $
DELIMITER ;
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;
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms FROM employees;
END $
DELIMITER ;

CALL show_min_salary(@ms);
# View variable values
SELECT @ms;

# 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
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(25))
BEGIN
	SELECT salary FROM employees WHERE last_name = empname;
END //
DELIMITER ;
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
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(25), OUT empsalary DOUBLE)
BEGIN
	SELECT salary INTO empsalary FROM employees WHERE last_name = empname;
END //
DELIMITER ;
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;
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
	SELECT last_name INTO empname FROM employees WHERE employee_id = (SELECT manager_id FROM employees WHERE last_name = empname);
	
END //
DELIMITER ;
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)

format

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 ....]
BEGIN
	Function body #There must be a RETURN statement in the function body
END

explain:

Parameter list: the specified parameter IN, OUT or INOUT is only legal for PROCEDURE, and FUNCTION always defaults to IN parameter
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.
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.
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
	RETURN(SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
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
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
	RETURN(SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;

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;
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
	RETURN(SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
SELECT count_by_id(60);

4.3 comparison of 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 updating
Storage function	FUNCTION	SELECT function ()	Only 1	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 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

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

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

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

6.2 disadvantages

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

Topics: MySQL SQL server

Programmer Think