Mysql-存储过程

简介

MySQL存储过程（Stored Procedure）是一种在数据库中保存并可以重复执行的SQL代码块。存储过程的主要作用是提高操作的效率，减少网络通信量，并确保业务逻辑的一致性和可重用性。

下面是一个基本的MySQL存储过程示例，以及如何创建、调用和删除存储过程的步骤。

创建存储过程

DELIMITER $$

CREATE PROCEDURE ProcedureName(IN parameter1 INT, OUT parameter2 INT)
BEGIN
    -- 这里写你的SQL逻辑
    SET parameter2 = parameter1 * 2;
END$$

DELIMITER ;

在这个例子中：

ProcedureName 是存储过程的名称。

IN parameter1 INT 是输入参数。

OUT parameter2 INT 是输出参数。

DELIMITER $$ 用来改变MySQL语句结束符，防止在定义过程中出现问题。

调用存储过程

CALL ProcedureName(5, @output);
SELECT @output;

在这个例子中：

CALL ProcedureName(5, @output); 调用存储过程并传递参数。

SELECT @output; 用来获取输出参数的值。

删除存储过程

DROP PROCEDURE IF EXISTS ProcedureName;

基本语法

创建存储过程的基本语法如下：

DELIMITER $$

CREATE PROCEDURE procedure_name([parameter_mode parameter_name data_type[, ...]])
BEGIN
    -- SQL语句
END$$

DELIMITER ;

procedure_name 是存储过程的名称。

parameter_mode 是参数模式，可以是 IN、OUT 或 INOUT。

IN：输入参数，调用存储过程时传递的值。

OUT：输出参数，存储过程执行后返回的值。

INOUT：既是输入参数又是输出参数。

parameter_name 是参数的名称。

data_type 是参数的数据类型。

BEGIN ... END 包含存储过程的SQL语句块。

删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

调用存储过程

CALL procedure_name([parameter[, ...]]);

查看存储过程的状态

SHOW PROCEDURE STATUS LIKE '存储过程名称';

查看存储过程的创建语句

SHOW CREATE PROCEDURE 存储过程名称;

系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)

查看系统变量

SHOW [SESSION|GLOBAL] VARIABLES;   查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '....';   可以通过UKE模糊匹配方式查找变量
SELECT @@[SESSION|GLOBAL] 系统变量名;   查看指定变量的值

设置系统变量

SET  [SESSION|GLOBAL] 系统变量名=值;
SET  @@[SESSION|GLOBAL] 系统变量名=值;

如果没有指定SESSION/GLOBAL，默认是SESSION，

mysql服务重新启动之后，所设置的全局参数会失效，

要想不失效，可以在 /etc/my.cnf 中配置

用户定义变量

用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接。

赋值

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

使用

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名 ;

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

局部变量

是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量

的范围是在其内声明的BEGIN..END块。

声明

DECLARE 变量名 变量类型 [DEFAULT ..];

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

赋值

SET 变量名=值;
SET 变量名:= 值;
SELECT 字段名 INTO 变量名 FROM 表名;

IF使用

两种主要形式

单一条件的 IF 语句

多条件的 IF ... ELSEIF ... ELSE 语句

单一条件的 IF 语句

IF condition THEN
    -- SQL 语句
END IF;

condition：这是一个布尔表达式。如果表达式为真，则执行 THEN 部分的SQL语句。

示例

DELIMITER $$

CREATE PROCEDURE checkStock(IN productID INT)
BEGIN
    DECLARE stock INT;

    SELECT quantity INTO stock FROM products WHERE id = productID;

    IF stock > 0 THEN
        SELECT 'In Stock';
    END IF;
END$$

DELIMITER ;

多条件的 IF ... ELSEIF ... ELSE 语句

IF condition1 THEN
    -- SQL 语句
ELSEIF condition2 THEN
    -- SQL 语句
ELSE
    -- SQL 语句
END IF;

condition1：第一个布尔表达式。如果为真，则执行其后的SQL语句。

condition2：第二个布尔表达式，如果 condition1 为假且 condition2 为真，则执行其后的SQL语句。

ELSE 部分是可选的，如果所有条件都不满足，则执行 ELSE 部分的SQL语句。

示例

DELIMITER $$

CREATE PROCEDURE checkStockLevel(IN productID INT)
BEGIN
    DECLARE stock INT;

    SELECT quantity INTO stock FROM products WHERE id = productID;

    IF stock > 100 THEN
        SELECT 'Stock Level: High';
    ELSEIF stock BETWEEN 50 AND 100 THEN
        SELECT 'Stock Level: Medium';
    ELSEIF stock BETWEEN 1 AND 49 THEN
        SELECT 'Stock Level: Low';
    ELSE
        SELECT 'Out of Stock';
    END IF;
END$$

DELIMITER ;

参数类型

参数分为三种类型：IN 参数、OUT 参数和 INOUT 参数。

IN 参数：

用于向存储过程传递数据。

在存储过程中可以读取，但不能修改。

默认参数类型，如果不指定参数类型，MySQL将其视为 IN 参数。

OUT 参数：

用于从存储过程返回数据。

在存储过程中可以修改，但在存储过程外部无法读取其初始值。

INOUT 参数：

既可以向存储过程传递数据，也可以从存储过程返回数据。

在存储过程中可以读取和修改。

基本语法

CREATE PROCEDURE procedure_name([IN|OUT|INOUT] parameter_name data_type, ...)
BEGIN
    -- SQL语句
END;

示例：使用 IN 参数

DELIMITER $$

CREATE PROCEDURE getUserInfo(IN userId INT)
BEGIN
    SELECT name, email FROM users WHERE id = userId;
END$$

DELIMITER ;

示例：使用 OUT 参数

DELIMITER $$

CREATE PROCEDURE getUserCount(OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM users;
END$$

DELIMITER ;

示例：使用 INOUT 参数

DELIMITER $$

CREATE PROCEDURE updateUserName(INOUT userId INT, IN newUserName VARCHAR(100))
BEGIN
    UPDATE users SET name = newUserName WHERE id = userId;
    SELECT ROW_COUNT() INTO userId;  -- 返回受影响的行数
END$$

DELIMITER ;

使用 IN、OUT 和 INOUT 参数

DELIMITER $$

CREATE PROCEDURE manageUser(IN userId INT, IN newEmail VARCHAR(100), OUT userExists BOOLEAN, INOUT userName VARCHAR(100))
BEGIN
    DECLARE userCount INT;

    -- 检查用户是否存在
    SELECT COUNT(*) INTO userCount FROM users WHERE id = userId;
    SET userExists = (userCount > 0);

    IF userExists THEN
        -- 更新用户的邮箱
        UPDATE users SET email = newEmail WHERE id = userId;
        -- 返回用户的名字
        SELECT name INTO userName FROM users WHERE id = userId;
    ELSE
        -- 如果用户不存在，将userName设为空
        SET userName = NULL;
    END IF;
END$$

DELIMITER ;

小结

• IN 参数：用于传递数据给存储过程，只能读取，不能修改。

• OUT 参数：用于从存储过程返回数据，可以修改，但在存储过程外部无法读取其初始值。

• INOUT 参数：用于传递和返回数据，可以读取和修改

CASE使用

CASE 语句有两种主要形式：

1、简单 CASE 语句：用于比较单个表达式的多个值。

2、搜索 CASE 语句：用于基于多个条件表达式的判断。

简单 CASE 语句

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

expression 是要比较的表达式。

value1, value2, ... 是要与 expression 进行比较的值。

result1, result2, ... 是在 expression 与相应的 value 匹配时返回的结果。

ELSE 是可选的，指定当没有匹配的值时返回的结果。

示例

SELECT 
    product_id,
    CASE category_id
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Books'
        ELSE 'Other'
    END AS category_name
FROM products;

搜索 CASE 语句

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

condition1, condition2, ... 是要判断的条件。

result1, result2, ... 是在相应的条件为真时返回的结果。

ELSE 是可选的，指定当所有条件都不为真时返回的结果。

示例

SELECT 
    order_id,
    total_amount,
    CASE
        WHEN total_amount > 1000 THEN 'High'
        WHEN total_amount BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Low'
    END AS order_priority
FROM orders;

在存储过程中使用CASE

在存储过程中，你也可以使用CASE语句来实现条件控制。

示例

DELIMITER $$

CREATE PROCEDURE determineGrade(IN score INT, OUT grade CHAR(1))
BEGIN
    CASE
        WHEN score >= 90 THEN SET grade = 'A';
        WHEN score >= 80 THEN SET grade = 'B';
        WHEN score >= 70 THEN SET grade = 'C';
        WHEN score >= 60 THEN SET grade = 'D';
        ELSE SET grade = 'F';
    END CASE;
END$$

DELIMITER ;

在 UPDATE 语句中使用 CASE

你可以在 UPDATE 语句中使用 CASE 来有条件地更新表中的数据。

示例

UPDATE employees
SET salary = CASE
    WHEN job_title = 'Manager' THEN salary * 1.10
    WHEN job_title = 'Engineer' THEN salary * 1.05
    ELSE salary * 1.03
END;

小结

• CASE 语句可以在 SELECT, UPDATE, DELETE 等SQL语句中使用，用于条件判断。

• CASE 语句有两种形式：简单 CASE 语句和搜索 CASE 语句。

• 可以在存储过程中使用 CASE 语句实现复杂的逻辑判断。

WHILE使用

WHILE 语句允许你在存储过程中实现循环操作，直到指定的条件为假时才退出循环。

基本语法

WHILE condition DO
    -- SQL语句
END WHILE;

condition：这是一个布尔表达式。在每次循环开始时计算，如果为真，则执行循环体内的SQL语句，否则退出循环。

DO ... END WHILE：包含循环体内要执行的SQL语句。

示例

DELIMITER $$

CREATE PROCEDURE printNumbers()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10 DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

小结

• 变量声明和初始化：在使用 WHILE 循环之前，确保所有循环控制变量已声明和正确初始化。

• 条件控制：确保循环条件能够在适当的时间变为假，以防止死循环。

• 使用 LEAVE 语句：可以在特定条件下使用 LEAVE 语句提前退出循环

REPEAT 

REPEAT 语句用于在满足某个条件之前反复执行一组SQL语句。与 WHILE 语句不同的是，REPEAT 语句会先执行循环体内的SQL语句，然后再检查条件。

基本语法

REPEAT
    -- SQL语句
UNTIL condition
END REPEAT;

UNTIL condition：这是一个布尔表达式。在每次循环结束时计算，如果为真，则退出循环；如果为假，则继续执行循环。

示例 ：打印从1到10的数字。

DELIMITER $$

CREATE PROCEDURE printNumbers()
BEGIN
    DECLARE i INT DEFAULT 1;

    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > 10
    END REPEAT;
END$$

DELIMITER ;

LOOP使用

与 WHILE 和 REPEAT 语句不同的是，LOOP 语句没有内置的条件检查机制，通常需要结合 LEAVE 语句和条件控制来终止循环

基本语法

label: LOOP
    -- SQL语句
END LOOP label;

label 是循环的标签，用于标识并在需要时退出该循环。

LOOP 语句内的SQL语句会被反复执行，直到执行 LEAVE label; 语句。

示例 ：一个简单的示例，在存储过程中使用LOOP语句打印从1到10的数字。

DELIMITER $$

CREATE PROCEDURE printNumbers()
BEGIN
    DECLARE i INT DEFAULT 1;

    loop_label: LOOP
        IF i > 10 THEN
            LEAVE loop_label;
        END IF;
        SELECT i;
        SET i = i + 1;
    END LOOP loop_label;
END$$

DELIMITER ;

游标

游标（Cursor）用于遍历查询结果集，逐行处理数据。游标主要用于需要对查询结果集进行逐行处理的情况，例如批量更新或复杂计算。使用游标可以实现对每一行数据进行详细处理。

使用游标通常包括以下几个步骤：

1. 声明游标。

2. 打开游标。

3. 获取数据（逐行）。

4. 关闭游标。

基本语法

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor_name;

FETCH cursor_name INTO variable1, variable2, ...;

CLOSE cursor_name;

示例，使用游标遍历 employees 表，并输出每个员工的 id 和 name。

DELIMITER $$

CREATE PROCEDURE listEmployees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT emp_id, emp_name;
    END LOOP read_loop;

    CLOSE emp_cursor;
END$$

DELIMITER ;

示例使用游标遍历 employees 表，并将每个员工的工资增加10%。

DELIMITER $$

CREATE PROCEDURE updateEmployeeSalaries()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;

    DECLARE emp_cursor CURSOR FOR SELECT id FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO emp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE employees SET salary = salary * 1.10 WHERE id = emp_id;
    END LOOP read_loop;

    CLOSE emp_cursor;
END$$

DELIMITER ;

游标的声明：游标必须在存储过程的声明部分（即 BEGIN 之后，第一条 SQL 语句之前）声明。

错误处理：使用 DECLARE CONTINUE HANDLER 处理 NOT FOUND 情况，以便在游标到达结果集末尾时退出循环。

关闭游标：在存储过程结束前关闭游标以释放资源

小结

1. 游标用于遍历查询结果集，逐行处理数据。

2. 使用游标的步骤包括：声明、打开、获取数据、关闭。

3. 结合其他控制结构可以实现更复杂的数据处理逻辑。

4. 确保正确处理游标的结束情况并释放资源。

Handler（条件处理程序）

条件处理程序（Handler）用于处理在执行SQL语句时可能发生的异常情况。通过使用条件处理程序，你可以捕获并处理错误和其他特定条件，从而提高存储过程的健壮性和可靠性。

基本语法

DECLARE handler_type HANDLER FOR condition_type [condition_value] statement;

handler_type：可以是 CONTINUE 或 EXIT。CONTINUE 表示在处理程序执行后继续执行后续语句，而 EXIT 表示在处理程序执行后退出当前块。

condition_type：可以是 SQLSTATE、SQLWARNING、NOT FOUND 或 SQLEXCEPTION。

condition_value：在使用 SQLSTATE 时指定特定的SQL状态代码。

statement：当触发条件时要执行的语句。

示例：处理未找到的记录

以下示例演示了如何使用条件处理程序来处理 NOT FOUND 条件，即当游标到达结果集的末尾时触发

DELIMITER $$

CREATE PROCEDURE listEmployees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO emp_id, emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT emp_id, emp_name;
    END LOOP read_loop;

    CLOSE emp_cursor;
END$$

DELIMITER ;

示例：处理SQL异常

以下示例演示了如何使用条件处理程序来处理SQL异常（SQLEXCEPTION）。

DELIMITER $$

CREATE PROCEDURE safeInsertEmployee(IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 异常处理逻辑，例如记录错误日志或回滚事务
        ROLLBACK;
        SELECT 'Error occurred, rolling back changes' AS error_message;
    END;

    START TRANSACTION;

    -- 假设这里可能会抛出异常
    INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);

    COMMIT;
END$$

DELIMITER ;

示例：处理特定SQLSTATE

以下示例演示了如何使用条件处理程序来处理特定的SQL状态代码（SQLSTATE）。

DELIMITER $$

CREATE PROCEDURE handleDuplicateKey()
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        -- 处理重复键错误
        SELECT 'Duplicate key error occurred' AS error_message;
    END;

    -- 可能引发重复键错误的操作
    INSERT INTO employees (id, name) VALUES (1, 'John Doe');
END$$

DELIMITER ;

小结

1. DECLARE ... HANDLER：用于声明条件处理程序。

2. CONTINUE 和 EXIT：指定在处理程序执行后是否继续执行后续语句或退出当前块。

3. 条件类型：包括 NOT FOUND、SQLWARNING、SQLEXCEPTION 和特定的 SQLSTATE 代码。

4. 使用场景：处理游标结束、SQL异常、特定错误等。