创建存储过程(真正存储过程procedure)
存储过程和函数的区别:
存储过程无需return返回值,函数必须有return返回值,
存储过程可以使用事物,函数中无法使用事物,
create table accounts(
id serial primary key,
name varchar(50) not null,
account numeric
);
insert into accounts values
(1,'Tom','9000'),
(2,'Jerry','9000');
select * from accounts;
创建存储过程语法:
create or replace procedure 存储过程名(param_list)
language plpgsql
as $$
declare 变量名 type [default value];
begin
sql_statement;
commit;
end;
$$; -- 分号可要可不要
举例:
create or replace procedure transfer(id1 int,id2 int,num numeric)
language plpgsql
as $$
begin
update accounts set account=account-num where id = id1;
update accounts set account=account+num where id = id2;
commit; --提交事务
end;
$$
或:
create or replace procedure transfer(int,int,numeric)
language plpgsql
as $$
begin
update accounts set account=account-$3 where id = $1; --$1表示第一个参数
update accounts set account=account+$3 where id = $2; --$3表示第三个参数
commit; --提交事务
end;
$$
调用存储过程:
call transfer(1,2,1000);