PostgreSQL 教程: PL/pgSQL for 循环

八月 21, 2023

摘要:在本教程中,您将了解 PL/pgSQL 中的for循环语句,来迭代遍历一系列整数或查询结果集。

使用 PL/pgSQL for 循环迭代遍历一系列整数

下面说明了循环遍历一系列整数的for循环语句的语法:

[ <<label>> ]
for loop_counter in [ reverse ] from.. to [ by step ] loop
    statements
end loop [ label ];

在这个语法中:

  • 首先,for loop创建一个只能在循环内部访问的整数变量loop_counter。默认情况下,for loop在每次迭代后都会给loop_counter加上step数值。但是,当您使用reverse选项时,for loop会从loop_counter中减去step
  • 其次,fromto是指定范围下限和上限的表达式。for 循环在进入循环之前计算这些表达式。
  • 第三,关键字by后面的step指定迭代步长。默认为 1。for loop只计算step表达式一次。

下面的流程图说明了该for loop语句:

PL/pgSQL FOR loop

以下示例使用for循环语句遍历从 1 到 5 的五个数字,并在每次迭代中显示每个数字:

do $$
begin
  for counter in 1..5 loop
    raise notice 'counter: %', counter;
  end loop;
end $$;

输出:

NOTICE:  Counter: 1
NOTICE:  Counter: 2
NOTICE:  Counter: 3
NOTICE:  Counter: 4
NOTICE:  Counter: 5

以下示例遍历从 5 到 1 的 5 个数字,并显示每次迭代中的每个数字:

do $$
begin
   for counter in reverse 5..1 loop
      raise notice 'counter: %', counter;
   end loop;
end $$;

输出:

NOTICE:  Counter: 5
NOTICE:  Counter: 4
NOTICE:  Counter: 3
NOTICE:  Counter: 2
NOTICE:  Counter: 1

以下示例使用for loop语句遍历从 1 到 6 的六个数字。每次迭代后,它都会向计数器添加 2:

do $$
begin 
  for counter in 1..6 by 2 loop
    raise notice 'counter: %', counter;
  end loop;
end $$;

输出:

NOTICE:  Counter 1
NOTICE:  Counter 3
NOTICE:  Counter 5

使用 PL/pgSQL for 循环遍历结果集

以下语句显示如何使用for循环语句遍历查询的结果集:

[ <<label>> ]
for target in query loop
    statements
end loop [ label ];

以下语句使用for循环显示最长的 10 部电影的标题。

do $$
declare
    f record;
begin
    for f in select title, length
	       from film
	       order by length desc, title
	       limit 10
    loop
	    raise notice '%(% mins)', f.title, f.length;
    end loop;
end $$;
NOTICE:  Chicago North(185 mins)
NOTICE:  Control Anthem(185 mins)
NOTICE:  Darn Forrester(185 mins)
NOTICE:  Gangs Pride(185 mins)
NOTICE:  Home Pity(185 mins)
NOTICE:  Muscle Bright(185 mins)
NOTICE:  Pond Seattle(185 mins)
NOTICE:  Soldiers Evolution(185 mins)
NOTICE:  Sweet Brotherhood(185 mins)
NOTICE:  Worst Banger(185 mins)

使用 PL/pgSQL for 循环遍历动态查询的结果集

以下形式的for循环语句允许您执行动态查询并遍历其结果集:

[ <<label>> ]
for row in execute query_expression [ using query_param [, ... ] ] 
loop
    statements
end loop [ label ];

在这个语法中:

  • query_expression是一条 SQL 语句。
  • using子句用于将参数传递给查询。

下面的代码块显示了如何使用for循环语句来遍历动态查询的结果集。它有两个配置变量:

  • sort_type:值为 1 时按标题对电影进行排序,值为 2 时按发行年份对电影进行排序。
  • rec_count:是从film表中要查询的行数。我们将在for循环的using子句中使用它。

匿名块根据sort_type变量的值生成查询语句,并使用 for 循环遍历结果集的行。

do $$
declare
    -- sort by 1: title, 2: release year
    sort_type smallint := 1;
	-- return the number of films
	rec_count int := 10;
	-- use to iterate over the film
	rec record;
	-- dynamic query
    query text;
begin
	query := 'select title, release_year from film ';

	if sort_type = 1 then
		query := query || 'order by title';
	elsif sort_type = 2 then
	  query := query || 'order by release_year';
	else
	   raise 'invalid sort type %s', sort_type;
	end if;

	query := query || ' limit $1';

	for rec in execute query using rec_count
    loop
	    raise notice '% - %', rec.release_year, rec.title;
	end loop;
end $$;

输出:

NOTICE:  2006 - Academy Dinosaur
NOTICE:  2006 - Ace Goldfinger
NOTICE:  2006 - Adaptation Holes
NOTICE:  2006 - Affair Prejudice
NOTICE:  2006 - African Egg
NOTICE:  2006 - Agent Truman
NOTICE:  2006 - Airplane Sierra
NOTICE:  2006 - Airport Pollock
NOTICE:  2006 - Alabama Devil
NOTICE:  2006 - Aladdin Calendar

如果将sort_type的值更改为 2,您将得到以下输出:

NOTICE:  2006 - Grosse Wonderful
NOTICE:  2006 - Airport Pollock
NOTICE:  2006 - Bright Encounters
NOTICE:  2006 - Academy Dinosaur
NOTICE:  2006 - Ace Goldfinger
NOTICE:  2006 - Adaptation Holes
NOTICE:  2006 - Affair Prejudice
NOTICE:  2006 - African Egg
NOTICE:  2006 - Agent Truman
NOTICE:  2006 - Chamber Italian

在本教程中,您学习了 PL/pgSQL 中各种形式的 for 循环语句。