plpgsql系列教程(5)-条件语句与循环语句_pgsql循环语句for

本文由 @小刘先森 原创，转载请注明出处。

plpgsql系列文章，方便小伙伴们学习。

PL/pgSQL从入门到放弃(1)-入门
PL/pgSQL从入门到放弃(2)-变量定义与数据类型
PL/pgSQL从入门到放弃(3)-函数
PL/pgSQL从入门到放弃(4.1)-函数返回常用数据类型——组合数据类型
PL/pgSQL从入门到放弃(4.2)-函数返回常用数据类型——行数据类型
PL/pgSQL从入门到放弃(4.3)-函数返回常用数据类型——记录数据类型
PL/pgSQL从入门到放弃(4.4)-函数返回常用数据类型——复制数据类型
PL/pgSQL从入门到放弃(5)-条件语句与循环语句
PL/pgSQL从入门到放弃(6)-游标

条件语句

PL/pgSQL中有两种条件语句分别为if与case语句。

if

if语句形式包含以下几种：

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

示例
示例函数 test_if，将下方示例语句复制到对应位置即可进行测试。

create or replace function test_if(i int) returns void as $$
DECLARE
BEGIN
	-- 替换对应if语句
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6

IF … THEN … END IF

该示例当输入值i大于10时会打印i的值为：%

if i > 10 then
	raise notice 'i的值为：%', i;
end if;
1
2
3

IF … THEN … ELSE … END IF

该示例当输入值i大于10时会打印i的值大于10否则会打印i的值小于等于10

if i > 10 then
	raise notice 'i的值大于10';
else 
	raise notice 'i的值小于等于10';
end if;
1
2
3
4
5

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

该示例当输入值i大于10时会打印i的值大于10，当输入值为5时会打印'i的值为5'，否则会打印i的值小于等于10

if i > 10  then
	raise notice 'i的值大于10';
elsif i = 5 then
	raise notice 'i的值为5';
else 
	raise notice 'i的值小于等于10';
end if;
1
2
3
4
5
6
7

小提示

• 关键词ELSIF也可以写成ELSEIF。
• elsif可以写多个

case

CASE ... WHEN ... THEN ... ELSE ... END CASE

CASE WHEN ... THEN ... ELSE ... END CASE
1
2
3

示例
示例函数 test_case，将下方示例语句复制到对应位置即可进行测试。

create or replace function test_case(i int) returns void as $$
DECLARE
BEGIN
	-- 替换对应case语句
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6

CASE … WHEN … THEN … ELSE … END CASE

该示例当输入值i为1时会打印i的值为1，当输入值为2时会打印'i的值为2'，否则会打印i的值既不为1也不为2

case i
	when 1,3 then
		raise notice 'i的值为1';
	when 2 then
		raise notice 'i的值为2';
	else
		raise notice 'i的值既不为1也不为2';
end case;
1
2
3
4
5
6
7
8

小提示

• 如果没有找到匹配，ELSE 语句会被执行
• 如果ELSE不存在，将会抛出一个CASE_NOT_FOUND异常

CASE WHEN … THEN … ELSE … END CASE

case when是基于布尔表达式真假来执行

该示例当输入值i为0-10之间时会打印i的值在0-10之间，当输入值为11-20之间时会打印i的值在11-20之间'，否则会打印i的值不在0-20之间

CASE
    WHEN i BETWEEN 0 AND 10 THEN
        raise notice 'i的值在0-10之间';
    WHEN i BETWEEN 11 AND 20 THEN
        raise notice 'i的值在11-20之间';
     else 
     	raise notice 'i的值不在0-20之间';
END CASE;
1
2
3
4
5
6
7
8

小提示

• 如果没有找到匹配，ELSE 语句会被执行
• 如果ELSE不存在，将会抛出一个CASE_NOT_FOUND异常

循环语句

LOOP循环

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];
1
2
3
4

LOOP定义一个无条件的循环，它会无限重复直到被EXIT或RETURN语句终止。可选的label可以被EXIT和CONTINUE语句用在嵌套循环中指定这些语句引用的是哪一层循环。
示例
示例函数 test_loop，将下方示例语句复制到对应位置即可进行测试。

create or replace function test_loop(i int) returns int as $$
DECLARE
BEGIN
	-- 替换对应loop语句
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6

exit 退出

该示例首先对i的值加1，如果i的值大于10则退出循环，执行return语句返回结果。输入i的值为0时，返回结果11，输入i的值为20时，返回结果21。

LOOP
    -- 一些计算
    i = i + 1;
    IF i > 10 THEN
        EXIT;  -- 退出循环
    END IF;
END LOOP;
return i;
1
2
3
4
5
6
7
8

exit when 退出

该示例与上方示例效果一样。

LOOP
    -- 一些计算
    i = i + 1;
    EXIT WHEN i > 10;  -- 和前一个例子相同的结果
END LOOP;
return i;
1
2
3
4
5
6

exit [lable]

该示例执行select test_loop(0)时输出结果为11，而不是100。当exit指定退出标签时，会退出整个块语句继续执行，以下示例退出twoblock，执行return语句，所以该值为11；

create or replace function test_loop(i int) returns int as $$
<<oneblock>>
DECLARE

BEGIN
	<<twoblock>>
	DECLARE
	BEGIN
		<<threeblock>>
		DECLARE
		begin
			LOOP
			-- 一些计算
				i = i + 1;
				IF i > 10 THEN
						EXIT twoblock;  -- 退出循环
				END IF;
			END LOOP;
		END;
		i = 100;
	END;
   
return i;
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

continue

CONTINUE [ label ] [ WHEN boolean-expression ];
1

该示例会打印输出i的值，其中当i的值为5时，不会打印。

LOOP
	i = i + 1;
	EXIT WHEN i > 10;
   	CONTINUE WHEN i = 5;
	raise notice 'i的值为：%',i;
END LOOP;
1
2
3
4
5
6

小提示

• CONTINUE可以被用在所有类型的循环中，它并不限于在LOOP中使用。

WHILE循环

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];
1
2
3
4

示例
示例函数 test_，将下方示例语句复制到对应位置即可进行测试，下方所有循环都可以使用此函数测试。

create or replace function test_(i int) returns int as $$
DECLARE
BEGIN
	-- 替换对应循环语句
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6

该示例输入值为0，判断i的值是否小于10，小于10则执行+1，否则return。

WHILE i < 10 LOOP
    i = i + 1;
END LOOP;
return i;
1
2
3
4

FOR循环

这种形式的FOR会创建一个在一个整数范围上迭代的循环。变量name会自动定义为类型integer并且只在循环内存在（任何该变量名的现有定义在此循环内都将被忽略）。给出范围上下界的两个表达式在进入循环的时候计算一次。如果没有指定BY子句，迭代步长为 1，否则步长是BY中指定的值，该值也只在循环进入时计算一次。如果指定了REVERSE，那么在每次迭代后步长值会被减除而不是增加。

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

示例

FOR i IN 1..10 LOOP
    -- 我在循环中将取值 1,2,3,4,5,6,7,8,9,10 
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- 我在循环中将取值 10,9,8,7,6,5,4,3,2,1 
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- 我在循环中将取值 10,8,6,4,2 
END LOOP;
1
2
3
4
5
6
7
8
9
10
11

查询结果循环（FOR…IN…）

FOR…IN

通过一个查询的结果进行迭代并且操纵相应的数据。语法是：

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];
1
2
3
4

target是一个记录变量、行变量或者逗号分隔的标量变量列表。target被连续不断被赋予来自query的每一行，并且循环体将为每一行执行一次。下面是一个例子：

create or replace function test_for_in() returns int as $$
DECLARE
	cddm record;
BEGIN
		RAISE NOTICE 'reading jcb_cddm...';

    FOR cddm IN SELECT * FROM jcb_cddm limit 5 LOOP
        RAISE NOTICE '场地代码为：%，场地名称为 %', cddm.dm, quote_ident(cddm.mc);
    END LOOP;

return 1;
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13

FOR…IN…EXECUTE

FOR-IN-EXECUTE语句是在行上迭代的另一种方式,示例如下：
该示例将代码作为参数传入，使用using动态替换。

create or replace function test_for_in(dm varchar) returns int as $$
DECLARE
	cddm record;
BEGIN
		RAISE NOTICE 'reading jcb_cddm...';

    FOR cddm IN execute 'SELECT * FROM jcb_cddm where dm = $1 limit 5' using dm LOOP
        RAISE NOTICE '场地代码为：%，场地名称为 %', cddm.dm, quote_ident(cddm.mc);
    END LOOP;

return 1;
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13

数组循环（FOREACH）

FOREACH语法结构如下：

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

示例

• 不使用slice
  该示例使用select test_sum(array[1,2,3])语句测试会返回和为6

CREATE FUNCTION test_sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12

• slice示例
  通过一个正SLICE值，FOREACH通过数组的切片而不是单一元素迭代。SLICE值必须是一个不大于数组维度数的整数常量。target变量必须是一个数组，并且它接收数组值的连续切片，其中每一个切片都有SLICE指定的维度数。这里是一个通过一维切片迭代的例子：

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10

关于作者

大家有需要介绍哪些部分，或者一些想法欢迎联系我。

GISer
QQ: 1016817543
邮箱：1016817543@qq.com
github：https://github.com/MrSmallLiu (欢迎star)

相关链接

以下为本人参与开发的一些库，欢迎各位Star、Issues、PR

• 基于PostgreSQL和PostGIS的坐标转换函数，支持点、线、面的WGS84和CGCS2000与GCJ02和BD09坐标系与之间互转
• 统一社会信用代码相关的PostgreSQL函数，包括验证、随机生成等