GaussDB存储过程深度解析：从开发到生产实践

一、核心优势对比

二、开发规范与实现

1. 基础语法结构

CREATE OR REPLACE PROCEDURE process_orders(
    IN p_status VARCHAR(20),
    OUT p_total INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_batch_size INT := 1000;
    v_processed INT := 0;
BEGIN
    -- 事务控制
    BEGIN
        LOOP
            UPDATE orders 
            SET status = 'PROCESSED'
            WHERE status = p_status
            LIMIT v_batch_size;
            
            GET DIAGNOSTICS v_processed = ROW_COUNT;
            
            EXIT WHEN v_processed = 0;
            
            COMMIT;
            PERFORM pg_sleep(0.1);
        END LOOP;
        
        p_total := v_processed;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE EXCEPTION 'Error: %', SQLERRM;
    END;
END;
$$;

2. 高级功能实现
   游标操作示例

CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(user_id INT, login_time TIMESTAMP) AS $$
DECLARE
    cur CURSOR FOR 
        SELECT id, last_login 
        FROM users 
        WHERE last_login > NOW() - INTERVAL '7 days';
BEGIN
    OPEN cur;
    LOOP
        FETCH NEXT FROM cur INTO user_id, login_time;
        EXIT WHEN NOT FOUND;
        RETURN NEXT;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;

三、性能优化策略

1. 执行计划优化

-- 启用详细执行计划分析
EXPLAIN ANALYZE CALL process_orders('PENDING', NULL);

– 关键指标优化方向

| 指标                | 优化目标         | 实现手段               |
|---------------------|------------------|-----------------------|
| 事务提交次数        | 减少50%          | 批量提交优化          |
| 临时表使用          | 避免创建         | 使用内存表替代        |
| 锁等待时间          | <100ms           | 行级锁替代表级锁      |

2. 批量操作优化

-- 批量插入优化模板
CREATE OR REPLACE PROCEDURE bulk_insert_data(
    IN data JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    row_data JSONB;
    batch_size INT := 1000;
BEGIN
    FOR row_data IN SELECT * FROM jsonb_array_elements(data) LOOP
        INSERT INTO target_table (col1, col2)
        VALUES (
            (row_data->>'field1')::INT,
            (row_data->>'field2')::TEXT
        );
        
        IF MOD(row_data.id, batch_size) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
$$;

四、安全管理实践

1. 权限控制模型

-- 最小权限配置模板
CREATE ROLE proc_executor NOLOGIN;
GRANT EXECUTE ON PROCEDURE process_orders() TO proc_executor;
GRANT USAGE ON SCHEMA orders TO proc_executor;

-- 行级安全策略
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY proc_access_policy 
ON orders 
FOR SELECT 
USING (status = 'ACTIVE');

2. 审计追踪配置

-- 启用存储过程审计
ALTER SYSTEM SET audit_proc_events = 'all';
ALTER SYSTEM SET audit_log_parameter = on;

-- 查看审计日志
SELECT 
    event_time,
    object_name,
    parameter,
    return_value
FROM pg_audit_log
WHERE object_type = 'PROCEDURE';

五、运维监控体系

1. 性能指标监控

-- 创建监控视图
CREATE VIEW proc_monitor AS
SELECT 
    proname,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_user_functions
WHERE schemaname = 'public';

-- 设置告警规则
SELECT * FROM proc_monitor 
WHERE total_time > 1000 
OR rows > 1000000;

2. 版本管理方案

-- 版本控制脚本示例
\set ON_ERROR_STOP on

BEGIN;

-- 旧版本回滚准备
ALTER PROCEDURE process_orders(...) 
SET SCHEMA public;

-- 新版本部署
CREATE OR REPLACE PROCEDURE process_orders(...) 
AS $$
...
$$;

COMMIT;

六、实战案例：电商订单处理

背景需求

日均订单量：50万+
处理时效要求：<5秒/批次
数据一致性要求：ACID
存储过程实现

CREATE OR REPLACE PROCEDURE process_daily_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    v_max_id INT;
    v_batch_size INT := 1000;
BEGIN
    LOOP
        -- 获取待处理订单
        SELECT COALESCE(MAX(id), 0) INTO v_max_id 
        FROM orders 
        WHERE status = 'CREATED' 
        AND created_at < NOW() - INTERVAL '5 minutes';
        
        EXIT WHEN v_max_id = 0;
        
        -- 批量处理
        UPDATE orders 
        SET status = 'PROCESSING'
        WHERE id IN (
            SELECT id 
            FROM orders 
            WHERE id > v_max_id - v_batch_size 
            AND id <= v_max_id 
            AND status = 'CREATED'
        );
        
        PERFORM pg_sleep(0.2);
    END LOOP;
    
    -- 触发后续流程
    PERFORM pg_notify('order_channel', 'PROCESS_COMPLETED');
END;
$$;

性能指标

七、最佳实践指南

1. 开发规范
   ​​代码结构​​：

-- 标准模板
CREATE OR REPLACE PROCEDURE proc_name(
    IN p_in_param TYPE,
    OUT p_out_param TYPE
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明区
BEGIN
    -- 业务逻辑区
EXCEPTION
    -- 异常处理区
END;
$$;

​​参数校验​​：

IF p_status NOT IN ('PENDING', 'PROCESSING') THEN
    RAISE EXCEPTION 'Invalid status code';
END IF;

2. 运维建议

| 维护周期   | 操作内容                   | 目标                          |
|------------|----------------------------|-------------------------------|
| 每日       | 执行ANALYZE VERBOSE        | 保持统计信息最新              |
| 每周       | 检查依赖对象变更           | 预防失效引用                  |
| 每月       | 审计权限配置               | 确保最小权限原则              |

GaussDB通过合理应用存储过程技术，某金融机构实现了：

日终结算时间从小时级缩短至分钟级
业务逻辑变更响应速度提升90%
数据库连接池利用率稳定在85%
建议建立存储过程版本控制系统，结合单元测试和性能基线监控，确保核心业务逻辑的稳定运行。

作者：肖冰