我来为您详细介绍 PostgreSQL 存储过程的编写方法。PostgreSQL 从 11 版本开始引入了完整的存储过程（`PROCEDURE`）支持，在此之前通常使用函数（`FUNCTION`）来实现类似功能。
一、存储过程 vs 函数
特性 函数 (FUNCTION) 存储过程 (PROCEDURE)
返回值 必须有返回值 无返回值
事务控制 不能控制事务 可以使用 `COMMIT`/`ROLLBACK`
调用方式 `SELECT func()` 或 SQL 表达式 `CALL proc()`
适用场景 计算、查询 复杂业务逻辑、批量操作
二、基础语法
1. 创建存储过程
```sql
-- 基本结构
CREATE OR REPLACE PROCEDURE 过程名(
参数名 数据类型 [IN | OUT | INOUT],
...
)
LANGUAGE plpgsql
AS $$
DECLARE
-- 声明变量
BEGIN
-- 业务逻辑
EXCEPTION
-- 异常处理
END;
$$;
```
2. 完整示例
```sql
-- 示例1：简单的用户注册过程
CREATE OR REPLACE PROCEDURE sp_user_register(
p_username VARCHAR(50),
p_email VARCHAR(100),
p_password VARCHAR(255)
)
LANGUAGE plpgsql
AS $$
BEGIN
-- 检查用户名是否已存在
IF EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
RAISE EXCEPTION '用户名已存在: %', p_username;
END IF;
-- 插入新用户
INSERT INTO users (username, email, password, created_at)
VALUES (p_username, p_email, p_password, NOW());
RAISE NOTICE '用户 % 注册成功', p_username;
END;
$$;
-- 调用方式
CALL sp_user_register('john_doe', 'john@example.com', 'hashed_password');
```
三、常用控制结构
1. 条件判断
```sql
-- IF-THEN-ELSE
IF 条件 THEN
-- 语句
ELSIF 条件 THEN
-- 语句
ELSE
-- 语句
END IF;
-- CASE 表达式
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE 默认结果
END;
```
2. 循环结构
```sql
-- LOOP 循环
LOOP
EXIT WHEN 条件; -- 退出条件
END LOOP;
-- WHILE 循环
WHILE 条件 LOOP
-- 语句
END LOOP;
-- FOR 循环（整数范围）
FOR i IN 1..10 LOOP
-- 语句
END LOOP;
-- FOR 循环（查询结果集）
FOR rec IN SELECT * FROM users LOOP
RAISE NOTICE '用户: %', rec.username;
END LOOP;
-- FOREACH（遍历数组）
FOREACH item IN ARRAY arr LOOP
-- 语句
END LOOP;
```
四、事务控制（存储过程特有）
```sql
CREATE OR REPLACE PROCEDURE sp_batch_transfer()
LANGUAGE plpgsql
AS $$
DECLARE
v_count INT := 0;
BEGIN
-- 第一批操作
INSERT INTO logs (msg) VALUES ('开始批量转账');
COMMIT; -- 提交第一批
-- 第二批操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
GET DIAGNOSTICS v_count = ROW_COUNT;
IF v_count = 0 THEN
ROLLBACK; -- 回滚
RAISE EXCEPTION '转账失败';
ELSE
COMMIT; -- 提交第二批
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
$$;
```
五、游标操作（处理大数据量）
```sql
CREATE OR REPLACE PROCEDURE sp_process_large_table()
LANGUAGE plpgsql
AS $$
DECLARE
cur CURSOR FOR SELECT id, name FROM large_table WHERE processed = false;
rec RECORD;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO rec;
EXIT WHEN NOT FOUND;
-- 逐行处理
UPDATE large_table
SET processed = true, updated_at = NOW()
WHERE id = rec.id;
-- 每1000条提交一次，避免长事务
IF rec.id % 1000 = 0 THEN
COMMIT;
END IF;
END LOOP;
CLOSE cur;
END;
$$;
```
六、动态 SQL
```sql
CREATE OR REPLACE PROCEDURE sp_dynamic_query(
p_table_name VARCHAR(50),
p_limit INT DEFAULT 100
)
LANGUAGE plpgsql
AS $$
DECLARE
sql_query TEXT;
rec RECORD;
BEGIN
-- 构建动态SQL（注意SQL注入风险）
sql_query := format('SELECT * FROM %I LIMIT %L', p_table_name, p_limit);
-- 执行动态查询
FOR rec IN EXECUTE sql_query LOOP
RAISE NOTICE '记录: %', rec;
END LOOP;
-- 或使用 EXECUTE ... INTO
EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(p_table_name) INTO v_count;
END;
$$;
```
> 安全提示：使用 `format()` 的 `%I`（标识符）和 `%L`（字面值）或 `quote_ident()`/`quote_literal()` 防止 SQL 注入。
七、异常处理
```sql
CREATE OR REPLACE PROCEDURE sp_safe_operation()
LANGUAGE plpgsql
AS $$
BEGIN
-- 主逻辑
INSERT INTO critical_table VALUES (...);
EXCEPTION
-- 唯一约束冲突
WHEN unique_violation THEN
RAISE NOTICE '记录已存在，跳过';
-- 外键约束冲突
WHEN foreign_key_violation THEN
RAISE EXCEPTION '关联数据不存在';
-- 检查约束冲突
WHEN check_violation THEN
RAISE EXCEPTION '数据不符合约束条件';
-- 捕获所有其他异常
WHEN OTHERS THEN
RAISE EXCEPTION '未知错误: % - %', SQLSTATE, SQLERRM;
-- 或使用 RAISE NOTICE 记录后忽略
END;
$$;
```
八、输出参数
```sql
CREATE OR REPLACE PROCEDURE sp_get_user_stats(
IN p_user_id INT,
OUT total_orders INT,
OUT total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*), COALESCE(SUM(amount), 0)
INTO total_orders, total_amount
FROM orders
WHERE user_id = p_user_id;
END;
$$;
-- 调用
CALL sp_get_user_stats(123); -- 返回结果集
```
九、最佳实践
1. 命名规范：使用 `sp_` 前缀区分存储过程
2. 参数命名：输入参数用 `p_` 前缀，输出用 `o_`，内部变量用 `v_`
3. 错误处理：始终包含 EXCEPTION 块
4. 事务粒度：合理控制事务范围，避免长事务
5. 权限控制：使用 `SECURITY DEFINER` 或 `SECURITY INVOKER`
6. 文档注释：添加详细的注释说明
```sql
COMMENT ON PROCEDURE sp_user_register IS
'用户注册存储过程
参数：
p_username - 用户名
p_email - 邮箱
p_password - 加密后的密码
异常：
用户名已存在时抛出异常';
```
十、管理命令
```sql
-- 查看所有存储过程
SELECT * FROM pg_proc WHERE prokind = 'p';
-- 修改存储过程
ALTER PROCEDURE sp_name RENAME TO new_name;
-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_name;
-- 查看存储过程源码
SELECT pg_get_functiondef('sp_name'::regprocedure);
```
需要我针对某个具体业务场景（如订单处理、数据同步、报表生成等）编写更详细的存储过程示例吗？
