Mariadb复合语句和流程控制怎么实现

发布时间：2022-02-19 14:04:08 作者：iii
来源：亿速云阅读：152

# MariaDB复合语句和流程控制实现详解

## 1. 复合语句基础概念

### 1.1 什么是复合语句

复合语句（Compound Statement）是MariaDB中一组SQL语句的集合，它们被封装在一个BEGIN...END块中，逻辑单元执行。与简单的SQL语句不同，复合语句可以包含：

- 多个SQL语句
- 变量声明
- 流程控制结构
- 异常处理
- 嵌套的BEGIN...END块

复合语句主要应用于以下场景：
- 存储过程(Stored Procedures)
- 函数(Functions)
- 触发器(Triggers)
- 事件(Events)

### 1.2 基本语法结构

```sql
BEGIN
    [声明部分]
    [执行部分]
    [异常处理部分]
END;

简单示例：

DELIMITER //
CREATE PROCEDURE simple_compound()
BEGIN
    DECLARE var1 INT DEFAULT 0;
    SET var1 = 10;
    SELECT var1;
END //
DELIMITER ;

2. 变量声明与使用

2.1 变量声明语法

在复合语句中声明变量的基本语法：

DECLARE variable_name datatype [DEFAULT default_value];

示例：

DECLARE customer_count INT DEFAULT 0;
DECLARE order_total DECIMAL(10,2);
DECLARE start_date DATE DEFAULT '2023-01-01';

2.2 变量作用域规则

MariaDB中的变量作用域遵循以下规则：

局部变量：在BEGIN…END块中DECLARE声明的变量，作用域仅限于该块及其嵌套块
用户变量：使用@var_name格式的变量，作用域为整个会话
系统变量：使用@@var_name格式的变量，分为全局和会话级别

作用域示例：

CREATE PROCEDURE scope_demo()
BEGIN
    DECLARE local_var INT DEFAULT 1;  -- 局部变量
    
    BEGIN
        DECLARE nested_var INT DEFAULT 2;  -- 嵌套块中的局部变量
        SET @user_var = 3;  -- 用户变量
        SELECT local_var, nested_var, @user_var;
    END;
    
    -- 这里可以访问local_var和@user_var，但不能访问nested_var
    SELECT local_var, @user_var;
END;

2.3 变量赋值方法

MariaDB提供多种变量赋值方式：

使用SET语句：

SET var_name = expression;
SET var_name := expression;

使用SELECT INTO语句：

SELECT column_name INTO var_name FROM table_name WHERE condition;

在存储过程参数中使用OUT或INOUT参数

示例：

CREATE PROCEDURE calculate_stats(IN id INT, OUT total DECIMAL(10,2))
BEGIN
    DECLARE avg_value DECIMAL(10,2);
    
    -- SET赋值
    SET total = 0;
    
    -- SELECT INTO赋值
    SELECT SUM(amount) INTO total FROM orders WHERE customer_id = id;
    
    -- 表达式计算
    SET avg_value = total / (SELECT COUNT(*) FROM orders WHERE customer_id = id);
    
    SELECT total, avg_value;
END;

3. 流程控制结构

3.1 条件控制：IF语句

IF语句允许基于条件执行不同的代码块。

基本语法：

IF condition THEN
    statements;
[ELSEIF condition THEN
    statements;]
[ELSE
    statements;]
END IF;

示例：

CREATE PROCEDURE check_discount(IN order_amount DECIMAL(10,2), OUT discount_rate DECIMAL(3,2))
BEGIN
    IF order_amount > 1000 THEN
        SET discount_rate = 0.15;
    ELSEIF order_amount > 500 THEN
        SET discount_rate = 0.10;
    ELSEIF order_amount > 200 THEN
        SET discount_rate = 0.05;
    ELSE
        SET discount_rate = 0.00;
    END IF;
END;

3.2 条件控制：CASE语句

CASE语句提供更复杂的条件分支结构。

两种形式：

简单CASE：

CASE case_value
    WHEN when_value THEN statements;
    [WHEN when_value THEN statements;]
    [ELSE statements;]
END CASE;

搜索CASE：

CASE
    WHEN condition THEN statements;
    [WHEN condition THEN statements;]
    [ELSE statements;]
END CASE;

示例：

CREATE PROCEDURE get_season(IN month_num INT, OUT season VARCHAR(10))
BEGIN
    CASE 
        WHEN month_num BETWEEN 3 AND 5 THEN
            SET season = 'Spring';
        WHEN month_num BETWEEN 6 AND 8 THEN
            SET season = 'Summer';
        WHEN month_num BETWEEN 9 AND 11 THEN
            SET season = 'Autumn';
        ELSE
            SET season = 'Winter';
    END CASE;
END;

3.3 循环控制：LOOP语句

基本LOOP结构需要明确的退出条件。

语法：

[begin_label:] LOOP
    statements;
    IF condition THEN
        LEAVE [begin_label];  -- 退出循环
    END IF;
END LOOP [end_label];

示例：

CREATE PROCEDURE factorial(IN n INT, OUT result INT)
BEGIN
    DECLARE counter INT DEFAULT 1;
    SET result = 1;
    
    calc_loop: LOOP
        IF counter > n THEN
            LEAVE calc_loop;
        END IF;
        
        SET result = result * counter;
        SET counter = counter + 1;
    END LOOP calc_loop;
END;

3.4 循环控制：WHILE语句

WHILE循环在每次迭代前检查条件。

语法：

[begin_label:] WHILE condition DO
    statements;
END WHILE [end_label];

示例：

CREATE PROCEDURE countdown(IN start INT)
BEGIN
    DECLARE counter INT DEFAULT start;
    
    WHILE counter > 0 DO
        SELECT counter;
        SET counter = counter - 1;
        DO SLEEP(1);  -- 等待1秒
    END WHILE;
    
    SELECT 'Blastoff!';
END;

3.5 循环控制：REPEAT语句

REPEAT循环在每次迭代后检查条件（至少执行一次）。

语法：

[begin_label:] REPEAT
    statements;
UNTIL condition
END REPEAT [end_label];

示例：

CREATE PROCEDURE random_until_gt(IN target INT)
BEGIN
    DECLARE r INT;
    
    REPEAT
        SET r = FLOOR(RAND() * 100);
        SELECT r;
    UNTIL r > target
    END REPEAT;
END;

3.6 迭代控制：LEAVE和ITERATE

LEAVE：用于退出标记的代码块（类似于break）
ITERATE：用于跳过当前迭代继续下一次循环（类似于continue）

示例：

CREATE PROCEDURE process_numbers()
BEGIN
    DECLARE i INT DEFAULT 0;
    
    loop_label: WHILE i < 10 DO
        SET i = i + 1;
        
        IF i = 5 THEN
            ITERATE loop_label;  -- 跳过5
        END IF;
        
        IF i > 8 THEN
            LEAVE loop_label;  -- 退出循环
        END IF;
        
        SELECT i;
    END WHILE loop_label;
END;

4. 错误处理机制

4.1 声明处理程序

MariaDB允许定义错误处理程序来捕获和处理异常。

基本语法：

DECLARE handler_type HANDLER FOR condition_value [, condition_value] ... handler_statements;

handler_type可以是： - CONTINUE：继续执行后续语句 - EXIT：退出当前BEGIN…END块

condition_value可以是： - SQLSTATE值 - MySQL错误代码 - 命名条件 - SQLWARNING - NOT FOUND - SQLEXCEPTION

4.2 实际应用示例

CREATE PROCEDURE safe_divide(IN a INT, IN b INT, OUT result DECIMAL(10,2))
BEGIN
    DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
    DECLARE EXIT HANDLER FOR division_by_zero
    BEGIN
        SET result = NULL;
        SELECT 'Error: Division by zero attempted' AS message;
    END;
    
    SET result = a / b;
    SELECT result AS division_result;
END;

4.3 获取错误信息

可以使用以下函数获取错误详情： - GET DIAGNOSTICS - FOUND_ROWS() - ROW_COUNT()

示例：

CREATE PROCEDURE insert_with_check()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, 
        @text = MESSAGE_TEXT;
        
        SELECT CONCAT('Error occurred: ', @errno, ' (', @sqlstate, '): ', @text) AS error;
    END;
    
    INSERT INTO non_existent_table VALUES (1);
END;

5. 高级复合语句技巧

5.1 嵌套复合语句

BEGIN…END块可以嵌套使用，创建更复杂的逻辑结构。

示例：

CREATE PROCEDURE complex_logic(IN id INT)
BEGIN
    DECLARE total_orders INT;
    DECLARE avg_amount DECIMAL(10,2);
    
    BEGIN
        DECLARE customer_name VARCHAR(100);
        
        SELECT name INTO customer_name FROM customers WHERE customer_id = id;
        SELECT CONCAT('Processing customer: ', customer_name) AS info;
    END;
    
    BEGIN
        DECLARE order_count INT;
        
        SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = id;
        
        IF order_count > 0 THEN
            SELECT SUM(amount), AVG(amount) INTO total_orders, avg_amount 
            FROM orders WHERE customer_id = id;
        ELSE
            SET total_orders = 0;
            SET avg_amount = 0.00;
        END IF;
    END;
    
    SELECT total_orders AS total, avg_amount AS average;
END;

5.2 动态SQL执行

使用PREPARE和EXECUTE执行动态构建的SQL语句。

示例：

CREATE PROCEDURE dynamic_query(IN table_name VARCHAR(64), IN column_name VARCHAR(64))
BEGIN
    DECLARE query TEXT;
    SET @sql = CONCAT('SELECT ', column_name, ' FROM ', table_name, ' LIMIT 10');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

5.3 使用游标处理结果集

对于返回多行数据的结果集，可以使用游标进行迭代处理。

基本流程： 1. 声明游标 2. 打开游标 3. 获取数据 4. 关闭游标

示例：

CREATE PROCEDURE process_orders(IN status_filter VARCHAR(20))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_date DATE;
    DECLARE order_total DECIMAL(10,2);
    
    -- 1. 声明游标
    DECLARE cur CURSOR FOR 
        SELECT id, order_date, total_amount 
        FROM orders 
        WHERE status = status_filter;
    
    -- 2. 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        id INT,
        processed_date DATETIME,
        original_amount DECIMAL(10,2),
        discounted_amount DECIMAL(10,2)
    );
    
    -- 3. 打开游标
    OPEN cur;
    
    -- 4. 循环处理
    read_loop: LOOP
        FETCH cur INTO order_id, order_date, order_total;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 业务逻辑处理
        INSERT INTO temp_results VALUES (
            order_id,
            NOW(),
            order_total,
            order_total * 0.9  -- 应用10%折扣
        );
    END LOOP;
    
    -- 5. 关闭游标
    CLOSE cur;
    
    -- 返回处理结果
    SELECT * FROM temp_results;
    DROP TEMPORARY TABLE temp_results;
END;

6. 性能优化建议

减少数据库往返：复合语句的主要优势是减少客户端和服务器之间的通信次数
合理使用事务：

START TRANSACTION;
-- 复合语句操作
COMMIT;  -- 或 ROLLBACK;

避免过度复杂的逻辑：过深的嵌套和复杂的流程控制会影响性能
使用临时表缓存中间结果：对于复杂计算，临时表可以提高性能
注意游标性能：游标处理大数据集时可能较慢，考虑批量处理
合理使用索引：复合语句中的查询同样受益于良好的索引设计
定期维护：对存储过程进行重新编译以保持最佳性能

ALTER PROCEDURE procedure_name REORGANIZE;

7. 实际应用案例

7.1 数据迁移脚本

CREATE PROCEDURE migrate_old_data()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE old_id INT;
    DECLARE old_name VARCHAR(100);
    DECLARE old_value DECIMAL(10,2);
    
    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, name, value FROM legacy_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 打开游标
    OPEN cur;
    
    -- 处理数据
    migrate_loop: LOOP
        FETCH cur INTO old_id, old_name, old_value;
        IF done THEN
            LEAVE migrate_loop;
        END IF;
        
        -- 转换逻辑
        IF old_value > 1000 THEN
            INSERT INTO new_table (id, name, category, amount)
            VALUES (old_id, old_name, 'PREMIUM', old_value * 0.95);
        ELSE
            INSERT INTO new_table (id, name, category, amount)
            VALUES (old_id, old_name, 'STANDARD', old_value);
        END IF;
    END LOOP;
    
    -- 清理
    CLOSE cur;
    
    -- 记录迁移完成
    INSERT INTO migration_log (process_name, records_processed, process_date)
    VALUES ('legacy_data', ROW_COUNT(), NOW());
    
    -- 提交事务
    COMMIT;
END;

7.2 复杂报表生成

CREATE PROCEDURE generate_monthly_report(IN report_month DATE)
BEGIN
    DECLARE month_start DATE;
    DECLARE month_end DATE;
    DECLARE total_sales DECIMAL(12,2);
    DECLARE customer_count INT;
    
    -- 计算月份范围
    SET month_start = DATE_FORMAT(report_month, '%Y-%m-01');
    SET month_end = LAST_DAY(month_start);
    
    -- 创建临时表存储结果
    DROP TEMPORARY TABLE IF EXISTS temp_report;
    CREATE TEMPORARY TABLE temp_report (
        category VARCHAR(50),
        sales_amount DECIMAL(12,2),
        customer_count INT,
        avg_sale DECIMAL(10,2)
    );
    
    -- 计算总销售额
    SELECT SUM(amount) INTO total_sales 
    FROM orders 
    WHERE order_date BETWEEN month_start AND month_end;
    
    -- 计算客户数
    SELECT COUNT(DISTINCT customer_id) INTO customer_count
    FROM orders
    WHERE order_date BETWEEN month_start AND month_end;
    
    -- 按类别插入数据
    INSERT INTO temp_report
    SELECT 
        p.category,
        SUM(o.amount) AS sales_amount,
        COUNT(DISTINCT o.customer_id) AS customer_count,
        SUM(o.amount) / COUNT(DISTINCT o.customer_id) AS avg_sale
    FROM orders o
    JOIN products p ON o.product_id = p.id
    WHERE o.order_date BETWEEN month_start AND month_end
    GROUP BY p.category;
    
    -- 插入汇总行
    INSERT INTO temp_report
    VALUES (
        'TOTAL',
        total_sales,
        customer_count,
        total_sales / NULLIF(customer_count, 0)
    );
    
    -- 输出结果
    SELECT * FROM temp_report ORDER BY 
        CASE WHEN category = 'TOTAL' THEN 1 ELSE 0 END,
        sales_amount DESC;
    
    -- 清理
    DROP TEMPORARY TABLE temp_report;
END;

8. 常见问题与解决方案

8.1 调试技巧

使用SELECT输出中间值：

CREATE PROCEDURE debug_demo()
BEGIN
    DECLARE var1 INT DEFAULT 10;
    SELECT 'Debug Point 1', var1;  -- 调试输出
    
    SET var1 = var1 * 2;
    SELECT 'Debug Point 2', var1;  -- 调试输出
END;

使用SIGNAL语句抛出自定义错误：

CREATE PROCEDURE validate_input(IN input_value INT)
BEGIN
    IF input_value < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Input value cannot be negative';
    END IF;
    
    -- 正常处理
END;

8.2 权限问题

执行复合语句需要足够的权限：

GRANT EXECUTE ON PROCEDURE db_name.procedure_name TO 'user'@'host';

8.3 性能问题排查

使用EXPLN分析复合语句中的查询
检查是否使用了合适的索引
避免在循环中执行查询

8.4 最佳实践总结

保持复合语句简洁，适度拆分复杂逻辑
使用清晰的命名规范
添加充分的注释
实现全面的错误处理
考虑使用版本控制管理存储过程代码