数据库存储过程、函数、触发器与数据库编程核心元素详解

数据库存储过程、函数与触发器详解

本文将系统介绍数据库中存储过程、函数、触发器的定义、语法、示例及应用场景，并对比三者区别，同时补充变量、流程控制语句、游标等核心数据库编程元素，帮助读者全面掌握数据库编程技能。

一、存储过程

存储过程（Stored Procedure）是一组预编译并存储在数据库中的SQL语句集合，可完成特定业务功能，支持输入/输出参数，能有效提高数据库操作效率与安全性。

1. 核心特性

• 预编译执行：创建时编译，后续调用无需重复编译，减少执行时间。
• 减少网络流量：客户端仅需传递存储过程名和参数，无需传输大量SQL语句。
• 封装逻辑：将复杂业务逻辑（如多表关联、事务处理）封装，降低客户端代码复杂度。
• 安全可控：可通过权限管理限制存储过程访问，避免直接操作表数据。

2. 语法结构

CREATE DEFINER=`用户名`@`主机名` PROCEDURE 存储过程名(
    [IN 参数名 数据类型,  -- 输入参数（默认，可省略IN）
     OUT 参数名 数据类型, -- 输出参数（需通过@变量接收结果）
     INOUT 参数名 数据类型] -- 输入输出参数
)		
BEGIN
    -- 业务SQL语句（可包含变量、流程控制等）
END;

• DEFINER（可选）：指定存储过程的所有者，仅该用户可执行（如root@localhost）。
• 参数类型：
  • IN：仅用于传入值，过程内不可修改。
  • OUT：仅用于输出值，过程内需赋值，客户端通过@变量接收。
  • INOUT：既传入值，也可输出修改后的值。

3. 示例：批量插入多条数据

需求：向course表批量插入max_num条课程数据，课程名称为“计算机”，教师ID自增。

-- 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_course`(
    IN max_num INT(10)  -- 输入参数：插入数据条数
)	
BEGIN
    DECLARE i INT DEFAULT 0;  -- 局部变量：循环计数器
    SET autocommit = 0;       -- 关闭自动提交，提升批量插入效率
    REPEAT
        SET i = i + 1;
        INSERT INTO course (teacher_id, name) VALUES (i + 1, "计算机");
    UNTIL i = max_num  -- 循环条件：计数器达到max_num时退出
    END REPEAT;
    COMMIT;  -- 手动提交事务
END;

-- 调用存储过程（插入10条数据）
CALL insert_course(10);

4. 调用与结果查看

• 调用带输出参数的存储过程：

  -- 示例：定义带输出参数的存储过程（统计课程总数）
  CREATE PROCEDURE `count_course`(OUT total INT)
  BEGIN
      SELECT COUNT(*) INTO total FROM course;
  END;
  
  -- 调用并查看结果
  CALL count_course(@total);  -- @total：用户变量，接收输出结果
  SELECT @total;  -- 查看统计结果
  

5. 应用场景

• 事务处理：封装多步SQL（如转账：扣钱+加钱），确保原子性（要么全成功，要么全回滚）。
• 批量操作：批量插入、更新、删除数据（如每月数据归档）。
• 复杂业务逻辑：数据清洗、多表关联计算（如生成月度销售报表）。
• 安全控制：限制客户端直接操作表，仅通过存储过程访问数据（如隐藏敏感字段）。

二、函数

函数（Stored Function）与存储过程类似，但必须返回一个值，且参数仅支持IN类型（默认，不可指定其他类型），主要用于数据计算或格式化。

1. 核心特性

• 强制返回值：函数体中必须包含RETURN语句，指定返回数据类型。
• 参数限制：仅支持输入参数（IN），不可用OUT或INOUT。
• 可嵌入查询：函数可直接作为查询语句的一部分（如SELECT 函数名(参数)）。

2. 语法结构

CREATE FUNCTION 函数名(参数名 数据类型, ...)
RETURNS 返回数据类型  -- 必须指定返回类型（如INT、VARCHAR）
[特性选项]  -- 如DETERMINISTIC（输入相同则输出相同）
BEGIN
    -- 业务逻辑（需通过RETURN返回值）
    RETURN 结果值;
END;

3. 示例

示例1：返回字符串长度

-- 创建函数：计算输入字符串的长度
DELIMITER $$  -- 临时修改语句结束符（避免与函数内;冲突）
CREATE FUNCTION get_string_length(input_string VARCHAR(255))
RETURNS INT
DETERMINISTIC  -- 输入相同，输出必相同（优化执行效率）
BEGIN
    RETURN CHAR_LENGTH(input_string);  -- 返回字符串长度
END$$
DELIMITER ;  -- 恢复语句结束符为;

-- 调用函数
SELECT get_string_length('Hello MySQL');  -- 结果：10

示例2：计算两个数字的和

DELIMITER $$
CREATE FUNCTION add_numbers(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN num1 + num2;
END$$
DELIMITER ;

-- 调用函数
SELECT add_numbers(10, 20);  -- 结果：30

4. 函数管理

• 删除函数：

  DROP FUNCTION IF EXISTS 函数名;  -- 如DROP FUNCTION add_numbers;
  

• 查看函数定义：

  SHOW CREATE FUNCTION 函数名;
  

5. 应用场景

• 数据格式化：日期转换（如DATE_FORMAT(now(), '%Y-%m-%d')）、字符串拼接（如CONCAT(name, '-', age)）。
• 数值计算：四舍五入（ROUND(3.1415, 2)）、绝对值（ABS(-100)）、累加统计（自定义函数计算订单总金额）。
• 业务规则判定：分数等级判定（如IF(score >= 85, '优秀', '及格')）、员工入职天数计算（DATEDIFF(now(), hire_date)）。

三、触发器

触发器（Trigger）是与表关联的“自动执行程序”，当表发生INSERT、UPDATE、DELETE事件时，触发器会自动触发执行，无需手动调用，主要用于数据校验、日志记录、数据同步。

1. 核心特性

• 事件驱动：仅在指定表的指定事件（INSERT/UPDATE/DELETE）发生时触发。
• 执行时机：可指定BEFORE（事件前执行）或AFTER（事件后执行）。
• 行级触发：通过FOR EACH ROW指定，每操作一行数据就触发一次。

2. 语法结构

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}  -- 执行时机+触发事件
ON 关联表名  -- 触发器绑定的表（仅该表的事件会触发）
FOR EACH ROW  -- 行级触发（每操作一行触发一次）
BEGIN
    -- 触发后执行的SQL语句（可引用NEW/OLD关键字）
END;

• NEW/OLD关键字：
  • NEW：仅INSERT/UPDATE可用，代表“新增/修改后的数据行”（如NEW.id获取新增记录的ID）。
  • OLD：仅UPDATE/DELETE可用，代表“修改/删除前的数据行”（如OLD.name获取删除前的名称）。

3. 示例：数据同步

需求：向table1插入数据时，自动同步一条相同数据到table2。

-- 创建触发器
DELIMITER $$
CREATE TRIGGER insert_trigger_table1
AFTER INSERT ON table1  -- table1插入数据后触发
FOR EACH ROW
BEGIN
    -- 同步数据到table2（使用NEW获取新增数据）
    INSERT INTO table2(id, name) VALUES (NEW.id, NEW.name);
END$$
DELIMITER ;

-- 测试触发效果
INSERT INTO table1(id, name) VALUES (1, 'kobe'), (2, 'lebron');
-- 执行后，table2会自动新增2条数据：(1,'kobe')、(2,'lebron')

4. 触发器管理

• 删除触发器：

  DROP TRIGGER IF EXISTS 触发器名;  -- 如DROP TRIGGER insert_trigger_table1;
  

• 查看触发器：

  SELECT * FROM information_schema.TRIGGERS WHERE TABLE_NAME = '关联表名';
  

5. 应用场景

• 数据校验：BEFORE INSERT触发，校验插入的年龄是否合法（如IF NEW.age < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能为负';）。
• 日志记录：AFTER UPDATE触发，记录数据修改日志（如将修改前的旧值、修改时间、操作人插入日志表）。
• 数据同步：多表间自动同步数据（如订单表新增后，库存表自动扣减对应商品库存）。
• 复杂默认值：BEFORE INSERT触发，根据其他字段计算默认值（如根据生日自动计算年龄并赋值到age字段）。

四、存储过程、函数与触发器的区别

三者均为数据库编程元素，但在定义、用途、调用方式上差异显著，具体对比如下：

五、数据库编程核心元素补充

1. 变量

数据库变量分为系统变量、用户变量、局部变量，用途与作用域不同：

2. 分隔符（DELIMITER）

默认情况下，MySQL以;作为语句结束符，但存储过程/函数/触发器内可能包含多个;，会导致MySQL提前结束编译。此时需临时修改分隔符，语法如下：

DELIMITER 新分隔符  -- 如DELIMITER $$
-- 编写存储过程/函数/触发器（内部用;分隔语句）
新分隔符  -- 结束定义
DELIMITER ;  -- 恢复默认分隔符

3. 流程控制语句

（1）IF…ELSE

用于条件判断，语法：

IF 条件1 THEN
    语句1;
ELSEIF 条件2 THEN
    语句2;
ELSE
    语句3;
END IF;

（2）CASE语句

两种语法形式，用于多条件判断：

• 形式1（值匹配）：

  CASE 变量
      WHEN 值1 THEN 语句1;
      WHEN 值2 THEN 语句2;
      ELSE 语句3;
  END CASE;
  

• 形式2（表达式匹配）：

  CASE
      WHEN 条件1 THEN 语句1;
      WHEN 条件2 THEN 语句2;
      ELSE 语句3;
  END CASE;
  

（3）循环语句

示例：LOOP循环（计算1~n的和）

CREATE PROCEDURE sum_num(n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    sum_loop: LOOP  -- 定义循环标记sum_loop
        IF n <= 0 THEN
            LEAVE sum_loop;  -- 退出循环
        END IF;
        SET total = total + n;
        SET n = n - 1;
    END LOOP sum_loop;
    SELECT total;
END;

CALL sum_num(100);  -- 结果：5050

4. 游标（CURSOR）

游标是用于遍历查询结果集的工具，适用于存储过程/函数中逐行处理数据，步骤如下：

1. 声明游标：绑定查询语句。
2. 打开游标：初始化结果集。
3. 获取数据：逐行读取结果到变量。
4. 关闭游标：释放资源。

示例：用游标批量插入数据

DELIMITER $$
CREATE PROCEDURE cursor_demo(in uage DATE)
BEGIN
    DECLARE usname VARCHAR(100);  -- 存储用户名
    DECLARE upro VARCHAR(100);    -- 存储专业
    DECLARE done INT DEFAULT 0;   -- 游标结束标记
    
    -- 1. 声明游标（查询出生日期小于uage的学生）
    DECLARE u_cursor CURSOR FOR 
        SELECT sname, smajor FROM students WHERE sbirthday < uage;
    
    -- 2. 声明条件处理程序（游标无数据时设置done=1）
    DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;
    
    -- 3. 创建目标表（存储结果）
    DROP TABLE IF EXISTS tb_user;
    CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(100), sdapt VARCHAR(100));
    
    -- 4. 打开游标
    OPEN u_cursor;
    
    -- 5. 遍历游标
    fetch_loop: LOOP
        FETCH u_cursor INTO usname, upro;  -- 读取一行数据
        IF done = 1 THEN
            LEAVE fetch_loop;  -- 无数据时退出循环
        END IF;
        INSERT INTO tb_user(uname, sdapt) VALUES (usname, upro);  -- 插入数据
    END LOOP fetch_loop;
    
    -- 6. 关闭游标
    CLOSE u_cursor;
END$$
DELIMITER ;

-- 调用存储过程（查询1999年3月1日前出生的学生）
CALL cursor_demo('1999-03-01');

5. 条件处理程序（HANDLER）

用于捕获存储过程/函数/触发器中的异常或状态（如“无数据”“语法错误”），并定义处理逻辑，语法：

DECLARE 处理类型 HANDLER FOR 状态列表 处理语句;

• 处理类型：
  • CONTINUE：处理后继续执行后续语句。
  • EXIT：处理后退出当前代码块（如BEGIN…END）。
• 状态列表：
  • SQLSTATE '状态码'：如'02000'代表“无数据”。
  • NOT FOUND：等价于SQLSTATE '02000'。
  • SQLEXCEPTION：捕获所有非01/02开头的错误状态码。

示例：捕获“无数据”异常

DECLARE EXIT HANDLER FOR NOT FOUND 
    SELECT '查询无结果' AS message;  -- 无数据时输出提示并退出

六、常用管理命令