PostgreSQL15——PL/pgSQL 存储过程

最新推荐文章于 2026-04-05 12:40:57 发布

原创最新推荐文章于 2026-04-05 12:40:57 发布 · 899 阅读

12 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

PostgreSQL

PL/pgSQL 存储过程

一、概述
二、PL/pgSQL 代码块结构
- 2.1、嵌套子块
三、声明与赋值
- 3.1、变量
- 3.2、常量
四、控制结构
五、游标
六、错误处理
七、自定义函数
八、存储过程
- 8.1、事务管理

一、概述

除了标准SQL 语句之外， PostgreSQL 还支持使用各种过程语言（例如 PL/pgSQL、 C、 PL/Tcl、PL/Python、PL/Perl、PL/Java 等）创建复杂的过程和函数，称为存储过程（Stored Procedure）和自定义函数（User-Defined Function）。存储过程支持许多过程元素，例如控制结构、循环和复杂的计算。

使用存储过程带来的好处包括：

减少应用和数据库之间的网络传输。所有的SQL 语句都存储在数据库服务器中，应用程序只需要发送函数调用并获取除了结果，避免了发送多个 SQL 语句并等待结果。
提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。
可重用性。存储过程和函数的功能可以被多个应用同时使用。

当然，使用存储过程也可能带来一些问题：

导致软件开发缓慢。因为存储过程需要单独学习，而且很多开发人员并不具备这种技能。
不易进行版本管理和代码调试。
不同数据库管理系统之间无法移植，语法存在较大的差异。

本文主要介绍 PL/pgSQL 存储过程，它和 Oracle PL/SQL 非常类似，是 PostgreSQL 默认支持的存储过程。使用PL/pgSQL 的原因包括：

PL/pgSQL 简单易学，无论是否具有编程基础都能够很快学会。
PL/pgSQL 是PostgreSQL 默认支持的过程语言， PL/pgSQL 开发的自定义函数可以和内置函数一样使用。
PL/pgSQL 提高了许多强大的功能，例如游标，可以实现复杂的函数。

二、PL/pgSQL 代码块结构

PL/pgSQL 是一种块状语言，因此存储过程和函数以代码块的形式进行组织。以下是一个PL/pgSQL 代码块的定义：

[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements;
...
END [ label ];

label 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定变量的名称；
DECLARE 是一个可选的声明部分，用于定义变量；
BEGIN 和 END 之间是代码主体，也就是主要的功能代码；所有的语句都使用分号（;）结束，END 之后的分号表示代码块结束。

以下是一个简单的代码块示例：

DO $$
DECLARE
	name text;
BEGIN
	name := 'PL/pgSQL';
	RAISE NOTICE 'Hello %!', name;
END $$;

在这里插入图片描述

以上是一个匿名块，与此相对的是命名块（也就是存储过程和函数）。其中，DO 语句用于执行匿名块；我们定义了一个字符串变量 name，然后给它赋值并输出一个信息；RAISE NOTICE用于输出通知消息。

$$用于替换单引号（'），因为 PL/pgSQL 代码主体必须是字符串文本，意味着代码中所有的单引号都必须转义（重复写两次）。对于上面的示例，需要写成以下形式：

DO
'DECLARE
	name text;
BEGIN
	name := ''PL/pgSQL'';
	RAISE NOTICE ''Hello %!'', name;
END ';

显然这种写法很不方便，因此PL/pgSQL 提供了$ $避免单引号问题。我们经常还会遇到其他形式的符号，例如$ function $或者$ procedure$，作用也是一样。

2.1、嵌套子块

PL/pgSQL 支持代码块的嵌套，也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代码块被称为子块（subblock），包含子块的代码块被称为外部块（subblock）。子块可以将代码进行逻辑上的拆分，子块中可以定义与外部块重名的变量，而且在子块内拥有更高的优先级。例如：

DO $$
<<outer_block>>
DECLARE
	name text;
BEGIN
	name := 'outer_block';
	RAISE NOTICE 'This is %', name;
	DECLARE
		name text := 'sub_block';
	BEGIN
		RAISE NOTICE 'This is %', name;
		RAISE NOTICE 'The name FROM the outer block is %', outer_block.name;
	END;
	RAISE NOTICE 'This is %', name;
END outer_block $$;

首先，外部块中定义了一个变量 name，值为“outer_block”，输出该变量的值；然后在子块中定义了同名的变量，值为“sub_block”，输出该变量的值，并且通过代码块标签输出了外部块的变量值；最后再次输出该变量的值。以上代码执行的输出结果如下：

NOTICE:  This is outer_block
NOTICE:  This is sub_block
NOTICE:  The name FROM the outer block is outer_block
NOTICE:  This is outer_block

三、声明与赋值

3.1、变量

变量是一个有意义的名字，代表了内存中的某个位置。变量总是属于某个数据类型，变量的值可以在运行时被修改。

在使用变量之前，需要在代码的声明部分进行声明：

variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

其中，variable_name 是变量的名称，通常需要指定一个有意义的名称；data_type 是变量的类型，可以是任何 SQL 数据类型；如果指定了 NOT NULL，必须使用后面的表达式为变量指定初始值。

-- 示例
user_id integer;
quantity numeric(5) DEFAULT 0;
url varchar := 'http://mysite.com';

除了基本的SQL 数据类型之外， PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量：

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
amount quantity%TYPE;

myrow 是一个行类型的变量，可以存储查询语句返回的数据行（数据行的结构要和tablename相同）；myfield 的数据类型取决于 tablename.columnname 字段的定义；amount 和quantity 的类型一致。

与行类型变量类似的还有记录类型变量，例如：

arow RECORD;

记录类型的变量没有预定义的结构，只有当变量被赋值时才确定，而且可以在运行时被改变。

记录类型的变量可以用于任意查询语句或者FOR 循环变量。
除此之外，PL/pgSQL 还可以使用 ALIAS 定义一个变量别名：

newname ALIAS FOR oldname;

此时，newname 和oldname 代表了相同的对象。

3.2、常量

如果在定义变量时指定了 CONSTANT 关键字，意味着定义的是常量。常量的值需要在声明时初始化，并且不能修改。
以下示例通过定义常量PI 计算圆的面积：

DO $$
DECLARE
	PI CONSTANT NUMERIC := 3.14159265;
	radius NUMERIC;
BEGIN
	radius := 1.0;
	RAISE NOTICE 'The area is %', PI * radius * radius;
END $$;


NOTICE:  The area is 3.1415926500

常量可以用于避免魔数（magic number），提高代码的可读性；也可以减少代码的维护工作，所有使用常量的代码都会随着常量值的修改而同步，不需要修改多个硬编码的数据值。

四、控制结构

4.1、IF语句

IF 语句可以基于条件选择性执行操作， PL/pgSQL 提供了三种形式的 IF 语句。

IF … THEN … END IF
IF … THEN … ELSE … END IF
IF … THEN … ELSIF … THEN … ELSE … END IF

-- 最简单的 IF 语句如下：
IF boolean-expression THEN
	statements
END IF;

如果表达式 boolean-expression 的值为真，执行 THEN 之后的语句；否则，忽略这些语句。例如：

DO $$
BEGIN
	IF 2 > 3 THEN
		RAISE NOTICE '2 大于 3';
	END IF;
	IF 2 < 3 THEN
		RAISE NOTICE '2 小于 3';
	END IF;
END $$;


NOTICE:  2 小于 3

第二种 IF 语句的语法如下：

IF boolean-expression THEN
	statements
ELSE
	other-statements
END IF;

如果表达式 boolean-expression 的值为真，执行 THEN 之后的语句；否则，执行 ELSE 之后的语句。例如：

DO $$
BEGIN
	IF 2 > 3 THEN
		RAISE NOTICE '2 大于 3';
	ELSE
		RAISE NOTICE '2 小于 3';
	END IF;
END $$;

NOTICE:  2 小于 3

第三种 IF 语句支持多个条件分支：

IF boolean-expression THEN
	statements
[ ELSIF boolean-expression THEN
	statements ]
[ ELSIF boolean-expression THEN
	statements ]
	...
[ ELSE
	statements ]
END IF;

依次判断条件中的表达式，如果某个条件为真，执行相应的语句；如果所有条件都为假，执行 ELSE 后面的语句；如果没有 ELSE 就什么都不执行。例如：

DO $$
DECLARE
	i integer := 3;
	j integer := 3;
BEGIN
	IF i > j THEN
		RAISE NOTICE 'i 大于 j';
	ELSIF i < j THEN
		RAISE NOTICE 'i 小于 j';
	ELSE
		RAISE NOTICE 'i 等于 j';
	END IF;
END $$;

NOTICE:  i 等于 j

4.2、CASE语句

除了IF 语句之外，PostgreSQL 还提供了 CASE 语句，同样可以根据不同的条件执行不同的分支语句。CASE 语句分为两种：简单CASE 语句和搜索 CASE 语句。

简单CASE 语句的结构如下：

CASE search-expression
WHEN expression [, expression [ ... ]] THEN
statements
[ WHEN expression [, expression [ ... ]] THEN
statements
... ]
[ ELSE
statements ]
END CASE;

首先，计算 search-expression 的值；然后依次和 WHEN 中的表达式进行等值比较；如果找到了相等的值，执行相应的 statements；后续的分支不再进行判断；如果没有匹配的值，执行 ELSE语句；如果此时没有 ELSE，将会抛出 CASE_NOT_FOUND 异常。

DO $$
DECLARE
	i integer := 3;
BEGIN
	CASE i
		WHEN 1, 2 THEN
			RAISE NOTICE 'one or two';
		WHEN 3, 4 THEN
			RAISE NOTICE 'three or four';
		ELSE
			RAISE NOTICE 'other value';
	END CASE;
END $$;


NOTICE:  three or four

简单CASE 语句只能进行简单的等值比较，搜索CASE 语句可以实现更复杂的控制逻辑：

CASE
	WHEN boolean-expression THEN
		statements
	[ WHEN boolean-expression THEN
		statements
	... ]
	[ ELSE
		statements ]
END CASE;

依次判断每个 WHEN 之后的表达式，如果为真则执行相应的语句；后续的分支不再进行判断；如果没有匹配的值，执行 ELSE 语句；如果此时没有 ELSE，将会抛出CASE_NOT_FOUND异常。例如：

DO $$
DECLARE
	i integer := 3;
BEGIN
	CASE
		WHEN i BETWEEN 0 AND 10 THEN
			RAISE NOTICE 'value is between zero and ten';
		WHEN i BETWEEN 11 AND 20 THEN
			RAISE NOTICE 'value is between eleven and twenty';
		ELSE
			RAISE NOTICE 'other value';
	END CASE;
END $$;


NOTICE:  value is between zero and ten

4.3、循环语句

PostgreSQL 提供 4 种循环执行命令的语句：LOOP、WHILE、FOR 和FOREACH 循环，以及循环控制的 EXIT 和 CONTINUE 语句。

首先，LOOP 用于定义一个无限循环语句：

[ <<label>> ]
LOOP
	statements
END LOOP [ label ];

一般需要使用 EXIT 或者 RETURN 语句退出循环， label 可以用于 EXIT 或者 CONTINUE 语句退出或者跳到执行的嵌套循环中。例如：

DO $$
DECLARE
	i integer := 0;
BEGIN
	LOOP
		EXIT WHEN i = 5;
		i := i + 1;
		RAISE NOTICE 'Loop: %', i;
	END LOOP;
END $$;


NOTICE:  Loop: 1
NOTICE:  Loop: 2
NOTICE:  Loop: 3
NOTICE:  Loop: 4
NOTICE:  Loop: 5

其中，EXIT 语句用于退出循环。完整的 EXIT 语句如下：

EXIT [ label ] [ WHEN boolean-expression ];

另一个控制循环的语句是 CONTINUE：

CONTINUE [ label ] [ WHEN boolean-expression ];

CONTINUE 表示忽略后面的语句，直接进入下一次循环。例如：

DO $$
DECLARE
	i integer := 0;
BEGIN
	LOOP
		EXIT WHEN i = 10;
		i := i + 1;
		CONTINUE WHEN mod(i, 2) = 1;
		RAISE NOTICE 'Loop: %', i;
	END LOOP;
END $$;


NOTICE:  Loop: 2
NOTICE:  Loop: 4
NOTICE:  Loop: 6
NOTICE:  Loop: 8
NOTICE:  Loop: 10

当变量 i 为奇数时，直接进入下一次循环，不会打印出变量的值。

WHILE 循环的语法如下：

[ <<label>> ]
WHILE boolean-expression LOOP
	statements
END LOOP [ label ];

当表达式 boolean-expression 的值为真时，循环执行其中的语句；然后重新计算表达式的值，当表达式的值假时退出循环。例如：

DO $$
DECLARE
	i integer := 0;
BEGIN
	WHILE i < 5 LOOP
		i := i + 1;
		RAISE NOTICE 'Loop: %', i;
	END LOOP;
END $$;


NOTICE:  Loop: 1
NOTICE:  Loop: 2
NOTICE:  Loop: 3
NOTICE:  Loop: 4
NOTICE:  Loop: 5

FOR 循环可以用于遍历一个整数范围或者查询结果集，遍历整数范围的语法如下：

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
	statements
END LOOP [ label ];

FOR 循环默认从小到大进行遍历， REVERSE 表示从大到小遍历； BY 用于指定每次的增量，默认为 1。例如：

DO $$
BEGIN
	FOR i IN 1..5 BY 2 LOOP
		RAISE NOTICE 'Loop: %', i;
	END LOOP;
END $$;


NOTICE:  Loop: 1
NOTICE:  Loop: 3
NOTICE:  Loop: 5

变量i 不需要提前定义，可以在FOR 循环内部使用。
遍历查询结果集的 FOR 循环如下：

[ <<label>> ]
FOR target IN query LOOP
	statements
END LOOP [ label ];

其中，target 可以是一个RECORD 变量、行变量或者逗号分隔的标量列表。在循环中，target代表了每次遍历的行数据。例如：

DO $$
DECLARE
	emp record;
BEGIN
	FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP
		RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name;
	END LOOP;
END $$;


NOTICE:  Loop: Steven,King
NOTICE:  Loop: Neena,Kochar
NOTICE:  Loop: Lex,De Haan
NOTICE:  Loop: Alexander,Hunold
NOTICE:  Loop: Bruce,Ernst

FOREACH 循环与FOR 循环类似，只不过变量的是一个数组：

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
	statements
END LOOP [ label ];

如果没有指定SLICE 或者指定SLICE 0，FOREACH 将会变量数组中的每个元素。例如：

DO $$
DECLARE
	x int;
BEGIN
	FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
	LOOP
		RAISE NOTICE 'x = %', x;
	END LOOP;
END $$;


NOTICE:  x = 1
NOTICE:  x = 2
NOTICE:  x = 3
NOTICE:  x = 4
NOTICE:  x = 5
NOTICE:  x = 6

如果指定了一个正整数的 SLICE，FOREACH 将会变量数组的切片；SLICE 不能大于数组的维度。例如：

DO $$
DECLARE
	x int[];
BEGIN
	FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]])
	LOOP
		RAISE NOTICE 'row = %', x;
	END LOOP;
END $$;


NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}

以上示例通过FOREACH 语句遍历了数组的一维切片。

五、游标

PL/pgSQL 游标允许我们封装一个查询，然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录，避免内存溢出；另外，我们可以定义一个返回游标引用的函数，然后调用程序可以基于这个引用处理返回的结果集。

使用游标的步骤大体如下：

声明游标变量；
打开游标；
从游标中获取结果；
判断是否存在更多结果。如果存在，执行第3 步；否则，执行第5 步；
关闭游标。

我们直接通过一个示例演示使用游标的过程：

DO $$
DECLARE
	rec_emp RECORD;
	cur_emp CURSOR(p_deptid INTEGER) FOR
		SELECT first_name, last_name, hire_date
		FROM employees
		WHERE department_id = p_deptid;
	BEGIN
	-- 打开游标
		OPEN cur_emp(60);
		LOOP
		-- 获取游标中的记录
			FETCH cur_emp INTO rec_emp;
			-- 没有找到更多数据时退出循环
			EXIT WHEN NOT FOUND;
			RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name,
rec_emp.hire_date;
		END LOOP;
		-- Close the cursor
		CLOSE cur_emp;
END $$;


NOTICE:  Alexander,Hunold hired at:2006-01-03
NOTICE:  Bruce,Ernst hired at:2007-05-21
NOTICE:  David,Austin hired at:2005-06-25
NOTICE:  Valli,Pataballa hired at:2006-02-05
NOTICE:  Diana,Lorentz hired at:2007-02-07

首先，声明了一个游标 cur_emp，并且绑定了一个查询语句，通过一个参数 p_deptid 获取指定部门的员工；然后使用 OPEN 打开游标；接着在循环中使用 FETCH 语句获取游标中的记录，如果没有找到更多数据退出循环语句；变量 rec_emp 用于存储游标中的记录；最后使用 CLOSE语句关闭游标，释放资源。

六、错误处理

6.1、报告错误和信息

PL/pgSQL 提供了RAISE 语句，用于打印消息或者抛出错误：

RAISE level format;

不同的 level 代表了错误的不同严重级别，包括:

DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION

在上文示例中，我们经常使用NOTICE 输出一些信息。如果不指定level，默认为EXCEPTION，将会抛出异常并且终止代码运行。

format 是一个用于提供信息内容的字符串，可以使用百分号（%）占位符接收参数的值，两个连写的百分号（%%）表示输出百分号自身。

以下是一些RAISE 语句示例：

DO $$
BEGIN
	RAISE DEBUG 'This is a debug text.';
	RAISE INFO 'This is an information.';
	RAISE LOG 'This is a log.';
	RAISE WARNING 'This is a warning at %', now();
	RAISE NOTICE 'This is a notice %%';
END $$;


INFO:  This is an information.
WARNING:  This is a warning at 2025-09-09 18:01:34.971208+08
NOTICE:  This is a notice %

从结果可以看出，并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参数 client_min_messages 和log_min_messages 进行设置。

对于EXCEPTION 级别的错误，可以支持额外的选项：

RAISE [ EXCEPTION ] format USING option = expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];
RAISE [ EXCEPTION ] USING option = expression [, ... ];

其中，option 可以是以下选项：

MESSAGE，设置错误消息。如果 RAISE 语句中已经包含了 format 字符串，不能再使用该选项。
DETAIL，指定错误详细信息。
HINT，设置一个提示信息。
ERRCODE，指定一个错误码（SQLSTATE）。可以是文档中的条件名称或者五个字符组成的 SQLSTATE 代码。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相关对象的名称。

以下是一些示例：

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
RAISE division_by_zero;
RAISE SQLSTATE '22012';

6.2、检查断言

PL/pgSQL 提供了 ASSERT 语句，用于调试存储过程和函数：

ASSERT condition [ , message ];

其中，condition 是一个布尔表达式；如果它的结果为真，ASSERT 通过；如果结果为假或者NULL，将会抛出 ASSERT_FAILURE 异常。message 用于提供额外的错误信息，默认为“assertionfailed”。例如：

DO $$
DECLARE
	i integer := 1;
BEGIN
	ASSERT i = 0, 'i 的初始值应该为 0！';
END $$;


ERROR:  i 的初始值应该为 0！
CONTEXT:  PL/pgSQL function inline_code_block line 5 at ASSERT

注意，ASSERT 只适用于代码调试；输出错误信息使用RAISE 语句。

6.3、捕获异常

默认情况下，PL/pgSQL 遇到错误时会终止代码执行，同时撤销事务。我们也可以在代码块中使用 EXCEPTION 捕获错误并继续事务：

[ <<label>> ]
[ DECLARE
	declarations ]
BEGIN
	statements
EXCEPTION
	WHEN condition [ OR condition ... ] THEN
		handler_statements
	[ WHEN condition [ OR condition ... ] THEN
		handler_statements
	... ]
END;

如果代码执行出错，程序将会进入 EXCEPTION 模块；依次匹配 condition，找到第一个匹配的分支并执行相应的 handler_statements；如果没有找到任何匹配的分支，继续抛出错误。

以下是一个除零错误的示例：

DO $$
DECLARE
	i integer := 1;
BEGIN
	i := i / 0;
EXCEPTION
	WHEN division_by_zero THEN
		RAISE NOTICE '除零错误！';
	WHEN OTHERS THEN
		RAISE NOTICE '其他错误！';
END $$;


NOTICE:  除零错误！

OTHERS 用于捕获未指定的错误类型。

七、自定义函数

要创建一个自定义的PL/pgSQL 函数，可以使用CREATE FUNCTION 语句：

CREATE [ OR REPLACE ] FUNCTION
	name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ]
[, ...] ] )
	RETURNS rettype
AS $$
DECLARE
	declarations
BEGIN
	statements;
	...
END; $$
LANGUAGE plpgsql;

CREATE 表示创建函数， OR REPLACE 表示替换函数定义； name 是函数名；括号内是参数，多个参数使用逗号分隔；argmode 可以是 IN（输入）、OUT（输出）、INOUT（输入输出）或者 VARIADIC （数量可变），默认为 IN； argname 是参数名称； argtype 是参数的类型； default_expr是参数的默认值；rettype 是返回数据的类型；AS 后面是函数的定义，和上文中的匿名块相同；最后，LANGUAGE 指定函数实现的语言，也可以是其他过程语言。

以下示例创建一个函数 get_emp_count，用于返回指定部门中的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer)
RETURNS integer
AS $$
DECLARE
	ln_count integer;
BEGIN
	SELECT count(*) into ln_count
	FROM employees
	WHERE department_id = p_deptid;
	return ln_count;
END; $$
LANGUAGE plpgsql;

创建该函数之后，可以像内置函数一样在SQL 语句中进行调用：

SELECT department_id,department_name,get_emp_count(department_id)
FROM departments d;

在这里插入图片描述

PL/pgSQL 函数支持重载（Overloading），也就是相同的函数名具有不同的函数参数。例如，以下语句创建一个重载的函数 get_emp_count，返回指定部门指定日期之后入职的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date)
RETURNS integer
AS $$
DECLARE
	ln_count integer;
BEGIN
	SELECT count(*) into ln_count
	FROM employees
	WHERE department_id = p_deptid and hire_date >= p_hiredate;
	return ln_count;
END; $$
LANGUAGE plpgsql;

查询每个部门2005 年之后入职的员工数量：

SELECT department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01')
FROM departments d;

在这里插入图片描述

我们再来看一个 VARIADIC 参数的示例：

CREATE OR REPLACE FUNCTION sum_num(
	VARIADIC nums numeric[])
	RETURNS numeric
AS $$
DECLARE ln_total numeric;
BEGIN
	SELECT SUM(nums[i]) INTO ln_total
	FROM generate_subscripts(nums, 1) t(i);
	RETURN ln_total;
END; $$
LANGUAGE plpgsql;

参数nums 是一个数组，可以传入任意多个参数；然后计算它们的和值。例如：

SELECT sum_num(1,2), sum_num(1,2,3);

在这里插入图片描述

八、存储过程

PostgreSQL 11 增加了存储过程，使用 CREATE PROCEDURE 语句创建：

CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ]
[, ...] ] )
AS $$
DECLARE
	declarations
BEGIN
	statements;
	...
END; $$
LANGUAGE plpgsql;

存储过程的定义和函数主要的区别在于没有返回值，其他内容都类似。以下示例创建了一个存储过程 update_emp，用于修改员工的信息：

CREATE OR REPLACE PROCEDURE update_emp(
	p_empid in integer,
	p_salary in numeric,
	p_phone in varchar)
AS $$
BEGIN
	UPDATE employees
	set salary = p_salary, phone_number = p_phone
	WHERE employee_id = p_empid;
END; $$
LANGUAGE plpgsql;

调用存储过程使用 CALL 语句：

call update_emp(100, 25000, '515.123.4560')

8.1、事务管理

在存储过程内部，可以使用 COMMIT 或者ROLLBACK 语句提交或者回滚事务。例如：

create table test(a int);

CREATE PROCEDURE transaction_test()
LANGUAGE plpgsql
AS $$
BEGIN
	FOR i IN 0..9 LOOP
		INSERT INTO test (a) VALUES (i);
		IF i % 2 = 0 THEN
			COMMIT;
		ELSE
			ROLLBACK;
		END IF;
	END LOOP;
END
$$;

CALL transaction_test();

SELECT * FROM test;