当前位置： 首页 > article >正文

mysql 存储过程和自定义函数 详解

article 2025/2/10 22:20:26

首先创建存储过程或者自定义函数时，都要使用use database 切换到目标数据库，因为存储过程和自定义函数都是属于某个数据库的。

存储过程是一种预编译的 SQL 代码集合，封装在数据库对象中。以下是一些常见的存储过程的关键字：

存储过程

1. 存储过程的定义

  • CREATE PROCEDURE: 用于创建一个新的存储过程。

    CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- 存储过程体
END;

  • DROP PROCEDURE: 用于删除已存在的存储过程。

    DROP PROCEDURE procedure_name;

2. 参数定义

  • IN: 表示输入参数。

  • OUT: 表示输出参数。

  • INOUT: 表示既可以输入又可以输出的参数。

    CREATE PROCEDURE my_procedure(IN param1 INT, OUT param2 INT)
BEGIN
    -- 存储过程体
END;

3. 变量声明与操作

  • DECLARE: 定义局部变量、条件和游标。

    DECLARE var1 INT DEFAULT 0;
DECLARE var2 VARCHAR(50);

  • SET: 用于给变量赋值。

    SET var1 = 10;

4. 控制流程

  • BEGIN/END: 标记存储过程块的开始和结束。

    BEGIN
    -- 存储过程体
END;

  • IF 条件判断

    IF 语句用于简单的条件分支。

    语法格式：
    IF condition THEN
    SQL逻辑
ELSEIF condition THEN
    SQL逻辑
ELSE
    SQL逻辑
END IF;
    案例：

    根据用户 ID 返回不同的信息。

    DELIMITER //
CREATE PROCEDURE CheckUser(IN userId VARCHAR(32))
BEGIN
    DECLARE userName VARCHAR(32);
    IF userId = 'APP-2016-00494878' THEN
        SELECT username INTO userName FROM users WHERE userid = userId;
        SELECT userName;
    ELSEIF userId = 'APP-2016-7777777' THEN
        SELECT userage INTO userName FROM users WHERE userid = userId;
        SELECT userName;
    ELSE
        SELECT userId;
    END IF;
END //
DELIMITER ;

    调用存储过程：

    CALL CheckUser('APP-2016-00494878');

  • CASE: 用于多条件判断。

  • 语法格式：
    CASE
    WHEN condition1 THEN
        SQL逻辑
    WHEN condition2 THEN
        SQL逻辑
    ELSE
        SQL逻辑
END CASE;

  • 根据用户角色返回不同的权限级别。

  • DELIMITER //
CREATE PROCEDURE GetUserRole(IN userId INT)
BEGIN
    DECLARE userRole VARCHAR(20);
    SELECT role INTO userRole FROM users WHERE id = userId;
    CASE userRole
        WHEN 'admin' THEN
            SELECT 'Administrator';
        WHEN 'user' THEN
            SELECT 'Standard User';
        ELSE
            SELECT 'Guest';
    END CASE;
END //
DELIMITER ;

    调用存储过程：

    CALL GetUserRole(1);

  • LOOP/WHILE/REPEAT: 实现循环操作。

    LOOP_LABEL: LOOP
    -- 循环体
    IF condition THEN
        LEAVE LOOP_LABEL;
    END IF;
END LOOP;

 WHILE 循环

WHILE 循环在条件为真时执行循环体中的 SQL 语句。

语法格式：
WHILE 条件 DO
    SQL逻辑
END WHILE;
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSum(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    WHILE n > 0 DO
        SET total = total + n;
        SET n = n - 1;
    END WHILE;
    SELECT total;
END //
DELIMITER ;

调用存储过程：

CALL CalculateSum(100);

 

REPEAT 循环

REPEAT 循环至少执行一次循环体中的 SQL 语句，直到条件为真时退出循环。

语法格式：
REPEAT
    SQL逻辑
UNTIL 条件
END REPEAT;
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSumRepeat(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    REPEAT
        SET total = total + n;
        SET n = n - 1;
    UNTIL n <= 0
    END REPEAT;
    SELECT total;
END //
DELIMITER ;

调用存储过程：

CALL CalculateSumRepeat(100);

LOOP 循环

LOOP 循环是无条件循环，通常与 LEAVE 语句结合使用来退出循环。

语法格式：
[loop_label:] LOOP
    SQL逻辑
END LOOP [loop_label];
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSumLoop(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    sum_loop: 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 //
DELIMITER ;

调用存储过程：

CALL CalculateSumLoop(100);

 

5. 条件处理

  • DECLARE HANDLER: 定义异常处理程序。

    DECLARE CONTINUE HANDLER FOR SQL_ERROR_CODE error_code
BEGIN
    -- 异常处理语句
END;

6. 游标操作

  • DECLARE CURSOR: 声明游标。

    DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table WHERE condition;

  • OPEN CURSOR: 打开游标。

    OPEN cursor_name;

  • FETCH CURSOR: 读取游标数据。

    FETCH cursor_name INTO var1, var2;

  • CLOSE CURSOR: 关闭游标。

    CLOSE cursor_name;

7. 调试与优化

  • SET: 调整优化器参数。

    SET optimizer_switch = 'index_merge=on';

  • SHOW VARIABLES: 查看系统变量。

    SHOW VARIABLES LIKE 'optimizer_switch';

8.案例 

以下是一个包含事务处理的存储过程示例：

DELIMITER //

CREATE PROCEDURE transfer_funds(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    START TRANSACTION;

    IF amount > 0 THEN
        -- 从源账户扣除金额
        UPDATE accounts SET balance = balance - amount WHERE id = from_account;
        -- 向目标账户增加金额
        UPDATE accounts SET balance = balance + amount WHERE id = to_account;
        -- 提交事务
        COMMIT;
    ELSE
        -- 回滚事务
        ROLLBACK;
    END IF;
END //

DELIMITER ;

这个存储过程通过事务确保资金转账操作的原子性。

9.补充知识：游标

注意事项

  • 游标只能在存储过程和函数中使用。

  • 游标是只读的，不能用于修改数据，但可以通过 SELECT 查询结果集。

  • 需要显式地打开和关闭游标，以确保资源的正确释放。

通过使用游标，可以对查询结果集进行逐行处理，这对于需要对每行数据执行复杂操作的场景非常有用

DELIMITER //
CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE orderId INT;
    DECLARE orderStatus VARCHAR(20);

    -- 声明一个名为 cur 的游标。
    -- 该游标基于查询 SELECT id, status FROM orders，用于存储查询结果集。
    -- 可以通过游标逐行访问和操作 orders 表中的每一行数据。
    DECLARE cur CURSOR FOR SELECT id, status FROM orders;
    -- 定义一个异常处理程序，当游标遍历完所有行（触发 NOT FOUND 异常）时，将变量 done 设置为 1。
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur; -- 打开游标
    order_loop: LOOP
        FETCH cur INTO orderId, orderStatus; -- 从游标中提取数据
        IF done THEN
            LEAVE order_loop; -- 如果没有更多数据，退出循环
        END IF;
        -- 在这里处理每一行数据
        UPDATE orders SET status = 'processed' WHERE id = orderId;
    END LOOP order_loop;
    CLOSE cur; -- 关闭游标
END //
DELIMITER ;

在这个存储过程中，当游标遍历完所有行时，FETCH cur INTO orderId, orderStatus 会触发 NOT FOUND 异常。此时，CONTINUE HANDLER 将 done 设置为 1，退出循环。

通过这种方式，可以优雅地处理游标遍历完成的情况，确保程序不会因异常而中断。

10.查询当前数据库有哪些存储过程 

SELECT 
    SPECIFIC_NAME AS '存储过程名', 
    ROUTINE_SCHEMA AS '数据库名', 
    CREATED AS '创建时间', 
    LAST_ALTERED AS '最后修改时间'
FROM 
    information_schema.ROUTINES
WHERE 
    ROUTINE_TYPE = 'PROCEDURE'
    AND ROUTINE_SCHEMA = 'test_mybatis';

自定义函数

1. 定义自定义函数

  • CREATE FUNCTION: 用于创建一个新的自定义函数。

    CREATE FUNCTION function_name (parameter_list)
RETURNS return_type
BEGIN
    -- 函数体
END;

    • function_name: 函数名称。

    • parameter_list: 参数列表，参数可以是 IN 类型。

    • RETURNS: 指定函数返回值的类型。

2. 参数定义

  • IN: 定义输入参数。MySQL 的自定义函数只支持 IN 类型的参数。

    CREATE FUNCTION add_numbers(IN a INT, IN b INT)
RETURNS INT
BEGIN
    RETURN a + b;
END;

3. 函数体

  • BEGIN/END: 标记函数体的开始和结束。

    BEGIN
    -- 函数逻辑
END;

4. 返回值

  • RETURN: 用于返回函数的计算结果。

    RETURN value;

5. 属性

  • DETERMINISTIC 或 NOT DETERMINISTIC: 标记函数是否是确定性的。

    • 确定性函数：对于相同的输入参数总是返回相同的结果。

    • 非确定性函数：对于相同的输入参数可能返回不同的结果。

    CREATE FUNCTION function_name (parameter_list)
RETURNS return_type DETERMINISTIC
BEGIN
    -- 函数逻辑
END;

6. 修改或删除函数

  • ALTER FUNCTION: 修改已存在的自定义函数。

  • DROP FUNCTION: 删除已存在的自定义函数。

    DROP FUNCTION function_name;

7. 调用函数

  • 可以直接在 SQL 语句中调用自定义函数。

    SELECT add_numbers(5, 10);

存储过程和自定义函数区别 

特性存储过程自定义函数
定义是一组预编译的 SQL 语句和流程控制语句的集合，可以包含多个 SQL 语句和复杂逻辑。是一个用户定义的函数，用于封装特定的功能逻辑，返回一个值。
返回值可以没有返回值，也可以通过 OUT 参数返回多个值。必须返回一个值，返回值类型在创建函数时指定。
参数类型支持 IN、OUT 和 INOUT 类型的参数。只支持 IN 类型的参数。
调用方式使用 CALL 语句调用，不能在 SELECT 中直接调用。可以在 SQL 语句中直接调用，如 SELECT、WHERE、ORDER BY 等。
事务处理可以包含事务操作，控制事务的提交和回滚。不支持事务操作。
代码重用适合封装复杂的业务逻辑，便于维护和代码重用。适合封装简单的逻辑，如计算、转换或条件判断，便于在多个地方重用。
性能存储过程在服务器端执行，与应用程序交互次数少，性能较高。自定义函数在查询中调用时，可能会导致性能问题，需要谨慎使用。
适用场景适用于执行复杂操作的场景，如批量更新数据、执行多个 SQL 语句或返回结果集。适用于需要返回单个值的场景，如计算、转换或简单的逻辑判断。

总结

  • 存储过程：适合执行复杂的操作，可以返回多个值或结果集，适用于批量更新数据、执行多个 SQL 语句或返回结果集等场景。

  • 自定义函数：适合返回单个值的简单逻辑，可以直接在 SQL 语句中调用，适用于计算、转换或条件判断等场景。


http://www.kler.cn/a/538226.html

相关文章：

  • Docker Desktop安装到其他盘
  • Java 中 ArrayList 和 LinkedList 有什么区别？
  • Vue与Konva：解锁Canvas绘图的无限可能
  • TCP服务器与客户端搭建
  • kubernetes 集群命令行工具 kubectl
  • CPP集群聊天服务器开发实践（一）：用户注册与登录
  • 2025年软件测试五大趋势：AI、API安全、云测试等前沿实践
  • （免费送源码）计算机毕业设计原创定制：C#+Asp.Net+SQL Server C#(asp.net)大学生创新创业项目管理系统
  • yolo11训练模型与测试
  • CNN-day8-经典神经网络GoogleNet
  • Android T(13) 源码分析 — BufferQueue 的分析
  • 【无标题】堆
  • F - Building Roads S
  • 实验5 配置OSPFv2验证
  • Kafka中的KRaft算法
  • 探秘 C++ list：在复杂数据管理的编程世界里，它宛如灵动的魔法链条，高效实现元素频繁增删，有序维系数据秩序，无论是海量动态数据缓存、游戏角色属性集处理，还是复杂任务调度编排
  • 网络通信小白知识扫盲（五）
  • deepseek本地部署-linux
  • 设计模式实战运用之模板方法模式
  • 算法兵法全略
  • 链表专题-01
  • Delphi语言的云计算
  • 【免费】2011-2020年各省长途光缆线路长度数据
  • Linux 调用可执行程序
  • pytest-xdist 进行多进程并发测试
  • 网络安全 架构 网络安全架构师考试