# 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是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; 使用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字符串
– 好 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_statusbilling_generate_invoice文档注释： “`sql /*
”`
错误处理：
测试策略：
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; $$; 版本控制：
OR REPLACEPostgreSQL存储过程是强大的服务器端编程工具，通过合理使用可以： - 减少网络往返 - 保证数据一致性 - 实现复杂业务逻辑 - 提高性能
掌握PL/pgSQL语言和存储过程开发技巧，可以显著提升数据库应用的开发效率和运行性能。 “`
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
