Chapter 15 stored procedures and functions

Chapter 15 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.

Overview of stored procedures

understand

meaning:

Stored Procedure is Stored Procedure in English. Its idea is very simple, which is the encapsulation of a set of precompiled SQL statements.
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.

Benefits:

1. Simplify the operation, improve the reusability of sql statements and reduce the pressure of developers

2. Reduce errors 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 exposing SQL statements to the Internet and improves the security of data query

Comparison with views and functions:

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

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.

Function has only one return value.

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 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 (with parameters and return)

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

Create stored procedure

Grammar analysis

Syntax:

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

Similar to methods in Java:

Modifier return type method name(Parameter type parameter name,...){
     Method body;
}

explain:

1. 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 parameter;
    After execution, the client or application that calls the stored procedure 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.characteristics refers to the constraints on the stored procedure specified when creating the stored procedure, and 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 execution body of the stored procedure is composed of SQL statements. The language supported by the current system is SQL.

  • [NOT] DETERMINISTIC: indicates whether the execution result of the stored procedure is determined. DETERMINISTIC indicates that the result is certain. Each time the stored procedure is executed, the same input will get the same output. NOT DETERMINISTIC means that the result is uncertain, and the same input may get different outputs. If no value is specified, the default value is NOT DETERMINISTIC.

  • {contains SQL | no SQL | reads SQL data | modifications SQL data}: indicates the restrictions on the use of SQL statements by subroutines.

    • CONTAINS SQL means that the subroutine of the current stored procedure CONTAINS SQL statements, but does not contain SQL statements for reading and writing data;
    • NO SQL means that the subprogram of the current stored procedure does not contain any SQL statements;
    • Read SQL data indicates that the subroutine of the current stored procedure contains SQL statements that read data;
    • Modifications SQL data indicates that the subroutine of the current stored procedure contains SQL statements to write data.
    • By default, it is specified as CONTAINS SQL.

  • SQL security {determiner | invoker}: the permission to execute the current stored procedure, that is, it indicates which users can execute the current stored procedure.

    • Determiner means that only the creator or DEFINER of the current stored procedure can execute the current stored procedure;
    • INVOKER means that the user who has access to the current stored procedure can execute the current stored procedure.
    • If the relevant value is not set, MySQL specifies the default value as determiner.

  • COMMENT 'string': comment information, which can be used to describe stored procedures.

4. There can be multiple SQL statements in the stored procedure body. If there is only one SQL statement, BEGIN and END can be omitted.

Writing stored procedures is not a simple thing. Complex SQL statements may be required in stored procedures.

  1. BEGIN... END: BEGIN... END contains multiple statements, each with (;) The number is the terminator.
  2. DECLARE: DECLARE is used to DECLARE variables. It is used in the middle of BEGIN... END statements, and variables need to be declared before other statements are used.
  3. SET: assignment statement, used to assign values to variables.
  4. Assign a value to the variable INTO the query table, that is, assign a value to the data stored in the table.

5. 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 terminator in the stored procedure, you need to use DELIMITER to change the terminator of the stored procedure.

For example, "DELIMITER / /" statement is used to set the END character of MySQL to / / and END the stored procedure with "END / /". Use "DELIMITER" after the stored procedure is defined Restore the default terminator. DELIMITER can also specify other symbols as terminators.

Use / / and $as temporary end tags. Don't forget to modify them at last.

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

Example:

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

Code example

Example 1: create 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 ;

Example 2: create stored procedure avg_employee_salary(), return the average salary of all employees

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

Example 3: create stored procedure show_max_salary() is used to view the maximum salary value in the "emps" table.

DELIMITER //

CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'View maximum salary'

BEGIN
   SELECT MAX(salary) FROM emps;  
END //

DELIMITER ;

With OUT type When defining the output value, the data type should be consistent with the original corresponding data type, or the range should be larger, so as to ensure that no error will be reported.

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"

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

Example 5: create stored procedure show_someone_salary(), view the salary of an employee IN the "emps" table, and enter the employee's 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 ;

Example 6: create stored procedure show_someone_salary2(), view the salary of an employee IN the "emps" table, enter the employee's name with the IN parameter empname, and output the employee's 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 ;

Example 7: create stored procedure show_mgr_name(), query the name of an employee's leader, enter the employee's name with the INOUT parameter "empname", 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 ;

Call stored procedure

Call 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 calling inout mode:

SET @name = value;	 #: = is an assignment symbol in SQL, so SET @name: = value can also be written out;
CALL sp1(@name); 
SELECT @name;

Code example

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 ;

Call stored procedure:

CALL CountProc (101, @num);

View the returned results:

SELECT @num;

The stored procedure returned the specified s_ For the fruit type provided by the fruit vendor with id = 101, the return value is stored in the num variable and checked with SELECT. The return result is 3.

Example 2: create a stored procedure, realize the accumulation operation, and calculate how much 1 + 2 +... + n is equal to. The specific codes are as follows:

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT) 
BEGIN
		 #Declare variable
         DECLARE i INT;
         DECLARE sum INT;

         #assignment
         SET i = 1;
         SET sum = 0;

         WHILE i <= n DO
             SET sum = sum + i;    
             SET i = i +1;
         END WHILE;

         #see
         SELECT sum;
END //
DELIMITER ;

If you use Navicat tool, Navicat will automatically set DELIMITER to other symbols when writing stored procedures. We don't need to do the operation of DELIMITER.

Directly use CALL add_num(50); Just. The parameter passed in here is 50, which is the sum of the accumulation of 1 + 2 +... + 50.

practice:

#Create stored procedure
#preparation
CREATE DATABASE dbtest15;

USE dbtest15;

#The following copy operations will only retain non empty constraints, and other constraints will not be retained
CREATE TABLE employees
AS
SELECT *
FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT *
FROM atguigudb.departments;

SELECT *
FROM employees;

SELECT *
FROM departments;

#Create stored procedure
#Type 1: no parameter, no return value
#Example 1: create stored procedure select_all_data() to view all data in the emps table
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	 SELECT * FROM employees;
END $
DELIMITER ;

#SELECT is used for function calls and CALL is used for stored procedure calls	

#Call of stored procedure
CALL select_all_data();

#Example 2: create stored procedure avg_employee_salary(), return the average salary of all employees
DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
   	 SELECT AVG(salary) AS avg_salary FROM emps; 
END //
DELIMITER ;

#Call of stored procedure
CALL avg_employee_salary();

#Example 3: create stored procedure show_max_salary() is used to view the maximum salary value in the "emps" table.
DELIMITER //
CREATE PROCEDURE show_max_salary ()
BEGIN
   	 SELECT MAX(salary) AS avg_salary FROM emps; 
END //
DELIMITER ;

#Call of stored procedure
CALL show_max_salary();

#Type 2: with OUT When defining the output value, the data type should be consistent with the original corresponding data type, or the range should be larger
#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"
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
     SELECT AVG(salary) INTO ms
	 FROM employees; 
END //
DELIMITER ;

#call
CALL show_min_salary(@ms);

#View variable values
SELECT @ms;

#Type III: with IN
#Example 5: create stored procedure show_someone_salary(), view the salary of an employee IN the "emps" table, and enter the employee's name with the IN parameter empname.
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
		SELECT salary FROM employees
		WHERE last_name = empname;
END //
DELIMITER ;

#Call mode 1
CALL show_someone_salary('Abel');
#Call mode 2
SET @empname = 'Abel';	#(or set @ empname: ='abel '),: = is an assignment symbol in SQL
CALL show_someone_salary(@empname);

#Type 4: with IN and OUT
#Example 6: create stored procedure show_someone_salary2(), view the salary of an employee IN the "emps" table, enter the employee's name with the IN parameter empname, and output the employee's salary with the OUT parameter empsalary.
DELIMITER //
CREATE PROCEDURE show_some_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
		SELECT salary INTO empsalary
		FROM employees
		WHERE last_name = empname;
END //
DELIMITER ;

#call
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary;

#Type 5: with INOUT
#Example 7: create stored procedure show_mgr_name(), query the name of an employee's leader, enter the employee's name with the INOUT parameter "empname", and output the leader's name.
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
		SELECT last_name
		FROM employees
		WHERE employee_id = (
                              SELECT manager_id
                              FROM employees
                              WHERE last_name = empname;
                             );
END $
DELIMITER ;

#call
SET @empname = 'Abel'
CALL show_mgr_name(@empname);

SELECT @empname;

How to debug

In MySQL, stored procedures do not have a special integrated development environment like ordinary programming languages (such as VC + +, Java, etc.). Therefore, you can query the intermediate results of program execution through the SELECT statement to debug the correctness of an SQL statement. After successful debugging, move the SELECT statement to the next SQL statement, and then debug the next SQL statement. In this way, you can complete the debugging of all operations in the stored procedure. Of course, you can also copy the SQL statements in the stored procedure and debug them separately section by section.

It is precisely because of the need for step-by-step debugging and difficult maintenance that many companies require that stored procedures be prohibited.

Use of storage functions

I learned a lot of functions before. Using these functions, I can perform various processing operations on the data, which greatly improves the user's management efficiency of the database. MySQL supports custom functions. When it is defined, it is the same as calling MySQL predefined system functions.

Grammar analysis

Learned functions: LENGTH, SUBSTR, CONCAT, etc

Syntax format:

CREATE FUNCTION Function name(Parameter name parameter type,...) 
RETURNS return type		#Note that this is RETURNS
[characteristics ...]
BEGIN
		Function body  #There must be a RETURN statement in the function body 	.  Note that there must be	
END

explain:

1. Parameter list: the specified parameter is IN, OUT or INOUT, which is only legal for PROCEDURE. The IN parameter is always defaulted IN FUNCTION. (the default is IN, so there is no need to write)

2. The returns type statement 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. (note that only one can be included)

3. Character the constraint on the function specified when creating the function. The value is the same as when creating a stored procedure, which will not be repeated here.

4. 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.

Call storage function

In mysql, the use of storage functions is the same as that of MySQL internal functions. In other words, the 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.

As long as it is a function, the call uses the SELECT statement.

SELECT Function name(Argument list)

Code example

Example 1: create a storage function named email_by_name(), the parameter definition is null. This function queries Abel's email and returns a string data type.

DELIMITER //

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

DELIMITER ;

Call:

SELECT email_by_name();

Example 2: create a storage function named email_by_id(), parameter passed in emp_id, this function queries emp_id, and return it. The data type is string.

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 ;

Call:

SET @emp_id = 102; 
SELECT email_by_id(@emp_id);

#or
SELECT email_by_id(102);

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.

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT) 
RETURNS INT
    LANGUAGE SQL
    NOT DETERMINISTIC  
    READS SQL DATA
    SQL SECURITY DEFINER  
    COMMENT 'Query Department average salary'
    
BEGIN
 	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //

DELIMITER ;

Call:

SET @dept_id = 50;
SELECT count_by_id(@dept_id);

be careful:

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}"

  • Method 2: execute the following statement before creating the storage function

    SET GLOBAL log_bin_trust_function_creators = 1;

This is the difference between stored procedures and stored functions. You can omit the part of not writing characters in stored procedures, but if omitted in stored functions, you need to set SET GLOBAL log_bin_trust_function_creators = 1; Otherwise, an error will be reported.

practice:

#Storage function
#FUNCTION always defaults to the IN parameter. Don't write
#Example 1: create a storage function named email_by_name(), the parameter definition is null. This function queries Abel's email and returns a string data type.
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 ;

#call
SELECT email_by_name();

#Example 2: create a storage function named email_by_id(), parameter passed in emp_id, this function queries emp_id, and return it. The data type is string.
#Execute this statement before creating a function to ensure that the function will be created successfully
SET GLOBAL log_bin_trust_function_creators = 1;

#Declarative function
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN 
		RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;


#call
#Mode 1
SELECT email_by_id(101);

#Mode 2
SET @emp_id := 102;#Use = also OK
SELECT email_by_id(@emp_id);

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

#call
SET @dept_id = 50;
SELECT count_by_id(@dept_id);

Compare stored functions and stored procedures

keywordCall syntaxReturn valueApplication scenario
stored procedurePROCEDURECALL stored procedure ()Understood as 0 or moreGenerally used for updating
Storage functionFUNCTIONSELECT function ()It can only be oneIt is generally used when the query result is a value and is returned

Pay attention to understanding their application scenarios.

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

View, modify and delete stored procedures and functions

see

MySQL stores the status information of stored procedures and functions. Users can use the SHOW STATUS statement or SHOW CREATE statement to view it, or directly from the system information_ Query in schema database. Here are three methods.

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

The basic syntax structure is as follows:

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

give an example:

\G can only be used on the command line, and only ';' can be used in visualization tools Semicolon.

SHOW CREATE FUNCTION test_db.CountProc \G

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

The basic syntax structure is as follows:

SHOW {PROCEDURE | 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 stored procedure or function, which can be omitted. When not writing is omitted, the information of all stored procedures or functions existing in MySQL database will be listed.

LIKE supports fuzzy query.

Example: SHOW STATUS statement example, the code is as follows:

At the command line:

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)

3. From information_ schema. View information about stored procedures and functions in the routes table

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

Its basic grammatical form is as follows:

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

Note: if the names of stored procedures and functions 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. And route is used when a stored procedure and a function have the same name_ Type, and it must be capitalized only at this time, otherwise the corresponding stored procedure or stored function cannot be recognized.

For example: query the information of the storage function named CountProc from the routes table. The code is as follows:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id'　AND　ROUTINE_TYPE = 'FUNCTION';

practice:

#View of stored procedures and functions
#Mode 1 Use the SHOW CREATE statement to view the creation information of stored procedures and functions
SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

#Mode 2 Use the SHOW STATUS statement to view the status information of stored procedures and functions
SHOW PROCEDURE STATUS;	#View all stored procedures

SHOW PROCEDURE STATUS LIKE 'show_max_salary';#View the specified stored procedure and support fuzzy query

SHOW FUNCTION STATUS LIKE 'email_by_id';

#Mode 3 From information_ schema. View information about stored procedures and functions in the routes table
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'email_by_id';

#And route is used when a stored procedure and a function have the same name_ Type, and it must be capitalized only at this time, otherwise the corresponding stored procedure or stored function cannot be recognized.
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'email_by_id' AND ROUTINE_TYPE = 'FUNCTION';

SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';

modify

Modifying a stored procedure or function does not affect the function of the stored procedure or function, but only modifies the relevant characteristics. Use the ALTER statement. You can only modify the relevant features. The method body cannot be modified. If the method body is wrong, you can rewrite the stored procedure or stored function.

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

Among them, characteristic specifies the characteristics of the stored procedure or function, and its value information is slightly different from that when creating the stored procedure or function.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

  • CONTAINS SQL indicates that the subroutine CONTAINS SQL statements, but does not contain statements to read or write data.

  • NO SQL means that the subroutine does not contain SQL statements.

  • READS SQL DATA, which means that the subroutine contains statements to read data.

  • Modifications SQL data indicates that the subroutine contains statements to write data.

  • SQL security {determiner | invoker}, indicating who has permission to execute.

    • DEFINER, which means that only the DEFINER can execute it.
    • INVOKER, indicating that the caller can execute.

  • COMMENT 'string' indicates comment information.

explain:

ALTER PROCEDURE statement is used to modify the stored procedure, and ALTER FUNCTION statement is used to modify the stored function. However, the structure of the two statements is the same, and all parameters in the statement are the same.

Example 1: modify the definition of the stored procedure CountProc. Change the read-write permission to modify SQL data and indicate that the caller can execute. The code is as follows:

ALTER　PROCEDURE　CountProc 
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

Query the modified information:

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

The results show that the stored procedure is modified successfully. It can be seen from the query results that the permission to access data (sql_data_access) has changed to modifications SQL data, and the SECURITY_TYPE has changed to INVOKER.

Example 2: modify the definition of the storage function CountProc. Change the read-write permission to read SQL data and add the comment "FIND NAME", with the code as follows:

ALTER　FUNCTION　CountProc 
READS SQL DATA
COMMENT 'FIND NAME';

Storage function modified successfully. It can be seen from the query results that the permission to access data (SQL_DATA_ACCESS) has become read SQL data, and the function comment (route_comment) has become FIND NAME.

practice:

#Modification of stored procedures and functions (only modification of relevant characteristics, method body cannot be modified, and rewritten if it is wrong)
ALTER PROCEDURE show_max_sarary
SQL SECURITY INVOKER
COMMENT 'Query maximum wage';

delete

To delete stored procedures and functions, you can use the DROP statement,

Its syntax structure is as follows:

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

IF EXISTS: if the program or function is not stored, it can prevent errors and generate a warning viewed with SHOW WARNINGS.

give an example:

DROP PROCEDURE CountProc;

DROP FUNCTION CountProc;


#Deletion of stored procedures and functions
DROP FUNCTION IF EXISTS count_by_id;

DROP PROCEDURE IF EXISTS show_min_salary;

Disputes about the use of stored procedures

Although stored procedures have many advantages, there have always been many disputes about the use of stored procedures. For example, some companies require the use of stored procedures for large projects, while some companies explicitly prohibit the use of stored procedures in the manual. Why do these companies have such different needs for the use of stored procedures?

advantage

1. Stored procedures can be compiled and used multiple times at a time. Stored procedures are compiled only when they are created, and they do not need to be recompiled for subsequent use, which improves the execution efficiency of SQL.

2. It can reduce the development workload. Encapsulating the code into modules is actually one of the core ideas of programming. In this way, complex problems can be disassembled into different modules, and then the modules can be reused. While reducing the development workload, it can also ensure the clear structure of the code.

3. The security of stored procedure is strong. When setting the stored procedure, we can set the permission of the user, which has strong security like the view.

4. It can reduce the amount of network transmission. Because the code is encapsulated in the stored procedure, you only need to call the stored procedure every time, which reduces the amount of network transmission.

5. Good encapsulation. During relatively complex database operations, SQL statements that used to be used one by one and may need to be connected to the database many times have now become a stored procedure that only needs to be connected once.

shortcoming

Based on these advantages, many large companies require large projects to use stored procedures, such as Microsoft, IBM and other companies. But domestic Alibaba does not recommend developers to use stored procedures. Why?

Alibaba development specification

[mandatory] it is forbidden to use stored procedures, which are difficult to debug and expand, and have no portability.

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

1. Poor portability. Stored procedures cannot be transplanted across databases. For example, stored procedures written in MySQL, Oracle and SQL Server need to be rewritten when replaced with other databases.

2. Difficult commissioning. Only a few DBMS support debugging of stored procedures. For complex stored procedures, development and maintenance are not easy. Although there are some third-party tools that can debug stored procedures, they are charged.

3. Version management of stored procedures is very difficult. For example, if the data table index changes, the stored procedure may become invalid. We often need version management when developing software, but the stored procedure itself has no version control, so it is very troublesome to update the version iteratively.

4. It is not suitable for high concurrency scenarios. In high concurrency scenarios, it is necessary to reduce the pressure on the database. Sometimes the database will adopt the method of dividing databases and tables, and has high requirements for scalability. In this case, the stored procedure will become difficult to maintain and increase the pressure on the database, which is obviously not applicable.

Summary:

Stored procedures are both convenient and limited. Although different companies have different attitudes towards stored procedures, for our developers, mastering stored procedures is one of the necessary skills anyway.

practice

Exercise of stored procedure:

preparation

#0. Preparation
CREATE DATABASE test15_pro_func;

USE test15_pro_func;

1. Create stored procedure insert_user(), the user name and password are passed in and inserted into the admin table

CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);

DELIMITER $
CREATE PROCEDURE insert_user(IN user_name VARCHAR(15),IN pwd VARCHAR(25))
BEGIN
		INSERT INTO admin(user_name,pwd)
		VALUES(user_name,pwd);
END $
DELIMITER ;

#call
CALL insert_user('Tom','abc123');

2. Create stored procedure get_phone() to pass in the goddess number and return the goddess name and goddess phone

CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);

INSERT INTO beauty(`name`,phone,birth)
VALUES
('Zhu Yin','13201233453','1982-02-12'),
('Sun Yanzi','13501233653','1980-12-09'),
('Hebe ','13651238755','1983-08-21'),
('Deng Ziqi','17843283452','1991-11-12'),
('Liu Ruoying','18635575464','1989-05-18'),
('Yang Chaoyue','13761238755','1994-05-11');

DELIMITER //
CREATE PROCEDURE get_phone(IN id INT,OUT `name` VARCHAR(15),OUT phone VARCHAR(15))
BEGIN
		SELECT b.`name`,b.phone INTO `name`,phone	#Note that there is no need to add (), but an error will be reported
		FROM beauty b
		WHERE b.id = id;
END //
DELIMITER ;

#call
CALL get_phone(1,@name,@phone);
SELECT @name,@phone;

3. Create stored procedure date_diff() to pass in two goddess birthdays and return the size of the date interval

DELIMITER //
CREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT)
BEGIN
		SELECT DATEDIFF(birth1,birth2) INTO sum_date
		FROM DUAL;
END //
DELIMITER ;

#call
SET @birth1 = '1992-09-08';
SET @birth2 = '1992-10-08';

CALL date_diff(@birth1,@birth2,@sum_date);

SELECT @sum_date;

4. Create stored procedure format_date(), which is used to pass in a date, format it into xx month xx of xx year and return it

DELIMITER //
CREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25))
BEGIN
		SELECT DATE_FORMAT(my_date,'%y year%m month%d day') INTO str_date;
END //
DELIMITER ;

#call
CALL format_date(CURDATE(),@str_date);
SELECT @str_date;

5. Create stored procedure beauty_limit(), query the records of the goddess table according to the incoming starting index and the number of entries

DELIMITER //
CREATE PROCEDURE beauty_limit(IN start_index INT,IN size INT)
BEGIN
		SELECT * FROM beauty LIMIT start_index,size;
END //
DELIMITER ;

#call
CALL beauty_limit(1,3);

Create a stored procedure with inout mode parameters

6. Pass in two values a and b, and finally double A and b and return

DELIMITER //
CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN
		SET a = a * 2;
		SET b = b * 2;
END //
DELIMITER ;

#call
SET @a = 3,@b = 5;
CALL add_double(@a,@b);

SELECT @a,@b;

7. Delete the stored procedure of topic 5

DROP PROCEDURE IF EXISTS beauty_limit;

8. View the stored procedure information in topic 6

SHOW CREATE PROCEDURE add_double;

SHOW PROCEDURE STATUS LIKE 'add_%';

Exercise on storing functions:

preparation:

#0. Preparation
USE test15_pro_func;

CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.departments;

Return without parameters:

1. Create function get_count() returns the number of employees in the company

#Execute this statement before creating a function to ensure that the function will be created successfully
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN
		RETURN (SELECT COUNT(*) FROM employees);
END $
DELIMITER ;

#call
SELECT get_count();

Participation and return

2. Create function ename_salary() returns the salary of the employee according to the employee's name

DELIMITER $
CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
		RETURN (SELECT salary FROM employees WHERE last_name = emp_name);
END $
DELIMITER ;

#call
SELECT ename_salary('Abel');

3. Create the function dept_sal() returns the average salary of the department according to the Department name

DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
		RETURN (
                 SELECT AVG(salary)
                 FROM employees e JOIN departments d
                 ON e.department_id = d.department_id
                 WHERE d.department_name = dept_name
                );
END //
DELIMITER ;

#call
SELECT dept_sal('Marketing');

4. Create function add_float(), pass in two floats and return the sum of them

DELIMITER //
CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)
RETURNS FLOAT
BEGIN
		RETURN (SELECT value1 + value2);
END //
DELIMITER ;

#call
SET @v1 = 12.2;
SET @v2 := 2.3;
SELECT add_float(@v1,@v2);

Keywords: MySQL SQL

Added by tmed on Mon, 28 Feb 2022 18:25:13 +0200

Popular Keywords

©2024 Programming VIP