[MySQL] Stored procedures and stored functions

[MySQL] Stored procedures and stored functions

1. Overview of stored procedures and functions

Meaning: The English name for stored procedure is Stored Procedure. Its idea is very simple, it is a set of pre-compiled SQL statements
of packaging.
Execution process: The stored procedure is stored on the MySQL server in advance. When it needs to be executed, the client only needs to make a call to the server.
Stored procedure commands, the server can execute all the pre-stored series of SQL statements.

2. Create stored procedures

2.1 Syntax Analysis

The syntax format for creating a stored procedure is as follows:

CREATE PROCEDURE stored procedure name (IN|OUT|INOUT parameter name parameter type,...)
[characteristics...]
BEGIN
stored procedure body
END

illustrate:

1. What parameter modifiers represent:

   • IN: Indicates that the current parameter is an input parameter. The stored procedure can read this parameter. When no parameter modifier is specified, the default is IN.
   • OUT: Indicates that the current parameter is an output parameter. When the stored procedure is executed, the result will be stored in the output parameter.
   • INOUT: Indicates that the current parameter can be either an input parameter or an output parameter.

2. [characteristics] indicates the constraints on the stored procedure when creating the stored procedure

   Its value information is as follows:

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

   • LANGUAGE SQL: Indicates that the current stored procedure uses SQL statements, and the current system supports SQL.
   • [NOT] DETERMINISTIC: Indicates whether the execution result of the current stored procedure is certain, that is, whether the same input will get the same output. If it is the same, it indicates that it is but definitely DETERMINISTICThe opposite is NOT DETERMINISTIC
   • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: used to specify restrictions on the use of SQL by subprograms
     • CONTAINS SQL: Indicates that the subroutine contains SQL statements, but does not contain SQL statements for reading and writing data. It is the system’s default mode
     • NO SQL: Indicates that the subroutine does not contain SQL statements
     • READS SQL DATA: Represents the subroutine containing SQL statements for reading data
     • MODIFIES SQL DATA: Represents the subroutine containing SQL statements that write data
   • SQL SECURITY: used to specify the permissions of the current stored procedure
     • DEFINER: Indicates that only the creator or definer of the current stored procedure can execute the current stored procedure.
     • INVOKER: Indicates that only those with current access rights to the stored procedure can execute the current stored procedure.

3. Usually when defining a stored procedure, we need to set a new end mark by using the DELIMITER symbol to prevent the SQL statement from mistakenly ending the statement early due to a semicolon, for example:

   DELIMITER $
   
   CREATE PROCEDURE stored procedure name (IN|OUT|INOUT parameter name parameter type,...)
   [characteristics...]
   BEGIN
   sql statement 1;
   sql statement 2;
   END $
   
   DELIMITER;
   

2.2 Code examples

Example 1: Create the stored procedure select_all_data() to view all data in the emps table

DELIMITER $

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

DELIMITER;
CALL select_all_data(); # Call stored procedure

Example 2: Create a stored procedure avg_employee_salary() to return the average salary of all employees

DELIMITER $

CREATE PROCEDURE avg_employee_salary()
BEGIN
SELECT AVG(salary) FROM emps;
END $

DELIMITER;

CALL avg_employee_salary(); # Call stored procedure

Example 3: Create a stored procedure show_max_salary() to view the maximum salary value of the “emps” table.

DELIMITER $

CREATE PROCEDURE show_max_salary()
BEGIN
SELECT MAX(salary) FROM emps;
END $

DELIMITER;
CALL show_max_salary(); # Call stored procedure

Example 4: Create the stored procedure show_min_salary() to view the minimum salary value of the “emps” table. And pass the minimum salary through the OUT parameter “ms”
output

DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE(8,2))
BEGIN
SELECT MIN(salary) INTO ms
FROM emps;
END $
DELIMITER;

CALL show_min_salary(@ms);
SELECT @ms;

Note: The type of the ms output parameter here needs to comply with the type constraints in the original table when defining it.

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

DELIMITER $

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(25))
BEGIN
SELECT salary FROM employees
WHERE last_name = empname;
END $

DELIMITER;

SET @empname = 'Abel'; # Variable assignment
CALL show_someone_salary(@empname); # Call stored procedure

Note: The SET @empname = 'Abel' syntax is used here to assign values to variables.

Example 6: Create the stored procedure show_someone_salary2(), check the salary of an employee in the “emps” table, and use the IN parameter empname
Enter the employee’s name and use the OUT parameter empsalary to output the employee’s salary.

DELIMITER $

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(25),OUT empsalary DOUBLE(8,2))
BEGIN
SELECT salary INTO employee
FROM emps WHERE last_name = empname;
END $

DELIMITER;

SET @empname := 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;

Example 7: Create a stored procedure show_mgr_name(), query the name of an employee leader, and use the INOUT parameter “empname” to enter the name of the employee
Worker name, output the name of the leader

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name INTO empname FROM emps
WHERE employee_id = (
SELECT manager_id FROM emps
WHERE last_name = empname);
END $

DELIMITER;

SET @empname = 'Kochhar';
CALL show_mgr_name(@empname);
SELECT @empname;

3. Call stored procedures

There are many formats for calling stored procedures, but they must be called through the keyword CALL

CALL stored procedure name (actual parameter list)

Format:

1. Call parameters of IN mode

   CALL sp1('value')
   

2. Parameters for calling OUT mode

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

3. Call parameters of INOUT mode

   SET @name = 'value';
   CALL sp1(@name);
   select @name;
   

4. Use of storage functions

4.1 Syntax Analysis

Syntax format:

CREATE FUNCTION stores function name (formal parameter name, formal parameter type,...)
RETURNS return value type
[characteristics...]
BEGIN
Function body # There must be a return statement in the function body
END

illustrate:

1. Parameter list, IN, OUT, INOUT are only valid for stored procedures. For stored functions, the parameter modifier defaults to IN.
2. The RETURNS statement indicates the type of data returned by the function. The return value is mandatory for the function FUNCTION, and there must be a RETURN clause in the function body.
3. characteristics represents the constraints of the stored function, which are consistent with those described in the stored procedure, and will not be repeated here.
4. The function body can also use begin and end to indicate the beginning and end of the SQL statement. If there is only one line, it can be omitted.

4.2 Call format

In MySQL, the use of stored functions and system functions is the same, and there is essentially no difference. It’s just that system functions are defined by MySQL developers, while stored functions are defined by users

Call syntax format:

select function name (actual parameter list);

4.3 Code examples

Example 1: Create a storage function with the name email_by_name() and parameter definition as empty. This function queries Abel’s email and returns it. The data type is
string type

DELIMITER $

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC

BEGIN
  RETURN (SELECT email FROM emps
  WHERE last_name = 'Abel');
END $

DELIMITER;

SELECT email_by_name();

Note: If you do not add constraints [characteristics], an error may be reported during operation. In this case, just add constraints.

Example 2: Create a storage function with the name email_by_id(). The parameters are passed in emp_id. This function queries the email of emp_id and returns the data type.
Is a string type.

DROP FUNCTION email_by_id;
DELIMITER $

CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
BEGIN
RETURN (SELECT email FROM emps
WHERE employee_id = emp_id);
END $

DELIMITER;

SELECT email_by_id(100);

Example 3: Create the storage function count_by_id(), and pass in dept_id as the parameter. This function queries the number of employees in the dept_id department and returns the data type.
is an integer

DELIMITER $

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT COUNT(*) FROM emps
WHERE department_id = dept_id);
END $

DELIMITER;

SELECT count_by_id(80);

5. View, modify and delete stored procedures and functions

5.1 View

We have created stored procedures and stored functions, so can they be observed? Here are three methods

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

   The basic syntax structure is as follows:

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

   Example:

   SHOW CREATE FUNCTION count_by_id;

   

2. SHOW STATUS statement to view status information of stored procedures and functions

   The basic syntax structure is as follows:

   SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]

   Example:

   SHOW PROCEDURE STATUC LIKE 'select_all_data'

   

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

   The information about stored procedures and stored functions in MySQL will be saved in the system database table. We can view it through information_schema.Routines

   The basic syntax format is as follows:

   SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'Stored procedure or function name' AND ROUTINE_TYPE = {'PROCEDURE' | 'FUNCTION'}

   Example:

   SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'avg_employee_salary' AND ROUTINE_TYPE = 'PROCEDURE';

   

5.2 Modify stored procedures and stored functions

The scope of the modification here is very small. It cannot modify the contents of the internal stored procedure or stored function body. It can only modify the constraints when establishing the stored procedure and stored function, that is, the previously mentioned [characteristics]

Syntax format:

ALTER FUNCTION count_by_id CONTAINS SQL COMMENT 'Query the number of department employees';

Query again:

5.3 Delete stored procedures and stored functions

Syntax format:

DROP {PROCEDURE | FUNCTION} IF EXISTS stored procedure or stored function name

Example:

DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS count_by_id;

6. Controversy about stored procedures

Advantages of stored procedures:

1. Reflects better encapsulation
2. Stored procedures can be compiled once and run multiple times, improving code reuse rate.
3. The stored procedure encapsulates the code and only needs to be called when using it. Reduced network traffic
4. Stored procedures can set user permissions to improve security

Disadvantages of stored procedures:

1. Poor portability and large syntax differences between different DBMSs
2. Debugging is difficult
3. Not suitable for high-concurrency scenarios of database and table partitioning

(img-75V3miCN-1698831122611)]

5.3 Delete stored procedures and stored functions

Syntax format:

DROP {PROCEDURE | FUNCTION} IF EXISTS stored procedure or stored function name

Example:

DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS count_by_id;

6. Controversy about stored procedures

Advantages of stored procedures:

1. Reflects better encapsulation
2. Stored procedures can be compiled and run multiple times to improve code reuse.
3. The stored procedure encapsulates the code and only needs to be called when using it. Reduced network traffic
4. Stored procedures can set user permissions to improve security

Disadvantages of stored procedures:

1. Poor portability and large syntax differences between different DBMSs
2. Debugging is difficult
3. Not suitable for high-concurrency scenarios of database and table partitioning