MySQL基础学习（创建存储过程）

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第5天，点击查看活动详情

1、创建存储过程

存储过程就是一条或者多条SQL语句的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。

创建存储过程的语句为 CREATE PROCEDURE。

创建存储函数的语句为CREATE FUNCTION。

调用存储过程的语句为CALL。

调用存储函数的形式就像调用MySQL内部函数一样。

DROP TABLE IF EXISTS t_student;
 
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);
 
INSERT INTO t_student VALUES(NULL,'大宇',22),(NULL,'小宇',20);

如上述，t_student表中的数据有两条。如果我们要分别查询出来这两条数据，显然就是根据ID来查询。查询出来了第一条数据以后，我们可能会去做其它的操作。等过两天，我们要查询另外一条记录的时候，可能又要再写一次这样的查询语句。

如果能像Java那样，提供一个ID，就能查询到指定ID的记录，这样就可以复用之前写的SQL语句。对于查询SQL语句，我们能不能像Java那样，封装这个查询学生的SQL呢？存储过程与存储函数应运而生。

定义一个根据ID查询学生记录的存储过程。

DROP PROCEDURE IF EXISTS getStuById;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id'  -- 提示信息
SQL SECURITY DEFINER  -- DEFINER指明只有定义此SQL的人才能执行，MySQL默认也是这个
BEGIN
   SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加
​
DELIMITER ;  -- 重新定义存储过程结束符为分号

语法： CREATE PROCEDURE sp_name(定义输入输出参数) [ 存储特性 ] BEGIN SQL语句； END

IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数。sp_name为存储过程的名字。

如果此存储过程没有任何输入输出，其实就没什么意义了，但是sp_name()的括号不能省略。

查看刚才创建的存储过程。

SHOW PROCEDURE STATUS LIKE 'g%'

下面是调用存储过程。对于存储过程提供的临时变量而言，MySQL规定要加上@开头。

#study 是当前数据库名称
 
CALL study.getStuById(1,@name,@age);
 
SELECT @name AS stuName,@age AS stuAge;
​
-- =================================
CALL getStuById(2,@name,@age);
 
SELECT @name AS stuName,@age AS stuAge;

这样做的好处是，如果一段较为复杂的SQL语句，我们可能过了几天再去写它，又费事又费力。存储过程可以封装我们写过的SQL，在下次需要调用它的时候，直接提供参数并指明查询结果输出到哪些变量中即可。

提示：如果存储过程一次查询出两个记录，将会提示出错。[Err] 1172 - Result consisted of more than one row

所以需要在存储过程的SQL后面加上LIMIT 1。从位偏移量为0的，即从查询结果的第一条数据开始，查询一条记录。

2、创建存储函数

存储函数与存储过程本质上是一样的，都是封装一系列SQL语句，简化调用。

我们自己编写的存储函数可以像MySQL函数那样自由的被调用。

DROP FUNCTION IF EXISTS getStuNameById;
 
DELIMITER //
CREATE FUNCTION getStuNameById(stuId INT)  -- 默认是IN，但是不能写上去。stuId视为输入的临时变量
RETURNS VARCHAR(255)   -- 指明返回值类型
RETURN  (SELECT name FROM t_student WHERE id = stuId); //  -- 指明SQL语句,并使用结束标记。注意分号位置
DELIMITER ;

这里遇到一个问题：

在执行上述sql语句的时候，会报错信息为：mysql创建function 报错误1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in

解决方法：

执行这条sql就可以了：

set global log_bin_trust_function_creators=1;

使用存储函数。

SELECT getStuNameById(1);

提示：在RETURN 语句后面，有趣的是，分号在SQL语句的外面。如果不加分号，查询结果居然查询出两条记录，很奇怪。

从上述存储函数的写法上来看，存储函数有一定的缺点。首先与存储过程一样，只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果，而存储过程能够指明多列数据作为结果。