MySQL 存储过程 存储函数 触发器 笔记

1.存储过程

1. 创建和调用存储过程

  • 创建存储过程：使用 CREATE PROCEDURE 定义存储过程。 
    CREATE PROCEDURE 存储过程名称 ([IN|OUT|INOUT] 参数名 参数类型)
BEGIN
  -- SQL 语句
END;
  • 调用存储过程：通过 CALL 语句调用存储过程。 
    CALL 存储过程名称 (参数);

2. 存储过程参数类型

MySQL 存储过程支持三种类型的参数：

  • IN：输入参数，在调用存储过程时传入值。
  • OUT：输出参数，存储过程执行后返回结果。
  • INOUT：既可以作为输入参数，又可以作为输出参数。

3. 变量

MySQL 存储过程支持用户定义变量和局部变量：

  • 用户定义变量：

    • 直接使用 @变量名 定义，作用域为当前连接，不需要提前声明，未赋值时默认为 NULL。
    • 赋值语法： 
      SET @var_name = 值;
SELECT 字段名 INTO @var_name FROM 表名;

  • 局部变量：

    • 在存储过程内部定义，需使用 DECLARE 声明，作用范围为 BEGIN...END 块。
    • 声明和赋值语法： 
      DECLARE 变量名 变量类型 [DEFAULT 默认值];
SET 变量名 = 值;
SELECT 字段名 INTO 变量名 FROM 表名;

4. 条件控制结构

MySQL 存储过程支持 IF 和 CASE 语句，用于根据不同条件执行不同操作。

  • IF 语句：

    IF 条件1 THEN
  -- 执行的SQL语句1
ELSEIF 条件2 THEN
  -- 执行的SQL语句2
ELSE
  -- 执行的SQL语句3
END IF;

  • CASE 语句：有两种形式

    1. 基于值的判断： 
      CASE case_value
 WHEN when_value1 THEN statement_list1
 WHEN when_value2 THEN statement_list2
 ...
 ELSE statement_list
END CASE;
    2. 基于条件的判断： 
      CASE
 WHEN search_condition1 THEN statement_list1
 WHEN search_condition2 THEN statement_list2
 ...
 ELSE statement_list
END CASE;

5. 循环控制结构

MySQL 提供 WHILE、REPEAT 和 LOOP 三种循环结构。

  • WHILE 循环：在条件为真时执行循环。

    WHILE 条件 DO
  -- SQL逻辑
END WHILE;

  • REPEAT 循环：先执行循环体，再判断条件，条件为真时退出。

    REPEAT
  -- SQL逻辑
UNTIL 条件
END REPEAT;

  • LOOP 循环：配合 LEAVE 和 ITERATE 实现循环退出和继续。

    [begin_label:] LOOP
  -- SQL逻辑
  IF 条件 THEN
      LEAVE begin_label;
  END IF;
END LOOP [end_label];
    • LEAVE：退出当前循环。
    • ITERATE：跳过剩余语句，进入下一次循环。

6. 游标（Cursor）

游标用于逐行处理查询结果集，通常用于存储过程中的复杂数据处理。使用游标的步骤如下：

  1. 声明游标： 
    DECLARE 游标名称 CURSOR FOR 查询语句;
  2. 打开游标： 
    OPEN 游标名称;
  3. 获取游标记录： 
    FETCH 游标名称 INTO 变量 [, 变量 ...];
  4. 关闭游标： 
    CLOSE 游标名称;

示例：

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_name;
REPEAT
    FETCH cursor_name INTO var1, var2;
    IF NOT done THEN
        -- 处理每条记录的逻辑
    END IF;
UNTIL done END REPEAT;

CLOSE cursor_name;

7. 条件处理程序（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 或 NOT FOUND 捕获的所有异常。

示例：

DECLARE done INT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_name;
REPEAT
    FETCH cursor_name INTO var1, var2;
    IF NOT done THEN
        -- 逻辑处理
    END IF;
UNTIL done END REPEAT;

CLOSE cursor_name;

2.存储函数

  1. 定义存储函数：使用 CREATE FUNCTION 关键字，后跟函数名称和参数列表。参数只能是 IN 类型，表示只作为输入使用。

  2. 返回值类型：使用 RETURNS type 指定函数返回的数据类型。

  3. 特性 (characteristic)：

    • DETERMINISTIC：表示相同的输入参数会生成相同的结果。
    • NO SQL：函数不包含任何 SQL 语句。
    • READS SQL DATA：函数包含读取数据的 SQL 语句，但不包含写入数据的语句。

  4. 函数体：在 BEGIN ... END 块中编写 SQL 语句，并使用 RETURN 语句返回结果。

3.触发器

  1. 触发器概述
    触发器是关联到数据库表的特定对象，用于在 INSERT、UPDATE 或 DELETE 操作之前或之后执行指定的 SQL 语句。触发器的主要功能包括数据完整性验证、日志记录以及数据校验等。触发器使用别名 OLD 和 NEW 来引用记录的变化情况，类似于其他数据库系统中的用法。当前的触发器仅支持行级触发，不支持语句级触发。

  2. 触发器类型与作用

    • INSERT 型触发器：NEW 表示即将插入或已新增的数据。
    • UPDATE 型触发器：OLD 表示修改前的数据，NEW 表示即将修改或已修改后的数据。
    • DELETE 型触发器：OLD 表示即将删除或已删除的数据。

  3. 触发器语法

    • 创建触发器 

      CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW
BEGIN
 trigger_stmt;
END;

      这里的 FOR EACH ROW 表示该触发器为行级触发。

    • 查看触发器 

      SHOW TRIGGERS;

    • 删除触发器 

      DROP TRIGGER [schema_name.]trigger_name;

      如果未指定 schema_name，则默认使用当前数据库。