Databend SQL 存储过程使用指南

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

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

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

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

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

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

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

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

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

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

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

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

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