Stored procedure and stored function

Posted by fodder on Wed, 02 Feb 2022 23:15:32 +0100

1, Stored procedure

1.1 overview of stored procedures

Stored Procedure is Stored Procedure in English. Its idea is very simple, which is the encapsulation of a set of precompiled SQL statements.
The stored procedure is stored on the MySQL server in advance. When it needs to be executed, the client only needs to issue a command to call the stored procedure to the server, and the server can execute all the pre stored series of SQL statements.
Benefits:
- Simplify the operation, improve the reusability of sql statements and reduce the pressure of developers
- 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 statements exposed on 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 also reduce the amount of 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.

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

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

1.3. Create stored procedure

DELIMITER New end tag

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

DELIMITER ;

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

Meaning of the symbol before 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.
characteristics refers to the constraints on the stored procedure specified when creating the stored procedure. Its value information is as follows:
- 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 subroutine 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.
There are multiple SQL statements in the middle of the stored procedure body, and each statement is marked with (;) The number is the terminator.
- If there is only one SQL statement, BEGIN and END can be omitted
DECLARE: used to DECLARE variables. The position used is in the middle of the BEGIN... END statement, and variables need to be declared before other statements are used.
SET: assignment statement, used to assign values to variables.
SELECT... INTO: store the query results from the data table INTO variables, that is, assign values to variables.
Need to set a new end tag: 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.
- Use "DELIMITER" after the stored procedure is defined Restore the default terminator.
- When using the DELIMITER command, you should avoid using the backslash ('\') character, because the backslash is the escape character of MySQL.

example:

DROP DATABASE IF EXISTS template;
CREATE DATABASE IF NOT EXISTS template;
USE template;

CREATE TABLE IN NOT EXISTS employees
AS 
SELECT * FROM atguigudb.employees;

CREATE TABLE IF NOT EXISTS departments
AS
SELECT * FROM atguigudb.departments;

-- Create stored procedure: no parameters
DELIMITER $

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

DELIMITER ;

-- Create stored procedure: with OUT
DELIMITER //

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

DELIMITER ;

-- Create stored procedure: with IN
DELIMITER $

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

DELIMITER ;

-- Create stored procedure: with IN and OUT
DELIMITER //

CREATE PROCEDURE show_somebody_salary(IN employee_name VARCHAR(25),OUT employee_salary DOUBLE)
BEGIN 
	SELECT salary INTO employee_salary 
	FROM employees 
	WHERE last_name = employee_name;
END //

DELIMITER ;

-- Create stored procedure: with INOUT
DELIMITER $

CREATE PROCEDURE show_manager_name(INOUT employee_name VARCHAR(25))
BEGIN
	SELECT last_name INTO employee_name
	FROM employees
	WHERE employee_id = (
		SELECT manager_id 
		FROM employees 
		WHERE last_name = employee_name
	);
END $

DELIMITER ;

1.4. Call stored procedure

stored procedures have multiple 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.

CALL Stored procedure name(Argument list)

-- 1. call in Parameters of mode:
CALL Stored procedure name('value');

-- 2. call out Parameters of mode:
SET @name;
CALL Stored procedure name(@name);
SELECT @name;

-- 3. call inout Parameters of mode:
SET @name=value;
CALL Stored procedure name(@name);
SELECT @name;

example:

-- Calling a parameterless stored procedure
call select_all_date();

-- call OUT Stored procedure of pattern
CALL show_min_salary(@min_salary);
SELECT @min_salary;

-- call IN Stored procedure of pattern
-- Call mode 1
CALL show_someone_salary('Abel');
-- Call mode 2
SET @employee_name := 'Abel';
CALL show_someone_salary(@employee_name);

-- call IN and OUT Stored procedure of pattern
SET @emp_name = 'ABel';
CALL show_somebody_salary(@emp_name,@emp_salary);
SELECT @emp_salary;

-- call INOUT Stored procedure of pattern
SET @emp_name = 'ABel';
CALL show_manager_name(@emp_name);
SELECT @emp_name;

1.5. Viewing stored procedures

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 information of the system_ Query in schema database. Here are three methods.

-- 1.use SHOW CREATE Statement to view the creation information of the stored procedure
SHOW CREATE PROCEDURE Stored procedure name;
-- 2. use SHOW STATUS Statement to view the status information of a stored procedure
-- 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 and can be omitted. When omitted, it will be listed MySQL Information about all stored procedures that exist in the database.
SHOW PROCEDURE STATUS [LIKE 'pattern'];
-- 3. from information_schema.Routines View stored procedure information in table
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='Stored procedure name' [AND ROUTINE_TYPE = 'PROCEDURE'];

example:

-- 1.use SHOW CREATE Statement to view the creation information of the stored procedure
SHOW CREATE PROCEDURE show_manager_name;
-- 2. use SHOW STATUS Statement to view the status information of a stored procedure
SHOW PROCEDURE STATUS LIKE 'show_min_salary';
-- 3. from information_schema.Routines View stored procedure information in table
-- If in MySQL If the names of stored procedures and functions are the same in the database, it is best to specify them ROUTINE_TYPE Query criteria to indicate whether the query is a stored procedure or a function.
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_some_salary' AND ROUTINE_TYPE = 'PROCEDURE';

1.6 modification of stored procedure

-- 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 Stored procedure name [characteristic ...];

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

example:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary';

ALTER PROCEDURE show_min_salary
SQL SECURITY INVOKER
COMMENT 'Query minimum wage';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary';

1.7 deletion of stored procedure

DROP PROCEDURE [IF EXISTS] The name of the stored procedure;

example:

DROP PROCEDURE IF EXISTS select_all_data;

2, Use of storage functions

2.1. Create storage function

DELIMITER New end tag

CREATE FUNCTION Function name(Parameter name parameter type,...) 
RETURNS return type
[characteristics ...]
BEGIN
	Function body   
	RETURN sentence
END New end tag

DELIMITER ;

Parameter list: the specified parameter is IN, OUT or INOUT, which is only legal for PROCEDURE. The IN parameter is always the default IN FUNCTION.
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.
characteristic the constraint on the function specified when the function is created.
- 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 subroutine 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.
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.

example:

USE template;

DELIMITER $

CREATE FUNCTION email_by_name(employee_name VARCHAR(25))
RETURNS VARCHAR(25)
	DETERMINISTIC 
	CONTAINS SQL
	READS SQL DATA
BEGIN 
	RETURN (SELECT email FROM employees WHERE last_name = employee_name);
END $

DELIMITER ;

note: 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: SET GLOBAL log_bin_trust_function_creators = 1;

2.2. Call storage function

SELECT Function name(Argument list)

example:

SELECT email_by_name('Abel');

2.3. Viewing storage functions

-- 1.use SHOW CREATE Statement to view the creation information of the storage function
SHOW CREATE FUNCTION Store function name;
-- 2. use SHOW STATUS Statement to view the status information of the storage function
-- 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 and can be omitted. When omitted, it will be listed MySQL Information about all stored functions that exist in the database.
SHOW FUNCTION STATUS [LIKE 'pattern']
-- 3. from information_schema.Routines View the information of the stored function in the table
-- If in MySQL If the names of stored procedures and functions are the same in the database, it is best to specify them ROUTINE_TYPE Query criteria to indicate whether the query is a stored procedure or a function.
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='Name of the storage function' [AND ROUTINE_TYPE = 'FUNCTION'];

example:

-- 1.use SHOW CREATE Statement to view the creation information of the storage function
SHOW CREATE FUNCTION email_by_name;
-- 2. use SHOW STATUS Statement to view the status information of stored procedures and functions
SHOW FUNCTION STATUS LIKE 'email_by_name';
-- 3. from information_schema.Routines View the information of the stored function in the table
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name' AND ROUTINE_TYPE = 'FUNCTION';

2.4 modification of storage function

-- Modifying a stored procedure or function does not affect the function of the stored procedure or function, but only modifies the relevant characteristics
ALTER FUNCTION Name of the storage function [characteristic ...];

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

example:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name';

ALTER FUNCTION email_by_name
SQL SECURITY INVOKER
COMMENT 'Email address of an employee';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name';

2.5 deletion of storage function

DROP FUNCTION [IF EXISTS] Name of the storage function;

example:

DROP FUNCTION IF EXISTS email_by_name;

3, Compare 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 ()	It can only be one	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, 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.

4, Exercises

-- 1.preparation 
CREATE DATABASE test15_pro_func; 
USE test15_pro_func; 

-- 2. Create stored procedure insert_user(),Realize the incoming user name and password, and insert it into admin In the 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 insert_user('Sakura','sakura27185');
SELECT * FROM admin;

-- 3. Create stored procedure get_phone(),Realize the incoming 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'); 
       
SELECT * FROM beauty; 

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 FROM beauty b WHERE b.id = id;
END //

DELIMITER ;

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

-- 4. Create stored procedure date_diff()，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;
END //

DELIMITER ;

SET @birth1 = '1982-02-12';
SET @birth2 = '1980-12-09';
CALL date_diff(@birth1,@birth2,@sum_date);
SELECT @sum_date;

-- 5. Create stored procedure format_date(),Pass in a date and format it as xx year xx month xx Day and return 
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 format_date(CURDATE(),@str);
SELECT @str;

-- 6. Create stored procedure beauty_limit()，The number of entries to start the query, based on the number of entries entered 
DELIMITER $

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

DELIMITER ;

CALL beauty_limit(1,3);

-- 7.  Create band inout Stored procedure for mode parameters,afferent a and b Two values, final a and b Double and return 
DELIMITER //

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

DELIMITER ;

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

-- 8. Delete stored procedure of title 5 
DROP PROCEDURE IF EXISTS beauty_limit;

-- 9. View the stored procedure information in topic 6
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE 'add_double';

-- 10. preparation 
USE test15_pro_func; 
CREATE TABLE employees AS SELECT * FROM atguigudb.employees; 
CREATE TABLE departments AS SELECT * FROM atguigudb.departments; 

-- 11. Return without parameters ,Create function get_count(),Returns the number of employees in the company 
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $

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

DELIMITER ;

SELECT get_count();

-- 12. Participation and return ,Create function ename_salary(),Returns the salary of an employee based on its name 
DELIMITER $

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

DELIMITER ;

SELECT ename_salary('Abel');

-- 13. Create function dept_sal() ,Return 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 ;

SELECT dept_sal('Marketing');

-- 14. Create function add_float()，Implement the incoming two float，Returns the sum of the two
DELIMITER $

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

DELIMITER ;

SET @value1 = 12.3;
SET @value2 = 12.4;
SELECT add_float(@value1,@value2);

Topics: MySQL SQL function

Programmer Think

Stored procedure and stored function

1, Stored procedure

1.1 overview of stored procedures

1.2 classification

1.3. Create stored procedure

1.4. Call stored procedure

1.5. Viewing stored procedures

1.6 modification of stored procedure

1.7 deletion of stored procedure

2, Use of storage functions

2.1. Create storage function

2.2. Call storage function

2.3. Viewing storage functions

2.4 modification of storage function

2.5 deletion of storage function

3, Compare stored functions and stored procedures

4, Exercises

Hot Topics