我在广州学 Mysql 系列——存储过程与存储函数详解

ℹ️大家好，我是练小杰，今天周五了，一周就这样从手上溜走了，还有两星期过年！！
本文将学习MYSQL中存储过程与存储函数的概念~~
回顾：👉【索引详解】【索引相关练习】
数据库专栏👉【数据库专栏】~
想要了解更多内容，主页 【练小杰的优快云】

存储过程与存储函数

存储过程（Stored Procedure）

存储过程是一组预编译的 SQL 语句集合，可以接受输入参数、执行操作并返回结果集或输出参数。它类似于编程语言中的函数或方法，但主要目的是执行一系列数据库操作。

• 特点：

  1. 参数类型：可以有输入参数(IN) 、输出参数(OUT)和输入输出参数（INOUT）
  2. 返回值： 可以通过输出参数返回多个值或结果集，但不支持像函数那样直接返回一个单一的值。
  3. 调用方式： 使用 CALL 语句调用。
  4. 用途： 适用于执行复杂的业务逻辑、批量操作、数据迁移等。

存储函数（Stored Function）

存储函数是一组预编译的 SQL 语句集合，主要用于执行计算并返回一个单一的值。它类似于编程语言中的函数，可以用在 SQL 语句中，如 SELECT、WHERE 子句等。

• 特点：

  1. 参数类型： 只能有输入参数（IN）。
  2. 返回值： 必须有一个 RETURN 语句返回一个单一的值。
  3. 调用方式： 可以像内置函数一样在 SQL 语句中使用。
  4. 用途： 适用于执行简单的计算、逻辑判断、数据转换等

⚠️主要区别

选择存储过程还是存储函数

• 存储过程： 若需要执行一系列数据库操作、批量处理数据或执行复杂的业务逻辑，使用存储过程更方便。

• 存储函数： 若需要在 SQL 查询中进行简单的计算、数据转换或逻辑判断时，使用存储函数更好。

创建存储过程

• 创建存储过程，使用CREATE PROCEDURE语句

CREATE PROCEDURE  sp_name 
 ([proc_parameter[,...]])  [characteristics ...]
	routine_body

命令解释

• CREATE PROCEDURE : 用于创建一个新的存储过程，后面跟存储过程的名称。
• sp_name： 指定存储过程的名称。
• [proc_parameter[,...]] ： 存储过程的参数列表

参数类型包括：

IN： 输入参数（默认类型）。用于将值传递给存储过程。
OUT： 输出参数。用于从存储过程返回值给调用者。
INOUT： 输入输出参数。既可以接收调用者传递的值，也可以返回修改后的值。

• [characteristics ...]： 指定存储过程的特性或选项
• 常见的特性如下：

LANGUAGE SQL：指定存储过程使用的语言为 SQL（这是默认设置）

---

DETERMINISTIC ：表示相同的输入参数总是产生相同的结果。

---

NOT DETERMINISTIC（默认）：表示相同的输入参数可能产生不同的结果。

---

CONTAINS SQL：存储过程包含 SQL 语句，但不读取或修改数据。

---

NO SQL：存储过程不包含 SQL 语句。

---

READS SQL DATA：存储过程读取数据，但不修改数据。

---

MODIFIES SQL DATA（默认）：存储过程修改数据。

---

SQL SECURITY DEFINER | INVOKER：

• DEFINER (默认)：存储过程以定义者的权限执行。
• INVOKER：存储过程以调用者的权限执行。

---

• routine_body : 包含存储过程的主体逻辑，即一系列的 SQL 语句。

• 使用 BEGIN 和 END 包含多个语句，或者单个语句不需要 BEGIN…END

例如：

 BEGIN
	   SELECT * FROM employees WHERE department_id = dept_id;
 END

创建存储函数

• 创建存储过程，使用CREATE FUNCTION语句

CREATE FUNCTION 
	func_name ([func_parameter[,...]])
	RETURNS type [characteristic ...]
	routine_body

命令解释

• CREATE FUNCTION ： 用于创建一个新的存储函数，后跟函数的名称。

• func_name： 指定存储函数的名称。

• ([func_parameter[,...]]) ： 定义存储函数的参数列表，参数类型只有 IN 参数（默认），用于将值传递给函数。例如，(salary DECIMAL(10,2))

• RETURNS type： 指定函数返回值的类型，如 INT、VARCHAR(15)、DECIMAL(10,2) 等

• [characteristic ...]： 指定存储函数的特性或选项，常见特性看上面！！

• routine_body： 一系列的 SQL 语句，与存储过程的语法一样

变量的使用

定义变量

• DECLARE 是用于在 MySQL 的存储过程（Stored Procedure）或存储函数（Stored Function）内部声明局部变量的命令。
• DECLARE 语句只能在存储过程或存储函数的 BEGIN...END 块内使用，且必须在任何其他语句之前声明变量。
• 基本语法：

DECLARE var_name[,varname]… date_type [DEFAULT value];

• 说明：

DECLARE： 用于声明一个或多个局部变量。
var_name [, var_name] ... : 指定要声明的一个或多个变量名称
data_type ： 指定变量的数据类型, 如 INT、VARCHAR(50)、DECIMAL(10,2)、DATE等。
[DEFAULT value] ： 为变量指定一个默认值。如果声明变量时没有提供 DEFAULT 子句，变量将被初始化为 NULL。 DEFAULT 后跟一个常量值或表达式。

• 实例：

DECLARE total INT  DEFAULT 0;  
DECLARE name VARCHAR(100) DEFAULT 'Unknown';

为变量赋值

SET 是用于在 MySQL 的存储过程（Stored Procedure）、存储函数（Stored Function）或其他编程结构中为变量赋值的基本命令。

SET var_name = expr [, var_name = expr] ...;

• 说明：

SET ： 用于为变量赋值，可以一次为多个变量赋值，变量之间用逗号分隔。
var_name ： 指定要赋值的变量名称

• 用户定义的变量以 @ 符号开头，例如 @total。
• 局部变量在存储过程或函数内部使用 DECLARE 声明的变量
• 系统变量是在 MySQL 提供的变量，例如 @@session.sql_mode

expr：指定赋给变量的值或表达式，可以是常量、表达式、函数调用、另一个变量的值等。

定义条件和处理程序

定义条件（Condition Definitions）

定义条件用于为特定的错误代码或 SQLSTATE 值指定一个名称。

• 基本语法