关于mysql:存储过程

介绍

MySQL 从 5.0 版本开始反对存储过程和函数。存储过程和函数可能将简单的 SQL 逻辑封装在一起，应用程序毋庸关注存储过程和函数外部简单的 SQL 逻辑，而只须要简略地调用存储过程和函数即可。

一句话总结：存储过程是一组为了实现特定性能的 SQL 语句汇合。

关键字：封装 、 重用

根本语法

根本语法包含存储过程的创立、调用、查看、与删除。

创立存储过程：

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    -- SQL 语句
END ;

调用存储过程：

CALL 名称 ([参数]);

查看存储过程：

1. 查问某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

2. 查问指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';

删除存储过程：

DROP PROCEDURE [IF EXISTS] 存储过程名称；

简略的例子

后面提到了，存储过程是为了实现特定工作的 SQL 汇合，能够只有一条 SQL 语句，也能够有多条。即 存储过程能够非常简单也能够非常复杂，上面就通过一个简略的例子来阐明存储过程的根本应用。

创立一个查问用户表所有数据的存储过程：

CREATE PROCEDURE `p_user`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查问用户表的所有数据'
BEGIN
    SELECT * FROM tb_user;
END

调用该存储过程：

CALL p_user();

后果：

查看该存储过程：

SHOW CREATE PROCEDURE p_user;

删除该存储过程

DROP PROCEDURE IF EXISTS p_user;

通过这个简略的例子，咱们应用了一遍存储过程的根本语法，然而想要写出更为简单的存储过程，还须要理解“变量”、“分支”、“循环”、“参数”、“游标”等概念。

更简单的存储过程

除了“游标”，“变量”、“分支”、“循环”、“参数”等概念与其余后端语言的相似。

变量

在 MySQL 中，变量分为零碎变量、用户定义变量和局部变量。因为存储过程中更多的是用局部变量，所以这里只记录局部变量的应用。

变量的申明、赋值与输入：

1. 申明
DECLARE 变量名 变量类型 [DEFAULT ...] ;

2. 赋值
SET 变量名 := 值 ;    
SELECT 字段名 INTO 变量名 FROM 表名 ... ; # 将表中某个字段的值赋值给变量

3. 输入
SELECT 变量名;

例子，申明一个 name 变量，并将其赋值为申玉非：

CREATE PROCEDURE `p_var`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE name VARCHAR(50);
    SET name := "申玉非";
    SELECT name;
END

判断

在 MySQL 中，用于判断的语句有两种：if 和 case。

if 判断：

IF 条件 1 THEN
    .....
ELSEIF 条件 2 THEN       -- 可选
    .....
ELSE                    -- 可选
    .....
END IF;

例子，依据分数判断分数段，例如 71 分就是良好：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_if`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '测试 if 语句'
BEGIN
    DECLARE score INT DEFAULT 89;   # 申明 score 变量并赋值为 89
    DECLARE result VARCHAR(50);     # 申明 result 变量，留神这里的数据类型要定义长度。if score >= 0 && score < 60 then
        SET result := '不及格';
    ELSEIF score >= 60 && score < 80 then
        SET result := '良好';
    ELSEIF score >= 80 && score < 100 then
        SET result := '优良';
    ELSEIF score = 100 then
        SET result := '满分';
    ELSEIF score < 0 || score > 100 then
        SET result := '分数不非法';
    end if;

    SELECT result;
END

case 判断：

CASE case_value
    WHEN when_value1 THEN statement_list1
    [WHEN when_value2 THEN statement_list2] ...
    [ELSE statement_list]
END CASE;

含意：

• 当 case_value 的值为 when_value1 时，执行 statement_list1；
• 当值为 when_value2 时，执行 statement_list2；
• 否则就执行 statement_list。

例子，依据分数判断分数段:

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_case`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
    DECLARE score INT DEFAULT 89;
    DECLARE result VARCHAR(50);
    DECLARE my_score INT(11);
    SET my_score := FLOOR(score / 10);

    case 
    when my_score >= 0 && my_score < 6 then
        SET result := '不及格';
    when my_score = 6 then
        SET result := '及格';
    when my_score = 7 then
        SET result := '良好';
    when my_score = 8 then
        SET result := '优良';
    when my_score = 9 then
        SET result := '十分优良';
    when my_score = 10 then
        SET result := '满分';
    when my_score < 0 || my_score > 10 then
        SET result := '分数不非法，分数必须在 0~100 之间';
    END case;

    SELECT result;
END

参数

在 MySQL 中参数分为 3 类：

例子，传入两个数，返回两个数之和：

CREATE PROCEDURE `p_param`(
    IN `x` INT,     # 第一个传入的参数
    IN `y` INT,     # 第二个传入的参数
    OUT `s` INT     # 返回值
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '测试参数'
BEGIN
    SET s := x + y;
END

调用：

-- 定义用户变量 @result 来接管返回的数据, 用户变量能够不必申明

CALL p_param(1, 2, @s);
SELECT @s AS s;

# 后果为 3；

循环

在 MySQL 中有三种循环：while、repeat、loop。

while

while 循环是有条件的循环管制语句。满足条件后，再执行循环体中的 SQL 语句，语法如下：

-- 先断定条件，如果条件为 true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
    SQL 逻辑...
END WHILE;

例子，从 1 加到 10：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '从 1 加到 10'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    while n <= 10 DO
        SET sum := sum + n;
        SET n := n + 1;
    END while;

    SELECT sum;
END

# 调用
CALL p_sum(); 
# 后果：55

repeat

repeat 是有条件的循环管制语句, 当满足 until 申明的条件的时候，则退出循环，语法如下：

-- 先执行一次逻辑，而后断定 UNTIL 条件是否满足，如果满足，则退出。如果不满足，则持续下一次循环
REPEAT
    SQL 逻辑...
    UNTIL 条件
END REPEAT;

和 do...while 相似，但又不同：

• do...while 是 满足条件 则循环，否则退出；
• repeat 是 不满足条件 则循环，否则就退出。

例子，从 1 加到 10：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '从 1 加到 10'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    repeat
        SET sum := sum + n;
        SET n := n + 1;
        until n > 10
    END repeat;

    SELECT sum;
END

# 调用
CALL p_sum();
# 后果：55

留神： until语句前面没有“;”。

loop

loop 绝对简略，是没有条件的循环。然而配合 label 却能够实现“终止循环”和“跳过循环”，语法如下：

[begin_label:] LOOP
    SQL 逻辑...
END LOOP [end_label];

• 终止循环：LEAVE label;
• 跳过循环：ITERATE label;

例子：求 10 以内（包含 10）的偶数和：

CREATE PROCEDURE `p_sum`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '求 10 以内（包含 10）的偶数和'
BEGIN
    DECLARE n INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;

    s:loop                # s 是标签名
        if n > 10 then
            leave s;      # 终止循环
        END if;
    
        if n % 2 = 0 then
            SET sum := sum + n;
        END if;
        SET n := n + 1;
    END loop s;
    
    SELECT sum;
END

# 调用
p_sum();
# 后果：30

游标

后面提到，咱们能够把数据表中某个字段的值赋值给变量：

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

当初咱们想把查问到的后果存到另一张表中，光靠变量是不够的，因为一个变量只能存储一个数据。咱们就会想要是在 MySQL 中有数组就好了，咱们能够把查出来的数据保留到数组中，而后循环增加数据。很惋惜，在 MySQL 中没有数组，然而有一个相似的概念——游标。

游标（CURSOR）是用来 存储查问后果集的数据类型，在存储过程和函数中能够应用游标对后果集进行循环的解决。

和把大象塞进冰箱一样简略，应用游标分为四步：

• 申明游标；
• 关上游标；
• 读取游标；
• 敞开游标。

申明游标：

DECLARE 游标名称 CURSOR FOR 查问语句;

关上游标：

OPEN 游标名称;

读取游标：

FETCH 游标名称 INTO 变量 [, 变量];

敞开游标：

CLOSE 游标名称;

要实现把查问进去的后果保留到另外的表，光有游标还不够。咱们遍历数组的时候，什么时候进行遍历？遍历到数组的最初一个元素的时候。在大多数编程语言中，都有伎俩来统计数组的个数，把数组的个数作为终止循环的判断条件。然而在 MySQL 中，并没有这样的伎俩，也就意味着没有终止循环的条件，一旦越界，就会报错。

那这个问题是否是无解的呢？答案是否定的，在 MySQL 中，恰好就是利用这个报错来终止循环，也就是利用 MySQL 中的“条件处理程序”来打上这个补丁。

条件处理程序（Handler）能够用来定义在流程控制结构执行过程中遇到问题时相应的解决步骤。语法为如下：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action 的取值：CONTINUE: 继续执行以后程序
    EXIT: 终止执行以后程序

condition_value 的取值：SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING: 所有以 01 结尾的 SQLSTATE 代码的简写
    NOT FOUND: 所有以 02 结尾的 SQLSTATE 代码的简写
    SQLEXCEPTION: 所有没有被 SQLWARNING 或 NOTFOUND 捕捉的 SQLSTATE 代码的简写

游标和条件处理程序都介绍完了，上面举一个把性别为女的用户保留到 tb_user_women 表中的例子：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_cursor`(IN `ugender` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE uname VARCHAR(50);
    DECLARE uage INT(11);
    DECLARE u_cursor CURSOR FOR SELECT name,age FROM tb_user WHERE gender = ugender; 
    -- 申明条件处理程序：当 SQL 语句执行抛出的状态码为 02000 时，将敞开游标 u_cursor，并退出
    DECLARE exit handler FOR SQLSTATE   '02000' close u_cursor;

    -- 创立 tb_user_women 表
    DROP TABLE if EXISTS tb_user_women;
    CREATE TABLE if not exists tb_user_women(id INT(11) PRIMARY KEY  AUTO_INCREMENT,
        name VARCHAR(50),
        age INT(11)
    ); 

    -- 关上游标
    OPEN u_cursor;

    -- 读取游标
    while TRUE DO
        fetch u_cursor INTO uname, uage;
        INSERT INTO tb_user_women(name,age) VALUES(uname,uage);
    END while;
    -- 敞开游标
    close u_cursor;
END

# 调用
CALL p_cursor(0);

后果：

留神：申明自定义变量要写在申明游标后面。

至于 p_cursor 存储过程中，为什么要把条件处理程序中的 SQLSTATE 的值设置为 02000，能够参考 MySQL 官网文档——谬误状态码

优缺点

长处

• 存储过程能够一次编译屡次应用；
• 存储过程的安全性强；
• 能够缩小网络传输量。

毛病

• 可移植性差。存储过程不能跨数据库移植，比方在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其余数据库时都须要从新编写。
• 存储过程的版本治理很艰难。比方数据表索引发生变化了，可能会导致存储过程生效。咱们在开发软件的时候往往须要进行版本治理，然而存储过程自身没有版本控制，版本迭代更新的时候很麻烦。
• 它不适宜高并发的场景。高并发的场景须要缩小数据库的压力，有时数据库会采纳分库分表的形式，而且对可扩展性要求很高，在这种状况下，存储过程会变得难以保护，减少数据库的压力，显然就不实用了。

参考

1. MySQL 视图 / 存储过程 / 触发器
2. 存储过程与存储函数
3. 官网文档——谬误状态码