Mysql之存储过程

1.存储过程概述

• 定义：MySQL 存储过程是一组预编译的 SQL 语句，它们被存储在数据库中，并可以通过调用来执行。
• 用途：存储过程可以用于封装复杂的 SQL 操作、提高代码的重用性、增强数据操作的安全性和性能。
• 特点：可以接受参数，也可以返回数据，减少网络交互，效率提升

2.存储过程的基本语法

2.1创建存储过程

DELIMITER $$  -- 修改分隔符以避免与语句结束符冲突
CREATE PROCEDURE procedure_name (parameters)
BEGIN
   -- SQL 语句
END$$
DELIMITER ;  -- 恢复分隔符

存储过程的参数：

• IN：输入参数，用于向存储过程传递值。
• OUT：输出参数，用于存储过程返回数据。
• INOUT：输入输出参数，既可以接收输入数据，又可以返回结果。

为什么需要改变分割符？

改变分隔符 (DELIMITER) 主要是为了避免语句结束符（;）与存储过程、触发器等程序块中的语法冲突。如图一所示：

2.2调用存储过程

call procedure_name(parameters)

2.3查看存储过程信息

方法一：查看所有数据库的存储过程（！！！非必要不用）

show procedure status;

方法二：查看当前数据库的存储过程

show procedure status where db='db_name';

• Db：存储过程所在的数据库
• Name：存储过程的名称
• Type：存储过程类型（例如 PROCEDURE）
• Definer：存储过程的定义者
• Modified：最后修改时间
• Created：创建时间
• Security_type：安全类型
• Comment：存储过程的注释

2.4查看存储过程定义

show create procedure procedure_name;

2.5删除存储过程

drop procedure procedure_name;

3.变量

3.1查看系统变量

3.1.1查看所有系统变量

show session variables;------查看当前环境变量
show global variables;-------查看全局环境变量

3.1.2系统变量的模糊匹配

show session variables like '...';
​show global variables like '...';

3.1.3查看指定变量

select @@global.tname;----查看指定全局环境变量
select @@session.tname;----查看当前会话环境变量

3.2设置全局变量vs设置当前（会话）变量

set global transaction isolation level read committed;

设置全局隔离级别

现象：全局隔离级别发生变化，会话隔离级别没有变化

重新启动一个新的会话：

设置当前会话的隔离级别：

set session transaction isolation level read committed;

重新启动一个会话：

总结：

• 全局与会话隔离权限的关系：在大多数系统中，会话和全局权限是两者独立的，但又有关系。全局权限通常决定了系统中的默认行为和限制，而会话的权限则可以根据用户的需求做出适当的修改。在此系统中，修改全局的隔离权限时，现有会话的权限不会立刻发生变化，因为当前会话已被初始化并已经从全局权限中导入了隔离配置。
• 会话的初始化与导入：当启动新会话时，系统会从全局隔离权限中导入当前的配置。此时新会话会继承全局隔离权限的设置。由于会话和全局权限是分离的，当当前会话发生变化时，它并不会影响到全局设置，反之亦然。
• 修改全局隔离权限的时效性：这种机制的设计可以确保当前会话的稳定性，在进行全局权限修改时，避免对现有会话产生影响。但在重新启动会话时，新的会话会基于当前全局权限的状态进行初始化。
• 对系统设计的影响：这种设计确保了全局权限的统一性和管理的集中性，同时又能保持会话的灵活性，使得管理员或系统能够灵活调整权限，而不需要影响到现有的会话状态。

3.3用户定义变量

在 MySQL 中，用户定义变量（User-defined Variables）是指由用户在会话级别创建的临时变量。用户可以在 SQL 语句中使用这些变量来存储数据，进行计算或临时保存查询结果。用户定义变量的作用范围仅限于当前会话（连接），并且会话结束时这些变量会被自动销毁。

在 MySQL 中，用户定义变量的语法非常简单。变量名通常以 @ 开头，后面跟着变量名。使用 SET 语句或在查询中直接赋值给变量。

使用 SET 语句定义变量：

SET @variable_name = value;-----方法一
SET @variable_name := value;----方法二

例如，定义一个名为 @age 的变量并赋值为 25：

SET @age = 25;

也可以直接在查询语句中进行赋值：

SELECT @variable_name := expression;

例如，将查询结果赋值给变量：

SELECT @age := age FROM users WHERE name = 'John';-----方法一

SELECT age into @age FROM users WHERE name = 'John';---方法二

3.4局部变量

在 MySQL 中，局部变量是指在存储过程、函数或触发器内部定义的变量。它们的作用范围仅限于该存储过程、函数或触发器的执行期间，超出范围后会自动销毁。局部变量通常用于在存储过程或函数中临时存储数据，执行逻辑运算，或传递信息。

3.4.1.局部变量的声明

局部变量在 MySQL 存储过程或函数中使用 DECLARE 语句声明。它们的作用范围仅限于声明它们的存储过程、函数或触发器，并且不能在 SQL 查询外部使用

局部变量特点：

• 局部性：局部变量仅在存储过程、函数或触发器的执行期间有效。在存储过程或函数执行完成后，局部变量会被自动销毁。
• 不能在查询外部使用：局部变量只能在存储过程、函数或触发器内访问，不能在 SQL 查询的其他地方使用。
• 生命周期：当存储过程或函数执行结束时，所有局部变量的值都会丢失。每次执行存储过程或函数时，局部变量会重新创建并赋予初始值（如果有的话）。

语法：

DECLARE variable_name data_type [DEFAULT value];

• variable_name：变量的名称。
• data_type：变量的数据类型（如 INT, VARCHAR, DATE 等）。
• [DEFAULT value]：可选，设置默认值。如果不指定，则默认值为 NULL。

例子：

DECLARE @user_id INT DEFAULT 100;
DECLARE @user_name VARCHAR(255) DEFAULT 'John';

3.4.2 局部变量的使用

局部变量通常用于存储中间结果、进行计算或在存储过程/函数中存储查询结果。可以在存储过程的 BEGIN 和 END 之间访问这些变量。

• 声明位置：DECLARE 语句必须在存储过程、函数或触发器的开头部分，即 BEGIN 之后。
• 不能使用用户定义变量的命名方式：局部变量不能以 @ 开头，@ 用于用户定义的会话变量。
• 变量的初始值：如果局部变量未指定初始值，则其值默认为 NULL，因此需要注意处理 NULL 的情况。

示例 1：在存储过程中使用局部变量

delimiter ##
create procedure s2()
begin
  declare a int;
  declare b int;
  declare c varchar(20);
  set b=1;
  set c='小王';
  select a,b,c;
end ##


DELIMITER ;

在上述例子中：

• original_price, discount_rate, final_price 都是局部变量。
• DECLARE 语句在 BEGIN 和 END 之间定义这些变量。
• 通过 SET 语句为局部变量赋值，进行计算，并在存储过程中使用。

4.存储过程

4.1 if判断

在 MySQL 存储过程中，IF 函数（或语句）用于根据条件执行不同的逻辑。IF 语句是控制流程语句的一部分，常用于在存储过程中根据特定条件执行不同的 SQL 操作。IF 语句可以单独使用，也可以与 ELSE 和 ELSEIF 配合使用，从而形成多分支的条件判断结构。

最简单的 IF 语句用于判断一个条件是否为 TRUE，如果为 TRUE，则执行特定的 SQL 操作。

语法：

IF condition THEN
    -- 执行的 SQL 语句
ELSEIF condition THEN
    -- 执行的 SQL 语句
ELSE
    -- 执行的 SQL 语句
END IF;

示例：

DELIMITER $$

CREATE PROCEDURE CheckScore()
BEGIN
   declare score int default 58;
   declare result varchar(10);
   if score>=80 then
        set result:='优秀';
   elseif score>=60 then
        set result:='及格';
   else
        set result:='不及格';
   end if;
   select result;
END$$

DELIMITER ;

4.2存储参数

1. IN：含义：该类参数作为输入，也就是需要调用时传入值。
2. OUT：含义：该类参数作为输出，也就是该参数可以作为返回值。
3. INOUT：含义：该类参数既可以作为输入参数，也可以作为输出参数。

CREATE PROCEDURE 存储过程名称 (IN/OUT/INOUT 参数名 参数类型)
BEGIN
    -- SQL语句
END;

4.2.1 IN简单使用

DELIMITER $$

CREATE PROCEDURE CheckScoreIN(in score int)
BEGIN
   declare result varchar(10);
   if score>=80 then
        set result:='优秀';
   elseif score>=60 then
        set result:='及格';
   else
        set result:='不及格';
   end if;
   select result;
END$$

DELIMITER ;

4.2.2 OUT简单使用

DELIMITER $$

CREATE PROCEDURE CheckScoreOUT(in score int,out result varchar(20))
BEGIN
   if score>=80 then
        set result:='优秀';
   elseif score>=60 then
        set result:='及格';
   else
        set result:='不及格';
   end if;
END$$

DELIMITER ;

4.2.3INOUT简单使用

DELIMITER $$

CREATE PROCEDURE CheckScoreINOUT(inout score double)
BEGIN
     set score:=score*0.5;
END$$

DELIMITER ;

4.3 case语句

在 MySQL 中，CASE 语句是一种条件表达式，允许您根据不同的条件选择不同的结果。CASE 语句通常用于 SELECT 查询、UPDATE 语句或存储过程中的条件判断。

语法：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    WHEN value3 THEN result3
    ELSE default_result
END

• expression：需要判断的表达式。
• value1, value2, value3：与表达式值进行比较的不同情况。
• result1, result2, result3：与相应条件匹配时返回的结果。
• ELSE：如果没有任何条件匹配时返回的默认值（可选）。

实例：

delimiter ##
create procedure s4(in score int ,out grade varchar(3))
begin
case
    when score between 90 and 100  then set grade:='A';
    when score between 80 and 90   then set grade:='B';
    when score between 60 and 79   then set grade:='C';
    when score between 0  and 59   then set grade:='D';
    else set grade:='NO';
end case;
end ##
delimiter ;

4.4 while循环

在 MySQL 中，WHILE 循环是一种常用的控制结构，用于在给定条件为 TRUE 时反复执行一组 SQL 语句。WHILE 循环适用于需要重复执行某些操作的场景，直到满足指定的退出条件。

WHILE condition DO
    -- 执行的 SQL 语句
END WHILE;

• condition：循环继续执行的条件。当条件为 TRUE 时，循环会执行；当条件为 FALSE 时，循环终止。
• 循环内的 SQL 语句会在每次循环时执行。
• 一旦 condition 不再为 TRUE，WHILE 循环就会停止