百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

MySQL存储过程了解一下

2022-02-22 分类：野生技术 阅读(262) 评论(0)

简介

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

存储过程的使用

创建存储过程

语法：

CREATE PROCEDURE 存储过程名( IN|OUT|INOUT 参数名 数据类型 , ...)
BEGIN
...
END;

MySQL存储过程的参数类型：

  • IN，表示存储过程的输入参数，该参数的值将会传递给存储过程，在存储过程中可以对该参数进行修改，但是在存储过程返回时，该参数值不会被返回，相当于在存储过程中对该参数的修改对调用者来说是不可见的。

  • OUT，表示存储过程的输入参数，该参数的值会在存储过程中初始化为NULL，当存储过程返回时，该值也会被返回，调用者可以看到被修改后的值。

  • INOUT，表示存储过程的输入输出参数，该参数由调用者初始化，在存储过程中的做的任何更改都会被返回，调用者可以看到修改后的值。

    • 存储过程创建示例：

    创建存储过程student_procedure，student_procedure有一个输入参数age和一个输出参数num，查询tb_student表学生年龄大于等于输入参数age的人数，并将人数设置到num。

    CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
    BEGIN
    SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
    END;

    MySQL命令行创建存储过程：

    如果是在MySQL命令行创建存储过程，则需要临时的修改语句分隔符，因为MySQL默认语句分隔符是;，会使存储过程中的语句被直接解析而导致语法错误。

    -- 设置//为语句分隔符
    mysql> DELIMITER //

    mysql> CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
    -> END;
    -> //
    Query OK, 0 rows affected

    --恢复为原来的分隔符
    mysql> DELIMITER ;

    调用存储过程

    tb_student表数据：

    +----+------+-----+-------------+-----------+----------+
    | id | name | age | phone | address | class_id |
    +----+------+-----+-------------+-----------+----------+
    | 1 | 小明 | 18 | 188xxxx1234 | xxxxxxxxx | 1 |
    | 2 | 小米 | 28 | 188xxxx1234 | xxxxxxxxx | 2 |
    | 3 | 小看 | 28 | 188xxxx1234 | xxxxxxxxx | 3 |
    | 4 | 小阿 | 38 | 188xxxx1234 | xxxxxxxxx | 3 |
    | 5 | 小鬼 | 48 | 188xxxx1234 | xxxxxxxxx | 3 |
    +----+------+-----+-------------+-----------+----------+

    调用存储过程，查询年龄大于38的学生人数：

    -- 调用存储过程
    mysql> CALL student_procedure(38, @num);
    Query OK, 1 row affected

    -- 查看返回结果
    mysql> select @num;
    +------+
    | @num |
    +------+
    | 2 |
    +------+

    查看存储过程的定义

    语法：

    SHOW CREATE PROCEDURE proc_name;

    如查看student_procedure的定义：

    mysql> SHOW CREATE PROCEDURE student_procedure;
    +-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
    +-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | student_procedure | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `student_procedure`(IN age TINYINT, OUT num INT) BEGIN SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;END | utf8 | utf8_general_ci | utf8_general_ci |
    +-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    修改存储过程

    语法：

    ALTER PROCEDURE proc_name [characteristic ...]

    characteristic: {
    COMMENT \'string\'
    | LANGUAGE SQL
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    }

    ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程。

    删除存储过程

    语法：

    DROP PROCEDURE [ IF EXISTS ] proc_name

    如删除student_procedure：

    mysql> DROP PROCEDURE student_procedure;
    Query OK, 0 rows affected

    mysql> CALL student_procedure(38, @num);
    1305 - PROCEDURE student_procedure does not exist

    流程控制语句

    IF语句

    语法：

    IF 判断条件 THEN 处理语句
    [ELSEIF 判断条件 THEN 处理语句]...
    [ELSE 处理语句]
    END IF

    示例：

    CREATE PROCEDURE test1(IN sex TINYINT)
    BEGIN
    IF sex=1 THEN SET @sex=\'男\';
    ELSEIF sex=0 THEN SET @sex=\'女\';
    ELSE SET @sex=\'未知\';
    END IF;
    END;

    CASE语句

    CASE语句有两种写法：

    语法1：

    CASE 值
    WHEN 值1 THEN 处理语句
    [WHEN 值2 THEN 处理语句]...
    [ELSE 处理语句]
    END CASE

    语法2：

    CASE
    WHEN 条件判断 THEN 处理语句
    [WHEN 条件判断 THEN 处理语句] ...
    [ELSE 处理语句]
    END CASE

    示例：

    -- 写法1
    CREATE PROCEDURE test2(IN sex TINYINT)
    BEGIN
    CASE sex
    WHEN 1 THEN SET @sex=\'男\';
    WHEN 0 THEN SET @sex=\'女\';
    ELSE SET @sex=\'未知\';
    END CASE;
    END;

    -- 写法2
    CREATE PROCEDURE test3(IN sex TINYINT)
    BEGIN
    CASE
    WHEN sex=1 THEN SET @sex=\'男\';
    WHEN sex=0 THEN SET @sex=\'女\';
    ELSE SET @sex=\'未知\';
    END CASE;
    END;

    LOOP语句

    LOOP循环是一个死循环，一般情况需要配合LEAVE语句和ITERATE语句使用，LEAVE语句表示跳出该循环（类似Java中的break），ITERATE语句表示跳出本次循环（类似Java中的continue）。

    语法：

    [别名:]LOOP
    处理逻辑
    END LOOP [别名]

    示例：

    CREATE PROCEDURE test4()
    BEGIN
    SET @num=0;
    add_num:LOOP
    SET @num=@num+1;
    IF @num=10 THEN LEAVE add_num;
    END IF;
    END LOOP add_num;
    END;

    REPEAT语句

    REPEAT语句是自带条件判断的循环语句，每次语句执行完毕后，会对条件进行判断，如果为true则退出循环，否则继续循环。（类似Java中的do while循环）

    语法：

    [别名:] REPEAT
    处理语句
    UNTIL 条件判断
    END REPEAT [别名]

    示例：

    CREATE PROCEDURE test5()
    BEGIN
    SET @num=0;
    add_num:REPEAT
    SET @num=@num+1;
    UNTIL @num=10 END REPEAT add_num;
    END;

    WHILE语句

    WHILE语句也是自带条件判断的循环，和REPEAT语句的区别在于WHILE语句会先进行条件判断，当条件判断为true时才继续执行循环中的语句，为false则直接退出循环。（类似于Java中的while循环）

    语法：

    [别名:] WHILE 条件判断 DO
    处理逻辑
    END WHILE [别名]

    示例：

    CREATE PROCEDURE test6()
    BEGIN
    SET @num=0;
    add_num:WHILE @num<10 DO
    SET @num=@num+1;
    END WHILE add_num;
    END;

    游标

    游标是用来逐行处理某个查询的结果集。

    游标的声明必须出现在HANDLER声明之前声明，变量和条件声明之后声明。

    创建游标：

    DECLARE 游标名称 CURSOR FOR sql查询;

    打开游标：

    OPEN 游标名称;

    使用游标：

    FETCH 游标名称 INTO 变量1 [,变量2]...

    将结果集中的数据保存到对应的变量当中去，游标第一次使用时默认读取结果集中的第一行，一般配合循环语句逐行处理整个结果集。

    关闭游标：

    CLOSE 游标名称;

    CLOSE释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭。游标关闭后不能使用，如果需要使用则需要重新打开游标。

    示例

    查询tb_student表，将所有学生名称连接成一个字符串设置到变量@name_Str中。

    CREATE PROCEDURE test7()
    BEGIN
    -- 声明局部变量student_name，用于接收数据集中的数据
    DECLARE student_name VARCHAR(10);
    -- 声明局部变量done，用于判断是否退出循环，默认值为FALSE
    DECLARE done INT DEFAULT FALSE;
    -- 声明游标my_cursor
    DECLARE my_cursor CURSOR FOR SELECT `name` FROM tb_student;
    -- 声明continue handler句柄，当出现SQLSTATE \'02000\'时将done设置为TRUE
    DECLARE CONTINUE HANDLER FOR SQLSTATE \'02000\' SET done = TRUE;

    -- 设置用户变量@name_Str为空字符串
    SET @name_Str=\'\';
    -- 打开游标
    OPEN my_cursor;
    -- 开始LOOP循环
    concat_name:LOOP
    -- 将数据集中的一行数据存放到指定的变量中
    FETCH my_cursor INTO student_name;
    -- 判断是否退出循环
    IF done THEN LEAVE concat_name;
    END IF;
    -- 连接学生名称字符串
    SET @name_Str = CONCAT(@name_Str,student_name);
    END LOOP concat_name;
    -- 关闭游标
    CLOSE my_cursor;
    END;

    结果：

    mysql> call test7();
    Query OK, 0 rows affected

    mysql> select @name_Str;
    +----------------------+
    | @name_Str |
    +----------------------+
    | 小明小米小看小阿小鬼 |
    +----------------------+

    关于SQLSTATE \'02000\'

    在使用游标时，可以通过FETCH将数据集中的数据保存到变量中进行处理，但是当整个数据集已经FETCH结束的时候，再去FETCH就会抛异常：

    1329 - No data - zero rows fetched, selected, or processed

    该异常对应的SQLSTATE为02000，所以需要指定句柄捕获这种异常情况来给标志赋值，后续就可以通过这个标志来判断数据集循环读取结束。

    异常信息详见：Error Reference

    来源：https://www.cnblogs.com/seve/p/14634319.html
    图文来源于网络，如有侵权请联系删除。

    未经允许不得转载：百木园 » MySQL存储过程了解一下
    标签：

    相关推荐

    • 暂无文章

    热门文章