《6》Mysql存储过程与存储函数详解

---

前言

网上关于存储过程的资料，繁多复杂，理论通篇。本章通过实际例子，先展示一下如何使用存储过程、存储函数，再讲细节。

---

一、Mysql创建存储过程

  存储过程就是一条或者多条SQL语句的集合。
  创建存储过程的语句为 CREATE PROCEDURE，创建存储函数的语句为CREATE FUNCTION。
  调用存储过程的语句为CALL。
  调用存储函数的形式就像调用MySQL内部函数一样。

1.准备数据

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查询学生信息，我们每次查询都要写一次select查询语句。
  是否能像Java那样，提供一个ID，就能查询到指定ID的记录呢？也就是封装这个查询学生的SQL，存储过程与存储函数应运而生。

2.创建存储过程

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()的括号不能省略。

3.查看存储过程

SHOW PROCEDURE STATUS LIKE 'g%' #查看存储过程

4.调用存储过程

CALL test.getStuById(1,@name,@age); #test 是当前数据库名称，对于存储过程提供的临时变量而言，MySQL规定要加上@开头。
SELECT @name AS stuName,@age AS stuAge;

二、Mysql创建存储函数

  存储函数与存储过程本质上是一样的，都是封装一系列SQL语句，简化调用。存储函数有返回值，存储过程没有返回值。
  我们自己编写的存储函数可以像MySQL函数那样自由的被调用。

set global log_bin_trust_function_creators=TRUE; 

DROP FUNCTION IF EXISTS getStuNameById;

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

SELECT getStuNameById(1);

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

三、定义变量

  这里的变量是用在存储过程中的SQL语句中的，变量的作用范围在BEGIN … END 中。

1.定义变量的操作

DECLARE name,address VARCHAR;  -- 发现了吗，SQL中一般都喜欢先定义变量再定义类型，与Java是相反的。
DECLARE age INT DEFAULT 20; -- 指定默认值。若没有DEFAULT子句，初始值为NULL。

2.为变量赋值

SET name = 'jay';  -- 为name变量设置值

DECLARE var1,var2,var3 INT;
SET var1 = 10,var2 = 20;  -- 其实为了简化记忆其语法，可以分开来写
-- SET var1 = 10;
-- SET var2 = 20;
SET var3 = var1 + var2;

3.定义变量的例子

DROP PROCEDURE IF EXISTS contStById;
 
DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE contStById(IN sid INT(11),OUT result INT(11)) -- 定义输入变量
BEGIN
    DECLARE sCount INT;
    SELECT COUNT(*) INTO sCount FROM t_student WHERE id = sid;
    SET result = sCount; -- 用变量为输出结果设值
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号
 
CALL contStById(1,@result);
SELECT @result;

四、定义条件与定义处理程序

定义条件CONDITION：定义在执行存储过程中的SQL语句的时候，可能出现的问题。

定义处理程序HANDLER：定义遇到了指定问题应该如何处理，避免存储过程执行异常而停止。

  定义条件与定义处理语句程序的位置应该在BEGIN … END 之间。

定义条件的语法：DECLARE condtion_name CONDTION FOR 错误码||错误值

  错误码可以视为一个错误的引用，比如404，它代表的就是找不到页面的错误，它的错误值可以视为NullPointerException。

DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; -- 错误值
DECLARE command_not_allowed CONDITION FOR 1148;  -- 错误码

定义处理程序语法：DECLARE HANDLER_TYPE HANDLER FOR condtion_name sp_statement；
  MySQL定义了三种HANDLER_TYPE ，CONTIUE是指遇到错误忽略，继续执行下面的SQL。EXIT表示遇到错误退出，默认的策略就是EXIT。
condtion_name可以是我们自己的定义的条件，也可以是MySQL内置的条件，比如SQLWARNING ，匹配01开头的错误代码。sp_statement指遇到错误的时候，需要执行的存储过程或存储函数。

DECLARE CONTINUE HANDLER FOR SQLSATTE '42S02' SET @info = 'NO_SUCH_TABLE'; -- 忽略错误值为42S02的SQL异常
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR_OCCUR'; -- 捕获SQL执行异常并输出信息
 
DECLARE no_such_table CONDITION FOR 1146; -- 为错误码为1146的错误定义条件
DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'no_such_table'; -- 为指定的条件设置处理程序

五、流程控制的使用

1.IF语句的使用

DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     IF val IS NULL
        THEN SET result = 'IS NULL';
        ELSE SET result = 'IS NOT NULL';
     END IF;
END //
DELIMITER ;
 
CALL testIf(@result);
SELECT @result;

2.CASE语句

DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     CASE val IS NULL
         WHEN 1 THEN SET result = 'val is true';
         WHEN 0 THEN SET result = 'val is false';
         ELSE SELECT 'else';
     END CASE;
END //
DELIMITER ;
 
CALL testCase(@result);
SELECT @result;

3.LOOP

DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(OUT result VARCHAR(255))
BEGIN
     DECLARE id INT DEFAULT 0;
     add_loop:LOOP
	   SET id = id + 1;
	   IF id>10 THEN LEAVE add_loop; -- 可在此处修改成批量插入
	   END IF;
     SET result = id;
     END LOOP add_loop;
END //
DELIMITER ;
 
CALL testLoop(@result);
SELECT @result;

下面是一个批量插入的例子

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
);
 
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(IN columnCount INT(11))
BEGIN
     DECLARE id INT DEFAULT 0;
     add_loop:LOOP
	SET id = id + 1;
	IF id>columnCount THEN LEAVE add_loop;
	END IF;
       INSERT INTO t_student(id,name,age) VALUES(id,'dayu',22);
     END LOOP add_loop;
END //
DELIMITER ;
 
CALL testLoop(15);

4.WHILE

DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
 
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
   DECLARE i INT DEFAULT 0 ; -- 定义变量
   WHILE i < myCount DO  -- 符合条件就循环
       -- 核心循环SQL;  
       SET i = i + 1 ; -- 计数器+1
   END WHILE;       -- 当不满足条件，结束循环  --分号一定要加！
   SET result = i;  -- 将变量赋值到输出
END //


CALL testWhile(10,@result);
SELECT @result AS 循环次数;

---

总结

若有帮助，请点赞+关注！