---
title: 'Databend SQL 存储过程使用指南'
date: 2025-11-14
slug: category-engineering/databend-sql-procedue
cover_url: '20251013-wechat/h8.png'
description: '存储过程（Stored Procedure）是一组预编译的 SQL 语句集合，它们被保存在数据库中，可以像函数一样被重复调用。想象一下，如果你经常需要执行一系列复杂的数据处理操作，与其每次都手动输入这些 SQL 语句，不如将它们封装成一个存储过程，需要时直接调用即可。'
tags: [databend]
category: Product
authors:
  - sundy-li
---

存储过程（Stored Procedure）是一组预编译的 SQL 语句集合，它们被保存在数据库中，可以像函数一样被重复调用。想象一下，如果你经常需要执行一系列复杂的数据处理操作，与其每次都手动输入这些 SQL 语句，不如将它们封装成一个存储过程，需要时直接调用即可。

{/* truncate */}

**存储过程的优势**

- 代码复用：一次编写，多次调用，避免重复代码
- **性能优化：预编译的 SQL 语句执行效率更高**
- **业务逻辑封装：将复杂的业务逻辑封装在数据库层**
- **维护便利：统一管理和修改业务逻辑**
- **安全性：通过权限控制，限制用户对底层数据的直接访问**

第一个存储过程：Hello World

让我们从最简单的例子开始。假设我们需要一个简单的加法存储过程：

```sql
CREATE PROCEDURE my_add(a Int32, b Int32)
RETURNS Int32
LANGUAGE SQL
AS $$
BEGIN
    RETURN a + b;
END;
$$;
```

**语法解析**

**让我们逐行理解这个存储过程：**

- `CREATE PROCEDURE my_add`：创建一个名为 my_add 的存储过程
- `(a Int32, b Int32)`：定义输入参数 a 和 b，类型为 Int32
- `RETURNS Int32`：指定返回值类型为 Int32
- `LANGUAGE SQL`：指定使用 SQL 语言编写（目前 Databend 仅支持 SQL）
- `AS $$ ... $$`：使用美元符号包裹存储过程的主体代码
- `BEGIN ... END`：存储过程主体的开始和结束标记
- `RETURN a + b`：执行计算并返回结果

**调用存储过程**

**创建后，我们可以这样调用它，注意参数类型需要显式指定：**

```sql
call PROCEDURE my_add(3::Int,4::Int);
----
7
```

**SqlScript**

**存储过程中的语法我们称之为 SqlScript，我们也可以直接使用** `execute immediate` 来执行 SqlScript 语句。

- 执行单个 SQL

```sql
execute immediate 'CREATE TABLE test (id Int32)';
```

- 执行多个 SQL, 用 begin 和 end 包裹

```sql
execute immediate $$
BEGIN
    select 33;
    let s RESULTSET := select number from numbers(100);
    RETURN TABLE(s);
END;
$$;
```

进阶：使用变量和流程控制

现在让我们学习如何在 SqlScript 中使用变量、条件判断和循环。

**变量声明和使用**

**Scalar 变量**

在 Databend 中，使用 `LET` 关键字声明变量：

语法有：

- `LET <variable_name> := <value>` -- 声明并初始化变量 x
- `LET <variable_name> [<type>] := <value>` -- 声明并初始化变量 x
- `LET <variable_name> [<type>] DEFAULT <value>` -- 声明并初始化变量 x
- `LET <variable_name> [<type>]` -- 声明变量 x, 后续初始化

```sql
execute immediate $$
BEGIN
    LET sum := 0;  -- 声明并初始化变量 sum

    FOR i IN 1 TO 10 DO
        IF i % 2 = 0 THEN
            sum := sum + i;  -- 累加偶数
        END IF;
    END FOR;

    RETURN sum;
END;
$$;
```

在这个例子中：

- `LET sum := 0`：声明一个名为 `sum` 的变量并初始化为 0
- `:=`：赋值操作符
- `RETURNS UInt8 NOT NULL`：指定返回值不能为 NULL

#### ResultSet 变量

ResultSet 变量用于存储查询结果集，语法有：

示例语法：

```sql
execute immediate $$
BEGIN
    LET x RESULTSET := select number from numbers(10);
    RETURN TABLE(x);
END;
$$;
```

```
上面是返回结果集，所以使用 RETURN TABLE(x) 语句
```

#### Cursor 变量

Cursor 变量用于遍历结果集，语法有：

1.  `LET <cursor_variable> CURSOR for <query>`
2.  `LET <cursor_variable> CURSOR for <result_set_variable>`
3.  `OPEN <cursor_variable>`
4.  `FETCH <cursor_variable> INTO <variable>`
5.  `CLOSE <cursor_variable>`
6.  `for <variable> in <cursor_variable> do ... end for`

示例语法：

```sql
execute immediate $$
BEGIN
    LET v Int;
    LET c CURSOR for select max(number) from numbers(10);
    OPEN c;
    FETCH c INTO v;
    CLOSE c;

    let d RESULTSET := select number from numbers(10);
    let e CURSOR for d;
    for v2 in e do
        v := v + v2.number;
    end for;

    return v;
END;
$$;
```

```
条件判断：IF-THEN-ELSEIF-ELSE
```

**IF 语句允许我们根据条件执行不同的代码分支：**

```sql
execute immediate $$
BEGIN
    LET score := 57 + 10 + 10 + 10;
    LET grade := '';

    IF score >= 90 THEN
        grade := '优秀';
    ELSEIF score >= 80 THEN
        grade := '良好';
    ELSEIF score >= 70 THEN
        grade := '中等';
    ELSEIF score >= 60 THEN
        grade := '及格';
    ELSE
        grade := '不及格';
    END IF;
    RETURN grade;
END;
$$;
```

```
循环：FOR 循环
```

**FOR 循环有两种常见形式：**

#### 形式一：范围循环

```sql
FOR i IN start_value TO end_value DO
    -- 循环体
END FOR;
```

```
示例：
```

```sql
execute immediate $$
BEGIN
    LET sum := 0;
    FOR i IN 1 TO 10 DO
        sum := sum + i;
    END FOR;
    RETURN sum;
END;
$$;
```

```
形式二：结果集循环
```

示例：

```sql
execute immediate $$
BEGIN
    -- 声明一个结果集变量
    LET x RESULTSET := SELECT number n FROM numbers(10);
    LET sum := 0;

    -- 遍历结果集
    FOR r IN x DO
        -- 使用 r.n 访问列值
        sum := sum + r.n;
    END FOR;
    RETURN sum;
END;
$$;
```

`高级应用：嵌套循环与复杂逻辑`

让我们看一个更复杂的例子，展示嵌套循环和多层逻辑：

```sql
execute immediate $$
BEGIN
    -- 声明结果集变量：从 0 到 9 的数字
    LET x RESULTSET := SELECT number n FROM numbers(10);
    LET sum := 0;

    -- 外层循环：遍历结果集
    FOR x IN x DO
        -- 内层循环：从 0 到当前数字
        FOR batch IN 0 TO x.n DO
            IF batch % 2 = 0 THEN
                sum := sum + batch;  -- 偶数加
            ELSE
                sum := sum - batch;  -- 奇数减
            END IF;
        END FOR;
    END FOR;

    RETURN sum;
END;
$$;
```

```
逻辑分析
```

**让我们分析一下这个过程的执行流程：**

- **外层循环**：遍历 0-9 这 10 个数字
- **内层循环**：对于每个数字 n，从 0 循环到 n
- **条件判断**：如果是偶数则加，奇数则减

例如当 x.n = 3 时：

- batch = 0（偶）：sum += 0
- batch = 1（奇）：sum -= 1
- batch = 2（偶）：sum += 2
- batch = 3（奇）：sum -= 3

```
动态拼接语句，嵌套执行
```

```sql
execute immediate $$
BEGIN
   LET tbl_name := 'abcd1' ;
   LET drop_sql := 'DROP TABLE default."' || tbl_name || '"' ;
   EXECUTE IMMEDIATE :drop_sql ;
END ;
$$ ; 
```

_返回表格数据_

**除了返回单个值，存储过程还可以返回整张表：**

```sql
execute immediate $$
BEGIN
    RETURN TABLE(
        SELECT
            number % 3 d,
            SUM(number) AS total_amount
        FROM numbers(10)
        GROUP BY d
    ) ;
END ;
$$; 
```

**存储过程管理**

| 操作             | SQL                                                                                                                           | 说明                                                                                                    |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------- |
| 查看所有存储过程 | SHOW PROCEDURES;                                                                                                              |                                                                                                         |
| 查看存储过程详情 | DESC PROCEDURE sum_even_numbers(UInt8, UInt8);<br/> DESCRIBE PROCEDURE sum_even_numbers(UInt8, UInt8);                        | **注意：** <br/> 无参数的存储过程使用空括号：DESC PROCEDURE proc_name()• 有参数的必须指定确切的参数类型 |
| 删除存储过程     | DROP PROCEDURE my_add(int int);                                                                                               |                                                                                                         |
| 替换存储过程     | CREATE OR REPLACE PROCEDURE my_add(a Int32, b Int32) <br/> RETURNS Int32 LANGUAGE SQL AS $$ BEGIN RETURN a + b + 3; END; $$;` |                                                                                                         |

_最佳实践_

**_命名规范_**

- 使用有意义的名称，清晰表达功能
- 使用下划线分隔单词（snake_case）
- 添加前缀区分不同类型的过程（如 `calc_`, `get_`, `update_`）

```
-- 好的命名
CREATE PROCEDURE calc_monthly_revenue(...)
CREATE PROCEDURE get_active_users(...)
CREATE PROCEDURE update_user_status(...)

-- 不好的命名
CREATE PROCEDURE proc1(...)
CREATE PROCEDURE x(...)
```

\***\*注释说明\*\***

始终为存储过程添加清晰的注释：

```sql
CREATE PROCEDURE process_orders(order_date DATE)
RETURNS INT
LANGUAGE SQL
COMMENT = '处理指定日期的订单，返回处理数量'
AS $$ ... $$ ; 
```

**\***性能考虑**\***

- **避免过度循环**：对于大数据集，尽量使用集合操作而非逐行循环
- 合理使用索引：在存储过程中查询的表应有适当的索引
- **批量操作**：尽可能使用批量插入/更新而非逐条处理
- **结果集大小**：返回表格时，使用 LIMIT 限制结果集大小

_实战案例：数据清洗流程_

**让我们用二个实际案例来综合运用所学知识：**

**_清理和归档不活跃用户数据_**

```sql
CREATE OR REPLACE PROCEDURE cleanup_user_data(days_threshold INT)
RETURNS TABLE(
    action VARCHAR,
    user_count INT,
    processed_at TIMESTAMP
)
LANGUAGE SQL
COMMENT = '清理和归档不活跃用户数据'
AS $$
BEGIN
    LET cutoff_date := DATE_SUB(DAY, days_threshold,today()) ;
    LET inactive_users := 0 ;
    LET deleted_users := 0 ;

    -- 统计不活跃用户
    LET inactive_resultset RESULTSET :=
        SELECT COUNT(*) AS cnt
        FROM users
        WHERE last_login_date < cutoff_date
        AND status = 'active' ;

    FOR r IN inactive_resultset DO
        inactive_users := r.cnt ;
    END FOR ;

    -- 标记不活跃用户
    UPDATE users
    SET status = 'inactive'
    WHERE last_login_date < cutoff_date
    AND status = 'active' ;

    -- 删除长期不活跃用户
    DELETE FROM users
    WHERE last_login_date < DATE_SUB(cutoff_date, INTERVAL days_threshold DAY)
    AND status = 'inactive' ;

    -- 返回处理结果
    RETURN TABLE(
        SELECT
            'Marked Inactive' AS action,
            inactive_users AS user_count,
            CURRENT_TIMESTAMP() AS processed_at
        UNION ALL
        SELECT
            'Deleted' AS action,
            deleted_users AS user_count,
            CURRENT_TIMESTAMP() AS processed_at
    ) ;
END ;
$$ ; 
```

**调用方式：**

```
-- 清理 90 天未登录的用户
CALL PROCEDURE cleanup_user_data(90::Int) ; 
```

**_扫描表并合并数据到 target 表_**

```sql
CREATE OR REPLACE PROCEDURE PROC_MERGE_GPS()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    create or replace table default.gps as select number from numbers(100) ;
    create or replace table default.abcd1 as select number from numbers(100) ;
    create or replace table default.abcd2 as select number from numbers(100) ;
    create or replace table default.abcd3 as select number from numbers(100) ;

    -- Step 1: 查询符合条件的表名（使用 INFORMATION_SCHEMA）
    LET records RESULTSET := (
        select name  from system.tables where database = 'default' and name like '%abcd%'
    ) ;
    LET table_count := 0 ;
    LET record_count := 0 ;
    LET table_names := [] ;
    LET union_parts := [] ;
    for table_record in records DO
        LET name := table_record.name ;
        table_count := table_count + 1 ;
        table_names := ARRAY_APPEND(table_names, name) ;
        union_parts := ARRAY_APPEND(union_parts, 'SELECT * FROM default.' || name) ;
    END FOR ;

    -- 如果没有匹配的表，直接返回
    IF (table_count = 0) THEN
        RETURN 'No data to process' ;
    END IF ;

    -- Step 3: 创建临时视图
    LET view_sql := 'CREATE OR REPLACE VIEW default.TEMPORARY_GPS_TABLES AS ' || ARRAY_TO_STRING(union_parts, ' UNION ALL ') ;
    EXECUTE IMMEDIATE :view_sql ;

    -- Step 2: 查询表中的记录数
    LET record_count_sql := 'SELECT COUNT(*) c FROM default.TEMPORARY_GPS_TABLES' ;
    LET r RESULTSET := EXECUTE IMMEDIATE :record_count_sql ;
    for record in r DO
        record_count := record.c ;
    END FOR ;

    -- Step 4: 设置会话参数，example
    EXECUTE IMMEDIATE 'set max_block_size = 65536' ;

    -- Step 5: 执行 示例 SQL
    LET merge_sql := 'insert into default.gps select * from default.TEMPORARY_GPS_TABLES;' ;
    EXECUTE IMMEDIATE :merge_sql ;

    -- Step 6: 清理：删除视图
    EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS default.TEMPORARY_GPS_TABLES' ;

    -- Step 7: 删除所有 %abcd% 表
    FOR i IN 1 TO ARRAY_SIZE(table_names) DO
        LET tbl_name := table_names[i]::STRING ;
        LET drop_sql := 'DROP TABLE default."' || tbl_name || '"' ;
        EXECUTE IMMEDIATE :drop_sql ;
    END FOR ;
    RETURN 'Merge completed successfully. Processed ' || table_count || ' tables. Total records: ' || record_count ;
END ;
$$ ; 
```

调用结果：

```sql
call PROCEDURE PROC_MERGE_GPS() ;
---
Merge completed successfully. Processed 3 tables. Total records: 300
```

总结

**Databend 的 SQL 存储过程为数据处理提供了强大而灵活的工具。通过本文，我们学习了：**

- **基础语法：如何创建和调用存储过程**
- **变量和赋值：使用 LET 声明和管理变量**
- **流程控制：IF 条件判断和 FOR 循环**
- **高级特性：嵌套循环、结果集遍历、返回表格**
- **管理操作：查看、描述、删除存储过程**
- **最佳实践：命名规范、注释、错误处理、性能优化**

**_关键要点回顾_**

- ✅ 使用 `CREATE PROCEDURE` 创建存储过程
- ✅ 使用 `CALL PROCEDURE` 调用存储过程
- ✅ 使用 `EXECUTE IMMEDIATE` 执行动态 SQL
- ✅ 使用 `LET` 声明变量，`:=` 赋值
- ✅ 支持 `IF-THEN-ELSE` 条件判断
- ✅ 支持 `FOR...IN...DO` 循环
- ✅ 可以返回单个值或整张表
- ✅ 使用 `CREATE OR REPLACE` 更新存储过程
- ✅ 使用 `RESULTSET` 类型处理查询结果

**_下一步_**

**现在你已经掌握了 Databend 存储过程的核心知识，可以开始：**

- 在自己的项目中创建简单的存储过程
- 逐步引入更复杂的逻辑和流程控制
- 将常用的数据处理任务封装为存储过程
- 探索更多高级特性和优化技巧

Happy coding with Databend! 🚀

关于 Databend

Databend 是一款开源、弹性、低成本，基于对象存储也可以做实时分析的新式湖仓。期待您的关注，一起探索云原生数仓解决方案，打造新一代开源 Data Cloud。

👨‍💻‍ Databend Cloud：https://databend.cn

📖 Databend 文档：https://docs.databend.cn

💻Wechat：Databend

✨GitHub：https://github.com/databendlabs/databend