SQL必知必会-读书笔记(十三)

存储过程

是什么

存储过程（Stored Procedure） 是一组预编译的SQL语句，它们被存储在数据库中，可以像函数一样被调用。存储过程可以接受输入参数、输出参数，并且可以包含控制流语句（如条件判断、循环等）。存储过程的主要目的是提高数据库操作的效率和安全性，减少网络流量，并且可以封装复杂的业务逻辑。

优点

  • 提高性能：存储过程在创建时会被编译并存储在数据库中，执行时不需要再次编译，提高了执行效率。
  • 减少网络流量：客户端只需要发送存储过程的调用请求，而不需要发送大量的SQL语句，减少了网络传输的数据量。
  • 封装业务逻辑：存储过程可以封装复杂的业务逻辑，使应用程序代码更加简洁。
  • 提高安全性：通过授予用户对存储过程的执行权限，可以限制用户直接访问底层表，提高数据的安全性。

虽然实践中基本用不到存储过程，但是可以作为了解的一环

如何创建

基本语法

1
2
3
4
5
6
7
8
9
DELIMITER //

CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2 datatype)
BEGIN
    -- SQL statements
END //

DELIMITER ;

定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER //

CREATE PROCEDURE GetCustomerOrderSummary(IN customer_id INT, OUT order_count INT, OUT total_amount DECIMAL(10, 2))
BEGIN
    -- 获取订单总数
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE orders.customer_id = customer_id;

    -- 获取订单总金额
    SELECT SUM(order_amount) INTO total_amount
    FROM orders
    WHERE orders.customer_id = customer_id;
END //

DELIMITER ;

调用

1
2
3
4
5
CALL GetCustomerOrderSummary(123, @order_count, @total_amount);

-- 查看结果
SELECT @order_count, @total_amount;

  1. DELIMITER //：更改语句结束符，以便在存储过程中使用分号。
  2. CREATE PROCEDURE GetCustomerOrderSummary：创建名为 GetCustomerOrderSummary 的存储过程。
  3. IN customer_id INT：定义一个输入参数 customer_id，类型为 INT。
  4. OUT order_count INT, OUT total_amount DECIMAL(10, 2)：定义两个输出参数 order_count 和 total_amount，分别用于存储订单总数和总金额。
  5. BEGIN … END：存储过程的主体部分，包含SQL语句和控制流逻辑。
  6. DELIMITER ;：将语句结束符恢复为默认的分号。

游标

游标可以和存储过程、函数配合使用，可以对结果集进行逐行或者每x行的复杂处理

游标（Cursor） 是一种数据库对象，用于遍历结果集中的每一行数据。游标允许逐行处理查询结果，这对于需要对每一行数据进行复杂逻辑处理的场景非常有用。以下是对MySQL游标的详细解释，包括其创建、使用和注意事项。

游标的用途

  • 逐行处理数据：游标允许逐行处理查询结果集，适用于需要对每一行数据进行复杂逻辑处理的场景。
  • 批量处理：在某些情况下，游标可以用于批量处理数据，提高数据处理的灵活性。
  • 复杂业务逻辑：游标可以用于实现复杂的业务逻辑，如循环、条件判断等。

游标的生命周期

游标的生命周期包括以下几个阶段：

  • 声明游标：定义游标并指定其查询结果集。
  • 打开游标：执行查询并将结果集加载到游标中。
  • 提取数据：从游标中逐行提取数据。
  • 关闭游标：释放游标占用的资源。

创建和使用游标

声明游标

使用 DECLARE 语句声明游标，并指定其查询结果集。

1
DECLARE cursor_name CURSOR FOR select_statement;

示例：

1
2
3
4
DECLARE cur_orders CURSOR FOR
SELECT order_id, order_date, customer_id
FROM orders
WHERE order_date >= '2023-01-01';

打开游标

使用 OPEN 语句打开游标，执行查询并将结果集加载到游标中。

1
OPEN cursor_name;

示例：

1
OPEN cur_orders;

提取数据

使用 FETCH 语句从游标中逐行提取数据。

1
FETCH cursor_name INTO variable_list;

示例：

1
FETCH cur_orders INTO v_order_id, v_order_date, v_customer_id;

关闭游标

使用 CLOSE 语句关闭游标，释放游标占用的资源。

1
CLOSE cursor_name;

示例：

1
CLOSE cur_orders;

示例代码

以下是一个完整的示例，展示了如何在存储过程中使用游标来逐行处理订单数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DELIMITER //

CREATE PROCEDURE ProcessOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE v_order_date DATE;
    DECLARE v_customer_id INT;

    -- 声明游标
    DECLARE cur_orders CURSOR FOR
    SELECT order_id, order_date, customer_id
    FROM orders
    WHERE order_date >= '2023-01-01';

    -- 声明继续处理标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur_orders;

    read_loop: LOOP
        FETCH cur_orders INTO v_order_id, v_order_date, v_customer_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 在这里处理每一行数据
        -- 例如，更新订单状态或插入日志
        UPDATE orders
        SET status = 'Processed'
        WHERE order_id = v_order_id;

        INSERT INTO order_logs (order_id, log_message)
        VALUES (v_order_id, CONCAT('Processed on ', NOW()));
    END LOOP;

    -- 关闭游标
    CLOSE cur_orders;
END //

DELIMITER ;

注意事项

  • 性能影响：游标通常比直接使用SQL查询慢，尤其是在处理大量数据时。因此，应谨慎使用游标，并考虑是否有更高效的替代方案。
  • 资源管理：确保在使用完游标后及时关闭游标，以释放资源。
  • 事务处理：在使用游标时，注意事务的管理，确保数据的一致性和完整性。
  • 错误处理：使用 HANDLER 语句来处理游标操作中的错误，确保程序的健壮性。

示例：带错误处理的游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
DELIMITER //

CREATE PROCEDURE ProcessOrdersWithHandler()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_order_id INT;
    DECLARE v_order_date DATE;
    DECLARE v_customer_id INT;

    -- 声明游标
    DECLARE cur_orders CURSOR FOR
    SELECT order_id, order_date, customer_id
    FROM orders
    WHERE order_date >= '2023-01-01';

    -- 声明继续处理标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 声明错误处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    -- 开始事务
    START TRANSACTION;

    -- 打开游标
    OPEN cur_orders;

    read_loop: LOOP
        FETCH cur_orders INTO v_order_id, v_order_date, v_customer_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 在这里处理每一行数据
        -- 例如，更新订单状态或插入日志
        UPDATE orders
        SET status = 'Processed'
        WHERE order_id = v_order_id;

        INSERT INTO order_logs (order_id, log_message)
        VALUES (v_order_id, CONCAT('Processed on ', NOW()));
    END LOOP;

    -- 关闭游标
    CLOSE cur_orders;

    -- 提交事务
    COMMIT;
END //

DELIMITER ;

游标总结

  • 游标 是一种用于逐行处理结果集的数据库对象。
  • 生命周期 包括声明、打开、提取和关闭游标。
  • 使用场景 适用于需要对每一行数据进行复杂逻辑处理的情况。
  • 注意事项 包括性能影响、资源管理、事务处理和错误处理。

通过合理使用游标，可以提高数据处理的灵活性和效率，但需要注意其潜在的性能开销。