活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式


&gt; GBase 8a 支持存储过程（Stored Procedure）和用户自定义函数（UDF），但其执行模型与 MySQL 有显著差异——存储过程的控制逻辑在 gcluster 上串行执行，不具备 MPP 并行能力，只有过程中调用的 SQL 语句才会分发到 gnode 并行执行。理解这个核心差异，是在 GBase 8a 中正确使用存储过程的前提。

---

## 一、存储过程的适用场景

在讨论语法之前，有必要先回答一个问题：GBase 8a 中什么时候该用存储过程，什么时候不该用？

存储过程适合的场景是​**多步骤的 ETL 流程**​，即需要依赖前一步的执行结果来决定下一步操作、需要统一的错误处理和日志记录的批处理任务。例如：每日凌晨先清理昨日的分区数据，再从明细表聚合写入汇总表，最后记录执行状态——这种有顺序依赖、有分支判断的多步骤操作，用存储过程封装后可以通过一个 `CALL` 触发，管理起来比多条独立 SQL 清晰得多。

存储过程**不适合**的场景是简单的单条 INSERT...SELECT 计算，这类操作直接写 SQL 反而更快更直观；也不适合高频的逐行处理逻辑，因为存储过程中的游标循环（CURSOR + FETCH）是在 gcluster 层面串行执行的，没有任何 MPP 并行，如果用游标处理千万行数据，其性能与直接在 gnode 用集合 SQL 操作相比会慢几个数量级。

还需要特别注意的是，GBase 8a ​**不支持事务**​，这意味着存储过程中无法使用 `BEGIN ... COMMIT ... ROLLBACK`。如果存储过程中间某个步骤失败，已经执行的操作不会自动回滚，需要在设计时考虑幂等性（即重复执行不会产生错误结果）。常见的做法是在每个写入步骤前先 DELETE 对应数据再 INSERT，确保无论执行几次结果都一致。

---

## 二、存储过程基础语法

GBase 8a 的存储过程语法与 MySQL 高度兼容，熟悉 MySQL 存储过程的开发人员基本可以直接上手，主要差异体现在不支持事务、不支持 PREPARE/EXECUTE 动态 SQL，以及临时表需要用普通表替代这几点。

存储过程支持三种参数类型：`IN` 表示输入参数，由调用方传入，过程内可读但改变不影响调用方；`OUT` 表示输出参数，过程执行结束后返回给调用方；`INOUT` 表示既可以传入初始值，也可以在过程中修改后返回。对于批处理类的存储过程，通常至少设计一个 `OUT` 参数用于返回本次处理的行数或状态码，便于调度系统判断执行是否成功。

```sql
DELIMITER $$

CREATE PROCEDURE calc_dept_summary(
    IN  p_start_date DATE,
    IN  p_end_date   DATE,
    OUT p_total_rows INT
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    
    -- 先删后写，保证幂等性
    DELETE FROM dept_summary
    WHERE report_date BETWEEN p_start_date AND p_end_date;
    
    -- 聚合写入（这条 SQL 会分发到 gnode 并行执行）
    INSERT INTO dept_summary (dept_id, report_date, total_amount, order_count)
    SELECT
        dept_id,
        order_date,
        SUM(amount),
        COUNT(*)
    FROM orders
    WHERE order_date BETWEEN p_start_date AND p_end_date
    GROUP BY dept_id, order_date;
    
    SELECT ROW_COUNT() INTO v_count;
    SET p_total_rows = v_count;
    
    INSERT INTO proc_log (proc_name, exec_time, rows_affected)
    VALUES ('calc_dept_summary', NOW(), v_count);
    
END$$

DELIMITER ;
```

调用存储过程并获取输出参数的方式：

```sql
CALL calc_dept_summary('2024-01-01', '2024-06-30', @total);
SELECT @total AS inserted_rows;
```

查看和删除存储过程：

```sql
SHOW CREATE PROCEDURE calc_dept_summary;
SHOW PROCEDURE STATUS WHERE Db = 'sales_db';
DROP PROCEDURE IF EXISTS calc_dept_summary;
```

---

## 三、流程控制：IF、WHILE 与游标

### IF / ELSEIF / ELSE

条件分支是存储过程中最常用的控制结构。GBase 8a 的 IF 语法与 MySQL 完全一致，支持多级 ELSEIF 嵌套。在 ETL 过程中，常见用法是根据某个标志表的记录判断当天是否已经执行过、是否需要跳过，从而避免重复执行。

```sql
DELIMITER $$
CREATE PROCEDURE classify_order(IN p_amount DECIMAL(18,2))
BEGIN
    DECLARE v_level VARCHAR(20);
    
    IF p_amount &gt;= 100000 THEN
        SET v_level = 'VIP';
    ELSEIF p_amount &gt;= 10000 THEN
        SET v_level = 'GOLD';
    ELSEIF p_amount &gt;= 1000 THEN
        SET v_level = 'SILVER';
    ELSE
        SET v_level = 'NORMAL';
    END IF;
    
    SELECT v_level AS order_level;
END$$
DELIMITER ;
```

### WHILE 循环

WHILE 循环最典型的用途是按时间范围逐天或逐月处理数据。这种模式的优势是可以把超大的批量处理任务分解成若干个中等规模的 SQL，每个 SQL 独立执行，单个 SQL 的资源占用处于可控范围内，也不会因为一次性处理的数据量太大导致内存溢出。

```sql
DELIMITER $$
CREATE PROCEDURE batch_process_by_date(
    IN p_start_date DATE,
    IN p_end_date   DATE
)
BEGIN
    DECLARE v_cur_date DATE;
    DECLARE v_rows INT;
    
    SET v_cur_date = p_start_date;
    
    WHILE v_cur_date &lt;= p_end_date DO
        -- 每次循环处理一天的数据（SQL 本身会在 gnode 并行）
        INSERT INTO daily_agg (agg_date, dept_id, total_amount, order_count)
        SELECT v_cur_date, dept_id, SUM(amount), COUNT(*)
        FROM orders
        WHERE order_date = v_cur_date
        GROUP BY dept_id;
        
        SET v_rows = ROW_COUNT();
        
        INSERT INTO proc_log (proc_name, exec_time, detail)
        VALUES ('batch_process', NOW(), CONCAT('date=', v_cur_date, ' rows=', v_rows));
        
        SET v_cur_date = DATE_ADD(v_cur_date, INTERVAL 1 DAY);
    END WHILE;
END$$
DELIMITER ;
```

值得注意的是，WHILE 循环中的每次迭代都是一次独立的 SQL 调用。如果循环次数很多（比如按天处理 3 年数据 = 1095 次循环），串行发起 1095 次 SQL 的开销本身就可能很可观，需要考虑是否能合并成更大的批次处理。

### 游标：谨慎使用的逐行处理

游标允许逐行遍历查询结果并对每行执行自定义逻辑。在 GBase 8a 中，游标的执行位置是 gcluster，每次 FETCH 都是一次数据交换，对于大数据集几乎不可用。但在某些必须逐行判断的业务逻辑场景（例如对每一行数据做无法用 SQL 表达的复杂计算），游标是唯一的选择，只需严格控制结果集行数。

```sql
DELIMITER $$
CREATE PROCEDURE process_outliers()
BEGIN
    DECLARE v_done      INT DEFAULT FALSE;
    DECLARE v_order_id  BIGINT;
    DECLARE v_amount    DECIMAL(18,2);
    DECLARE v_threshold DECIMAL(18,2);
    
    DECLARE cur CURSOR FOR
        SELECT order_id, amount FROM orders
        WHERE order_date = CURDATE() - INTERVAL 1 DAY;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    -- 先用集合 SQL 算阈值（走 gnode 并行，很快）
    SELECT AVG(amount) * 3 INTO v_threshold
    FROM orders WHERE order_date = CURDATE() - INTERVAL 1 DAY;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_order_id, v_amount;
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        -- 逐行判断（只有无法用 SQL 表达的逻辑才放这里）
        IF v_amount &gt; v_threshold THEN
            INSERT INTO outlier_orders (order_id, amount, flagged_at)
            VALUES (v_order_id, v_amount, NOW());
        END IF;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;
```

上面这个例子实际上可以完全用集合 SQL 改写（`INSERT INTO outlier_orders SELECT ... FROM orders WHERE amount &gt; threshold`），性能会快几十到几百倍。游标版本仅用于演示语法。**凡是能用集合 SQL 替代游标的，都应该替代。**

---

## 四、错误处理

生产级存储过程必须有明确的错误处理机制。GBase 8a 支持通过 `DECLARE ... HANDLER` 捕获 SQL 异常，在异常发生时执行自定义的处理逻辑（记录日志、设置状态变量等）。

错误处理有两种模式：`CONTINUE HANDLER` 表示捕获到异常后继续执行后续语句；`EXIT HANDLER` 表示捕获到异常后直接退出存储过程。对于需要"记录失败但不中断整体流程"的场景用 CONTINUE；对于"一步失败则整体失败"的场景用 EXIT。

```sql
DELIMITER $$
CREATE PROCEDURE safe_load_summary(IN p_date DATE)
BEGIN
    DECLARE v_err_code INT DEFAULT 0;
    DECLARE v_err_msg  TEXT;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_err_code = MYSQL_ERRNO,
            v_err_msg  = MESSAGE_TEXT;
        INSERT INTO error_log (proc_name, exec_date, err_code, err_msg)
        VALUES ('safe_load_summary', NOW(), v_err_code, v_err_msg);
    END;
    
    -- 先删后写，保证幂等
    DELETE FROM summary_daily WHERE agg_date = p_date;
    
    INSERT INTO summary_daily (agg_date, dept_id, total_amount)
    SELECT p_date, dept_id, SUM(amount)
    FROM orders
    WHERE order_date = p_date
    GROUP BY dept_id;
    
    IF v_err_code != 0 THEN
        SELECT CONCAT('FAILED: ', v_err_msg) AS result;
    ELSE
        SELECT CONCAT('OK: ', ROW_COUNT(), ' rows') AS result;
    END IF;
END$$
DELIMITER ;
```

---

## 五、用户自定义函数（UDF）

UDF 与存储过程的主要区别在于：函数有返回值，可以直接嵌入 SELECT 语句中使用；存储过程没有返回值（通过 OUT 参数传递结果），只能用 CALL 触发。对于数据转换、格式化、脱敏等需要在查询中按列调用的逻辑，UDF 是比存储过程更合适的载体。

UDF 的 `DETERMINISTIC` 关键字表示对于相同的输入，函数永远返回相同的输出。声明这个属性可以让优化器在某些情况下缓存函数结果、避免重复计算，因此只要函数逻辑确实满足这个条件，建议总是加上这个声明。如果函数内部用了 `NOW()`、`RAND()` 等非确定性函数，则不能加 DETERMINISTIC。

```sql
DELIMITER $$

-- 日期转季度字符串
CREATE FUNCTION get_quarter(p_date DATE)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    RETURN CONCAT(YEAR(p_date), 'Q', QUARTER(p_date));
END$$

-- 手机号脱敏：保留前 3 位和后 4 位
CREATE FUNCTION mask_phone(p_phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
COMMENT '手机号脱敏，中间替换为****'
BEGIN
    IF LENGTH(p_phone) &lt; 7 THEN
        RETURN p_phone;
    END IF;
    RETURN CONCAT(
        SUBSTR(p_phone, 1, 3),
        '****',
        SUBSTR(p_phone, LENGTH(p_phone) - 3, 4)
    );
END$$

DELIMITER ;
```

UDF 在 SELECT 中的使用方式与内置函数完全相同，对业务代码没有侵入性：

```sql
SELECT order_id, get_quarter(order_date) AS quarter, amount FROM orders LIMIT 10;
SELECT customer_id, mask_phone(phone) AS phone FROM customers LIMIT 10;
```

---

## 六、存储过程的执行模型：最关键的认知

这一节值得单独强调，因为对执行模型的误解会直接导致性能问题。

存储过程的控制流（变量赋值、IF 判断、WHILE 循环、游标 FETCH）全部在 ​**gcluster 节点上串行执行**​，完全不参与 MPP 并行。只有当存储过程中触发了 SQL 语句（SELECT、INSERT、DELETE 等），这条 SQL 才会被分发到各个 gnode 并行执行。

这意味着，一个有 180 次 WHILE 循环的存储过程，本质上是串行发起了 180 次独立的 MPP 查询。每次循环之间的间隔（变量计算、日志写入等）都是 gcluster 在单线程上完成的，无法利用 gnode 的计算资源。如果每次循环的 SQL 执行很快（比如每天只有少量数据），这种开销还在可接受范围；但如果循环次数非常多或每次循环的间隔逻辑非常重，就需要考虑优化循环结构，或者将循环逻辑移到应用层（Python/Java）来实现，在应用层可以使用多线程并发发起 SQL，绕开 gcluster 串行的限制。

另一个重要限制是 GBase 8a 的存储过程​**不支持 PREPARE / EXECUTE 动态 SQL**​。如果需要在过程中动态构建并执行 SQL（比如动态指定表名或列名），无法在存储过程内部实现，必须在应用层拼接 SQL 后再执行。

---

## 七、权限配置

存储过程和 UDF 的权限管理分为两个层面：创建权限和执行权限，分别对应不同的业务角色。

DBA 或数据工程师持有创建权限，负责编写和维护过程逻辑；业务系统账号只持有执行权限，只能调用已有的过程，无法修改。这种分离确保了业务系统不能通过存储过程意外修改数据库结构或执行超出预期的操作。

```sql
-- ETL 账号：可以创建和修改存储过程
GRANT CREATE ROUTINE, ALTER ROUTINE ON sales_db.* TO 'etl_user'@'10.168.10.%';

-- 业务账号：只能执行，不能修改
GRANT EXECUTE ON PROCEDURE sales_db.calc_dept_summary TO 'app_user'@'10.168.10.%';
GRANT EXECUTE ON FUNCTION  sales_db.get_quarter       TO 'app_user'@'10.168.10.%';
```

---

## 八、实用模板：带完整错误处理的每日 ETL

```sql
DELIMITER $$
CREATE PROCEDURE daily_etl(IN p_bizdate DATE)
BEGIN
    DECLARE v_start   DATETIME DEFAULT NOW();
    DECLARE v_rows    INT DEFAULT 0;
    DECLARE v_err_msg TEXT;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_err_msg = MESSAGE_TEXT;
        INSERT INTO etl_run_log (proc_name, bizdate, status, message, run_seconds)
        VALUES ('daily_etl', p_bizdate, 'FAILED', v_err_msg,
                TIMESTAMPDIFF(SECOND, v_start, NOW()));
    END;
    
    -- Step 1: 清理当天旧数据（幂等保证）
    DELETE FROM orders_daily_agg WHERE agg_date = p_bizdate;
    
    -- Step 2: 聚合计算（MPP 并行执行）
    INSERT INTO orders_daily_agg (agg_date, dept_id, total_amount, order_count)
    SELECT p_bizdate, dept_id, SUM(amount), COUNT(*)
    FROM orders
    WHERE order_date = p_bizdate
    GROUP BY dept_id;
    
    SET v_rows = ROW_COUNT();
    
    -- Step 3: 记录成功状态
    INSERT INTO etl_run_log (proc_name, bizdate, status, rows_processed, run_seconds)
    VALUES ('daily_etl', p_bizdate, 'SUCCESS', v_rows,
            TIMESTAMPDIFF(SECOND, v_start, NOW()));
END$$
DELIMITER ;
```

这个模板覆盖了生产 ETL 过程的基本要素：幂等设计（先删后写）、异常捕获与日志记录、执行时间统计、OUT 参数传递执行状态。可以在此基础上按业务需求扩展更多步骤。



运维管理

GBase 8a 存储过程与 UDF 实战指南

GBase 8a 存储过程与 UDF 实战指南

评论

热门帖子