MySQL存储过程

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动

概述

存储过程是指一组预先编译好的SQL语句的集合，类似于批处理语句，向外暴露一个名字，需要时通过名字进行调用。

优点

提高代码的重用性，经编译创建并保存在数据库中，用户即通过指定存储过程的名字并给定参数(需要时)来调用执行。
简化操作
改善性能，减少编译次数并且减少了和数据库的连接次数，提高了效率

存储过程的使用

声明语句结束符

如自定义声明语句结束符为$$的语句，👉 DELIMITER $$

创建存储过程

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

参数列表包含三部分：参数模式参数名参数类型 如：IN username varchar（20）
参数模式：

1. `IN` :使用该模式修饰的参数可以作为输入，也就是该参数需要调用方传入值
2. `OUT`：该参数可以作为输出，该参数可以作为返回值
3. `INTOUT`：该参数既可以作为输入值又可以作为输出值，也就是该参数即需要传入值，又可以作为返回值🚨如果存储过程体仅仅只有一条语句，则`BEGIN END`可以省略，存储过程体中的**每条sql语句的结尾必须加分号。**

调用存储过程

语法：CAll 存储过程名（实参列表）

例如

less复制代码#调用只有出参没有入参的存储过程
CALL 存储过程名(@outParam1,@outParam2...)

#获取执行存储过程的输出参数
select @outParam1,@outParam2...

删除存储过程

🚦一次只能删除一个存储过程，不能同时删除多个

1	sql复制代码DROP PROCEDURE 存过程名

查看存储过程的信息

1	sql复制代码SHOW CREATE PROCEDURE 存储过程名

例子

不带入参的存储过程

sql复制代码#$代表结束的一个标记 
DELIMITER $ 
CREATE PROCEDURE mypro() 
BEGIN 
    INSERT INTO USER (user_id,real_name) VALUES(4,'bb'); 
END$ 
#调用存储过程 
CALL mypro()$ 
#删除存储过程 
DROP PROCEDURE IF EXISTS mypro

创建带IN模型的存储过程

定义变量：当在存储过程中定义变量时，局部变量的声明一定要放在存储过程体的开始。

语法：DECLARE 变量名称数据类型;

变量赋值
语法：SET 变量名 = 值

sql复制代码#创建存储过程实现用户是否已经注册 
CREATE PROCEDURE mypro2(IN user_id INT,IN PASSWORD VARCHAR(20)) 
BEGIN 
#声明并初始化一个参数
DECLARE result INT DEFAULT 0; 
    SELECT COUNT(*) INTO result FROM USER WHERE user.`user_id`=user_id AND user.`password`=PASSWORD; 
    SELECT IF(result,'已注册', '未注册'); 
END $ 
#调用存储过程 CALL mypro2(1,'12334')$;

创建到INOUT模型的存储过程

创建

sql复制代码DELIMITER $$

DROP PROCEDURE IF EXISTS `test2`$$

CREATE PROCEDURE `test2`(INOUT username VARCHAR(20))
BEGIN
	INSERT INTO USER(username) VALUE(username);
END$$

DELIMITER ;

执行

1
2
3

less复制代码SET @username='不喝奶茶的Programmer'
CALL test2(@username)
SELECT @username

结果

创建既带IN模型又带OUT模型的存储过程

创建

sql复制代码#存储过程的功能为插入一条用户信息，然后返回插入的用户的信息的唯一id
DELIMITER $$

CREATE PROCEDURE `test`(IN username VARCHAR(20),IN sex VARCHAR(20),OUT id INT)
BEGIN
    INSERT INTO USER (username,sex,birthday) VALUES(username,sex,NOW());
    SELECT LAST_INSERT_ID() INTO id;
END$$

DELIMITER ;