Postgresql-11 存储过程

PG11之前采用Function. 并没有存储过程模式. PG10-存储过程)

PG11新增PROCEDURE[存储过程]模式对象

​ 存储过程与函数类似, 但存储过程并没有返回值.

Postgresql.org 官网主要介绍

  • SQL stored procedures that support embedded transactions

    • 在定义中允许使用commit & rollback语句.
  • Add SQL-level procedures, which can start and commit their own transactions (Peter Eisentraut)

    They are created with the new CREATE PROCEDURE command and invoked via CALL.

    使用call调用存储过程.

    The new ALTER/DROP ROUTINE commands allow altering/dropping of all routine-like objects, including procedures, functions, and aggregates.

    Also, writing FUNCTION is now preferred over writing PROCEDURE in CREATE OPERATOR and CREATE TRIGGER, because the referenced object must be a function not a procedure. However, the old syntax is still accepted for compatibility.

简单示例: - procedure创建;
1
2
3
4
5
6
create table tb1(id int, name text);

create procedure proc1(id integer, name text) returns void
LANGUAGE sql as $$
insert into tb1(id, name) values (id, name); //真正操作的sql语句;
$$

存储过程调用:

1
2
call proc1(1, 'the first value');
call proc1(1, 'the second value');
函数[Function]常见两种写法:
1
2
3
4
5
CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)
RETURNS NUMERIC
AS $$
SELECT a+b;
$$ LANGUAGE SQL;
1
2
3
4
5
CREATE OR REPLACE FUNCTION plus_and_minus
(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)
AS $$
SELECT a+b, a-b;
$$ LANGUAGE SQL;

主要带有返回值. 并表明返回值的行列个数, 类型

存储过程[procedure]写法
1
2
3
4
5
6
7
8
9
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...

其余与function相同;

关于Postgresql官网所说对于事务支持:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE PROCEDURE proc2()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO tbl(id, name) VALUES (i, 'value: '|| i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
$$ ;

结果展示:

1
2
3
4
5
6
7
8
9
10
11
12
13
ligang=# call proc2 ();
CALL
ligang=#
ligang=#
ligang=# select * from tbl ;
id | name
----+----------
0 | value: 0
2 | value: 2
4 | value: 4
6 | value: 6
8 | value: 8
(5 rows)
psql中:

\df 查看存储过程;

\sf 查看存储过程定义;

资料

sql create procedure

sql alter procedure

sql drop procedure

sql call

欣赏此文? 求鼓励,求支持!