Article catalogue

• 1, MySQL stored procedures and functions
  • 1. Concepts of stored procedures and functions
  • 2. Benefits of stored procedures and functions
  • 3. Differences between stored procedures and functions
  • 4. Create stored procedure
  • 5. Call stored procedure
  • 6. View stored procedures
  • 7. Delete stored procedure
  • 8. Stored procedure syntax
    • 8.1 introduction to stored procedure syntax
    • 8.2 use of variables
    • 8.3 use of if statement
    • 8.4 transfer of parameters
    • 8.5 use of case statement
    • 8.6 while loop
    • 8.7 repeat cycle
    • 8.8loop loop loop
    • 8.9 cursor
  • 9. Summary of stored procedures
  • 10. Storage function

1, MySQL stored procedures and functions

1. Concepts of stored procedures and functions

• Stored procedures and functions are a collection of SQL statements that have been compiled in advance and stored in the database

2. Benefits of stored procedures and functions

• Stored procedures and functions can be reused to reduce the workload of developers. Similar to the method in java, it can be called multiple times
• Reduce network traffic. Stored procedures and functions are located on the server. When calling, you only need to pass names and parameters
• Reducing the transmission of data between database and application server can improve the efficiency of data processing
• Implementing some business logic at the database level can reduce the business processing at the code level

3. Differences between stored procedures and functions

• Function must have a return value
• The stored procedure has no return value

4. Create stored procedure

• Little knowledge

/*
	This keyword is used to declare the separator of sql statement and tell MySQL that the command has ended!
	sql The default separator of the statement is semicolon, but sometimes we need a function. The semicolon is included in the sql statement, but it is not used as the end identifier.
	At this time, you can use DELIMITER to specify the separator!
*/
-- Standard grammar
DELIMITER Separator

• Data preparation

-- establish db8 database
CREATE DATABASE db8;

-- use db8 database
USE db8;

-- Create student table
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- student id
	NAME VARCHAR(20),					-- Student name
	age INT,							-- Student age
	gender VARCHAR(5),					-- Student gender
	score INT                           -- Student achievement
);
-- Add data
INSERT INTO student VALUES (NULL,'Zhang San',23,'male',95),(NULL,'Li Si',24,'male',98),
(NULL,'Wang Wu',25,'female',100),(NULL,'Zhao Liu',26,'female',90);

-- Group according to gender and query the total score of each group of students. Sort according to the ascending order of the total score
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

• Create stored procedure syntax

-- Modify the separator to $
DELIMITER $

-- Standard grammar
CREATE PROCEDURE Stored procedure name(parameter...)
BEGIN
	sql sentence;
END$

-- Change separator to semicolon
DELIMITER ;

• Create stored procedure

-- Modify the separator to $
DELIMITER $

-- Create a stored procedure to encapsulate and query the total score of students in groups sql sentence
CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- Change separator to semicolon
DELIMITER ;

5. Call stored procedure

• Call stored procedure syntax

-- Standard grammar
CALL Stored procedure name(Actual parameters);

-- call stu_group stored procedure
CALL stu_group();

6. View stored procedures

• View stored procedure syntax

-- Standard syntax for querying all stored procedures in the database
SELECT * FROM mysql.proc WHERE db='Database name';

7. Delete stored procedure

• Delete stored procedure syntax

-- Standard grammar
DROP PROCEDURE [IF EXISTS] Stored procedure name;

-- delete stu_group stored procedure
DROP PROCEDURE stu_group;

8. Stored procedure syntax

8.1 introduction to stored procedure syntax

• Stored procedures can be programmed. It means that variables, expressions, conditional control statements, loop statements, etc. can be used to complete more complex functions!

8.2 use of variables

• Define variables

-- Standard grammar
DECLARE Variable name data type [DEFAULT Default value];
-- be careful: DECLARE It defines local variables and can only be used in BEGIN END Within the scope

-- Define a int Type variable and assign a default value of 10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- Define variables
	SELECT num;                   -- Query variable
END$

DELIMITER ;

-- call pro_test1 stored procedure
CALL pro_test1();

• Assignment of variable 1

-- Standard grammar
SET Variable name = Variable value;

-- Define string type variables and assign values
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- Define variables
	SET NAME = 'stored procedure';       -- Assign values to variables
	SELECT NAME;                -- Query variable
END$

DELIMITER ;

-- call pro_test2 stored procedure
CALL pro_test2();

• Assignment of variables 2

-- Standard grammar
SELECT Listing INTO Variable name FROM Table name [WHERE condition];

-- Define two int Variable, used to store the total score of male and female students
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- Define variables
	SELECT SUM(score) INTO men FROM student WHERE gender='male';    -- Calculate the total score of male students and assign it to men
	SELECT SUM(score) INTO women FROM student WHERE gender='female';  -- Calculate the total score of female students and assign it to women
	SELECT men,women;           -- Query variable
END$

DELIMITER ;

-- call pro_test3 stored procedure
CALL pro_test3();

8.3 use of if statement

• Standard grammar

-- Standard grammar
IF Judgment condition 1 THEN Executive sql Statement 1;
[ELSEIF Judgment condition 2 THEN Executive sql Statement 2;]
...
[ELSE Executive sql sentence n;]
END IF;

• Case demonstration

/*
	Define an int variable to store the total grade of the class
	Define a varchar variable to store the score description
	According to the total score:
		380 Score or above, excellent in learning
		320 ~ 380     Good study
		320 The following learning is general
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- Define total score variable
	DECLARE total INT;
	-- Define score description variables
	DECLARE description VARCHAR(10);
	-- Assign a value to the total score variable
	SELECT SUM(score) INTO total FROM student;
	-- Total judgment score
	IF total >= 380 THEN 
		SET description = 'Excellent learning';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = 'Good study';
	ELSE 
		SET description = 'General learning';
	END IF;
	
	-- Query total score and description information
	SELECT total,description;
END$

DELIMITER ;

-- call pro_test4 stored procedure
CALL pro_test4();

8.4 transfer of parameters

• Syntax of parameter passing

DELIMITER $

-- Standard grammar
CREATE PROCEDURE Stored procedure name([IN|OUT|INOUT] Parameter name data type)
BEGIN
	Executive sql sentence;
END$
/*
	IN:Represents the input parameter, and the actual data needs to be passed by the caller. default
	OUT:Represents an output parameter, which can be used as a return value
	INOUT:Representatives can be used as input parameters or output parameters
*/
DELIMITER ;

8.5 use of case statement

• Standard syntax 1

-- Standard grammar
CASE expression
WHEN Value 1 THEN implement sql Statement 1;
[WHEN Value 2 THEN implement sql Statement 2;]
...
[ELSE implement sql sentence n;]
END CASE;

• Standard syntax 2

-- Standard grammar
CASE
WHEN Judgment condition 1 THEN implement sql Statement 1;
[WHEN Judgment condition 2 THEN implement sql Statement 2;]
...
[ELSE implement sql sentence n;]
END CASE;

• Case demonstration

/*
	Enter the total score variable to represent the total score of students
	Define a varchar variable to store the score description
	According to the total score:
		380 Score or above, excellent in learning
		320 ~ 380    Good study
		320 The following learning is general
*/
DELIMITER $

CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- Define variables
	DECLARE description VARCHAR(10);
	-- use case judge
	CASE
	WHEN total >= 380 THEN
		SET description = 'Excellent learning';
	WHEN total >= 320 AND total < 380 THEN
		SET description = 'Good study';
	ELSE 
		SET description = 'General learning';
	END CASE;
	
	-- Query score description information
	SELECT description;
END$

DELIMITER ;

-- call pro_test7 stored procedure
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

8.6 while loop

• Standard grammar

-- Standard grammar
 Initialization statement;
WHILE Conditional judgment statement DO
	Loop body statement;
	Conditional control statement;
END WHILE;

• Case demonstration

/*
	Calculate the even sum between 1 and 100
*/
DELIMITER $

CREATE PROCEDURE pro_test8()
BEGIN
	-- Define summation variables
	DECLARE result INT DEFAULT 0;
	-- Define initialization variables
	DECLARE num INT DEFAULT 1;
	-- while loop
	WHILE num <= 100 DO
		-- Even judgment
		IF num%2=0 THEN
			SET result = result + num; -- accumulation
		END IF;
		
		-- Give Way num+1
		SET num = num + 1;         
	END WHILE;
	
	-- Query summation results
	SELECT result;
END$

DELIMITER ;

-- call pro_test8 stored procedure
CALL pro_test8();

8.7 repeat cycle

• Standard grammar

-- Standard grammar
 Initialization statement;
REPEAT
	Loop body statement;
	Conditional control statement;
	UNTIL Conditional judgment statement
END REPEAT;

-- be careful: repeat The cycle stops when the conditions are met. while The loop is executed when the conditions are met

• Case demonstration

/*
	Calculate the sum between 1 and 10
*/
DELIMITER $

CREATE PROCEDURE pro_test9()
BEGIN
	-- Define summation variables
	DECLARE result INT DEFAULT 0;
	-- Define initialization variables
	DECLARE num INT DEFAULT 1;
	-- repeat loop
	REPEAT
		-- accumulation
		SET result = result + num;
		-- Give Way num+1
		SET num = num + 1;
		
		-- Stop cycle
		UNTIL num>10
	END REPEAT;
	
	-- Query summation results
	SELECT result;
END$

DELIMITER ;

-- call pro_test9 stored procedure
CALL pro_test9();

8.8loop loop loop

• Standard grammar

-- Standard grammar
 Initialization statement;
[Cycle name:] LOOP
	Conditional judgment statement
		[LEAVE Cycle name;]
	Loop body statement;
	Conditional control statement;
END LOOP Cycle name;

-- be careful: loop A simple loop can be implemented, but exiting the loop needs to be defined with other statements. We can use leave Statement complete!
--      If you do not add a statement to exit the loop, it becomes an endless loop.

• Case demonstration

/*
	Calculate the sum between 1 and 10
*/
DELIMITER $

CREATE PROCEDURE pro_test10()
BEGIN
	-- Define summation variables
	DECLARE result INT DEFAULT 0;
	-- Define initialization variables
	DECLARE num INT DEFAULT 1;
	-- loop loop
	l:LOOP
		-- If the condition holds, stop the cycle
		IF num > 10 THEN
			LEAVE l;
		END IF;
	
		-- accumulation
		SET result = result + num;
		-- Give Way num+1
		SET num = num + 1;
	END LOOP l;
	
	-- Query summation results
	SELECT result;
END$

DELIMITER ;

-- call pro_test10 stored procedure
CALL pro_test10();

8.9 cursor

-- establish stu_score surface
CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

/*
	Save all grades in the student table to stu_ In the score table
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- Define achievement variables
	DECLARE s_score INT;
	-- Create cursor,Query all student achievement data
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- Open cursor
	OPEN stu_result;
	
	-- Use the cursor to traverse the results,Get the first row of data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get the second row of data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get the data in line 3
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get line 4 data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Close cursor
	CLOSE stu_result;
END$

DELIMITER ;

-- call pro_test11 stored procedure
CALL pro_test11();

-- query stu_score surface
SELECT * FROM stu_score;


-- ===========================================================
/*
	Problems:
		student There are 4 pieces of data in the table. We have traversed the cursor 4 times. There is no problem!
		But how many more iterations in the cursor? There will be problems
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- Define achievement variables
	DECLARE s_score INT;
	-- Create cursor,Query all student achievement data
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- Open cursor
	OPEN stu_result;
	
	-- Use the cursor to traverse the results,Get the first row of data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get the second row of data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get the data in line 3
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get line 4 data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Use the cursor to traverse the results,Get line 5 data
	FETCH stu_result INTO s_score;
	-- Save data to stu_score In the table
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- Close cursor
	CLOSE stu_result;
END$

DELIMITER ;

-- call pro_test11 stored procedure
CALL pro_test11();

-- query stu_score surface,Although the data is correct, an error will be reported when executing the stored procedure
SELECT * FROM stu_score;

• Optimized use of cursors (with circular use)

/*
	When the cursor ends, the cursor end event will be triggered. We can use this feature to complete the loop operation
	Tagging idea:
		1.Define a variable. The default value is 0 (which means there is data)
		2.When the cursor ends, change the variable value to 1 (meaning there is no data)
*/
-- 1.Define a variable. The default value is 0(It means there is data)
DECLARE flag INT DEFAULT 0;
-- 2.When the cursor ends, change the variable value to 1(That means there's no data)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*
	Save all grades in the student table to stu_ In the score table
*/
DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- Define achievement variables
	DECLARE s_score INT;
	-- Define tag variables
	DECLARE flag INT DEFAULT 0;
	-- Create a cursor to query all student achievement data
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- After the cursor ends, change the marked variable to 1
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- Open cursor
	OPEN stu_result;
	
	-- Recycle cursors
	REPEAT
		-- Use the cursor to traverse the results,Get the data
		FETCH stu_result INTO s_score;
		-- Save data to stu_score In the table
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	
	-- Close cursor
	CLOSE stu_result;
END$

DELIMITER ;

-- call pro_test12 stored procedure
CALL pro_test12();

-- query stu_score surface
SELECT * FROM stu_score;

9. Summary of stored procedures

• A stored procedure is a collection of SQL statements that have been compiled in advance and stored in the database. You can do some business processing at the database level
• To put it bluntly, a stored procedure is to encapsulate sql statements into methods, and then call methods to execute sql statements
• Benefits of stored procedures
  • security
  • Efficient
  • Strong reusability

10. Storage function

• Stored functions and stored procedures are very similar. Stored functions can do things, stored procedures can also do!
• The stored function has a return value, but the stored procedure has no return value (the out of the parameter is actually equivalent to returning data)
• Standard grammar
  • Create storage function

DELIMITER $

-- Standard grammar
CREATE FUNCTION Function name([Parameter data type])
RETURNS return type
BEGIN
	Executive sql sentence;
	RETURN result;
END$

DELIMITER ;

• Call storage function

-- Standard grammar
SELECT Function name(Actual parameters);

• Delete storage function

-- Standard grammar
DROP FUNCTION Function name;

• Case demonstration

/*
	Define a storage function to obtain the number of students with scores greater than 95 in the student table
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- Define statistical variables
	DECLARE result INT;
	-- Query the number of students whose scores are greater than 95 and assign a value to the statistical variable
	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
	-- Return statistical results
	RETURN result;
END$

DELIMITER ;

-- call fun_test1 Storage function
SELECT fun_test1();