Stored procedures and stored functions

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

Using stored procedures can not only improve the access efficiency of database, but also improve the security of database use. For callers, stored procedures encapsulate SQL statements, and callers do not need to consider the specific implementation process of logical functions. It is simply called and can be called by programming languages such as Java and C#.

1. Overview of stored procedures

1.1 understand

The MySQL statements we have studied are all single SQL statements for a table or several tables, but in the actual operation of the database, there will often be multiple SQL statements processing multiple tables to complete the operation. For example, in order to confirm whether a student can graduate, it is necessary to query the student file table, score table and comprehensive table at the same time. At this time, it is necessary to use multiple SQL statements to complete processing requirements for these data tables. A stored procedure is a collection of SQL statements designed to accomplish a specific function. The purpose of using stored procedures is to write common or complex tasks in SQL statements in advance and store them with a specified name. After compilation and optimization, the procedures are stored in the database server, so they are called stored procedures. When you later need the database to provide the same service as the defined stored procedure, you can do this automatically by simply calling the CALL stored procedure name. SQL statements used to operate databases need to be compiled and then executed. Stored procedures take a different approach to executing SQL statements.

Definition: The English word for Stored Procedure is Stored Procedure. The idea is simple: a package of pre-compiled SQL statements.

Execution process: Stored procedures are stored on the MySQL server in advance. When the client needs to execute the stored procedures, it only needs to issue commands to the server to invoke the stored procedures. The server can execute all the SQL statements stored in advance.

Benefits:

  • Simplify operations, improve the reuse of SQL statements and reduce the pressure on developers
  • Reduce errors in operation and improve efficiency
  • Reduce network traffic (the client does not need to send all SQL statements to the server over the network)
  • This reduces the risk of SQL statements being exposed to the Internet and improves the security of data queries

Comparison with views and functions:

It has the same advantages as view: clarity, security, and reduced network traffic. Unlike a view, which is a virtual table and usually does not operate directly on the underlying table, stored procedures are programmatic SQL that can operate directly on the underlying table and perform more complex data processing than collections oriented operations.

Once a stored procedure is created, it is as simple to use as a function, simply by calling the stored procedure name. In contrast to functions, stored procedures do not return values.

1.2 classification

The parameter types of stored procedures can be IN, OUT, and INOUT. Based on this, the classification is as follows:

  • No arguments (no arguments, no return)
  • Only IN type (no return with arguments)
  • Only type OUT (returns with no arguments)
  • Both IN and OUT (with arguments and returns)
  • With INOUT (returns with arguments)

Note: IN, OUT, and INOUT can all take more than one IN a stored procedure.

2. Create a stored procedure

2.1 Grammar Analysis

Grammar:

CREATE PROCEDUREStored procedure name (IN|OUT| INOUT parameters of types,...). [characteristics ...]BEGINStored procedure bodyEND
Copy the code

Similar to the Java method:

Return type method name (parameter type parameter name,...) {method body; }Copy the code

Description:

1. The meaning of the symbol before the parameter

  • IN: The current parameter is the input parameter, that is, represents the input parameter;

    The stored procedure simply reads the value of this parameter. If no parameter type is defined, the default is IN, which represents the input parameter.

  • OUT: The current parameter is the output parameter, that is, the output parameter.

    After execution, the client or application calling the stored procedure can read the value returned by this parameter.

  • INOUT: The current parameter can be an input parameter or an output parameter.

The parameter type can be any type in the MySQL database.

Characteristics Indicates the constraints on the stored procedure specified during the creation of the stored procedure. The values are as follows:

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Copy the code

  • LANGUAGE SQL: Indicates that the execution body of a stored procedure consists of SQL statements. The system supports SQL.

  • [NOT] DETERMINISTIC: Indicates whether a stored procedure execution is DETERMINISTIC. DETERMINISTIC means the result is DETERMINISTIC. The same input produces the same output each time the stored procedure is executed. NOT DETERMINISTIC means that the result is indeterminate, the same input may get different outputs. If no value is specified, the default is NOT DETERMINISTIC.

  • {the CONTAINS SQL SQL | | NO READS SQL DATA | MODIFIES SQL DATA} : indicate the subroutines using SQL statements.

    • CONTAINS SQL indicates that the subroutine of the current stored procedure CONTAINS SQL statements, but does not contain SQL statements that read or write data.
    • NO SQL indicates that the subroutine of the current stored procedure does not contain any SQL statements.
    • READS SQL DATA represents the SQL statement that contains read DATA in the subroutine of the current stored procedure;
    • MODIFIES SQL DATA indicates that the subroutine of the current stored procedure contains SQL statements to write DATA.
    • By default, the system specifies CONTAINS SQL.

  • SQL SECURITY {DEFINER | INVOKER} : execute the stored procedure, which indicates which users will be able to execute the stored procedure.

    • DEFINERIndicates that only the creator or definer of the current stored procedure can execute the current stored procedure.
    • INVOKERIndicates that users who have access to the current stored procedure can execute the current stored procedure.
    • If no associated values are set, MySQL specifies DEFINER by default.

  • COMMENT ‘string’ : COMMENT information that can be used to describe stored procedures.

4. The stored procedure body can contain multiple SQL statements. If there is only one SQL statement, omit BEGIN and END

Writing a stored procedure is not simple, and it may require complex SQL statements.

1. BEGIN...END:BEGIN...ENDIt contains multiple statements, each beginning with (;). Sign is the end character. 2.DECLARE:DECLAREIt is used to declare variables in the location whereBEGIN...ENDThe variable is declared in the middle of a statement and needs to be declared before any other statement is used.3. SET: Assignment statement, used to assign a value to a variable.4. SELECT...INTO: Stores the result of a query from a table into a variable, i.e. assigning a value to a variable.Copy the code

5. You need to set a new end tag

DELIMITER New end tagCopy the code

MySQL’s default statement terminator is semicolon ‘; ‘. To avoid conflicts with SQL statement terminators in stored procedures, DELIMITER is used to change the stored procedure terminators.

For example, the “DELIMITER //” statement sets the MySQL terminator to // and ends the stored procedure with “END //”. Use “DELIMITER;” after the stored procedure is defined. Restores the default end character. DELIMITER can also specify other symbols as terminators.

When using the DELIMITER command, you should avoid the backslash (‘ \ ‘) character because the backslash is the MySQL escape character.

Example:

DELIMITER $

CREATE PROCEDUREStored procedure name (IN|OUT| INOUT parameters of types,...). [characteristics ...]BEGIN
	sqlstatements1; SQL statements 2;END $
Copy the code

2.2 Code Examples

Example 1: Create select_all_data() and check all data in the EMps table

DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
	
END $

DELIMITER ;
Copy the code

Example 2: Create the stored procedure avg_employee_salary() that returns the average salary of all employees

DELIMITER //

CREATE PROCEDURE avg_employee_salary ()
BEGIN
	SELECT AVG(salary) AS avg_salary FROM emps;
END //

DELIMITER ;
Copy the code

Example 3: Create the stored procedure show_max_salary() to view the highest salary value in the emps table.

CREATE PROCEDURE show_max_salary()
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT 'View highest salary'
	BEGIN
		SELECT MAX(salary) FROM emps;
	END //

DELIMITER ;
Copy the code

Example 4: Create the stored procedure show_min_salary() and view the lowest salary in the EMps table. And output the minimum salary with the OUT parameter “ms”

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
	BEGIN
		SELECT MIN(salary) INTO ms FROM emps;
	END //

DELIMITER ;
Copy the code

Example 5: Create the stored procedure show_someone_salary(), check the salary of an employee IN the “emps” table, and enter the employee name with the IN parameter empname.

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
	BEGIN
		SELECT salary FROM emps WHERE ename = empname;
	END //

DELIMITER ;
Copy the code

Example 6: Create the stored procedure show_someone_salary2() and view the salary of an employee IN the “emps” table. Input 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(20),OUT empsalary DOUBLE)
	BEGIN
		SELECT salary INTO empsalary FROM emps WHERE ename = empname;
	END //

DELIMITER ;
Copy the code

Example 7: Create the stored procedure show_mgr_name() to query the name of an employee’s leader. Use the INOUT parameter empname to enter the employee’s name and output the leader’s name.

DELIMITER //

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
	BEGIN
		SELECT ename INTO empname FROM emps
		WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
	END //

DELIMITER ;
Copy the code

3. Invoke the stored procedure

3.1 Call Format

Stored procedures have multiple invocation methods. Stored procedures must be called using the CALL statement, and the stored procedure is related to the database. If you want to execute stored procedures in other databases, you need to specify the database name, for example, CALL dbname.procName.

CALLStored procedure name (argument list)Copy the code

Format:

1. Call in mode parameters:

CALL sp1('value');
Copy the code

2, Call out mode parameters:

SET @name;
CALL sp1(@name);
SELECT @name;
Copy the code

3, Call inout mode parameters:

SET @name= value;CALL sp1(@name);
SELECT @name;
Copy the code

3.2 Code Examples

Example 1:

DELIMITER //

CREATE PROCEDURE CountProc(IN sid INT.OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM fruits 
	WHERE s_id = sid;
END //

DELIMITER ;
Copy the code

Calling a stored procedure:

mysql> CALL CountProc (101The @num);
Query OK, 1 row affected (0.00 sec)
Copy the code

View the result:

mysql> SELECT @num;
Copy the code

The stored procedure returns the type of fruit provided by the specified fruit vendor with s_id=101. The return value is stored in the num variable.

Example 2: Create a stored procedure to perform accumulation and calculate 1+2+… What is plus n. The specific code is as follows:

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;
Copy the code

If you use Navicat, Navicat automatically sets DELIMITER as another symbol when you write the stored procedure, so we don’t need to DELIMITER anymore.

CALL add_num(50) directly; Can. Here I pass the parameter 50, which is statistics 1+2+… Plus 50.

3.3 Debugging

In MySQL, stored procedures do not have a dedicated integrated development environment like common programming languages such as VC++, Java, etc. Therefore, you can debug the correctness of an SQL statement by querying the intermediate results of program execution with the SELECT statement. After successful debugging, the SELECT statement is moved back to the next SQL statement, and then the next SQL statement is debugged. By doing this step by step, you can debug all the operations in the stored procedure. Of course, you can also copy SQL statements from stored procedures and debug them separately.

4. Use of storage functions

Before learning a lot of functions, these functions can be used for all kinds of data processing operations, greatly improve the user’s management efficiency of the database. MySQL supports custom functions. After they are defined, they can be called in the same way as the system functions predefined by MySQL.

4.1 Grammar Analysis

Functions learned: LENGTH, SUBSTR, CONCAT, etc

Syntax format:

CREATE FUNCTIONFunction name (parameter name Parameter type...)RETURNSReturn type [characteristics...]BEGINThe body of the functionThere must be a RETURN statement in the function body

END
Copy the code

Description:

Parameter list: Specifying arguments as IN, OUT, or INOUT is only legal for PROCEDURE. FUNCTION always defaults to IN arguments.

RETURNS type statement specifies the type of data returned by the function.

The RETURNS clause can only specify functions, which is mandatory for functions. It specifies the RETURN type of the function, and the function body must contain a RETURN value statement.

3, the constraints on the function specified when the function is created. The value is the same as when the stored procedure is created.

4, The function body can also use BEGIN… END represents the beginning and END of the SQL code. If the function body has only one statement, you can also omit BEGIN… The END.

4.2 Calling the Storage Function

In MySQL, storage functions are used in the same way as internal MySQL functions. In other words, user-defined storage functions are identical to MySQL internal functions. The difference is that the storage functions are user-defined, while the internal functions are defined by the MySQL developers.

SELECTFunction name (argument list)Copy the code

4.3 Code Examples

Example 1:

Create a storage function named email_by_name() with an empty parameter definition. This function queries the email address of an Abel and returns a string of data.

DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //

DELIMITER ;
Copy the code

Call:

SELECT email_by_name();
Copy the code

Example 2:

Create a storage function named email_by_id(), passing emp_id, that queries the email address of emp_id and returns a string of data.

DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
	RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //

DELIMITER ;
Copy the code

Call:

SET @emp_id = 102;
SELECT email_by_id(102);
Copy the code

Example 3:

Create a storage function count_by_id(), passing dept_id. This function queries the number of employees in dept_id and returns an integer.

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT 'Enquiry department Average Salary'
BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
	
END //

DELIMITER ;
Copy the code

Call:

SET @dept_id = 50;
SELECT count_by_id(@dept_id);
Copy the code

Note:

If there is an error message “You might want to use the less safe log_bin_trust_function_creators variable” in the creators of the storage function, there are two troubleshooting methods:

  • Way 1: add the necessary function features “[NOT] DETERMINISTIC” and “{the CONTAINS SQL SQL | | NO READS SQL DATA | MODIFIES SQL DATA}”

  • Method 2:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Copy the code

4.4 Comparing stored Functions and stored procedures

The keyword Call syntax The return value Application scenarios
The stored procedure PROCEDURE CALL stored procedure () It means there are zero or more Generally used to update
Storage function FUNCTION The SELECT function () It can only be one Generally used when the query result is a value and returned

In addition, stored functions can be used in query statements, but stored procedures cannot. Stored procedures, on the other hand, are more powerful, including the ability to perform operations on tables (such as creating tables, dropping tables, etc.) and transactions that storage functions do not have.

5. View, modify, and delete stored procedures and functions

5.1 check the

Once created, how do we know if the stored procedure or function we created was successful?

MySQL stores the STATUS information of stored procedures and functions. Users can use the SHOW STATUS statement or SHOW CREATE statement to view the STATUS information, or directly query the STATUS information from the information_SCHEMA database of the system. Here are three methods.

1. Run the SHOW CREATE statement to view the creation information about stored procedures and functions

The basic syntax is as follows:

SHOW CREATE {PROCEDURE | FUNCTION} Name of the stored procedure or functionCopy the code

For example:

SHOW CREATE FUNCTION test_db.CountProc \G
Copy the code

2. Run the SHOW STATUS statement to view the STATUS of stored procedures and functions

The basic syntax is as follows:

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

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 a stored procedure or function and can be omitted. When omitted, all stored procedures or functions that exist in the MySQL database are listed. For example, the code for the SHOW STATUS statement is as follows:

mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%' \G 
*************************** 1. row ***************************
                  Db: test_db
                Name: SelectAllData
                Type: PROCEDURE
             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
1 row in set (0.00 sec)
Copy the code

3. View information about stored procedures and functions from the information_schema.Routines table

Information about stored procedures and functions in MySQL is stored in the Routines table under the INFORMATION_SCHEMA database. You can query information about stored procedures and functions by querying the records in this table. The basic syntax is as follows:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='Name of stored procedure or function' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
Copy the code

Note: If a stored procedure has the same name as a function in the MySQL database, it is best to specify a ROUTINE_TYPE query condition to specify whether the procedure or function is being queried.

For example, query from the Routines table for a storage function named CountProc as follows:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id'　ANDROUTINE_TYPE ='FUNCTION' \G
Copy the code

5.2 to modify

Modifying a stored procedure or function does not affect the functions of the stored procedure or function, but only changes related features. Use the ALTER statement.

ALTER {PROCEDURE | FUNCTION} Names of stored procedures or functions [characteristic...]Copy the code

Characteristic specifies the characteristics of a stored procedure or function. Its values are slightly different from those when the stored procedure or function is created.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Copy the code
  • CONTAINS SQL, indicating that the subroutine contains SQL statements, but not statements that read or write data.
  • NO SQL, indicating that the subroutine does not contain SQL statements.
  • READS SQL DATARepresents a statement in a subroutine that contains read data.
  • MODIFIES SQL DATA, indicating that the subroutine contains statements that write data.
  • SQL SECURITY { DEFINER | INVOKER }, indicating who has the authority to execute.
    • DEFINER, indicating that only the definer himself can execute.
    • INVOKER, indicating that the caller can execute.
  • COMMENT 'string'Is a comment information.

The ALTER PROCEDURE statement is used to modify stored procedures and the ALTER FUNCTION statement is used to modify stored functions. However, the structure of the two statements is the same, and all the arguments in the statement are the same.

Example 1:

Modify the definition of the stored procedure CountProc. Change the read and write permissions to MODIFIES SQL DATA and indicate that the caller can execute as follows:

ALTER　PROCEDURECountProc MODIFIESSQL DATA
SQL SECURITY INVOKER ;
Copy the code

Query the modified information:

SELECT specific_name,sql_data_access,security_type
FROM information_schema.`ROUTINES`
WHERE routine_name = 'CountProc' AND routine_type = 'PROCEDURE';
Copy the code

The result shows that the stored procedure is successfully modified. As you can see from the results of the query, the permission to ACCESS DATA (SQL_DATA_ ACCESS) has changed to MODIFIES SQL DATA and the security type (SECURITY_TYPE) has changed to INVOKER.

Example 2:

Modify the definition of the storage function CountProc. Change read/write permissions to READS SQL DATA and comment information “FIND NAME” as follows:

ALTER　FUNCTIONCountProcREADS SQL DATA
COMMENT 'FIND NAME' ;
Copy the code

The storage function is modified successfully. As you can see from the results of the query, the DATA access permission (SQL_DATA_ACCESS) has been changed to READS SQL DATA, and the function comment (ROUTINE_COMMENT) has been changed to FIND NAME.

5.3 delete

To DROP a stored procedure or function, use the DROP statement.

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

IF EXISTS: IF a program or function is not stored, it prevents an error from occurring, generating a warning that is viewed with SHOW WARNINGS.

For example:

DROP PROCEDURE CountProc;
Copy the code
DROP FUNCTION CountProc;
Copy the code

6. Controversy over the use of stored procedures

Despite the advantages of stored procedures, there has always been a lot of controversy over the use of stored procedures. For example, some companies require stored procedures for large projects, while others explicitly prohibit the use of stored procedures in their manuals. Why do these companies have such different requirements for the use of stored procedures?

6.1 the advantages

Stored procedures can be compiled and used more than once. Stored procedures are compiled only at creation time and do not need to be recompiled for subsequent use, which improves the efficiency of SQL execution.

2, can reduce the development workload. The encapsulation of code into modules is actually one of the core ideas of programming, so that complex problems can be broken down into different modules, which can then be reused, reducing the development workload and ensuring the structure of the code is clear.

3. Strong security of stored procedures. When we set up the stored procedure, we can set the user permission, which is as strong as the view security.

4, can reduce the amount of network transmission. Because the code is encapsulated in a stored procedure, you only need to call the stored procedure each time you use it, which reduces network traffic.

5, good encapsulation. When performing relatively complex database operations, what used to be a one-by-one SQL statement that might require multiple database connections is now a stored procedure that requires only one connection.

6.2 disadvantages

Based on these advantages, many large companies, such as Microsoft and IBM, require the use of stored procedures for large projects. But ali in China does not recommend developers to use stored procedures, why?

Ali development specification

[Mandatory] Do not use stored procedures. Stored procedures are difficult to debug, expand, and transplant.

Although stored procedures have benefits such as the above, the disadvantages are also obvious.

1. Poor portability. Stored procedures cannot be ported across databases. For example, stored procedures written in MySQL, Oracle, or SQL Server need to be rewritten when they are moved to another database.

2. Debugging is difficult. Only a few DBMSS support debugging of stored procedures. Complex stored procedures are not easy to develop or maintain. There are third-party tools that can debug stored procedures for a fee, though.

3. Versioning stored procedures is difficult. For example, when the index of a data table changes, stored procedures may fail. When we develop software, we often need to do version management, but the stored procedure itself does not have version control, and the iteration of version update is troublesome.

4. It is not suitable for high concurrency scenarios. High concurrency scenarios need to reduce the pressure on the database. Sometimes the database will adopt a separate database and separate tables approach, and the scalability requirements are high. In this case, the stored procedures will become difficult to maintain, increasing the pressure on the database, obviously not suitable.

Summary:

Stored procedures are both convenient and limited. Although attitudes to stored procedures vary from company to company, for us developers, mastering stored procedures is a must-have skill, no matter what.