postgresql存储过程如何编写_postgresqlplpgsql开发指南-SQL-PHP中文网

postgresql存储过程如何编写_postgresqlplpgsql开发指南

舞夢輝影

发布： 2025-11-27 22:11:01

原创

664人浏览过

PostgreSQL从11开始支持存储过程，使用PL/pgSQL编写，可通过CALL调用，支持事务控制与复杂逻辑处理。

postgresql存储过程如何编写_postgresqlplpgsql开发指南

PostgreSQL 中的存储过程可以通过 PL/pgSQL 编写，这是一种功能强大的过程语言，允许你在数据库中定义复杂的逻辑。从 PostgreSQL 11 开始，正式支持使用 CREATE PROCEDURE 命令创建真正的存储过程（与函数不同，存储过程可以使用事务控制命令如 COMMIT 和 ROLLBACK）。

什么是 PL/pgSQL？

PL/pgSQL 是 PostgreSQL 的过程语言扩展，语法类似 Oracle 的 PL/SQL，支持变量定义、条件判断、循环、异常处理等结构化编程特性，非常适合编写复杂的数据操作逻辑。

基本语法：创建一个存储过程

使用 CREATE OR REPLACE PROCEDURE 来定义或修改存储过程：

CREATE OR REPLACE PROCEDURE insert_user(
    IN user_name TEXT,
    IN user_email TEXT
)
AS $$
BEGIN
    INSERT INTO users (name, email, created_at)
    VALUES (user_name, user_email, NOW());
END;
$$ LANGUAGE plpgsql;

登录后复制

说明：

IN 参数：表示输入参数，也可以省略 IN 关键字，默认就是输入参数。
LANGUAGE plpgsql：指定过程语言为 PL/pgSQL。
$$ ... $$：称为“美元引用”，用来包裹函数体，避免引号冲突。

调用存储过程

使用 CALL 命令来执行存储过程：

CALL insert_user('Alice', 'alice@example.com');

登录后复制

该语句会向 users 表插入一条记录。

带输出参数的存储过程

存储过程也可以返回值，通过 OUT 或 INOUT 参数实现：

CREATE OR REPLACE PROCEDURE get_user_count(
    OUT total_count INTEGER
)
AS $$
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END;
$$ LANGUAGE plpgsql;

登录后复制

调用方式：

零一万物开放平台

零一万物大模型开放平台

查看详情

CALL get_user_count(); -- 返回结果集中的 total_count

登录后复制

在存储过程中使用事务控制

这是存储过程区别于函数的重要特性 —— 可以在过程中提交或回滚事务（仅适用于 PostgreSQL 11+）：

CREATE OR REPLACE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount NUMERIC
)
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    IF NOT FOUND THEN
        RAISE EXCEPTION '源账户不存在';
    END IF;
<pre class='brush:php;toolbar:false;'>UPDATE accounts SET balance = balance + amount WHERE id = to_account;
IF NOT FOUND THEN
    RAISE EXCEPTION '目标账户不存在';
END IF;

COMMIT; -- 显式提交事务

登录后复制

EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; $$ LANGUAGE plpgsql;

这个例子展示了如何在出错时回滚事务，确保数据一致性。

常见结构和控制语句

PL/pgSQL 支持多种流程控制结构：

IF 判断：

IF score > 90 THEN
    grade := 'A';
ELSIF score > 80 THEN
    grade := 'B';
ELSE
    grade := 'C';
END IF;

登录后复制

LOOP 循环：

DECLARE
    i INTEGER := 1;
BEGIN
    WHILE i <= 10 LOOP
        INSERT INTO numbers (value) VALUES (i);
        i := i + 1;
    END LOOP;
END;

登录后复制

遍历查询结果：

FOR rec IN SELECT name, email FROM users WHERE active THEN
    RAISE NOTICE '用户: %, 邮箱: %', rec.name, rec.email;
END LOOP;

登录后复制

错误处理（异常捕获）

使用 EXCEPTION 块捕获运行时错误：

BEGIN
    DELETE FROM users WHERE id = user_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE '未找到用户 ID %', user_id;
    WHEN OTHERS THEN
        RAISE NOTICE '发生未知错误: %', SQLERRM;
END;

登录后复制