开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第6天，点击查看活动详情
1、定义变量
如果希望MySQL执行批量插入的操作，那么至少要有一个计数器来计算当前插入的是第几次。
这里的变量是用在存储过程中的SQL语句中的，变量的作用范围在BEGIN .... END 中。
没有DEFAULT子句，初始值为NULL。
定义变量的操作
DECLARE name,address VARCHAR; -- 发现了吗，SQL中一般都喜欢先定义变量再定义类型，与Java是相反的。
DECLARE age INT DEFAULT 20; -- 指定默认值。若没有DEFAULT子句，初始值为NULL。
为变量赋值
SET name = 'jay'; -- 为name变量设置值
DECLARE var1,var2,var3 INT;
SET var1 = 10,var2 = 20; -- 其实为了简化记忆其语法，可以分开来写
-- SET var1 = 10;
-- SET var2 = 20;
SET var3 = var1 + var2;
使用变量实例。如下表，在做了去除主键约束后，我又添加了一条id=1的数据。现在希望查询出id为1的记录的数量。
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;
显然，在存储过程中的变量，可以直接与输出变量进行相应的计算。本例直接把sCount这个变量的值赋值到输出中。
2、定义条件与定义处理程序
定义条件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。（小写：contiue）EXIT：表示遇到错误退出，默认的策略就是EXIT。undo：遇到错误之后撤回之前的操作，mysql目前不支持。
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'; -- 为指定的条件设置处理程序
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
);
现通过存储过程，为这张表插入数据。因为id属性有主键约束，所以不能插入相同的id。
DROP PROCEDURE IF EXISTS insertStu;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定输出结果
BEGIN
DECLARE flag INT(11) DEFAULT 0; -- 指定变量为0
DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000'; -- 主键约束的错误值
DECLARE CONTINUE HANDLER FOR primary_key_limit SET @info = -1; -- 设计如果出现错误，@info将会被设置为 -1
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，设置主键为1
SET flag = 1; -- 普通变量设值为1
SET result = flag; -- 如果下面的SQL执行出现异常，那么就退出，只有上面的SQL生效。将普通变量的值给输出
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，设置主键为1
SET flag = 2; -- 如果处理程序是EXIT，那么就不会执行到这一步了
SET result = flag; -- 将普通变量的值给输出
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号
CONTIUE是指遇到错误忽略，继续执行下面的SQL。因为是CONTINUE来处理程序，所以遇到错误将会继续执行。
另外，第二次插入记录，因为违反了主键约束，所以插入失败，但是存储过程仍然继续执行完毕。
CALL insertStu(@result);
SELECT @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
再次查看t_student表，只插入了一条记录，但是所有的存储过程都执行完毕了。
现在，重新执行下面的SQL。先重新建表，再将处理程序的处理策略换为EXIT：在执行存储过程中遇到了错误，那么就立即退出。
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 insertStu;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定输出结果
BEGIN
DECLARE flag INT(11) DEFAULT 0; -- 指定变量为0
DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000'; -- 主键约束的错误值
DECLARE EXIT HANDLER FOR primary_key_limit SET @info = -1; -- 设计如果出现错误，@info将会被设置为 -1
INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); --
SET flag = 1; -- 普通变量设值为1
SET result = flag; -- 如果下面的SQL执行出现异常，那么就退出，只有上面的SQL生效。将普通变量的值给输出
INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); --
SET flag = 2; -- 如果处理程序是EXIT，那么就不会执行到这一步了
SET result = flag; -- 将普通变量的值给输出
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号
CALL insertStu(@result);
SELECT @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
在声明条件后并遇到相关的错误条件后，那就看应该怎么处理。如果是EXIT，那么存储过程只生效到错误处的上一条SQL。如果是CONTINUE，那么将会忽略掉执行错误的SQL，继续执行下面的其它存储过程。
3、流程控制
3.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;
3.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.3、LOOP
LOOP用于重复执行SQL。LEAVE 用于退出循环。
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,' mysql',22);
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(15);
3.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 循环次数;
