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;
创建存储过程(真正的存储过程procedure)
存储过程和函数的区别：
创建存储过程语法:
create or replace procedure 存储过程名(param_list)
language plpgsql
as
d
e
c
l
a
r
e
变量名
t
y
p
e
[
d
e
f
a
u
l
t
v
a
l
u
e
]
;
b
e
g
i
n
s
q
l
s
t
a
t
e
m
e
n
t
;
c
o
m
m
i
t
;
e
n
d
;
declare 变量名 type [default value]; begin sql_statement; commit; end;
declare变量名type[defaultvalue];beginsqlstatement;commit;end;; – 分号可要可不要
举例：
create or replace procedure transfer(id1 int,id2 int,num numeric)
language plpgsql
as
b
e
g
i
n
u
p
d
a
t
e
a
c
c
o
u
n
t
s
s
e
t
a
c
c
o
u
n
t
=
a
c
c
o
u
n
t
−
n
u
m
w
h
e
r
e
i
d
=
i
d
1
;
u
p
d
a
t
e
a
c
c
o
u
n
t
s
s
e
t
a
c
c
o
u
n
t
=
a
c
c
o
u
n
t
+
n
u
m
w
h
e
r
e
i
d
=
i
d
2
;
c
o
m
m
i
t
;
−
−
提交事务
e
n
d
;
begin update accounts set account=account-num where id = id1; update accounts set account=account+num where id = id2; commit; --提交事务 end;
beginupdateaccountssetaccount=account−numwhereid=id1;updateaccountssetaccount=account+numwhereid=id2;commit;−−提交事务end;
或：
create or replace procedure transfer(int,int,numeric)
language plpgsql
as KaTeX parse error: Can't use function '$' in math mode at position 56: …ccount=account-$̲3 where id = $1…
调用存储过程：
call transfer(1,2,1000);
accounts之前数据：
id name account
1 Tom 9000
2 Jerry 9000
调用存储过程之后的数据：
id name account
1 Tom 8000
2 Jerry 10000
好了，我是Lee，一枚数据开发，不定期更新一下博客，希望一直这样下去，有东西写。