🏆 文章目标：帮助那些了解Oracle PL/SQL，但是不熟悉PL/pgSQL 的人，用于快速整改或者上手。
🍀 01-PostgreSQL 存储过程的基本介绍以及入门（基本结构、声明和赋值、控制结构）
✅ 创作者：Jay…
🎉 个人主页：Jay的个人主页
🍁 展望：若本篇点赞人数较多，将讲解Oracle PL/SQL 的使用方式，常见问题解答，以及一些编写规范。

概述

在 PostgreSQL 中，除了标准 SQL 语句之外，通过创建复杂的过程和函数来满足程序需要，我们称为存储过程（Stored Procedure）和自定义函数（User-Defined Function）。它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。

PL/pgSQL 简单易学，无论是否具有编程基础都能够很快学会。PL/pgSQL 存储过程，它和 Oracle PL/SQL 非常类似，是 PostgreSQL默认支持的存储过程，下面针对优缺点给大家做了简要分析：

优点

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

缺点

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

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$，作用也是一样。
运行结果如下：

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

NOTICE:  Hello PL/pgSQL!

嵌套子块

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

声明与赋值

与其他编程语言类似，PL/pgSQL 支持定义变量和常量。

变量

变量是一个有意义的名字，代表了内存中的某个位置。变量总是属于某个数据类型，变量的值可以在运行时被修改。
在使用变量之前，需要在代码的声明部分进行声明：

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 代表了相同的对象。

常量

如果在定义变量时指定了 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 语句遍历了数组的一维切片。