基于开发者空间OpenGauss数据库的PLPGSQL实践一

1 概述

1.1 案例介绍

PLPGSQL是一种程序语言，叫做过程化SQL语言(Procedural Language/Postgres SQL)，pl/pgsql是PostgreSQL数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编译语言的特点，所以pl/pgsql就是把数据操作和查询语句组织在pl/pgsql代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

通过实际操作，让大家深入了解如何利用 PLPGSQL 开发并部署一个函数功能模块。在这个过程中，大家将学习到从函数创建、数据批量读取到SQL程序编写以及与触发器集成等一系列关键步骤，从而掌握 PLPGSQL 的基本使用方法，体验其在应用开发中的优势。

1.2 适用对象

• 企业
• 个人开发者
• 高校学生

1.3 案例时间

本案例总时长预计60分钟。

1.4 案例流程

说明：

1. 领取空间开发桌面；
2. 在空间开发桌面终端进入OpenGaussDB；
3. 进行数据库的开发者空间进行OpenGaussDB之PL/pgSQL的操作；

1.5 资源总览

资源名称	规格	单价(元)	时长(分钟)
开发者空间-云主机	鲲鹏通用计算增强型 kC2 | 4vCPUs | 8G | Euler	免费	60

最新案例动态，请查阅 《基于开发者空间OpenGauss数据库的PLPGSQL实践一》。小伙伴快来领取华为开发者空间进行实操体验吧！

2 OpenGauss数据库PL/pgSQL实践

2.1 开发者空间配置

面向广大开发者群体，华为开发者空间提供一个随时访问的“开发桌面云主机”、丰富的“预配置工具集合”和灵活使用的“场景化资源池”，开发者开箱即用，快速体验华为根技术和资源。

领取云主机后可以直接进入 华为开发者空间工作台界面，点击打开云主机 \> 进入桌面连接云主机。

2.2 启动OpenGaussDB实例并登录

本案例中，使用OpenGaussDB开发平台，完成SQL的编程和自定义函数等多种功能。

基于之前案例《 基于开发者空间部署OpenGauss主备集中式数据库系统》。在云主机部署OpenGaussDB实例。并启动数据库服务。

进入OpenGaussDB的安装目录的bin文件，该案例云主机环境中安装目录在环境变量\$GAUSSHOME中，读者根据自己云主机安装目录进行操作修改。

登录后复制

cd $GAUSSHOME/bin

• 1.

初始化数据库实例，初始化数据库目录在当前目录下data，设置节点名称和初始化用户密码。如下所示

登录后复制

./gs_initdb -D data --nodename=n1 -w GaussDB@123

• 1.

以单节点模式启动数据库实例，并在当前目录下输出日志文件logfile

登录后复制

./gs_ctl start -D data -Z single_node -l logfile

• 1.

用gsql客户端工具，进入OpenGaussDB数据库。参数 -a表示追加、-r表示使用readline

登录后复制

./gsql -d postgres -ar

• 1.

3 PL/PGSQL实践

PLPGSQL是数据库的编程语言。相当于在数据库中用SQL语言进行逻辑处理与代码开发。可以把业务系统中封装的功能模块下沉到数据库端实现，以达到减轻业务系统的逻辑压力、降低架构复杂度和简化业务系统实现难度。

PLPGSQL是一种块结构型语言，例如匿名块，存储过程和函数体的完整文本必须是块。块定义如下：

登录后复制

[ <<标签>> ]
[ 声明
变量;
变量 := 赋值 ]
BEGIN
    SQL QUERY
END [ 标签];

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

PLPGSQL定义的功能模块(存储过程和函数)可以互相嵌套。例如SQL块中嵌套子SQL块，存储过程引用PLPGSQL定义的其他函数和模块功能。

3.1 变量赋值与引用

3.1.1 变更声明与赋值

SQL块中所使用的所有变量都必须在plpgsql定义body的开头，用关键字 DECLARE 声明。

变量声明的语法

登录后复制

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

• 1.

例如:

登录后复制

DECLARE
var1 TEXT;
var2 INTEGER := 10;
url VARCHAR;
quantity NUMERIC(5);
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
CURSOR c1 IN SELECT col1, col2 FROM table_name WHER 谓词过滤条件;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

以上的示例为声明块(DECLARE)， := 为PL/SQL中的等号赋值。

登录后复制

DECLARE
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
transaction_time CONSTANT timestamp with time zone := now();
tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

变量一旦声明，变量的值就可以在同一SQL块中后续初始化表达式中被使用，例如：

登录后复制

DECLARE
  x integer := 1;
  y integer := x + 1;

• 1.
• 2.
• 3.

3.1.2 变量声明之Function Parameters

引用变量不需要声明，变量引用主要用于函数参数引用。传递给函数的参数使用标识符 \$1、\$2 等命名，也可以为 \$n 参数名声明别名，以增加可读性。然后，可以使用别名或数字标识符来引用参数值。

有两种方法可以创建别名。首选的方法是在 CREATE FUNCTION 命令中为参数指定一个名称，例如：

登录后复制

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.

另一种方法是显式声明别名，使用声明语法

登录后复制

name ALIAS FOR $n;

• 1.

此样式中的相同示例如下所示：

登录后复制

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

注解：

下面两个用例不完全等价。在第一个用例情况下，参数可以引用为 int_t.sales_tax，但在第二个用例下，它不能引用(除非在内部块附加一个标签，参数可以使用该标签来替代)。

下面用例参数int_t的类型sometablename是当前表的表名。由于该函数中使用了表字段的f1,f3,f5,f7四列，所以这块根据实际引用表结构而对应的改变引用字段名。

登录后复制

CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.

登录后复制

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

当使用输出参数声明PL/PGSQL函数时，与正常输入参数相同的方式为输出参数提供 \$n 名称和可选别名。输出参数实际上是一个以NULL开关的变量；它应该在函数执行期间赋值。参数的最终值是返回值。例如上面的第一个示例也可以用下面的方式实现。

登录后复制

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.

创建函数前确保该函数不存在，否则同schema下同名函数冲突。

注意：此处省略了返回值 RETURN real 。

要调用具有OUT参数的函数，在函数调用中省略输出参数(s)。

登录后复制

SELECT sales_tax(100.00);

• 1.

在返回多个值时，输出参数非常有用。如下：

登录后复制

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM sum_n_product(2, 4);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

这种写法有效地创建了函数结果的匿名记录类型。如果给出 RETURN 子句，则必须是RETURN RECORD。

如果把上面的函数 sum_n_product 改写成存储过程，如下：

登录后复制

CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS 
BEGIN
    sum := x + y;
    prod := x * y;
END;

• 1.
• 2.
• 3.
• 4.
• 5.

在对存储过程的调用中，必须指定所有参数。对于输出参数，从普通SQL调用过程时可以指定NULL：

登录后复制

CALL sum_n_product(2, 4, NULL, NULL);

• 1.

截图运行结果

但是，当从PL/PGSQL调用存储过程时，应该为任何输出参数编写一个变量；该变量将接收调用的结果。PL/PGSQL函数的另一种方法是声明返回类型 RETURNS TABLE。例如：

登录后复制

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

该方式完全等价于声明一个或多个OUT参数并指定 RETURNS SETOF 类型。

当PL/PGSQL函数的返回类型声明为多态类型时将创建一个特殊参数 \$0 。它的数据类型根据实际输入类型推断出函数的返回类型。\$0 被初始化为NULL，并且可以被函数修改，因此它也可以用来保存返回值。\$0 也可作别名。例如下面函数适用于 + 运算符的任何数据类型：

登录后复制

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

通过将一个或多个输出参数声明为多态类型，可以获得相同的效果。在该情况下，不使用特殊的 \$0 参数，输出参数本身具备相同的结果，例如：

登录后复制

DROP FUNCTION IF EXISTS add_three_values;
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

anycompatible是PostgreSQL特性，OpenGaussDB暂时没有移植该功能。故下面anycompatible类型只了解即可。

在实践中，声明多态函数中使用任何兼容的数据类型会非常有效，以便自动将输入参数提升为一个常见的公共类型。例如：

登录后复制

CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
RETURNS anycompatible AS $$
BEGIN
    RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

上面用例引用方法如下：

登录后复制

SELECT add_three_values(1, 2, 4.7);

• 1.

该函数调用，会自动将参数值1, 2从integer整型转换成numeric小数型。

注：多态类型有11种，anyelement只是其中一个，该内容属于数据类型。由于数据类型较为简单不做过多说明，读者自学数据类型。

3.1.3 变量声明之ALIAS

该内容主要用于触发器的实现。因为触发器有涉及到Update和Delete等DML。而数据的Update和Delete会涉及到新元组(new tuple)和旧元组(old tuple)。比如Delete的谓词条件Where语句中column = old.tuple。

语法如下：

登录后复制

newname ALIAS FOR oldname;

• 1.

例如：

登录后复制

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

• 1.
• 2.
• 3.

注意：由于ALIAS创建了两种不同的方式来命名同一个对象，因此不受限制的使用可能会引起困惑。最好仅用于覆盖预定名称。

3.1.4 变量声明之Copying Types

语法如下：

登录后复制

name table.column%TYPE
name variable%TYPE

• 1.
• 2.

%TYPE提供表字段或者先前声明PL/PGSQL变量的数据类型，可以声明在数据库中已经存在的变量类型。例如：

变量与数据库中表 users 的字段 user_id 是相同的数据类型，则PL/PGSQL在声明variable时，可以用如下写法：

登录后复制

declare
  variable users.user_id%TYPE;

• 1.
• 2.

还可以在%TYPE之后写入数组修饰，创建一个保存引用类型的数组变量：

登录后复制

declare
  variables users.user_id%TYPE[];
  variables users.user_id%TYPE ARRAY[4];  -- equivalent to the above

• 1.
• 2.
• 3.

正如在声明表字段为数组时，无论编写多个括号还是特定的数组维度并不重要：OpenGaussDB将给定元素类型的所有数组为相同类型，而不考虑维度。

重点

通过使用**%TYPE**，不需要知道被引用的结构中的数据类型。如果被引用项的数据类型在将来发生变化(例如：将user_id的类型从整数更改为实数)，也不需要改变函数定义。

%TYPE 在多态函数中特别有用，因为内部变量所需的数据类型在一次调用到下一次调用时可能发生变化。可以通过将**%TYPE**应用于函数的参数或结果占位符来创建适当的变量。

表结构中使用数组类型时，则方法如下(其写法不同于PL/PGSQL变量声明)：

登录后复制

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
CREATE TABLE tictactoe (
    pay_by_quarter integer ARRAY,
    sales integer ARRAY[4],
    squares integer[3][3]
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

读者自学数组类型的字段写入，数组访问，此处不作过度讲解，自行学习。

3.1.5 变量声明之Row Types

语法如下

登录后复制

name table_name%ROWTYPE;
name composite_type_name;

• 1.
• 2.

复合类型的变量被称为行变量或者行类型变量。只要 SELECT 或 FOR 查询的列集合与变量声明的类型相匹配中，则该变量就能存储该查询的整行结果集(元组Tuple)。通常使用小数点表示访问元组的各个字段(例如：rowvar.field)。

使用 table_name%ROWTYPE 表示将 ROWTYPE 类型的变量声明为现有表或视图的行具有相同的类型，也可通过给出复合类型的类型名来声明 ROWTYPE 变量。由于每个表关系都有一个同名的关联复合类型，因此在 OpenGaussDB 中，是否写 %ROWTYPE 其实不重要。但是使用 %ROWTYPE 的表关系更具有可移植性。

%ROWTYPE 与 %TYPE 一样，后面可以跟数组修饰符来声明一个变量，该变量保存引用复合类型的数组。

函数的参数可以定义成复合类型(表完整的行)。在该情况下，对应的标识符 \$n 将是行变量，可以从中选择字段，例如 \$1.usr_id 。

复合类型的示例如下所示：Table1 和 Table2 是至少具有上述字段的现有表：

3.1.6 变量声明之Record Types

语法如下：

登录后复制

name RECORD;

• 1.

Record 变量类似于RowType变量，但其没有预定义结构。在 SELECT 或 FOR 操作期间分配的实际的Row结构就是Record变量的结构类型。所以每次在变量赋值时，变量的子结构都会发生变化。所以在变量被第一次赋值之前，它没有子结构，并且任何访问该变量的字段都将导致运行时报错。

注意：RECORD类型变量并不是一个实际的数据类型，只是占位符。当PL/PGSQL函数的返回类型被声明为RECORD时，它与RECORD变量的概念并不完全一样，即便该函数可能使用RECORD变量来保存其返回结果集。在这两种情况下，编写自定义函数时，实际的ROW结构是未知的，但是对于返回RECORD的函数，实际的结构是在解析调用查询时确定的，而RECORD变量可以动态地更改其行结构。

3.1.7 PL/PGSQL中变量的比较运算操作

该部分内容比较简单不做详细讲解，理解如下用例：

登录后复制

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.

登录后复制

SELECT less_than(text_field_1, text_field_2) FROM table1;

• 1.

登录后复制

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

登录后复制

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

• 1.
• 2.
• 3.
• 4.
• 5.

3.1.8 变量引用之循环变量迭代

• For Loop中迭代的循环整数变量。
• 迭代游标结果数据集的循环变量。

后续循环控制和游标章节会展示具体用法，此处不做过多详解。

3.2 条件控制

PL/pgSQL有两种条件控制语句：IF语句和CASE语句。

其中IF语句有3种形式：

登录后复制

IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

• 1.
• 2.
• 3.

CASE语句有2种形式：

登录后复制

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

• 1.
• 2.

3.2.1 IF-THEN

语法如下：

登录后复制

IF boolean-expression THEN
    statements
END IF;

• 1.
• 2.
• 3.

例如：(下面SQL需要在存储过程/函数中套用)

登录后复制

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

• 1.
• 2.
• 3.

3.2.2 IF-THEN-ELSE

语法如下：

登录后复制

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

• 1.
• 2.
• 3.
• 4.
• 5.

用法如下：(下面SQL需要在存储过程/函数中套用)

登录后复制

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.

3.2.3 IF-THEN-ELSIF

语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

例如：(下面SQL需要在存储过程/函数中套用)

登录后复制

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is null
    result := 'NULL';
END IF;

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.

3.2.4 CASE search-expression WHEN

语法如下:

登录后复制

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

该语法功能与C语言的SWITCH CASE类似。(下面SQL需要在存储过程/函数中套用)

登录后复制

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.

3.2.5 Searched CASE

语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

用例如下：(下面SQL需要在存储过程/函数中套用)

登录后复制

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

该形式的CASE语句和IF-THEN ELSEIF类似等价。

注：条件控制语句都是结合存储过程PROCEDURE和自定义函数FUNCTION，在SQL体中套用的。

3.3 循环控制

PL/pgSQL在执行一些重复的SQL语句时，一般用循环结构实现，PL/pgSQL包含的循环语法有LOOP, EXIT, CONTINUE, WHILE, FOR, FOREACH。

3.3.1 LOOP

语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.

LOOP语句定义了一个无条件循环，该循环将无限期地重复，直到由EXIT或RETURN语句终止。可选Label由嵌套循环中的EXIT和CONTINUE语句使用，以指定该语句的引用哪个循环。

3.3.2 EXIT

语法如下：

登录后复制

EXIT [ label ] [ WHEN boolean-expression ];

• 1.

EXIT后面若没有跟Label，内部循环到END LOOP则结束退出。如果EXIT有Label，则该标签是当前循环体或者外层嵌套循环体的标签。在循环体的END处结束或者控制SQL块。

若指定WHEN，则当 boolean-expression 为True时，才会退出循环。否则控制流程将运行SQL体退出后的语句。该语法用于所有类型的循环体，并不限制于无条件循环体。

当有BEGIN语句开始SQL块时，EXIT将跳到BEGIN开启的SQL块之后继续执行。

例如：(下面SQL需要在存储过程/函数中套用)

登录后复制

LOOP
    -- some computations
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
    EXIT WHEN count > 0;  -- same result as previous example
END LOOP;

<<ablock>>
BEGIN
    -- some computations
    IF stocks > 100000 THEN
        EXIT ablock;  -- causes exit from the BEGIN block
    END IF;
    -- computations here will be skipped when stocks > 100000
END;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.

3.3.3 CONTINUE

语句：

登录后复制

CONTINUE [ label ] [ WHEN boolean-expression ];

• 1.

如果CONTINUE语句不带Label标签，则SQL自动从循环体的下一次循环开始位置执行，即跳过循环体中剩余的SQL语句。如果CONTINUE语句带Label标签，则执行循环体中标签指示的位置。

若指定了关键字WHEN，并且boolean-expression为true时，才会执行下一次循环的迭代。如果boolean-expression为false，则执行流传递给CONTINUE后面的SQL。

CONTINUE语句能和所有类型的循环体一起使用；它不限于和无条件循环体。

示例如下：(下面SQL需要在存储过程/函数中套用)

登录后复制

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

3.3.4 WHILE循环

语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.

当 boolean-expression 为True时，则进行WHILE的SQL循环体执行statement。每次循环执行SQL时，都会判断 boolean-expression 。(下面SQL需要在存储过程/函数中套用)

登录后复制

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

3.3.5 FOR循环

语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.

FOR循环 IN 后面的 expression 表达式是一个整数值范围上迭代。变量名自动定义来整数类型，该变量生命周期只存在于内循环。在变量的起始值和结束值每次进入循环体时计数一次。如果未指定 BY 关键字，则迭代为1，否则为BY子句中指定的值。如果REVERSE关键字被指定，则表示FOR循环的迭代变量是从大到小遍历，每次循环迭代变量减少。

FOR循环体的几种示例写法如下：

登录后复制

-- 迭代变量i增序遍历，从1到10，增量默认为1
FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
-- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为1
FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
-- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为2(BY 2)
FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

注：

如果FOR循环的迭代变量i在增序遍历中，起始值比结束值大，则循环体SQL不会被执行，也不会报错。反之降序遍历同理。

3.3.6 Query Results作变循环迭代变量

用不同的FOR循环体语句，可以迭代SQL查询结果集，并做对应的操作。语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.

target可以是 record 变量，row 变量或者是逗号分隔的标题列表。依次为查询的每一行结果集分配给迭代变量，在循环体中被引用。

示例如下：

登录后复制

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';
    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
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.
• 26.
• 27.
• 28.

如果该循环体通过 EXIT 关键字退出，则在循环体退出后依然可以访问 Row 变量的数据。

FOR-IN-EXECUTE语句是ROW变量迭代的另一个语法

登录后复制

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

• 1.
• 2.
• 3.
• 4.

与上面的结构类似，不同的是Query查询结果集被作为字符串表达式处理，在FOR循环的SQL对其进行评估和重规划，可以像普通SQL一样使用预处理的SQL查询和灵活的动态SQL，参数值可以使用USING插入动态SQL。

Query查询结果集的处理的另一种方案是使用游标。

3.3.7 数组的LOOP循环体结构

FOREACH 循环体和 FOR 循环体比较类似，其用于替代SQL查询结果集迭代变量的语法，其迭代变量是一个数组变量。语法如下：

登录后复制

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

• 1.
• 2.
• 3.
• 4.

如果没有SLICE关键字，或者SLICE被指定为0，则通过计算表达式生成的数组里各个数值遍历循环体SQL，循环体将分配访问序列中每个变量的值。示例如下：

登录后复制

CREATE FUNCTION 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.

无论是多维数组，数据都是按存储顺序访问。虽然target只是一个变量，在遍历复合类型数组时，target则表示复合变量的数组列表。当SLICE值为正时，FOREACH遍历数组的SLICE不再是单个值，SLICE值则是一个不超过数组维度的整数常量。target变量是一个数组值，其接收遍历数组数据里每个SLICE，数组的维度用SLICE来指定。

下面用例通过一维数组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;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

3.3.8 ERROR抓取

默认情况下，PL/pgSQL函数中发生的任何错误都会中止该函数和周围事务的执行。通过使用带有EXCEPTION子句的BEGIN块来捕获错误并从中恢复。该语法是START语法的常规扩展：

登录后复制

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

当没有错误输出时，该语法会执行所有的SQL语句，SQL控制流传递给END关键字后的下一个SQL语句。但如果在语句中发生错误，则放弃对SQL的进一步处理，并将SQL控制流传递给异常列表，该列表用于搜索与发生错误第一个匹配的信息。如果找到匹配信息，则执行相应的 handler_statements ，然后将SQL控制流传递给END关键字后的下一个SQL语句。如果列表中没有匹配到发生错误的内容，刚该 ERROR 会通过 EXCEPTION 终止对该函数的处理。

该 condition 名字可能是任意一个错误码，如同类别的名称可以匹配类型中的任何错误。特殊condition的名称 OTHERS 匹配除QUERY_CANCELED和ASSERT_FAILURE之外的所有错误类型。condition 名称不区分大小写。另外错误条件可以由SQLSTATE代码指定。例如下面示例，其是等价的。

登录后复制

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

• 1.
• 2.

当错误被 EXCEPTION 子句捕获时，PL/pgSQL函数的局部变量被保存，但SQL块中对数据库持久状态的所有更改都将回滚。下面示例作为参考：

登录后复制

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

当控制流到达分配的y时，则会输出 division_by_zero分支要输出的错误信息。该数据会被EXCEPTION 子句捕获，RETURN 语句中返回的x的增量值，但 UPDATE 命令的效果则被回滚。但SQL体之前的 INSERT 不被回滚。因此数据库的最终结果是包含 Tom Jones 的数据，而不是包含 Joe Jones 的内容。

注：与不包含SQL子句块相比，包含子句块的进入和退出成本要高的多。因此，除非必要时才使用。

3.3.9 从ERROR中获取信息

在PL/pgSQL中，关于当前 exception 有两种方法获取 error message。指定特殊变量和 GET STACKED DIAGNOSTICS 语法关键字。

在一个 exception 处理句柄中，指定特殊变量 SQLSTATE 包含了引发异常对应的错误码。特殊变量 SQLERRM 包含与异常相关的错误信息。这些变量在 EXCEPTION 结构外是没有被定义的。

在异常处理 SQL 程序中，也可用 GET STACKED DIAGNOSTICS 关键字来检索当前有关的异常消息。命令格式如下：

登录后复制

GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

• 1.

每个关键字都具备指定变量的状态值。当前可用状态值如下表所示：

名称	类型	描述
RETURNED_SQLSTATE	text	exception的SQLSTATE错误 码
COLUMN_NAME	text	exception相关的字段名
CONSTRAINT_NAME	text	exception相关的约束名
PG_DATATYPE_NAME	text	exception相关的数据类型名
MESSAGE_TEXT	text	exception的主要消息文本
TABLE_NAME	text	exception相关的表名
SCHEMA_NAME	text	exception相关的模式名
PG_EXCEPTION_DETAIL	text	exception的详细信息，前提是该message存在
PG_EXCEPTION_HINT	text	exception提示消息的文本内容，前提是message存在
PG_EXCEPTION_CONTEXT	text	exception的堆栈文本信息

如果 EXCEPTION 没有设置变量值，则返回一个空字符串。例如：

登录后复制

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

该部分具体的示例，见OpenGaussDB官方网站用例，此处不再详述。

 GET DIAGNOSTICS语句

3.4 NULL语句

在PL/pgSQL中，NULL占位符是非常有用的。例如 NULL 以指示 if/then/else 链的一个分支故意为空。故此用NULL语句。

例如下面两个匿名块是等价的(匿名块是Oracle的语法，OpenGauss暂不支持，下面SQL了解即可)

登录后复制

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignore the error
END;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

注：在Oracle的PL/SQL中，不允许使用空语句列表，因此在这种情况下需要使用NULL语句。但PL/pgSQL中允许什么都不写来替代NULL语句。但目前OpenGaussDB不支持什么都不写的场景，故还是要用NULL语法。

3.5 匿名块和存储过程实现

匿名块是oracle中PLSQL的内容，GaussDB上不确定具有该功能，故此处暂时不做说明。

存储过程是PL/pgSQL的重要功能，其主要目的是把一连串SQL操作进行封装成一个功能模块，用户使用该一组SQL模块时，只需要调用该存储过程的名称即可执行一连串封装的SQL操作。存储过程里的SQL实现对于用户是黑盒。即用户不知道调用的存储过程其中具体的实现过程。存储过程与自定义函数最大的区别是，存储过程没有返回值，函数必须要有RETURN。但是存储过程也可以输出内容和结果，比较调用oracle的兼容包dbe_output.print_line中的输出函数，或者用RAISE输出内容，还有用Output参数来存储要输出的结果。

存储过程可带参数也可以不带参数。如下示例：(其中table需要替换成存在的表名，xxx是查询的where条件，根据具体情况自行修改，也可以省略where条件)

登录后复制

create or replace procedure cursor_function() as 
declare
	var1 int;
	var2 int;
	cursor c1 for select va1, va2 from table where xxx;
begin 
	open c1;
	loop fetch c1 into var1, var2;
	exit when c1%notfound;
		body;
		raise notice 'xx%, xx%',var1,var2;
	end loop;
	close c1;
end;
/

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.

编写存储过程，输入个数，生成student，id从100000开始，starttime是当前时间。示例如下：

登录后复制

create table student (id int,vdate timestamp);
create or replace procedure ins_student(num int)
as 
declare
    id int:= 100000;
    var int;
    jishu int;
begin
    for var in 1 .. num loop
        insert into student values (id, now());
        id := id + 1;
    end loop;
    select count(*) into jishu from student;
    raise info '已插入%行, 目前student表共有%行', num, jishu;
end;
/

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

结束游标的存储过程相结合使用，示例如下：

登录后复制

create table sjh_cursor (a int,b int,c int);
insert into sjh_cursor values(1,2,3);
insert into sjh_cursor values(4,5,6);
--创建游标，使用游标从表里查询并输出2字段
create or replace procedure pro_sjh() as
declare
    cursor c1 is select a, b from sjh_cursor;
    var1 int;
    var2 int;
begin
    open c1;
    loop fetch c1 into var1, var2;
        exit when c1%notfound;
        raise notice 'sjh_cursor表a列数据为: %, b列数据为: %', var1, var2;
     end loop;
     close c1;
end;
/
call pro_sjh();

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

编写存储过程, 输入学号和科目名称, 返回对应的平均成绩，示例如下：

登录后复制

create or replace procedure avg_score(id int,coursename varchar(20) ,avgscore out int)
as
begin
case when coursename='math' then 
select avg(math) into avgscore from student where student_id=id;
when coursename='pysical' then 
select avg(pysical) into avgscore from student where student_id=id;
when coursename='music' then 
select avg(music) into avgscore from student where student_id=id;
when coursename='art' then 
select avg(art) into avgscore from student where student_id=id;
end case;
end;
/

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.

PL/PGSQL更多知识请移步 基于开发者空间OpenGauss数据库的PLPGSQL实践二了解学习。