Mysql存储过程必知必会

·  阅读 1061
Mysql存储过程必知必会

「这是我参与11月更文挑战的第14天,活动详情查看:2021最后一次更文挑战」。

存储过程

为什么使用存储过程

  • 通过把 “某个处理” 封装在一个易用的单元中,可以简化复杂的操作。
  • 由于不要求反复建立一系列处理步骤,因而保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,那么只需要更改存储过程的代码。使用它的人员甚至不需要知道 这些变化。
  • 因为存储过程通常以编译过的形式存储,所以 DBMS 处理命令所需的工作量少,提高了性能。

简单来说,使用存储过程有三个主要的好处:简单、安全、高性能

创建存储过程

我们来看一个简单的存储过程例子,对 草帽海贼团 人数进行计数。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE personCount()
    -> BEGIN
    ->     SELECT COUNT(*) AS num_person
    ->     FROM one_piece
    ->     WHERE pirates = '草帽海贼团';
    -> END$$
复制代码

在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为 $$,使得过程体中使用的分号被直接传递到服务器,而不会被 Mysql 解释。

使用 CALL 存储过程名(参数) 来调用存储过程。

mysql> DELIMITER ;
mysql> CALL personCount();
复制代码

存储过程体

存储过程体包含了在过程调用时必须执行的语句,例如:dmlddl 语句,if-then-elsewhile-do 语句、声明变量的 declare 语句等。

过程体格式:以 begin 开始,以 end 结束(可嵌套)

BEGIN
  BEGIN
    BEGIN
      statements; 
    END
  END
END
复制代码

注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的 begin-end 块(又叫做复合语句 compound statement ),则不需要分号。

存储过程参数

  存储过程可以有0个或多个参数,用于存储过程的定义。

3种参数类型:

  • IN (输入参数):表示调用者向过程传入值(传入值可以是字面量或变量)
  • OUT (输出参数):表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
  • INOUT (输入输出参数):既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

IN输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE in_test(IN p_in INT)
    -> BEGIN
    ->     SELECT p_in;    -- 第一次查询
    ->     SET p_in = 2;   -- 修改p_in的值
    ->     SELECT p_in;    -- 第二次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_in = 1;

mysql> CALL in_test(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| p_in |
+------+
|    2 |
+------+

mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
复制代码

虽然 p_in 在存储过程中被修改,但并不影响 @p_in 的值,因为 in_test 只接受输入参数,并不输出参数,所以相当于在函数内改变,但并未将这个值输出给 @p_in

OUT输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE out_test(OUT p_out INT)
    -> BEGIN
    ->     SELECT p_out;    -- 第一次查询
    ->     SET p_out = 2;   -- 修改p_out的值
    ->     SELECT p_out;    -- 第二次查询
    -> END$$

mysql> DELIMITER ;

mysql> SET @p_out = 1;

mysql> CALL out_test(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+

+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
复制代码

第一个返回结果为 NULL 是因为 OUT 是向调用者输出参数,不接收输入的参数,所以第一次查询时 p_out 还未赋值,所以是 NULL 。最后 @p_out 变量的值变为2是因为调用了 out_test 存储过程,输出参数,改变了 p_out 变量的值。

INOUT输入输出参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE inout_test(INOUT p_inout INT)
    -> BEGIN
    ->     SELECT p_inout;    -- 第一次查询
    ->     SET p_inout = 2;   -- 修改p_inout的值
    ->     SELECT p_inout;    -- 第一次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_inout = 1;

mysql> CALL inout_test(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
复制代码

调用 inout_test 存储过程,既接受了输入的参数,也输出参数, @p_inout 的值被改变。

删除存储过程

使用 DROP PROCEDURE 来删除存储过程。

mysql> DROP PROCEDURE in_test;
复制代码


这就是今天要分享的内容,微信搜 Python新视野,每天带你了解更多有用的知识。更有整理的近千套简历模板,几百册电子书等你来领取哦!

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改