What are the tips for using mysql stored procedures?

Mysql stored procedure usage tips include: 1. Create a stored procedure with in mode parameters; 2. Create a stored procedure with out mode parameters; 3. Create a stored procedure with inout mode parameters.

Mysql stored procedure usage tips are:

Definition

A set of pre-compiled SQL statements, understood as batch statements, similar to the method in java

1. Improve code reusability

2. Simplify operations

3. Reduce the number of compilations and connections to the database server, improving efficiency

Creation syntax

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END

The parameter list contains three parts

Parameter mode Parameter name Parameter type

Example:

in stuname varchar(20)

Parameter mode:

• in: This parameter can be used as input, that is, the parameter requires the caller to pass in a value

• out: This parameter can be used as output, that is, this parameter can As a return value

• inout: This parameter can be used as both input and output, that is, the parameter needs to pass in a value and can return a value

• If the stored procedure body only has one sentence, begin end can be omitted.

• The end of each SQL statement in the stored procedure body must be preceded by a semicolon.

• The end of the stored procedure can be reset using delimiter

Syntax:

delimiter 结束标记

Case:

delimiter $

Calling syntax

CALL 存储过程名(实参列表);

Empty parameter list

Insert five records 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 $
#调用
CALL myp1()$

Create a stored procedure with in mode parameters

创建存储过程实现 根据女神名，查询对应的男神信息
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 myp2('柳岩')$

Create a stored procedure implementation, whether the user logs in successfully

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888')$

Create out mode Parameter stored procedure

Returns the corresponding male god name based on the input 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 $

Returns the corresponding male god name and charm value based on the input 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 myp7('小昭',@name,@cp)$
SELECT @name,@cp$

Create a stored procedure with inout mode parameters

Pass in two values ​​​​a and b, and eventually both a and b are doubled and return

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

Delete stored procedure

drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

View stored procedure information

DESC myp2;
SHOW CREATE PROCEDURE  myp2;

The above is the detailed content of What are the tips for using mysql stored procedures?. For more information, please follow other related articles on the PHP Chinese website!