01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(下)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)

01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(上)+https://developer.aliyun.com/article/1492279

常量

如果在定义变量时指定了 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),提高代码的可读性;也可以减少代码的维护工作,所有使用常量的代码都会随着常量值的修改而同步,不需要修改多个硬编码的数据值。

控制结构

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
DO

CASE 语句

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

⚠️CASE 语句和第 15 篇中介绍的 CASE 表达式不是一个概念,CASE 表达式是一个 SQL 表达式。

简单 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 $$;

搜索 CASE 表达式可以构造任意复杂的判断逻辑,实现 IF 语句的各种功能。

循环语句

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,Kochhar
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 语句遍历了数组的一维切片。

关注公众号:熊猫Jay字节之旅,了解更多 AI 技巧 ~

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
6月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL从入门到精通 - 第40讲:数据库不完全恢复
PostgreSQL从入门到精通 - 第40讲:数据库不完全恢复
208 1
|
3月前
|
SQL 存储 关系型数据库
新手如何入门学习PostgreSQL?
新手如何入门学习PostgreSQL?
|
5月前
|
存储 关系型数据库 MySQL
Mysql存储过程查询结果赋值到变量
Mysql存储过程查询结果赋值到变量
151 0
|
6月前
|
关系型数据库 数据库 PostgreSQL
|
6月前
|
SQL 人工智能 Oracle
PostgreSQL 递归查询(含层级和结构)
PostgreSQL 递归查询(含层级和结构)
|
6月前
|
存储 SQL 关系型数据库
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(上)
01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)
|
6月前
|
Oracle 关系型数据库 数据库
PostgreSQL从入门到精通教程 - 第42讲:pg_rman部署与使用
PostgreSQL从入门到精通教程 - 第42讲:pg_rman部署与使用
172 1
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
363 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
419 0