Databend SQL 存储过程使用指南

2025-11-14
北京
本文字数：5492 字
阅读完需：约 18 分钟

一、什么是存储过程？

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

存储过程的优势

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

二、第一个存储过程：Hello World

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

CREATE PROCEDURE my_add(a Int32, b Int32)RETURNS Int32LANGUAGE SQLAS $$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：执行计算并返回结果

调用存储过程

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

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

复制代码

SqlScript

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

执行单个 SQL

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

复制代码

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

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

复制代码

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

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

3.1 变量声明和使用

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, 后续初始化

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 变量用于存储查询结果集，语法有：

示例语法：

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

复制代码

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

Cursor 变量

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

LET <cursor_variable> CURSOR for <query>
LET <cursor_variable> CURSOR for <result_set_variable>
OPEN <cursor_variable>
FETCH <cursor_variable> INTO <variable>
CLOSE <cursor_variable>
for <variable> in <cursor_variable> do ... end for

示例语法：

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;$$;

复制代码

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

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

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;$$;

复制代码

3.3 循环：FOR 循环

FOR 循环有两种常见形式：

形式一：范围循环

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

复制代码

示例：

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

复制代码

形式二：结果集循环

示例：

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;$$;

复制代码

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

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

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

动态拼接语句，嵌套执行

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

复制代码

五、返回表格数据

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

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

复制代码

六、存储过程管理

七、最佳实践

7.1 命名规范

使用有意义的名称，清晰表达功能
使用下划线分隔单词（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(...)

复制代码

7.2 注释说明

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

CREATE PROCEDURE process_orders(order_date DATE)RETURNS INTLANGUAGE SQLCOMMENT = '处理指定日期的订单，返回处理数量'AS $$ ... $$ ;

复制代码

7.3 性能考虑

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

八、实战案例：数据清洗流程

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

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

CREATE OR REPLACE PROCEDURE cleanup_user_data(days_threshold INT)RETURNS TABLE(    action VARCHAR,    user_count INT,    processed_at TIMESTAMP)LANGUAGE SQLCOMMENT = '清理和归档不活跃用户数据'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) ;

复制代码

9.2 扫描表并合并数据到 target 表

CREATE OR REPLACE PROCEDURE PROC_MERGE_GPS()RETURNS STRINGLANGUAGE SQLAS$$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 ;$$ ;

复制代码

调用结果：

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：databend.cn

📖 Databend 文档：docs.databend.cn

💻 Wechat：Databend

✨ GitHub：github.com/databendlab…

发布于: 10 小时前阅读数: 8

原文链接:【http://xie.infoq.cn/article/748974a2ae88720ec9263ba50】。文章转载请联系作者。

Databend

关注

还未添加个人签名 2022-08-25 加入

还未添加个人简介

发布

暂无评论

创作场景

Databend SQL 存储过程使用指南

一、什么是存储过程？

存储过程的优势

二、第一个存储过程：Hello World

语法解析

调用存储过程

SqlScript

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

3.1 变量声明和使用

Scalar 变量

ResultSet 变量

Cursor 变量

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

3.3 循环：FOR 循环

形式一：范围循环

形式二：结果集循环

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

逻辑分析

动态拼接语句，嵌套执行

五、返回表格数据

六、存储过程管理

七、 最佳实践

7.1 命名规范

7.2 注释说明

7.3 性能考虑

八、实战案例：数据清洗流程

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

9.2 扫描表并合并数据到 target 表

九、总结

关键要点回顾

下一步

关于 Databend

Databend

评论

七、最佳实践