一、含义
存储过程和函数就是一组预先编译好的SQL语句的集合,可以理解成批处理语句。
二、好处
- 提高了代码的重用性。
- 简化操作步骤。
- 减少了编译次数,并且减少了和数据库服务器的连接次数,提高了效率。
三、使用
(一)创建
1. 语法:
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
2. 参数列表:
参数列表包含三个部分:参数模式
、参数名
、参数类型
,示例如下。
IN stuname VARCHAR(20)
3. 参数模式:
参数模式分为三种
参数模式 | 描述 |
---|---|
IN | 该参数作为输入,也就是该参数需要调用方传入值 |
OUT | 该参数作为输出,也就是该参数可以作为返回值 |
INOUT | 该参数既可以作为输入又可以作为输出。也就是该参数需要传入值,又可以返回值 |
4. 注意事项:
- 如果存储过程仅仅只有一句话,那么
BEGIN..END
可以省略。 - 存储过程体中每条SQL语句的结尾要求加分号。
- 存储过程的结尾可以使用
DELIMITER
重新设置,如:DELIMITER 结束标记
(二)使用
(1)空参调用方式
# 声明 结束标记
DELIMITER $
# 编写存储过程
CREATE PROCEDURE my_pro()
BEGIN
INSERT INTO admin(`name`, `password`) VALUES('job', '123'), ('tom', '123');
END $
# 调用存储过程
CALL my_pro()$
(2)带IN模式调用方式
示例: 使用存储过程实现判断用户是否登录成功,存在显示登录成功否则显示登录失败。
# 声明结束标记
DELIMITER $
# 编写存储过程体
CREATE PROCEDURE check_login_status(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
# 声明
DECLARE user_login_status INT DEFAULT 0;
SELECT COUNT(*) INTO user_login_status
FROM admin
WHERE admin.username = username AND admin.password = password;
SELECT IF(user_login_status, '登录成功', '登录失败');
END $
# 调用存储过程
CALL check_login_status('小明', '8888')$
(3)带OUT模式调用方式
示例1(单个OUT
的方式): 根据传入的女神名,返回对应的男神名?
# 声明结束标记
DELIMITER $
# 编写存储过程体
CREATE PROCEDURE getBoyNameByBeaty(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.`boyName` INTO boyName
FROM beauty b
JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $
# 设置用户变量
SET @boyName = ""$
# 调用存储过程
CALL getBoyNameByBeaty("热巴", @boyName)$
# 查询变量值
SELECT @boyName$
示例2(多个OUT
的方式): 根据传入的女神名,返回对应的男神名和魅力值?
CREATE PROCEDURE getBoyInfo(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT boyCP INT)
BEGIN
SELECT bo.`boyName`, bo.`userCP` INTO boyName, boyCP
FROM beauty b
JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $
SET @boyName = ""$
SET @boyCP = 0$
CALL getBoyInfo("热巴", @boyName, @boyCP)$
SELECT @boyName, @boyCP$
(4)带INOUT模式调用方式
示例:
CREATE PROCEDURE getDubleVar(INOUT a INT, INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2;
END $
SET @m = 10$
SET @n = 20$
CALL getDubleVar(@m, @n)$
SELECT @m, @n$
四、删除存储过程
1. 语法:
DROP PROCEDURE 存储过程名1 [, 存储过程名2];
2. 示例:
# 删除单个
DROP PROCEDURE p1;
# 删除多个
DROP PROCEDURE p1, p2. p3, ...;
五、查看存储过程
1. 语法:
SHOW CREATE PROCEDURE 存储过程名;