存储过程

存储过程(Stored Procedure)是预编译并存储在数据库中的一组 SQL 语句集合,可通过 “调用指令” 重复执行,无需每次重新编写 SQL。

1.基本语法

创建存储过程

delimiter //

create procedure 存储过程名(参数...)

begin

​ select * from 表名;

​ insert into 表名 values(...);

end //

delimiter ;

注:delimitee能临时修改语句结束符,防止mysql将存储过程内的;识别为数据结束,导致语法错误,因此在存储过程前添加delimiter //修改分隔符,在存储过程后添加delimiter ;恢复分隔符。

调用存储过程

call 存储过程名(参数...);

无参数时写:cal 存储过程名();

查看存储过程

查看所有存储过程:show procedure status;

查看某个存储过程的创建语句:show create procedure 存储过程名;

删除存储过程

drop procedure if exists 存储过程名;

2.变量

系统变量

分类

全局变量:影响整个mysql服务,前缀是global

会话变量:仅影响当前数据库连接,前缀是session

查看系统变量

show global variables;

show session variables;

select @@global.变量名;

select @@session.变量名;(session可省略,默认查当前会话)

修改系统变量

set global 变量名=值;(修改后需重启连接生效,重启 MySQL 服务后失效,需改配置文件永久生效)

set session 变量名=值;(仅当前连接生效,断开后恢复默认)

用户定义变量

作用范围为当前会话,无需显式声明类型(MySQL 自动推断,如整数、字符串),直接赋值即可

  1. set @变量名 = 值;(推荐)

    示例:SET @user_age = 25;

  2. select 值 into @变量名;(从查询结果中赋值,需确保查询结果只有一行一列)

    示例:SELECT age INTO @user_age FROM users WHERE id = 1;

局部变量

作用范围仅在存储过程的BEGIN...END块内部(或IF/循环等子块内),必须先声明、后使用,且需指定数据类型(如INTVARCHAR(20)

delimiter //
create procedure test_local_var()
begin
    declare var_name varchar(20);
    declare var_age int default 0;
    set var_name = '张三';
    select age into var_age from users where id = 1;
    select var_name, var_age;
end //
delimiter ;

局部变量不能加@

3.if 判断

IF 条件1 THEN
    -- 条件1为TRUE执行
ELSE IF 条件2 THEN
    -- 条件2为TRUE执行
ELSE
    -- 所有条件都为FALSE执行
END IF;

示例:根据年龄判断用户等级

DELIMITER //
CREATE PROCEDURE get_user_level(IN user_id INT)  -- IN参数:接收外部传入的用户ID
BEGIN
    DECLARE user_age INT;
    DECLARE level VARCHAR(10);
    
    SELECT age INTO user_age FROM users WHERE id = user_id;
    
    -- IF判断逻辑
    IF user_age < 18 THEN
        SET level = '未成年';
    ELSE IF user_age BETWEEN 18 AND 60 THEN
        SET level = '成年';
    ELSE
        SET level = '老年';
    END IF;
    
    SELECT level AS user_level;  -- 返回结果
END //
DELIMITER ;

-- 调用:查看ID=1的用户等级
CALL get_user_level(1);

4.参数(in,out,inout)

类型数据流向特点示例场景
IN外部→存储过程(仅传入数据)存储过程内不能修改外部传入的参数值传入用户 ID,查询用户信息
OUT存储过程→外部(仅返回数据)参数在外部需先声明(用用户变量),存储过程内赋值后传出计算结果返回给外部(如统计总数)
INOUT外部→存储过程→外部(双向传递)既传入初始值,存储过程修改后再传出传入数值,存储过程累加后返回
DELIMITER //
-- 1. IN参数:传入用户名,查询年龄
CREATE PROCEDURE get_age_by_name(IN in_name VARCHAR(20))
BEGIN
    SELECT age FROM users WHERE name = in_name;
END //

-- 2. OUT参数:统计用户总数,返回给外部
CREATE PROCEDURE count_total_users(OUT out_total INT)
BEGIN
    SELECT COUNT(*) INTO out_total FROM users;  -- 存储过程内赋值
END //

-- 3. INOUT参数:传入初始值,累加5后返回
CREATE PROCEDURE add_five(INOUT inout_num INT)
BEGIN
    SET inout_num = inout_num + 5;  -- 修改传入的参数值
END //
DELIMITER ;

-- 调用示例
CALL get_age_by_name('张三');  -- IN参数:直接传值

SET @total = 0;  -- 先声明用户变量接收OUT参数
CALL count_total_users(@total);
SELECT @total;  -- 查看返回的总数(OUT参数的结果)

SET @num = 10;  -- 声明并赋值INOUT参数的初始值
CALL add_five(@num);
SELECT @num;  -- 结果为15(INOUT参数的修改后值)

5.循环

(1)while
WHILE 条件表达式 DO
    -- 循环体(需执行的SQL语句)
    -- (必须包含“让条件最终为FALSE”的逻辑,否则无限循环)
END WHILE;
(2)repeat
REPEAT
    -- 循环体(至少执行1次)
    -- (需包含“让条件最终为TRUE”的逻辑,否则无限循环)
UNTIL 条件表达式  -- 注意:UNTIL后无“;”
END REPEAT;

eg. 用 REPEAT 循环计算 1~10 的累加和

DELIMITER //
CREATE PROCEDURE calculate_sum()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    
    REPEAT
        SET total = total + i;  -- 累加(先执行1次)
        SET i = i + 1;          -- 计数器自增
    UNTIL i > 10  -- 条件:i>10时退出(此时i从1到11,循环执行10次)
    END REPEAT;
    
    SELECT total AS sum_1_to_10;  -- 返回结果:55
END //
DELIMITER ;

-- 调用:计算1~10的和
CALL calculate_sum();
(3)loop

LOOP本身是 “无限循环”,必须通过LEAVE主动退出,灵活性最高。

-- 1. 定义循环标签(自定义名称,如loop_label)
loop_label: LOOP
    -- 循环体(默认无限循环)
    
    -- 2. 退出循环(必须用LEAVE,配合条件判断)
    IF 退出条件 THEN
        LEAVE loop_label;  -- 满足条件时,退出loop_label标签对应的循环
    END IF;
    
    -- 3. 跳过当前循环剩余部分,进入下一次循环(可选,用ITERATE)
    IF 跳过条件 THEN
        ITERATE loop_label;  -- 满足条件时,跳过后续代码,直接开始下一次循环
    END IF;
END LOOP loop_label;

eg. 用 LOOP 循环筛选 1~20 中的偶数

DELIMITER $$
CREATE PROCEDURE find_even_numbers()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE even_numbers TEXT DEFAULT '';
    
    loop_label: LOOP
        IF i > 20 THEN
            LEAVE loop_label;
        END IF;
        
        IF i % 2 = 0 THEN
            SET even_numbers = CONCAT(even_numbers, i, ' ');
        END IF;
        
        SET i = i + 1;
    END LOOP;
    
    SELECT even_numbers AS '1~20中的偶数';
END $$
DELIMITER ;

-- 执行存储过程
CALL find_even_numbers();

6.游标-cursor

数据库中的“结果集指针”,可定位到查询结果的某一行,读取该行数据,实现逐行读取查询结果集,解决存储过程中无法直接遍历多行数据的问题。

  1. 只能单向移动(只能从第一行往后读,不能回退);
  2. 只能关联一个查询结果集(一个游标对应一个SELECT语句);
  3. 需配合“条件处理程序(Handler)”处理“游标遍历到末尾”的情况(否则会报错)。
DELIMITER //
CREATE PROCEDURE use_cursor_demo()
BEGIN
    -- 步骤1:声明局部变量(用于存储游标读取的每行数据)
    DECLARE var_id INT;
    DECLARE var_name VARCHAR(20);
    DECLARE var_age INT;
    DECLARE is_end BOOLEAN DEFAULT FALSE;  -- 标记游标是否遍历结束(关键)
    -- 步骤2:声明游标(关联查询结果集)
    DECLARE user_cursor CURSOR FOR
        SELECT id, name, age FROM users WHERE age > 18;  -- 游标对应的查询(只查成年用户)
    -- 步骤3:声明“条件处理程序”(处理“游标无数据”的情况)
    -- 当触发“SQLSTATE '02000'”(游标到达末尾,无更多数据)时,设置is_end为TRUE
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_end = TRUE;
    -- 步骤4:打开游标(激活游标,关联查询结果集)
    OPEN user_cursor;
    -- 步骤5:循环读取游标数据(用LOOP/REPEAT/WHILE)
    cursor_loop: LOOP
        FETCH user_cursor INTO var_id, var_name, var_age; -- 读取游标当前行数据,赋值给局部变量(顺序需与游标查询的列一致)
        IF is_end THEN -- 判断是否遍历结束,是则退出循环
            LEAVE cursor_loop; 
        END IF;
        -- 步骤6:处理数据(示例:打印成年用户信息,或执行其他业务逻辑)
        SELECT CONCAT('ID:', var_id, ',姓名:', var_name, ',年龄:', var_age) AS adult_user;
    END LOOP cursor_loop;
    -- 步骤7:关闭游标(释放资源,必须执行)
    CLOSE user_cursor;
END //
DELIMITER ;
-- 调用:遍历并打印所有成年用户信息
CALL use_cursor_demo();
  1. 声明顺序严格:必须先声明局部变量 → 再声明游标 → 最后声明条件处理程序(否则语法错误)。
  2. FETCH顺序匹配FETCH ... INTO后的变量顺序,必须与游标SELECT语句的列顺序完全一致。
  3. 资源释放:游标使用后必须用CLOSE关闭,避免占用数据库连接资源。

7.条件处理程序-handler

条件处理程序是 “预先定义的规则”:当存储过程中触发特定 SQL 状态码特定错误码时,自动执行规则中的操作(如设置变量、退出循环、忽略错误)。

DECLARE 处理方式 HANDLER FOR 触发条件
DO 执行的操作;

处理方式

CONTINUE:执行操作后,继续执行存储过程后续代码(常用,如游标结束后继续关闭游标)。

EXIT:执行操作后,立即退出当前BEGIN...END块(不常用,可能导致资源未释放)。

触发条件

特定 SQL 状态码:如SQLSTATE '02000'(游标无数据)、SQLSTATE '23000'(主键冲突 / 数据重复);

特定错误码:如1062(主键冲突,对应SQLSTATE '23000');

通用条件:SQLEXCEPTION(所有异常)、SQLWARNING(所有警告)、NOT FOUND(无数据,等价于SQLSTATE '02000')。

场景 1:处理 “数据重复插入”(主键冲突)

DELIMITER //
CREATE PROCEDURE insert_user_safe(IN in_id INT, IN in_name VARCHAR(20))
BEGIN
    -- 声明条件处理程序:当触发主键冲突(SQLSTATE '23000')时,打印提示
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT CONCAT('用户ID ', in_id, ' 已存在,插入失败') AS result;
    END;
    
    -- 尝试插入数据(若ID已存在,会触发上述Handler)
    INSERT INTO users (id, name) VALUES (in_id, in_name);
    SELECT CONCAT('用户ID ', in_id, ' 插入成功') AS result;
END //
DELIMITER ;

-- 调用测试:先插入ID=1,再插入ID=1(第二次会触发Handler)
CALL insert_user_safe(1, '张三');  -- 第一次:插入成功
CALL insert_user_safe(1, '张三');  -- 第二次:触发Handler,提示“已存在”

场景 2:处理 “游标无数据”(配合游标使用)

-- 当游标遍历到末尾(无数据,SQLSTATE '02000'),设置is_end为TRUE,继续执行后续关闭游标的代码
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_end = TRUE;

存储函数

存储函数是 “预编译并存储在数据库中的 SQL 逻辑块”,必须有且仅有一个返回值(类似 Java 的 “有返回值方法”),可直接在SELECT等 SQL 语句中调用。

1.基本语法

(1)创建存储函数
DELIMITER //
CREATE FUNCTION 函数名(参数列表)
RETURNS 返回值类型  -- 必须指定返回值类型(如INT、VARCHAR(20))
DETERMINISTIC  -- 可选:标记函数“输入相同则输出相同”(优化用)
NO SQL  -- 可选:标记函数“不操作SQL”(如纯计算);若操作SQL,用READS SQL DATA
BEGIN
    -- 函数体(需包含RETURN语句,返回结果)
    DECLARE 局部变量;
    -- 业务逻辑
    RETURN 返回值;  -- 必须有RETURN,且返回值类型与RETURNS一致
END //
DELIMITER ;
(2)调用存储函数

直接在 SQL 语句中使用(如SELECTWHERE条件),无需CALL(这也是与存储过程的区别)

-- 示例:调用函数获取用户年龄
SELECT get_user_age(1) AS user_age;  -- 1是函数的参数(用户ID)

2.应用示例

DELIMITER //
CREATE FUNCTION get_user_age(in_user_id INT)
RETURNS INT  -- 返回值类型:整数(年龄)
READS SQL DATA  -- 标记函数“仅读取SQL数据,不修改”
BEGIN
    DECLARE out_age INT;
    
    -- 查询用户年龄(若用户不存在,out_age为NULL)
    SELECT age INTO out_age FROM users WHERE id = in_user_id;
    
    RETURN out_age;  -- 返回结果
END //
DELIMITER ;

-- 调用方式1:直接查询
SELECT get_user_age(1) AS age_of_user1;  -- 若ID=1存在,返回年龄;否则返回NULL

-- 调用方式2:在WHERE条件中使用
SELECT * FROM users WHERE age > get_user_age(1);  -- 查询年龄大于“ID=1用户”的所有用户

3.存储函数 vs 存储过程

对比维度存储函数(Function)存储过程(Procedure)
返回值必须有且仅有 1 个返回值可选(可通过 OUT/INOUT 参数返回多个值)
调用方式直接在 SQL 语句中使用(如 SELECT)必须用 CALL 指令调用
参数类型仅支持 IN 参数(默认,无需显式声明)支持 IN、OUT、INOUT 三种参数
适用场景简单计算、单值查询(如根据 ID 查字段)复杂业务逻辑(多 SQL、流程控制、多值返回)
事务支持不能包含事务控制(如 COMMIT、ROLLBACK)可包含事务控制
分类: Java-Backend 标签: MySQL

评论

暂无评论数据

暂无评论数据

目录