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;

創(chuàng)建存儲(chǔ)過程(真正存儲(chǔ)過程procedure)
存儲(chǔ)過程和函數(shù)的區(qū)別：
存儲(chǔ)過程無需return返回值，函數(shù)必須有return返回值，
存儲(chǔ)過程可以使用事物，函數(shù)中無法使用事物，


創(chuàng)建存儲(chǔ)過程語法:
create or replace procedure 存儲(chǔ)過程名(param_list)
language plpgsql
as $$
    declare 變量名 type [default value];
    begin
        sql_statement;
        commit;
    end;
$$;  -- 分號(hào)可要可不要



舉例：
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;  --提交事務(wù)
    end;

$$

或：

create or replace procedure transfer(int,int,numeric)
language plpgsql
as $$
    begin
    
        update accounts set account=account-$3 where id = $1;  --$1表示第一個(gè)參數(shù)
        update accounts set account=account+$3 where id = $2;  --$3表示第三個(gè)參數(shù)
        
        commit;  --提交事務(wù)
    end;

$$


調(diào)用存儲(chǔ)過程：
call transfer(1,2,1000);

accounts之前數(shù)據(jù)：
id  name   account
1   Tom     9000
2   Jerry   9000

調(diào)用存儲(chǔ)過程之后的數(shù)據(jù)：
id  name   account
1   Tom     8000
2   Jerry   10000