mysql stored procedure

1, Stored procedure

Meaning: a set of precompiled SQL statements to understand batch processing statements
1. Improve code reusability
2. Simplify operation
3. The compilation times are reduced, the connection times with the database server are reduced, and the efficiency is improved

2, Creation of stored procedures

Syntax:

CREATE PROCEDURE Stored procedure name(parameter list)
BEGIN

	Stored procedure body (a of combinatorial method) SQL Statement)
END

be careful:
1. The parameter list consists of three parts
Parameter mode parameter name parameter type
give an example:
in stuname varchar(20)

Parameter mode:
In: this parameter can be used as input, that is, this parameter needs the value passed in by the caller
out: this parameter can be used as output, that is, it can be used as return value
inout: this parameter can be used as both input and output, that is, this parameter needs both incoming value and return value

2. If the stored procedure body has only one sentence, begin end can be omitted
The end of each sql statement in the stored procedure body requires a semicolon.
The end of the stored procedure can be reset using delimiter
Syntax:
delimiter end tag
Case:
delimiter $

3, Call of stored procedure

CALL stored procedure name (argument list);

4, Stored procedure case

1. Empty parameter list
Case: five records inserted into the admin table

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $

2. Create a stored procedure with in mode parameters
#Case 1: create a stored procedure to query the corresponding male god information according to the goddess name

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
	

END $
#call
CALL myp2('Liuyan')$

Case 2: create a stored procedure implementation, and check whether the user logs in successfully

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#Declare and initialize
	
	SELECT COUNT(*) INTO result#assignment
	FROM admin
	WHERE admin.username = username
	AND admin.password = PASSWORD;
	
	SELECT IF(result>0,'success','fail');#use
END $

#Call
CALL myp3('zhang Fei ',' 8888 ')$

3. Create a stored procedure for out mode parameters
Case 1: return the corresponding male god name according to the entered goddess name

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname
	FROM boys bo
	RIGHT JOIN
	beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName ;
	
END $

Case 2: return the corresponding male god name and charm value according to the entered goddess name

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname ,boys.usercp INTO boyname,usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName ;
	
END $

#Call
CALL myp7('xiao Zhao ', @ name,@cp)$
SELECT @name,@cp$

4. Create a stored procedure with inout mode parameters
Case 1: pass in two values a and b, and finally both a and b are doubled and returned

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

#call
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

5, Delete and view stored procedures

Syntax: drop procedure stored procedure name
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

Viewing stored procedure information
DESC myp2;×
SHOW CREATE PROCEDURE myp2

Tags: Database MySQL SQL

Posted on Wed, 22 Sep 2021 09:10:59 -0400 by fr34k2oo4