PostgreSQL 教程: 创建 PL/pgSQL 存储过程

八月 18, 2023

摘要:在本教程中,您将学习如何使用 PostgreSQL 的CREATE PROCEDURE语句创建新的存储过程。

CREATE PROCEDURE 语句简介

到目前为止,您已经学习了如何使用create function语句创建用户定义函数

用户定义函数的一个缺点是它们无法执行事务。换句话说,在用户定义的函数内部,您无法启动事务并提交或回滚它。

PostgreSQL 11 引入了支持事务的存储过程。

要定义新的存储过程,请使用create procedure语句。

下面说明了create procedure语句的基本语法:

create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$

在这个语法中:

  • 首先,在create procedure关键字后指定存储过程的名称。
  • 其次,定义存储过程的参数。存储过程可以接受零个或多个参数。
  • 第三,指定plpgsql为存储过程的过程语言。请注意,您可以使用其他过程语言来定义存储过程,例如 SQL、C 等。
  • 最后,使用美元符号引用的字符串常量语法来定义存储过程的主体。

存储过程中的参数可以有ininout模式。他们不能拥有out模式。

存储过程不返回值。您不能在存储过程中使用带有返回值的return语句,如下所示:

return expression;

但是,您可以使用不带expressionreturn语句来立即停止存储过程:

return;

如果要从存储过程返回值,可以使用inout模式的参数。

CREATE PROCEDURE 语句示例

我们将使用下面的accounts表进行演示:

drop table if exists accounts;

create table accounts (
    id int generated by default as identity,
    name varchar(100) not null,
    balance dec(15,2) not null,
    primary key(id)
);

insert into accounts(name,balance)
values('Bob',10000);

insert into accounts(name,balance)
values('Alice',10000);

下面的语句显示了accounts表中的数据:

select * from accounts;

img

以下示例创建一个名为transfer的存储过程,用于将指定金额从一个帐户转移到另一个帐户。

create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$

调用存储过程

要调用存储过程,请使用如下的CALL语句:

call stored_procedure_name(argument_list);

例如,此语句调用transfer存储过程从 Bob 的帐户转账$1,000到 Alice 的帐户。

call transfer(1,2,1000);

以下语句验证传输后accounts表中的数据:

SELECT * FROM accounts;

img

结果符合预期。

概括

  • 使用create procedure语句定义一个新的存储过程。

  • 使用call语句调用存储过程。