数据库|高效编程|SQL循环语句在实际开发中的巧妙应用

SQL循环语句：让数据库操作如虎添翼的实战技巧

场景引入：电商订单批量处理的烦恼

"小王，这批异常订单需要重新计算优惠金额，大概有5000多单，你处理一下。" 产品经理拍了拍小王的肩膀，小王看着Excel表格里密密麻麻的订单号，头皮发麻——难道要一条条手动更新？作为刚入职三个月的后端开发，他突然想起上周技术分享会上前辈提到的SQL循环语句...

SQL循环基础：不只是简单的重复

SQL循环语句在实际开发中经常被忽视,很多人觉得这是"高级功能"而不敢尝试，其实它就像数据库里的"自动化机器人"，能帮我们处理大量重复性工作。

1 主流数据库中的循环实现

不同数据库系统实现循环的语法略有差异：

MySQL中的循环：

-- 创建存储过程示例
DELIMITER //
CREATE PROCEDURE update_order_discounts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status = '异常';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO order_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 实际业务处理
        UPDATE order_details 
        SET discount = calculate_discount(order_id) 
        WHERE order_id = order_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

SQL Server的WHILE循环：

DECLARE @counter INT = 1;
DECLARE @max_id INT = (SELECT MAX(id) FROM products);
WHILE @counter <= @max_id
BEGIN
    -- 更新产品库存
    UPDATE products 
    SET stock = stock + 10 
    WHERE id = @counter AND discontinued = 0;
    SET @counter = @counter + 1;
END

Oracle的FOR循环：

BEGIN
    FOR rec IN (SELECT employee_id FROM employees WHERE department_id = 10)
    LOOP
        -- 给部门10的员工加薪
        UPDATE employees 
        SET salary = salary * 1.05 
        WHERE employee_id = rec.employee_id;
    END LOOP;
    COMMIT;
END;

实战技巧：循环语句的高阶应用

1 批量数据迁移的优雅方案

当我们需要将数据从旧表迁移到新表结构时,循环配合事务可以确保数据一致性：

-- PostgreSQL示例
DO $$
DECLARE
    old_rec record;
    new_id integer;
BEGIN
    FOR old_rec IN SELECT * FROM legacy_products WHERE stock > 0
    LOOP
        BEGIN
            -- 插入新表
            INSERT INTO products_new (name, price, inventory, category)
            VALUES (old_rec.prod_name, old_rec.unit_price, old_rec.stock, 
                   CASE WHEN old_rec.type = 'E' THEN '电子' ELSE '日用' END)
            RETURNING id INTO new_id;
            -- 更新关联表
            UPDATE product_suppliers 
            SET product_id = new_id 
            WHERE product_id = old_rec.prod_code;
            -- 记录迁移日志
            INSERT INTO migration_log (old_id, new_id, migrated_at)
            VALUES (old_rec.prod_code, new_id, NOW());
        EXCEPTION
            WHEN OTHERS THEN
                -- 错误处理
                INSERT INTO migration_errors (error_msg, old_data)
                VALUES (SQLERRM, row_to_json(old_rec));
        END;
    END LOOP;
END $$;

2 动态SQL与循环的完美结合

生成和执行动态SQL是循环的强大应用场景：

-- SQL Server动态分表统计示例
DECLARE @table_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @year INT = 2023;
-- 创建临时结果表
CREATE TABLE #yearly_stats (
    table_name NVARCHAR(128),
    record_count INT,
    total_amount DECIMAL(18,2)
);
-- 获取所有年度分表
DECLARE table_cursor CURSOR FOR 
SELECT name FROM sys.tables 
WHERE name LIKE 'orders_20%' AND name <= CONCAT('orders_', @year);
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    INSERT INTO #yearly_stats
    SELECT ''' + @table_name + ''', COUNT(*), SUM(total_amount)
    FROM ' + QUOTENAME(@table_name) + ' 
    WHERE payment_status = ''已完成''';
    EXEC sp_executesql @sql;
    FETCH NEXT FROM table_cursor INTO @table_name;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
-- 输出结果
SELECT * FROM #yearly_stats ORDER BY table_name;

3 递归查询与循环的替代方案

处理层级数据时,递归CTE比循环更高效：

-- 组织架构层级查询
WITH RECURSIVE org_hierarchy AS (
    -- 基础查询：获取顶级部门
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询：获取子部门
    SELECT d.id, d.name, d.parent_id, h.level + 1
    FROM departments d
    JOIN org_hierarchy h ON d.parent_id = h.id
)
SELECT 
    REPEAT('    ', level-1) || name AS department_tree,
    level
FROM org_hierarchy
ORDER BY level, name;

性能优化：避免循环变成性能杀手

1 什么时候不该用循环

• 大数据量更新：10万条记录以上的批量操作
• 简单条件过滤：能用WHERE解决就不要用循环
• 集合操作场景：JOIN通常比循环效率更高

2 提升循环效率的实用技巧

1. 批量提交：每1000次迭代提交一次事务

   -- MySQL批量提交示例
   DECLARE batch_size INT DEFAULT 1000;
   DECLARE counter INT DEFAULT 0;

WHILE condition DO -- 业务逻辑

SET counter = counter + 1;
IF counter % batch_size = 0 THEN
    COMMIT;
    START TRANSACTION;
END IF;

END WHILE; COMMIT;

2. **使用临时表减少重复查询**
```sql
-- 先查询需要处理的数据到临时表
CREATE TEMPORARY TABLE temp_processing_list AS
SELECT id, other_columns 
FROM large_table 
WHERE complex_conditions;
-- 然后基于临时表循环处理

3. 合理设置游标选项

   -- SQL Server高效游标设置
   DECLARE fast_cursor CURSOR 
   LOCAL STATIC READ_ONLY FORWARD_ONLY
   FOR SELECT id FROM huge_table;

真实案例：电商系统中的循环应用

1 订单超时自动取消

-- PostgreSQL定时任务中的循环处理
CREATE OR REPLACE FUNCTION cancel_unpaid_orders()
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER := 0;
    order_record RECORD;
BEGIN
    -- 使用游标锁定待处理订单
    FOR order_record IN 
        SELECT id FROM orders 
        WHERE status = '待支付' 
        AND created_at < NOW() - INTERVAL '30 minutes'
        FOR UPDATE SKIP LOCKED  -- 跳过已被锁定的行
    LOOP
        BEGIN
            -- 更新订单状态
            UPDATE orders SET status = '已取消' WHERE id = order_record.id;
            -- 释放库存
            UPDATE products p
            SET stock = p.stock + oi.quantity
            FROM order_items oi
            WHERE oi.order_id = order_record.id AND p.id = oi.product_id;
            -- 记录操作日志
            INSERT INTO order_logs (order_id, action, executed_at)
            VALUES (order_record.id, '自动取消', NOW());
            affected_rows := affected_rows + 1;
            -- 每100条提交一次
            IF affected_rows % 100 = 0 THEN
                COMMIT;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 错误处理
                INSERT INTO system_errors (error_time, function_name, error_message)
                VALUES (NOW(), 'cancel_unpaid_orders', SQLERRM);
        END;
    END LOOP;
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

2 用户标签批量更新

-- MySQL用户画像更新
DELIMITER //
CREATE PROCEDURE update_user_tags(IN batch_count INT)
BEGIN
    DECLARE processed INT DEFAULT 0;
    DECLARE user_id BIGINT;
    DECLARE done INT DEFAULT FALSE;
    -- 使用游标处理活跃用户
    DECLARE user_cursor CURSOR FOR 
        SELECT DISTINCT user_id 
        FROM user_activities 
        WHERE last_active_date > CURRENT_DATE - INTERVAL 30 DAY
        LIMIT batch_count;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN user_cursor;
    user_loop: LOOP
        FETCH user_cursor INTO user_id;
        IF done OR processed >= batch_count THEN
            LEAVE user_loop;
        END IF;
        -- 计算购买频次标签
        SET @purchase_freq = (
            SELECT 
                CASE 
                    WHEN COUNT(*) > 20 THEN '高频'
                    WHEN COUNT(*) > 5 THEN '中频'
                    ELSE '低频'
                END
            FROM orders 
            WHERE user_id = user_id
            AND created_at > CURRENT_DATE - INTERVAL 90 DAY
        );
        -- 计算客单价标签
        SET @avg_order_value = (
            SELECT 
                CASE 
                    WHEN AVG(total_amount) > 500 THEN '高价值'
                    WHEN AVG(total_amount) > 200 THEN '中价值'
                    ELSE '低价值'
                END
            FROM orders
            WHERE user_id = user_id
            AND status = '已完成'
        );
        -- 更新用户标签（存在则更新，不存在则插入）
        INSERT INTO user_tags (user_id, tag_type, tag_value, updated_at)
        VALUES (user_id, '购买频次', @purchase_freq, NOW()),
               (user_id, '客单价', @avg_order_value, NOW())
        ON DUPLICATE KEY UPDATE 
            tag_value = VALUES(tag_value),
            updated_at = VALUES(updated_at);
        SET processed = processed + 1;
        -- 进度输出（适合长时间运行的任务）
        IF processed % 100 = 0 THEN
            SELECT CONCAT('已处理: ', processed, '/', batch_count) AS progress;
        END IF;
    END LOOP;
    CLOSE user_cursor;
    SELECT CONCAT('处理完成，共更新: ', processed, '条记录') AS result;
END //
DELIMITER ;

避坑指南：SQL循环的常见陷阱

1. 无限循环：忘记更新循环条件变量

   

   -- 错误示例
   DECLARE @i INT = 0;
   WHILE @i < 10  -- 忘记增加@i的值导致无限循环
   BEGIN
       -- 业务逻辑
   END

2. 隐式提交：某些语句会自动提交事务

   -- MySQL中DDL语句会导致隐式提交
   BEGIN TRANSACTION;
   -- 一些DML操作...
   CREATE INDEX idx_name ON table_name(column); -- 这里会提交!
   -- 后续操作不在事务中了

3. 游标泄漏：忘记关闭游标

   -- 错误示例
   DECLARE cur CURSOR FOR SELECT...;
   OPEN cur;
   -- 业务逻辑...
   -- 忘记CLOSE cur;

4. 性能悬崖：N+1查询问题

   -- 低效做法
   FOR each user LOOP
       SELECT * FROM orders WHERE user_id = current_user;  -- 每次循环都查询
   END LOOP;
   -- 高效做法
   SELECT * FROM users u JOIN orders o ON u.id = o.user_id; -- 一次查询解决

循环虽好，但需慎用

SQL循环语句就像数据库编程中的"瑞士军刀"——在某些特定场景下它能发挥惊人效果，但滥用也可能导致性能灾难，掌握它的核心原则是：

1. 评估数据量：小数据量循环更方便，大数据量优先考虑集合操作
2. 事务控制：合理设置事务边界，平衡性能和数据一致性
3. 错误处理：完善的异常捕获机制让程序更健壮
4. 性能监控：执行后检查执行计划和耗时

回到开头的场景,小王最终用存储过程配合循环语句，10分钟就完成了原本需要手动处理一整天的工作，产品经理惊讶的表情，大概是他作为程序员最满足的时刻之一了。