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

首先创建存储过程或者自定义函数时，都要使用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 THENSQL逻辑
  ELSEIF condition THENSQL逻辑
  ELSESQL逻辑
  END IF;

  案例：

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

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

  调用存储过程：

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

• CASE: 用于多条件判断。

• 语法格式：

  CASEWHEN condition1 THENSQL逻辑WHEN condition2 THENSQL逻辑ELSESQL逻辑
  END CASE;

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

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

  调用存储过程：

  CALL GetUserRole(1);

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

  LOOP_LABEL: LOOP-- 循环体IF condition THENLEAVE LOOP_LABEL;END IF;
  END LOOP;

 WHILE 循环

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

语法格式：

WHILE 条件 DOSQL逻辑
END WHILE;

案例：

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

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

调用存储过程：

CALL CalculateSum(100);

 

REPEAT 循环

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

语法格式：

REPEATSQL逻辑
UNTIL 条件
END REPEAT;

案例：

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

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

调用存储过程：

CALL CalculateSumRepeat(100);

LOOP 循环

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

语法格式：

[loop_label:] LOOPSQL逻辑
END LOOP [loop_label];

案例：

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

DELIMITER //
CREATE PROCEDURE CalculateSumLoop(IN n INT)
BEGINDECLARE total INT DEFAULT 0;sum_loop: LOOPIF n <= 0 THENLEAVE 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))
BEGINSTART 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()
BEGINDECLARE 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: LOOPFETCH cur INTO orderId, orderStatus; -- 从游标中提取数据IF done THENLEAVE 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
  BEGINRETURN 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 语句中调用，适用于计算、转换或条件判断等场景。