MySQL 存储过程面试基础知识总结

发布于：2025-07-01 ⋅ 阅读:(19) ⋅ 点赞:(0)

文章目录

MySQL 存储过程面试基础知识总结

一、存储过程基础

存储过程（Stored Procedure）是一组 SQL 语句的集合，存储在数据库中，通过指定名称和参数调用执行。它主要用于提高性能、代码复用和增强安全性。

（一）概述

1.优点
  • 提高性能：存储过程在数据库服务器端执行，减少了网络传输的次数。例如，当需要对大量数据进行复杂的查询和更新操作时，存储过程可以将这些操作封装在一起，一次执行完成，大大减少了网络通信开销。而且存储过程在第一次执行时会被编译，之后的调用可以直接执行编译后的代码，提高了执行效率。
  • 代码复用：可以将常用的 SQL 操作封装成存储过程，方便在不同的应用程序和地方调用。比如，一个企业数据库中，经常需要查询员工的详细信息，包括姓名、部门、工资等，将这些查询操作封装成存储过程后，不同的部门在开发自己的应用程序时就可以直接调用这个存储过程来获取数据，避免重复编写代码。
  • 安全性增强：可以通过存储过程控制用户对数据库的访问权限。例如，只允许用户通过特定的存储过程来修改数据，而不能直接对表进行修改操作，这样可以防止用户对数据库进行不恰当的操作，提高数据库的安全性。
2.缺点
  • 可移植性差：不同数据库管理系统（DBMS）的存储过程语法有所不同。例如，SQL Server 使用 Transact - SQL（T - SQL）语言来编写存储过程，而 MySQL 使用自己的存储过程语法。如果要将一个数据库从 SQL Server 迁移到 MySQL，存储过程可能需要重新编写或修改。
  • 调试困难：存储过程的调试相对复杂，特别是在存储过程比较复杂，包含多个分支和循环时。与在应用程序代码中调试相比，数据库端的调试工具通常没有那么强大，很难像在高级语言（如 Java、Python）中那样方便地设置断点、查看变量值等。

（二）创建与调用

1.创建存储过程

在 MySQL 中，创建存储过程的基本语法如下：

DELIMITER $$

CREATE PROCEDURE procedure_name
    ( [ IN | OUT | INOUT ] parameter_name parameter_data_type, ... )
BEGIN
    -- SQL statements
END$$

DELIMITER ;
  • DELIMITER：MySQL 默认的语句分隔符是分号（;）。在存储过程内部，可能会包含多个 SQL 语句，这些语句也需要用分号分隔。为了避免与存储过程内部的分号冲突，需要将分隔符改为其他字符（如 $$）。在存储过程定义完成后，再将分隔符改回默认的分号。
  • IN、OUT、INOUT：用于指定参数的类型。IN 表示输入参数，OUT 表示输出参数，INOUT 表示输入输出参数。
  • parameter_name 和 parameter_data_type：分别是参数的名称和数据类型。例如，IN employee_id INT 表示一个名为 employee_id 的输入参数，数据类型为整数。
  • SQL statements：是存储过程要执行的 SQL 语句，可以是查询、插入、更新、删除等操作。

例如，创建一个存储过程，用于查询员工的工资信息：

DELIMITER $$

CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGIN
    SELECT salary
    FROM employees
    WHERE employee_id = employee_id;
END$$

DELIMITER ;
2.调用存储过程

调用存储过程的语法为：

CALL procedure_name(parameter1, parameter2, ...);

对于上面创建的 GetEmployeeSalary 存储过程，调用它来查询员工编号为 1001 的员工工资：

CALL GetEmployeeSalary(1001);
3.查看存储过程

查看某个数据库下面的存储过程：

select name from mysql.proc where db='数据库名';
 
或者
 
select routine_name from information_schema.routines where routine_schema='数据库名';
 
或者
 
show procedure status where db='数据库名';

在 MySQL 8.0 及更高版本中，存储过程和函数的元数据存储在 INFORMATION_SCHEMA.ROUTINES 表中，而不是 mysql.proc 表中。
在这里插入图片描述

在这里插入图片描述

详细查看存储过程：
1.SHOW CREATE PROCEDURE 语句

SHOW CREATE PROCEDURE procedure_name;

在这里插入图片描述

2.INFORMATION_SCHEMA 表

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'procedure_name';

在这里插入图片描述

4.修改存储过程

删除并重新创建

DROP PROCEDURE procedure_name;
CREATE PROCEDURE procedure_name ...

使用 ALTER PROCEDURE 语句（MySQL 8.0+）

ALTER PROCEDURE procedure_name ...
5.存储过程权限管理

授予权限

GRANT EXECUTE ON procedure_name TO 'username'@'host';

撤销权限

REVOKE EXECUTE ON procedure_name FROM 'username'@'host';

（三）参数

1.输入参数

输入参数是调用存储过程时从外部传入的参数，存储过程内部根据这些参数来执行相应的操作。在前面的 GetEmployeeSalary 存储过程中，employee_id 就是一个输入参数。

2.输出参数

输出参数是存储过程执行完成后返回给调用者的参数。在 MySQL 中，输出参数需要在参数定义时加上 OUT 关键字。例如，创建一个存储过程，计算两个数的和，并将结果通过输出参数返回：

DELIMITER $$

CREATE PROCEDURE AddTwoNumbers(IN number1 INT, IN number2 INT, OUT sum INT)
BEGIN
    SET sum = number1 + number2;
END$$

DELIMITER ;

调用这个存储过程并获取输出参数的值：

SET @result = 0;
CALL AddTwoNumbers(5, 3, @result);
SELECT @result AS Sum;
3.输入输出参数

输入输出参数既可以接收外部传入的值，也可以在存储过程执行完成后将修改后的值返回给调用者。在 MySQL 中，输入输出参数需要加上 INOUT 关键字。例如，创建一个存储过程，将输入的数字乘以 2，并将结果返回：

DELIMITER $$

CREATE PROCEDURE DoubleNumber(INOUT number INT)
BEGIN
    SET number = number * 2;
END$$

DELIMITER ;

调用这个存储过程：

SET @input_number = 10;
CALL DoubleNumber(@input_number);
SELECT @input_number AS Result;

（四）控制流语句

1.IF - ELSE 语句

用于条件判断。例如，创建一个存储过程，根据员工的工资等级（通过输入参数传入）来判断是否发放奖金：

DELIMITER $$

CREATE PROCEDURE CheckBonus(IN salary_grade INT)
BEGIN
    IF salary_grade = 1 THEN
        SELECT '发放奖金';
    ELSE
        SELECT '不发放奖金';
    END IF;
END$$

DELIMITER ;
2.WHILE 循环语句

用于循环操作。例如，创建一个存储过程，将表中的员工工资依次增加 100，直到工资超过 10000：

DELIMITER $$

CREATE PROCEDURE IncreaseSalary()
BEGIN
    DECLARE employee_id INT DEFAULT 1;
    DECLARE current_salary INT;

    WHILE TRUE DO
        SELECT salary INTO current_salary
        FROM employees
        WHERE employee_id = employee_id;

        IF current_salary IS NULL THEN
            LEAVE;
        END IF;

        IF current_salary < 10000 THEN
            UPDATE employees
            SET salary = salary + 100
            WHERE employee_id = employee_id;
        END IF;

        SET employee_id = employee_id + 1;
    END WHILE;
END$$

DELIMITER ;

非常抱歉遗漏了 CASE 语句的介绍。CASE 语句在 MySQL 中用于条件判断，类似于其他编程语言中的 switch 语句。它可以根据不同的条件执行不同的代码块。现在我将补充 CASE 语句的内容，并提供一个示例。

3.CASE 语句

CASE 语句用于根据不同的条件执行不同的代码块。它可以根据一个表达式的值选择执行多个分支中的一个。

CASE 语句的基本语法如下：

CASE case_value
    WHEN when_value1 THEN statement1;
    WHEN when_value2 THEN statement2;
    ...
    ELSE statementN;
END CASE;
  • case_value：要比较的表达式。
  • when_value：与 case_value 比较的值。
  • statement：当 case_value 等于 when_value 时执行的语句。
  • ELSE：可选，当 case_value 不等于任何 when_value 时执行的语句。

以下是一个使用 CASE 语句的存储过程示例，根据员工的工资等级（通过输入参数传入）来判断是否发放奖金，并打印相应的消息：

DELIMITER $$

CREATE PROCEDURE CheckBonus(IN salary_grade INT)
BEGIN
    CASE salary_grade
        WHEN 1 THEN
            SELECT '发放奖金';
        WHEN 2 THEN
            SELECT '发放小礼品';
        WHEN 3 THEN
            SELECT '发放感谢信';
        ELSE
            SELECT '不发放任何奖励';
    END CASE;
END$$

DELIMITER ;

在这个示例中：

  • CASE salary_grade 表示根据 salary_grade 的值进行条件判断。
  • WHEN 1 THEN 表示如果 salary_grade 等于 1，则执行 SELECT '发放奖金';。
  • WHEN 2 THEN 表示如果 salary_grade 等于 2，则执行 SELECT '发放小礼品';。
  • WHEN 3 THEN 表示如果 salary_grade 等于 3，则执行 SELECT '发放感谢信';。
  • ELSE 表示如果 salary_grade 不等于 1、2 或 3，则执行 SELECT '不发放任何奖励';。

调用上述存储过程，传入不同的 salary_grade 值：

CALL CheckBonus(1); -- 输出：发放奖金
CALL CheckBonus(2); -- 输出：发放小礼品
CALL CheckBonus(3); -- 输出：发放感谢信
CALL CheckBonus(4); -- 输出：不发放任何奖励
4.LOOP 循环

语法：

LOOP
    -- 循环体中的语句
END LOOP;

示例：
以下是一个使用 LOOP 循环的存储过程示例，该存储过程将表中的员工工资依次增加 100，直到工资超过 10000：

DELIMITER $$

CREATE PROCEDURE IncreaseSalary()
BEGIN
    DECLARE employee_id INT DEFAULT 1;
    DECLARE current_salary INT;

    my_loop: LOOP
        SELECT salary INTO current_salary
        FROM employees
        WHERE employee_id = employee_id;

        IF current_salary IS NULL THEN
            LEAVE my_loop;
        END IF;

        IF current_salary < 10000 THEN
            UPDATE employees
            SET salary = salary + 100
            WHERE employee_id = employee_id;
        END IF;

        SET employee_id = employee_id + 1;
    END LOOP my_loop;
END$$

DELIMITER ;
5.REPEAT 循环

语法：

REPEAT
    -- 循环体中的语句
UNTIL 条件
END REPEAT;

示例：
以下是一个使用 REPEAT 循环的存储过程示例，该存储过程将表中的员工工资依次增加 100，直到工资超过 10000：

DELIMITER $$

CREATE PROCEDURE IncreaseSalary()
BEGIN
    DECLARE employee_id INT DEFAULT 1;
    DECLARE current_salary INT;

    REPEAT
        SELECT salary INTO current_salary
        FROM employees
        WHERE employee_id = employee_id;

        IF current_salary IS NOT NULL THEN
            IF current_salary < 10000 THEN
                UPDATE employees
                SET salary = salary + 100
                WHERE employee_id = employee_id;
            END IF;
        ELSE
            LEAVE;
        END IF;

        SET employee_id = employee_id + 1;
    UNTIL current_salary IS NULL
    END REPEAT;
END$$

DELIMITER ;

（五）练习

练习一：创建和调用存储过程

创建一个存储过程，用于查询指定部门的员工数量。存储过程名称为 GetEmployeeCountByDepartment，参数为部门编号（@department_id）。
调用这个存储过程，查询部门编号为 10 的员工数量。
答案：

DELIMITER $$

CREATE PROCEDURE GetEmployeeCountByDepartment(IN department_id INT)
BEGIN
    SELECT COUNT(*) AS EmployeeCount
    FROM employees
    WHERE department_id = department_id;
END$$

DELIMITER ;

CALL GetEmployeeCountByDepartment(10);
练习二：使用输出参数

创建一个存储过程，用于计算两个数的乘积，并将结果通过输出参数返回。存储过程名称为 MultiplyTwoNumbers，输入参数为两个数字（@number1 和 @number2），输出参数为乘积（@product）。
调用这个存储过程，计算 4 和 6 的乘积，并将结果存储到一个变量中。
答案：

DELIMITER $$

CREATE PROCEDURE MultiplyTwoNumbers(IN number1 INT, IN number2 INT, OUT product INT)
BEGIN
    SET product = number1 * number2;
END$$

DELIMITER ;

SET @result = 0;
CALL MultiplyTwoNumbers(4, 6, @result);
SELECT @result AS Product;
练习三：控制流语句应用

创建一个存储过程，用于判断一个数字是否为偶数。如果是偶数，打印“偶数”，否则打印“奇数”。存储过程名称为 CheckEvenOdd，输入参数为数字（@number）。
调用这个存储过程，判断数字 7 是否为偶数。
答案：

DELIMITER $$

CREATE PROCEDURE CheckEvenOdd(IN number INT)
BEGIN
    IF number % 2 = 0 THEN
        SELECT '偶数';
    ELSE
        SELECT '奇数';
    END IF;
END$$

DELIMITER ;

CALL CheckEvenOdd(7);

二、变量

（一）局部变量

1.定义

局部变量是在存储过程、函数或语句块中声明的变量，其作用域仅限于声明它的存储过程、函数或语句块内部。

2.声明

使用 DECLARE 语句声明局部变量：

DECLARE variable_name variable_data_type [DEFAULT default_value];
  • variable_name：变量的名称，用于在存储过程中引用该变量。
  • variable_data_type：变量的数据类型，如 INT、VARCHAR、DATE 等。
  • DEFAULT default_value（可选）：为变量指定默认值。如果不指定默认值，则变量的初始值为 NULL。
3.赋值

可以使用 SET 语句或在 SELECT 语句中使用 INTO 子句为局部变量赋值：

SET variable_name = value;

或

SELECT column INTO variable_name
FROM table_name
WHERE condition;

示例：

DELIMITER $$

CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGIN
    -- 声明局部变量
    DECLARE employee_salary DECIMAL(10, 2);

    -- 使用 SELECT ... INTO ... 语句查询员工的工资并赋值给局部变量
    SELECT salary INTO employee_salary
    FROM employees
    WHERE employee_id = employee_id;

    -- 输出查询结果
    SELECT employee_salary AS Salary;
END$$

DELIMITER ;
4.作用域

局部变量的作用域仅限于声明它的存储过程、函数或语句块内部，存储过程或函数执行完毕后，局部变量被销毁。

（二）全局变量

1.定义

全局变量是在整个数据库会话中有效的变量，其值在会话期间保持不变，直到显式地更改它。

2.声明

全局变量不需要显式声明，它们是 MySQL 内置的系统变量，通常以 @@ 开头。例如：

SELECT @@global.variable_name;

或

SET GLOBAL variable_name = value;
3.赋值

可以使用 SET GLOBAL 语句或 SET @@global.variable_name 语法为全局变量赋值：

SET GLOBAL variable_name = value;

或

SET @@global.variable_name = value;
4.作用域

全局变量的作用域是整个数据库会话，所有用户都可以访问和修改全局变量的值，但修改后的值仅对当前会话有效，不会影响其他会话。

（三）示例

1.局部变量示例

以下是一个存储过程示例，展示了如何声明和使用局部变量：

DELIMITER $$

CREATE PROCEDURE CalculateTotal(IN order_id INT, OUT total DECIMAL(10, 2))
BEGIN
    -- 声明局部变量
    DECLARE item_price DECIMAL(10, 2);
    DECLARE item_quantity INT;
    DECLARE total_items INT DEFAULT 0;
    DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;

    -- 查询订单中的商品数量
    SELECT COUNT(*)
    INTO total_items
    FROM order_items
    WHERE order_id = order_id;

    -- 遍历订单中的每个商品
    DECLARE item_cursor CURSOR FOR
        SELECT price, quantity
        FROM order_items
        WHERE order_id = order_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET total_items = 0;

    OPEN item_cursor;

    fetch_loop: LOOP
        FETCH item_cursor INTO item_price, item_quantity;

        IF total_items = 0 THEN
            LEAVE fetch_loop;
        END IF;

        -- 计算每个商品的总价并累加到总金额
        SET total_amount = total_amount + (item_price * item_quantity);
    END LOOP;

    CLOSE item_cursor;

    -- 将总金额赋值给输出参数
    SET total = total_amount;
END$$

DELIMITER ;

在上述存储过程中：

  • 使用 DECLARE 声明了多个局部变量，包括 item_price、item_quantity、total_items 和 total_amount。
  • 这些变量在存储过程的逻辑中用于存储临时数据，如商品价格、数量、订单中商品的总数以及订单的总金额。
  • 变量 total_items 和 total_amount 被赋予了默认值，分别是 0 和 0.0。
  • 使用 SET 语句为变量赋值，例如 SET total_amount = total_amount + (item_price * item_quantity);。
2.全局变量示例

以下是一个示例，展示了如何使用全局变量：

-- 查看全局变量的值
SELECT @@global.max_connections;

-- 设置全局变量的值
SET GLOBAL max_connections = 1000;

-- 或者
SET @@global.max_connections = 1000;

在上述示例中：

  • 使用 SELECT @@global.max_connections; 查看了全局变量 max_connections 的值。
  • 使用 SET GLOBAL max_connections = 1000; 或 SET @@global.max_connections = 1000; 设置了全局变量 max_connections 的值。

（四）面试常问问题

1.局部变量和全局变量的区别是什么？
  • 作用域：局部变量的作用域仅限于声明它的存储过程、函数或语句块内部；全局变量的作用域是整个数据库会话。
  • 声明方式：局部变量使用 DECLARE 语句声明；全局变量不需要显式声明，使用 @@global.variable_name 或 SET GLOBAL 语句。
  • 赋值方式：局部变量使用 SET 语句或 SELECT ... INTO ... 语句赋值；全局变量使用 SET GLOBAL 语句或 SET @@global.variable_name 语法赋值。
  • 生命周期：局部变量的生命周期与存储过程或函数的执行周期相同；全局变量的生命周期是整个数据库会话。
2.如何声明和使用局部变量？
  • 使用 DECLARE 语句声明局部变量，例如 DECLARE variable_name variable_data_type [DEFAULT default_value];。
  • 使用 SET 语句或 SELECT ... INTO ... 语句为局部变量赋值，例如 SET variable_name = value; 或 SELECT column INTO variable_name FROM table_name WHERE condition;。
  • 局部变量的作用域仅限于声明它的存储过程、函数或语句块内部，存储过程或函数执行完毕后，局部变量被销毁。
3.如何查看和设置全局变量的值？
  • 使用 SELECT @@global.variable_name; 查看全局变量的值。
  • 使用 SET GLOBAL variable_name = value; 或 SET @@global.variable_name = value; 设置全局变量的值。
4.在存储过程中如何使用局部变量？
  • 在存储过程的开始部分使用 DECLARE 语句声明局部变量。
  • 使用 SET 语句或 SELECT ... INTO ... 语句为局部变量赋值。
  • 在存储过程的逻辑中通过变量名直接引用和操作局部变量。
5.全局变量和局部变量的命名冲突如何解决？
  • 全局变量和局部变量的命名冲突通常不会发生，因为它们的作用域不同。全局变量以 @@global. 开头，而局部变量在存储过程内部声明和使用。
  • 如果需要在存储过程中访问全局变量，可以显式地使用 @@global. 前缀来区分，例如 SELECT @@global.max_connections;。

附:【局部变量 用户定义的变量（带@的变量） 全局变量 】三者对比

特性 局部变量 用户定义的变量（带@的变量） 全局变量
作用域 仅限于声明它的存储过程、函数或语句块内部 整个数据库会话 整个数据库服务器
声明方式 使用DECLARE语句
示例：DECLARE variable_name variable_data_type [DEFAULT default_value];		 使用SET语句
示例：SET @variable_name = value;		 使用SET GLOBAL语句或SET @@global.语法
示例：SET GLOBAL variable_name = value; 或SET @@global.variable_name = value;
赋值方式 使用SET语句或SELECT ... INTO ...语句
示例：SET variable_name = value; 或SELECT column INTO variable_name FROM table_name WHERE condition;		 使用SET语句
示例：SET @variable_name = value;		 使用SET GLOBAL语句或SET @@global.语法
示例：SET GLOBAL variable_name = value; 或SET @@global.variable_name = value;
生命周期 存储过程或函数执行完毕后销毁 会话结束时销毁 服务器重启或显式更改时更新
用途 存储临时数据，参与计算或作为逻辑控制的依据 存储临时值，传递数据，接收存储过程的输出参数 配置数据库服务器的行为，影响所有会话
示例代码

  • 局部变量示例：

    DELIMITER $$
CREATE PROCEDURE CalculateTotal(IN order_id INT, OUT total DECIMAL(10, 2))
BEGIN
    DECLARE item_price DECIMAL(10, 2);
    DECLARE item_quantity INT;
    DECLARE total_items INT DEFAULT 0;
    DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;
    -- 其他逻辑...
END$$
DELIMITER ;

  • 用户定义的变量（带@的变量）示例：

    SET @result = 0;
CALL AddTwoNumbers(5, 3, @result);
SELECT @result AS Sum;

  • 全局变量示例：

    SELECT @@global.max_connections;
SET GLOBAL max_connections = 1000;
详细解释

  1. 作用域

    • 局部变量：仅限于声明它的存储过程、函数或语句块内部。一旦存储过程或函数执行完毕，局部变量将被销毁。
    • 用户定义的变量（带 @ 的变量）：整个数据库会话。在同一个会话中，这些变量可以跨多个 SQL 语句使用，直到会话结束。
    • 全局变量：整个数据库服务器。所有会话都可以访问和修改全局变量的值，修改后会影响所有当前和未来的会话。

  2. 声明方式

    • 局部变量：使用 DECLARE 语句在存储过程或函数的开始部分声明。
    • 用户定义的变量（带 @ 的变量）：使用 SET 语句在会话中声明和初始化。
    • 全局变量：使用 SET GLOBAL 语句或 SET @@global. 语法声明和赋值。

  3. 赋值方式

    • 局部变量：使用 SET 语句或 SELECT ... INTO ... 语句赋值。
    • 用户定义的变量（带 @ 的变量）：使用 SET 语句赋值。
    • 全局变量：使用 SET GLOBAL 语句或 SET @@global. 语法赋值。

  4. 生命周期

    • 局部变量：存储过程或函数执行完毕后销毁。
    • 用户定义的变量（带 @ 的变量）：会话结束时销毁。
    • 全局变量：服务器重启或显式更改时更新。

  5. 用途

    • 局部变量：用于存储临时数据，参与计算或作为逻辑控制的依据。
    • 用户定义的变量（带 @ 的变量）：用于存储临时值，传递数据，接收存储过程的输出参数。
    • 全局变量：用于配置数据库服务器的行为，影响所有会话。

三、 游标（CURSOR）

定义

游标（Cursor）是数据库中一个临时的工作区，用于存储和操作查询结果集中的数据。

声明

DECLARE cursor_name CURSOR FOR select_statement;

打开游标

OPEN cursor_name;

获取数据

FETCH cursor_name INTO variable_name [, variable_name ...];

关闭游标

CLOSE cursor_name;

示例

以下是一个使用游标的存储过程示例，该存储过程将表中的员工工资依次增加 100，直到工资超过 10000：

DELIMITER $$

CREATE PROCEDURE IncreaseSalary()
BEGIN
    DECLARE employee_id INT;
    DECLARE current_salary INT;
    DECLARE done INT DEFAULT 0;

    DECLARE cursor_name CURSOR FOR
        SELECT employee_id, salary
        FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;

    OPEN cursor_name;

    my_loop: LOOP
        FETCH cursor_name INTO employee_id, current_salary;

        IF done = 1 THEN
            LEAVE my_loop;
        END IF;

        IF current_salary < 10000 THEN
            UPDATE employees
            SET salary = salary + 100
            WHERE employee_id = employee_id;
        END IF;
    END LOOP my_loop;

    CLOSE cursor_name;
END$$

DELIMITER ;

四、处理程序（HANDLER）

定义

处理程序（Handler）用于捕获和处理存储过程或函数执行过程中发生的特定条件或异常。

声明

DECLARE [CONTINUE | EXIT | UNDO] HANDLER FOR condition
    -- 处理程序中的语句

示例

以下是一个使用处理程序的存储过程示例，该存储过程捕获了查询结果为空的情况：

DELIMITER $$

CREATE PROCEDURE GetEmployeeSalary(IN employee_id INT)
BEGIN
    DECLARE employee_salary DECIMAL(10, 2);
    DECLARE done INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;

    SELECT salary INTO employee_salary
    FROM employees
    WHERE employee_id = employee_id;

    IF done = 1 THEN
        SELECT '未找到员工' AS message;
    ELSE
        SELECT employee_salary AS Salary;
    END IF;
END$$

DELIMITER ;

五、 事务控制

事务概述

事务是一组 SQL 语句的集合，这些语句要么全部成功执行，要么全部不执行。事务的目的是确保数据的完整性和一致性。

BEGIN、COMMIT、ROLLBACK 语句

  • BEGIN：开始一个新的事务。
  • COMMIT：提交当前事务，使事务中的所有更改永久生效。
  • ROLLBACK：回滚当前事务，撤销事务中的所有更改。

示例

以下是一个使用事务控制的存储过程示例，该存储过程更新员工的工资，并在发生错误时回滚事务：

DELIMITER $$

CREATE PROCEDURE UpdateEmployeeSalary(IN employee_id INT, IN new_salary DECIMAL(10, 2))
BEGIN
    DECLARE exit_handler INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            SET exit_handler = 1;
        END;

    START TRANSACTION;

    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = employee_id;

    IF exit_handler = 1 THEN
        ROLLBACK;
        SELECT '更新失败' AS message;
    ELSE
        COMMIT;
        SELECT '更新成功' AS message;
    END IF;
END$$

DELIMITER ;

六、 动态 SQL

动态 SQL 概述

动态 SQL 是指在运行时生成和执行 SQL 语句的技术。

PREPARE、EXECUTE、DEALLOCATE PREPARE 语句

  • PREPARE：准备执行 SQL 语句。
  • EXECUTE：执行准备好的 SQL 语句。
  • DEALLOCATE PREPARE：释放准备好的 SQL 语句。

示例

以下是一个使用动态 SQL 的存储过程示例，该存储过程根据表名动态生成和执行 SQL 语句：

DELIMITER $$

CREATE PROCEDURE DynamicSQL(IN table_name VARCHAR(100))
BEGIN
    DECLARE sql_statement VARCHAR(255);

    SET sql_statement = CONCAT('SELECT * FROM ', table_name);

    PREPARE stmt FROM sql_statement;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

八、 存储过程案例：

批量数据处理示例：批量更新用户积分

以下是一个存储过程示例，该存储过程根据用户的消费金额批量更新用户的积分：

DELIMITER $$

CREATE PROCEDURE UpdateUserPoints()
BEGIN
    DECLARE user_id INT;
    DECLARE consumption DECIMAL(10, 2);
    DECLARE done INT DEFAULT 0;

    DECLARE cursor_name CURSOR FOR
        SELECT id, consumption
        FROM users;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;

    OPEN cursor_name;

    my_loop: LOOP
        FETCH cursor_name INTO user_id, consumption;

        IF done = 1 THEN
            LEAVE my_loop;
        END IF;

        UPDATE users
        SET points = points + consumption * 10
        WHERE id = user_id;
    END LOOP my_loop;

    CLOSE cursor_name;
END$$

DELIMITER ;

数据校验示例：校验用户输入数据

以下是一个存储过程示例，该存储过程校验用户输入的用户名和密码是否符合要求：

DELIMITER $$

CREATE PROCEDURE ValidateUser(IN username VARCHAR(100), IN password VARCHAR(100))
BEGIN
    DECLARE valid INT DEFAULT 0;

    IF LENGTH(username) >= 6 AND LENGTH(password) >= 8 THEN
        SET valid = 1;
    END IF;

    SELECT valid AS IsValid;
END$$

DELIMITER ;

复杂业务逻辑封装示例：计算订单总金额

以下是一个存储过程示例，该存储过程根据订单中的商品信息计算订单的总金额：

DELIMITER $$

CREATE PROCEDURE CalculateOrderTotal(IN order_id INT, OUT total DECIMAL(10, 2))
BEGIN
    DECLARE item_price DECIMAL(10, 2);
    DECLARE item_quantity INT;
    DECLARE total_items INT DEFAULT 0;
    DECLARE total_amount DECIMAL(10, 2) DEFAULT 0.0;

    DECLARE cursor_name CURSOR FOR
        SELECT price, quantity
        FROM order_items
        WHERE order_id = order_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET total_items = 0;

    OPEN cursor_name;

    my_loop: LOOP
        FETCH cursor_name INTO item_price, item_quantity;

        IF total_items = 0 THEN
            LEAVE my_loop;
        END IF;

        SET total_amount = total_amount + (item_price * item_quantity);
    END LOOP my_loop;

    CLOSE cursor_name;

    SET total = total_amount;
END$$

DELIMITER ;

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布