MySQL:存储过程

  本篇博客主要介绍MySQL中的存储过程及触发器的用法。

1 存储过程

  存储过程是一组为完成特定功能的SQL语句，类似一门程序设计语言，也包括数据类型、流程控制、输入和输出和它自己的函数库。存储过程举例如下，这个存储过程可以将score_copy表中student_id为s_id的记录删除。

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc_1`(IN s_id INTEGER)
BEGIN
 DELETE FROM score_copy
 WHERE	student_id=s_id;
END

Tips:以上代码来自navicat。definer部分由工具自动补全。另外，在navicat中可以使用可视化的方式运行存储过程(过程这里省略)，但在原MySQL中，必须使用call命令来运行存储过程。上述存储过程的运行代码如下：

call Proc_1(24);//24作为实参传递给变量s_id

2 基本语法

  MySQL中存储过程的完整语法如下。下面依次介绍部分。

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

2.1 definer部分

  definer部分主要用于指定存储过程的所有者。指定definer可以带来以下几个方面的好处：

• 对于特定的存储过程，只有其创建者和所有者才能执行该存储过程(若未使用definer指定创建者，默认当前用户为创建者);
• 对于特定的存储过程，非所有者必须通过授权才能使用该存储过程，可以提高安全性，并限制关键操作的访问;

2.2 proc_parameter部分

  proc_parameter部分可以指定存储过程的参数。MySQL中存储过程中的参数类型主要包括以下三种：

• IN类型：输入参数。表示调用者向存储过程传入值。这个值可以是字面量也可以是变量。
• OUT类型：输出参数。表示存储过程向调用者传出值，可以返回多个值传出值只能是变量。
• INOUT类型：输入输出参数:既表示调用者向过程传入值，又表示过程向调用者传出值，这个值只能是变量。

由于Navicat中无法定义变量，所以这一部分将会在原生Mysql中进行试验。代码如下：
先定义变量

mysql>set @a=10；

再定义存储过程及运行

mysql> DELIMITER // 修改分隔符
mysql> create procedure test_pr(IN a INTEGER)
    -> BEGIN
    ->  select a;
    -> END//
mysql> DELIMITER ;
mysql> call test_pr(@a); //这里要加@

2.3 characteristic部分

  在MySQL存储过程中，characteristic部分指的是存储过程的特点或特性，包括存储过程的语法结构、参数类型、返回值类型、权限等。具体包含的部分如下：

• comment语句：指存储过程的注释或描述信息。这些信息通常包含关于存储过程的用途、输入输出参数的说明、返回结果的类型等。
• LANGUAGE SQL：指存储过程使用的语言类型为SQL；
• DETERMINISTIC：指存储过程是否是确定性的。如果一个存储过程是确定性的，那么给定相同的输入参数，它将始终返回相同的结果。这个特性对于一些关键业务逻辑很重要，因为它保证了存储过程的行为是可预测的，从而提高了系统的可靠性和安全性。
• 以下四个语句是一组的，具体如下：
  • CONTAINS SQL：是指存储过程中不包含读数据或写数据的语句；
  • NO SQL：指存储过程不包含任何SQL语句。如果存储过程不需要访问或修改数据库中的数据，例如仅进行计算或逻辑判断等操作，则可以使用NO SQL语句来指定；
  • READS SQL DATA：表示存储过程只读取数据，不修改数据；
  • MODIFIES SQL DATA：表示存储过程可以修改数据；
• SQL SECURITY：存储过程中的是指指定存储过程是由哪个用户执行的。具体分为两种：
  • INVOKER：指定存储过程的执行者是定义该存储过程的用户。
  • DEFINER：指定存储过程的执行者是some_user用户，该用户必须具有执行该存储过程的权限。

2.4 存储过程体

  存储过程体包含了在过程调用时必须执行的语句，以BEGIN开始END结束，这一部分可以包括：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等。

2.4.1 声明变量

  在MySQL的存储过程体中可以使用declare来定义变量。但要注意在过程体中定义的变量其作用范围也只在过程中。其具体定义语法如下：

DECLARE 变量名[,...] type [DEFAULT value]

经过declare定义的变量可以使用set或select……into进行变量赋值。具体举例如下：

mysql> delimiter //
mysql> create procedure pro_1()
    -> begin
    -> declare a integer default 10;
    -> declare b integer default 20;
    -> set a=a+10;
    -> select 50 into b; //也可以使用sql中的聚合函数结果给变量赋值
    -> select a,b;
    -> end//
mysql> delimiter ;
mysql> call pro_1();

其结果如下：

2.4.2 逻辑判断语句

  在MySQL的存储过程体中可以使用if结构和case when结构来实现逻辑判断。其语法结构分别如下：

--if--
if search_condition(查询条件) then statement_list(SQL语句)
    [elseif search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end if;
--case--
--方式1--
case case_value(判断的值)
    when when_value(比较的值) then statement_list(SQL语句)
    [when when_value(比较的值) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;
​--方式2--
case
    when search_condition(查询条件) then statement_list(SQL语句)
    [when search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;

其用法举例如下(navicat代码)：

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_score`(IN score INTEGER)
BEGIN
declare result VARCHAR(10);
IF score<60 then set result='不合格';
elseif score<80 then set result='良好';
else set result='优秀';
end IF;
select result;
END

2.4.3 循环语句

  MySQL存储过程体中支持while循环、repeat循环、loop循环。其语法结构如下：

--while循环--
while search_condition(查询条件) do
    statement_list(SQL语句)
end while;

--repeat循环--
repeat 
    statement_list(SQL语句)
    until search_condition(查询添加)
end repeat;

--loop循环--
[begin_label:] loop
    statement_list
end loop [end_label]

参考资料：

• https://www.runoob.com/w3cnote/mysql-stored-procedure.html