PostgreSQL 的存储过程怎么写

发布时间：2021-09-17 11:07:21 作者：chen
来源：亿速云阅读：275

# PostgreSQL 的存储过程怎么写

## 目录
1. [存储过程概述](#存储过程概述)
2. [PL/pgSQL基础语法](#plpgsql基础语法)
3. [创建存储过程](#创建存储过程)
4. [参数传递](#参数传递)
5. [流程控制](#流程控制)
6. [异常处理](#异常处理)
7. [事务管理](#事务管理)
8. [动态SQL](#动态sql)
9. [性能优化建议](#性能优化建议)
10. [实际应用案例](#实际应用案例)
11. [与函数的区别](#与函数的区别)
12. [最佳实践](#最佳实践)

## 存储过程概述

PostgreSQL的存储过程是使用PL/pgSQL语言编写的数据库端程序，具有以下特点：

- **服务器端执行**：减少网络传输开销
- **预编译**：提高执行效率
- **事务控制**：可在过程中管理事务
- **复杂逻辑**：支持条件判断、循环等编程结构

```sql
-- 简单示例
CREATE OR REPLACE PROCEDURE greet(name TEXT)
AS $$
BEGIN
  RSE NOTICE 'Hello, %!', name;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL基础语法

PL/pgSQL是PostgreSQL的过程语言扩展，语法特点包括：

块结构：

[ <<label>> ]
[ DECLARE
   declarations ]
BEGIN
   statements
EXCEPTION
   WHEN condition THEN
       handler_statements
END [ label ];

变量声明：

DECLARE
   counter INTEGER := 0;
   user_name VARCHAR(50);
   start_time TIMESTAMP := NOW();

数据类型：支持所有PostgreSQL数据类型

创建存储过程

基本创建语法：

CREATE [OR REPLACE] PROCEDURE procedure_name([parameters])
[LANGUAGE lang_name]
AS $$
    -- 过程体
$$;

完整示例：

CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    sender_balance DECIMAL(10,2);
BEGIN
    -- 检查发送方余额
    SELECT balance INTO sender_balance FROM accounts WHERE id = sender_id;
    
    IF sender_balance < amount THEN
        RSE EXCEPTION 'Insufficient funds: %', sender_balance;
    END IF;
    
    -- 执行转账
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
    
    -- 记录交易
    INSERT INTO transactions(from_account, to_account, amount, trans_date)
    VALUES (sender_id, receiver_id, amount, NOW());
    
    COMMIT;
END;
$$;

参数传递

PostgreSQL存储过程支持三种参数模式：

IN参数（默认）：输入参数

CREATE PROCEDURE add_employee(IN name TEXT, IN salary NUMERIC)

OUT参数：输出参数

CREATE PROCEDURE get_stats(OUT max_salary NUMERIC, OUT min_salary NUMERIC)

INOUT参数：双向参数

CREATE PROCEDURE increment_counter(INOUT counter INTEGER)

调用示例：

-- 调用OUT参数过程
CALL get_stats(NULL, NULL);  -- 需要占位符

-- 更优雅的方式
DO $$
DECLARE
    max_val NUMERIC;
    min_val NUMERIC;
BEGIN
    CALL get_stats(max_val, min_val);
    RSE NOTICE 'Max: %, Min: %', max_val, min_val;
END;
$$;

流程控制

条件语句

-- IF-THEN-ELSE
IF condition THEN
    statements
[ ELSIF condition THEN
    statements ]
[ ELSE
    statements ]
END IF;

-- CASE语句
CASE search_expression
    WHEN expression THEN statements
    [ WHEN expression THEN statements ... ]
    [ ELSE statements ]
END CASE;

循环结构

-- 基本LOOP
LOOP
    statements
    EXIT WHEN condition;
END LOOP;

-- WHILE循环
WHILE condition LOOP
    statements
END LOOP;

-- FOR循环（整数范围）
FOR i IN 1..10 LOOP
    RSE NOTICE 'Counter: %', i;
END LOOP;

-- FOR循环（查询结果）
FOR record IN SELECT * FROM employees LOOP
    -- 处理每条记录
END LOOP;

异常处理

PostgreSQL提供完善的异常处理机制：

BEGIN
    -- 可能出错的代码
EXCEPTION
    WHEN division_by_zero THEN
        -- 处理除以零错误
    WHEN OTHERS THEN
        -- 处理其他所有错误
        RSE NOTICE 'Error: %', SQLERRM;
END;

常见异常条件： - NO_DATA_FOUND - TOO_MANY_ROWS - INTEGRITY_CONSTRNT_VIOLATION - CHECK_VIOLATION

事务管理

存储过程中可以控制事务：

CREATE PROCEDURE process_order(order_id INT)
AS $$
BEGIN
    -- 自动开始事务
    
    -- 锁定订单记录
    PERFORM * FROM orders WHERE id = order_id FOR UPDATE;
    
    -- 检查库存
    IF NOT check_inventory(order_id) THEN
        RSE EXCEPTION 'Insufficient inventory';
    END IF;
    
    -- 更新库存
    UPDATE inventory SET quantity = quantity - 1
    WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = order_id);
    
    -- 标记订单为已完成
    UPDATE orders SET status = 'completed' WHERE id = order_id;
    
    COMMIT;  -- 显式提交
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 出错时回滚
        RSE;
END;
$$ LANGUAGE plpgsql;

动态SQL

使用EXECUTE执行动态SQL：

CREATE PROCEDURE dynamic_query(table_name TEXT, column_name TEXT, search_value TEXT)
AS $$
DECLARE
    query TEXT;
    result RECORD;
BEGIN
    query := format('SELECT * FROM %I WHERE %I = $1', table_name, column_name);
    
    EXECUTE query INTO result USING search_value;
    
    RSE NOTICE 'Found: %', result;
END;
$$ LANGUAGE plpgsql;

安全注意事项： 1. 使用format()函数和%I标识符占位符 2. 参数化查询（USING子句） 3. 避免直接拼接SQL字符串

性能优化建议

使用绑定变量： “`sql – 不好 EXECUTE ‘UPDATE users SET status = “’ || new_status || “’ WHERE id = ‘ || user_id;

– 好 EXECUTE ‘UPDATE users SET status = \(1 WHERE id = \)2’ USING new_status, user_id;


2. **批量操作**：
   ```sql
   -- 使用RETURNING子句获取多行
   FOR result IN 
       UPDATE products SET price = price * 1.1 
       WHERE category = 'electronics'
       RETURNING id, name, price
   LOOP
       RSE NOTICE 'Updated: %', result;
   END LOOP;

游标使用：


DECLARE
   cur CURSOR FOR SELECT * FROM large_table;
   batch_size INT := 1000;
   batch RECORD[];
BEGIN
   OPEN cur;
   LOOP
       FETCH FORWARD batch_size FROM cur INTO batch;
       EXIT WHEN batch IS NULL;
       -- 处理批次数据
   END LOOP;
   CLOSE cur;
END;

实际应用案例

数据归档过程

CREATE PROCEDURE archive_old_data(retention_months INT DEFAULT 12)
AS $$
DECLARE
    cutoff_date DATE;
    archived_count INT;
BEGIN
    cutoff_date := CURRENT_DATE - (retention_months * INTERVAL '1 month');
    
    -- 创建归档表（如果不存在）
    CREATE TABLE IF NOT EXISTS archived_orders (LIKE orders INCLUDING ALL);
    
    -- 归档数据
    WITH moved_rows AS (
        DELETE FROM orders
        WHERE order_date < cutoff_date
        RETURNING *
    )
    INSERT INTO archived_orders SELECT * FROM moved_rows;
    
    GET DIAGNOSTICS archived_count = ROW_COUNT;
    
    RSE NOTICE 'Archived % orders older than %', archived_count, cutoff_date;
    
    -- 更新统计信息
    ANALYZE orders;
    ANALYZE archived_orders;
END;
$$ LANGUAGE plpgsql;

与函数的区别

特性	存储过程	函数
返回值	无或通过OUT参数	必须返回单个值或表
调用方式	CALL	SELECT或表达式
事务控制	可以包含COMMIT/ROLLBACK	不能控制事务
计划缓存	无	有
使用场景	执行操作	计算和返回数据

最佳实践

命名规范：
- 使用小写和下划线：update_customer_status
- 添加模块前缀：billing_generate_invoice
文档注释： “`sql /*
- 功能：处理用户注册
- 参数：
- username - 用户名
- email - 电子邮箱
- 返回：用户ID */ CREATE PROCEDURE register_user(…)
”`
错误处理：
- 记录错误日志
- 提供有意义的错误消息
- 考虑重试逻辑

测试策略：

DO $$
BEGIN
   -- 测试用例1
   BEGIN
       CALL transfer_funds(1, 2, 100);
       RSE NOTICE 'Test 1 passed';
   EXCEPTION WHEN OTHERS THEN
       RSE EXCEPTION 'Test 1 failed: %', SQLERRM;
   END;


   -- 测试用例2：余额不足
   BEGIN
       CALL transfer_funds(1, 2, 100000);
       RSE EXCEPTION 'Test 2 should have failed';
   EXCEPTION 
       WHEN OTHERS THEN
           IF SQLSTATE = 'P0001' THEN
               RSE NOTICE 'Test 2 passed (expected exception)';
           ELSE
               RSE EXCEPTION 'Test 2 failed with wrong exception: %', SQLERRM;
           END IF;
   END;
END;
$$;

版本控制：
- 将存储过程定义保存在SQL文件中
- 使用迁移工具（如Flyway或Liquibase）
- 每次修改使用OR REPLACE

总结

PostgreSQL存储过程是强大的服务器端编程工具，通过合理使用可以： - 减少网络往返 - 保证数据一致性 - 实现复杂业务逻辑 - 提高性能

掌握PL/pgSQL语言和存储过程开发技巧，可以显著提升数据库应用的开发效率和运行性能。 “`