SoFunction
Updated on 2025-04-05

Detailed explanation of the creation and use examples of MySQL stored procedures

1. Stored process

1.1 Introduction to stored procedures

Stored procedure:

Encapsulate SQL instructions that can complete specific functions (SQL instruction set), compile and store them on the database server, and name them. Clients can directly call the SQL instruction set to obtain the execution results.

Problems solved by stored procedures:

  • If there is SQL that requires multiple executions, each execution needs to be passed to the MySQL server through a connection, and it needs to be compiled and executed before returning the execution result. Repeat and waste of resources
  • If multiple SQL instructions need to be executed continuously, and the second SQL instruction needs to use the result set of the first SQL executed as a parameter.

Advantages of stored procedures:

  • SQL instructions do not need to be written by clients or transmitted over the network, which can save network overhead, and at the same time avoid malicious tampering of SQL instructions during transmission, ensuring security;
  • Stored procedures are compiled and created and saved in the database server. The execution process does not require compilation, which improves performance on the execution process of SQL instructions;
  • There is a logical relationship between multiple SQL instructions in a stored procedure, which supports process control statements (branches, loops), which can realize more complex business logic processing;

Disadvantages of stored procedures:

  • Stored procedures are compiled, created and stored in the database according to different database engines. If different types of database migrations are required, the database stored procedures need to be re-written.
  • Stored procedures are restricted and database products, which can become a problem if high-performance optimization is required;
  • In an Internet project, if high concurrent (connection) access of the database is required, the stored procedure will increase the connection execution time of the database. Because we hand over complex business to the database for processing.

1.2 Creation and Deletion of Stored Procedures

1.2.1 Creating a stored procedure

Encapsulate SQL instructions that can complete specific functions

grammar

CREATE PROCEDURE <proc_name>([IN/OUT args])
BEGIN
	-- SQL
END;

Example

-- Create stored procedures，Implement addition operations
-- Stored procedures have different parameters and output parameters
CREATE PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT)
BEGIN
	SET c = a+b;
END;

1.2.2 Delete stored procedures

-- Delete stored procedures
DROP PROCEDURE proc_test1;

1.3 Calling of stored procedures

-- Define variables
SET @m = 0;
-- Call stored procedures
CALL proc_test1(3,2,@m);
-- Display variable values
SELECT @m from DUAL;

1.4 Variable usage in stored procedures

There are two types of variables in stored procedures: local variables and user variables

1.4.1 Local variables

Local variables: Define variables in stored procedures and can only be used inside stored procedures.

-- Local variables need to be defined in stored procedures，And it must be defined at the beginning of the stored procedure
declare &lt;attr_name&gt; &lt;type&gt; [default value];
-- Create stored procedures:Calculate the square of input parameters and input parameters/2 The sum of
CREATE PROCEDURE proc_test2(IN a INT,OUT r INT)
BEGIN
	DECLARE x INT DEFAULT 0; -- Local variable definition
	DECLARE y INT DEFAULT 0; -- Local variable definition
	set x = a*a;
	set y = a/2;
	SET r = x + y ;
END

1.4.2 User variables

User variable: equivalent to a global variable, defined user variable, can be passedselect @attrName from dualConduct a query;

-- User variables will be stored inmysqlIn the data dictionary of the database（dual）
-- User variable definition usagesetDirect definition of keywords，The variable name should be@beginning
SET @a = 0; -- Define user variables
SELECT @a from DUAL;-- Query user variables

1.4.3 Assign query results to variables

In stored procedures, useselect ... intoAssign values ​​to variables

-- Create stored procedures，Query the number of students in the student table,Assign value to parameterss
CREATE PROCEDURE proc_test3(OUT s INT)
BEGIN
	SELECT COUNT(stu_num) INTO s from students;-- The number of students to be found，Assign value to parameterss
END;
-- Call stored procedurestest3
SET @s=0;
CALL proc_test3(@s);
select @s from DUAL;

Notice

User variables are equivalent to global variables and can be shared in SQL quality and multiple stored procedures. Therefore, it is recommended to minimize the use of user variables in development to prevent too many user variables from making it difficult to understand and maintain the program;

1.5 Parameters of stored procedures

There are three parameters of MySQL stored procedures: IN, OUT, and INOUT

1.5.1 Input parameter IN

Input parameters – Parameters passed to the stored procedure during the call to the stored procedure (the process invoked must be literal or literal with the actual variable)

-- Create stored procedures：Add student information
CREATE PROCEDURE proc_test4 (IN snum CHAR(8),IN sname VARCHAR(20),IN denger CHAR(2),IN age INT,IN tel VARCHAR(20),IN cid INT)
BEGIN
	INSERT INTO students(stu_num,stu_name,stu_denger,stu_age,stu_tel,cid) 	         		VALUES(snum,sname,denger,age,tel,cid);
END;
-- Call stored procedures
CALL proc_test4('8','Zhang Fei','male',88,'13667565656',2);

1.5.2 Output parameters OUT

Returning the data generated in the stored procedure to the procedure caller is equivalent to the return value of Java, but the difference is that the stored procedure can have multiple output parameters.

-- Create stored procedures：According to student number，Query student names
CREATE PROCEDURE proc_test5(IN snum INT,OUT sname VARCHAR(20))
BEGIN
	SELECT stu_name INTO sname from students where stu_num = snum;
END;
-- Set user parameters
SET @name = '';
-- Call stored procedures
CALL proc_test5(8,@name);
select @name from DUAL;

1.5.3 Input and output parameters INOUT

-- Stored procedures：According to student number，Query student names，useINOUT
CREATE PROCEDURE proc_test6(INOUT str VARCHAR(20))
BEGIN
	SELECT stu_name INTO str from students where stu_num = str;
END;
-- Set parameters,The default value is query parameter value
set @str = '8';
-- 调用Stored procedures
CALL proc_test6(@str);
SELECT @str from dual;

1.6 Process control in stored procedures

In stored procedures, support process control statements are used to implement logical control

1.6.1 Branch statement

if-then-else

Single branch:

– Single branch: if the condition is true, execute SQL
CREATE PROCEDURE test7(IN a INT)
BEGIN
IF CONDITION THEN
– SQL
END IF;
END;

Double branch

– Double branch: if the condition is true, execute SQL1; otherwise, execute SQL2
CREATE PROCEDURE test7(IN a INT)
BEGIN
IF CONDITION THEN
– SQL1
ELSE
– SQL2
END IF;
END;

case

– CASE
CREATE PROCEDURE proc_test8(IN a INT)
BEGIN
CASE a
WHEN 1 THEN – When parameter a = 1, execute SQL1
– SQL1
WHEN 2 THEN – SQL2 is executed when parameter a = 2
– SQL2
ELSE
– SQL3 – If the variable value and all when values ​​do not match, SQL3 is executed
END CASE;
END;

1.6.2 Loop statement

while

– Create stored procedures: Add parameters, create class information according to parameter values, that is, participate in 3, create 3 class information
CREATE PROCEDURE proc_test7(IN num INT)
BEGIN
DECLARE i INT; – Local variables
SET i = 0;
WHILE i<num DO – i < parameter variable, loop executes SQL statement
– SQL
SET i = i+1; – Every cycle, i increases by 1
END WHILE;
END;

repeat

– repeat Create stored procedure: add parameters, create class information according to parameter values, that is, participate in 3, create 3 class information
CREATE PROCEDURE proc_test7(IN num INT)
BEGIN
DECLARE i INT; – Local variables
SET i = 0;
REPEAT - Looping SQL execution
– SQL
SET i = i+1; – Every time it is executed, i+1
UNTIL i > num; – UNTIL determines the following result, and if it matches, it will jump out of the loop
END WHILE;
END;

loop

– LOOP Create stored procedures: Add parameters, create class information according to parameter values, that is, participate in 3, create 3 class information
CREATE PROCEDURE proc_test7(IN num INT)
BEGIN
DECLARE i INT; – Local variables
SET i = 0;
myloop:LOOP – Set myloop, loop through SQL execution in myloop
– SQL
set i = i+1; – Every cycle, i+1
IF i = num THEN – judge when i = parameter value
LEAVE myloop; – Break out of myloop loop
END IF;
END LOOP;
END;

1.7 Stored procedure management

1.7.1 Query stored procedures

Stored procedures belong to a database, that is, when we create stored procedures in a database, they can only be called in the current database, and cannot be called across databases.

-- By database name，Query stored procedures in the current database
show PROCEDURE STATUS where db = 'db_test';
-- Query stored procedure creation details
SHOW CREATE PROCEDURE db_test.proc_test1;

1.7.2 Modify stored procedures

Modifying stored procedures mainly refers to modifying the characteristics/characteristics of stored procedures.

alter procedure &lt;proc_name&gt; feature1 {feature2...}

Characteristic parameters of stored procedures

  • CONTAINS SQLIndicates that the subroutine contains SQL statements but does not contain read or write data operations.
  • NO SQLIndicates that the subroutine does not contain SQL statements
  • READS SQL DATAStatement that indicates that the subroutine contains reading data
  • MODIFIES SQL DATAStatement that contains the writing data in the subroutine
  • SQL SECURITY {DEFINER| INVOKER}Indicate who has permission to execute
  • DEFINEROnly the definer has execution permissionsINVOKERThe caller can execute
  • COMMENT stringIndicates comment information

– Modify stored procedures
ALTER PROCEDURE proc_test1 NO SQL;

1.7.3 Delete stored procedures

Delete stored procedures

-- Delete stored procedures DROP
DROP PROCEDURE proc_test1;

2. Stored process cases

Use stored procedures to complete the book borrowing operation

2.1 Prepare data

Database preparation: Create a new database

## Create a databasecreate database da_test3;
## Using Databaseuse db_test3;

Data table and data preparation

-- Create a book information table
CREATE TABLE books(
	book_id INT PRIMARY KEY auto_increment,
	book_name VARCHAR(50) NOT NULL,
	book_author VARCHAR(20) NOT NULL,
	book_price DECIMAL(10,2) NOT NULL,
	book_stock INT NOT NULL,
	book_desc VARCHAR(200)
);
-- Add book information
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('Java from Beginner to Give Up','Stephen',28.80,100,'A top Java textbook that takes you from getting started to giving up');
INSERT INTO books(book_name,book_author,book_price,book_stock,book_desc) 
VALUES ('MySQL from Beginner to Give Up','Curry',68.20,20,'A top mysql textbook that takes you from getting started to giving up');
-- Create a student information table
CREATE TABLE students(
	stu_num CHAR(8) PRIMARY KEY,
	stu_name VARCHAR(20) NOT NULL,
	stu_denger CHAR(2) NOT NULL,
	stu_age INT NOT NULL
);
-- Add student information
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1001','Bushima','female','20');
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1002','Anqira','female','25');
INSERT INTO students (stu_num,stu_name,stu_denger,stu_age) VALUES('1003','Yi Xing','male','30');

2.2 Creating a stored procedure

Create a stored procedure to implement the operation of borrowing books: which student has picked up which number of books, and the number of borrowed books

operate:

  • Save book borrowing records
  • Modify the library

condition:

  • Determine whether a student exists
  • Determine whether the book exists and whether the inventory is sufficient

Create a book loan record form

-- Book Borrowing Record Table
CREATE TABLE records(
	rid INT PRIMARY KEY auto_increment,
	snum CHAR(4) NOT NULL,
	bid INT NOT NULL,
	borrow_num INT NOT NULL,
	is_return INT NOT NULL,	-- 0-Not returned；1-Returned
	borrow_date date NOT NULL,
	CONSTRAINT FK_RECORDS_STUDENTS FOREIGN KEY(snum) REFERENCES students(stu_num),
	CONSTRAINT FK_RECORDS_BOOKS FOREIGN KEY(bid) REFERENCES books(book_id)
);

2.2.1 Creating a stored procedure

-- Realize book borrowing business
-- parameter1：	输入parameter	Student ID	a
-- parameter2：	输入parameter	Bookmark Number	b
-- parameter3：	输入parameter	Number of books borrowed	m
-- parameter4：	输出parameter	Borrowing status（1-Successful book borrowing；2-Student ID不存在；3-Books do not exist；4-Insufficient inventory）
CREATE PROCEDURE proc_borrow_book(IN a CHAR(4),IN b INT,IN m INT,OUT state INT)
BEGIN
	DECLARE stu_count INT DEFAULT 0;	
	DECLARE b_count INT DEFAULT 0;
	DECLARE b_stock INT DEFAULT 0;
	-- one、判断Student ID是否存在	根据parameteraGo to the student table to check whether there are students
	SELECT COUNT(stu_num) INTO stu_count from  students where stu_num = a;
	IF stu_count &gt; 0 THEN
		-- Student ID存在
		-- two、Check whether the book number exists
		SELECT COUNT(book_id) INTO b_count from books where book_id = b;
		IF b_count &gt; 0 THEN
			-- Books exist
			-- three、Check whether the book inventory is sufficient
			SELECT book_stock INTO b_stock from books where book_id = b;
			IF b_stock &gt;= m THEN
				-- Inventory satisfies
				-- 1、Insert the book loan record form
				INSERT INTO records(snum,bid,borrow_num,is_return,borrow_date) VALUES(a,b,m,0,SYSDATE());
				-- 2、renewbooksTable inventory databook_stock
				UPDATE books SET book_stock = (b_stock - m) where book_id = b;
				-- 3、Successful book borrowing，Return to success status 0 
				SET state = 1;
			ELSE
				-- Insufficient inventory
			SET state = 4;
			END IF;
		ELSE
			-- Books do not exist
		SET state = 3;
		END IF;
	ELSE
		-- Student ID不存在
	SET state = 2;
	END IF;
END;

2.2.2 Test

SELECT * FROM students;-- Student Table
select * from books;-- Book list
select * from records;-- Book Borrowing Record Table
-- Test the library storage process
-- 1、Successful business test for normal book borrowing：Student IDa = 1001;Borrowing numberb = 1;Number of books borrowedm = 10;
SET @state = 0;
CALL proc_borrow_book('1001',1,10,@state);
SELECT @state from DUAL;
-- 2、The test student number does not exist：Student IDa = 1008;Borrowing numberb = 1;Number of books borrowedm = 10;
SET @state = 0;
CALL proc_borrow_book('1008',1,10,@state);
SELECT @state from DUAL;
-- 3、Test book number does not exist：Student IDa = 1002;Borrowing numberb = 8;Number of books borrowedm = 10;
SET @state = 0;
CALL proc_borrow_book('1002',8,10,@state);
SELECT @state from DUAL;
-- 4、Test book inventory insufficient：Student IDa = 1002;Borrowing numberb = 2;Number of books borrowedm = 100;
SET @state = 0;
CALL proc_borrow_book('1002',2,100,@state);
SELECT @state from DUAL;

This is the end of this article about the creation and use of MySQL stored procedures. For more related content on the use of MySQL stored procedures, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!