当前位置：紫竹林-程序员中文网 > 科技 > 正文

基于开发者空间GaussDB云数据库的PLpgSQL实践一

2025-08-14 分类：科技 阅读(11) 评论(0) 


                                                                                                                                                <span id="OSC_h2_1"></span>

1 概述

1.1 案例介绍

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

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

1.2 适用对象

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

1.3 案例时间

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

1.4 案例流程

说明：

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

1.5 资源总览

资源名称 规格 单价（元） 时长（分钟）
开发者空间-GaussDB 鲲鹏通用计算增强型 kc1 | 2vCPUs | 4G | HCE2.0 免费 60

最新案例动态，请查阅  《基于开发者空间GaussDB云数据库的PLpgSQL实践一》。小伙伴快来领取华为开发者空间，进入云主机桌面版实操吧！

2 Gauss数据库PL/pgSQL实践

2.1 领取开发者空间GaussDB数据库

免费领取GaussDB在线试用版（2025年 06月 21日 – 2025年 12月 31日）。地址如下：
https://developer.huaweicloud.com/signup/75dae31d0eb04cdcab822c76d35eb9a1
有1000个名额，数量有限，速度为王。

领取后，按提示即可创建数据库，如下：



登录数据库

输入创建数据库时的密码，点击测试连接，通过后点击登录

创建测试数据库，点击确定

查看数据库兼容类型

执行sql：

3 PL/PGSQL实践

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

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

复制 
[ <span style="color:#ab5656"><<</span>标签<span style="color:#ab5656">>></span> ]
[ 声明
变量;
变量 :<span style="color:#ab5656">=</span> 赋值 ]
<strong>BEGIN</strong>
    <strong>SQL</strong> QUERY
<strong>END</strong> [ 标签];

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

3.1 变量赋值与引用

3.1.1 变更声明与赋值

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

变量声明的语法

复制 
name [ CONSTANT ] type [ <strong>COLLATE</strong> collation_name ] [ <strong>NOT</strong> <strong>NULL</strong> ] [ { <strong>DEFAULT</strong> <span style="color:#ab5656">|</span> :<span style="color:#ab5656">=</span> <span style="color:#ab5656">|</span> <span style="color:#ab5656">=</span> } expression ];

例如:

复制 
<strong>DECLARE</strong>
var1 TEXT;
var2 <span style="color:#880000">INTEGER</span> :<span style="color:#ab5656">=</span> <span style="color:#880000">10</span>;
url <span style="color:#880000">VARCHAR</span>;
quantity <span style="color:#880000">NUMERIC</span>(<span style="color:#880000">5</span>);
myrow tablename<span style="color:#ab5656">%</span>ROWTYPE;
myfield tablename.columnname<span style="color:#ab5656">%</span>TYPE;
arow RECORD;
<strong>CURSOR</strong> c1 <strong>IN</strong> <strong>SELECT</strong> col1, col2 <strong>FROM</strong> table_name WHER 谓词过滤条件;

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

复制 
<strong>DECLARE</strong>
quantity <span style="color:#880000">integer</span> <strong>DEFAULT</strong> <span style="color:#880000">32</span>;
url <span style="color:#880000">varchar</span> :<span style="color:#ab5656">=</span> <span style="color:#880000">'http://mysite.com'</span>;
transaction_time CONSTANT <span style="color:#880000">timestamp</span> <strong>with</strong> <span style="color:#880000">time</span> zone :<span style="color:#ab5656">=</span> now();
tax :<span style="color:#ab5656">=</span> subtotal <span style="color:#ab5656">*</span> <span style="color:#880000">0.06</span>;
my_record.user_id :<span style="color:#ab5656">=</span> <span style="color:#880000">20</span>;
my_array[j] :<span style="color:#ab5656">=</span> <span style="color:#880000">20</span>;
my_array[<span style="color:#880000">1</span>:<span style="color:#880000">3</span>] :<span style="color:#ab5656">=</span> <strong>array</strong>[<span style="color:#880000">1</span>,<span style="color:#880000">2</span>,<span style="color:#880000">3</span>];
complex_array[n].realpart <span style="color:#ab5656">=</span> <span style="color:#880000">12.3</span>;

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

复制 
<strong>DECLARE</strong>
  x <span style="color:#880000">integer</span> :<span style="color:#ab5656">=</span> <span style="color:#880000">1</span>;
  y <span style="color:#880000">integer</span> :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">+</span> <span style="color:#880000">1</span>;

3.1.2 变量声明之Function Parameters

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

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> sales_tax(subtotal <span style="color:#880000">real</span>) <strong>RETURNS</strong> <span style="color:#880000">real</span> <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> subtotal <span style="color:#ab5656">*</span> <span style="color:#880000">0.06</span>;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

复制 
name ALIAS <strong>FOR</strong> $n;

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

复制 
<strong>DROP</strong> <strong>FUNCTION</strong> IF <strong>EXISTS</strong> sales_tax();
<strong>CREATE</strong> <strong>OR</strong> REPLACE <strong>FUNCTION</strong> sales_tax(<span style="color:#880000">real</span>) <strong>RETURNS</strong> <span style="color:#880000">real</span> <strong>AS</strong> $$
<strong>DECLARE</strong>
    subtotal ALIAS <strong>FOR</strong> $<span style="color:#880000">1</span>;
<strong>BEGIN</strong>
    <strong>RETURN</strong> subtotal <span style="color:#ab5656">*</span> <span style="color:#880000">0.06</span>;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

注解：

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

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

复制 
<strong>CREATE</strong> <strong>TABLE</strong> test(f1 <span style="color:#880000">int</span>, f3 <span style="color:#880000">int</span>, f5 <span style="color:#880000">int</span>, f7 <span style="color:#880000">int</span>);
<strong>CREATE</strong> <strong>FUNCTION</strong> concat_selected_fields(in_t test) <strong>RETURNS</strong> text <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> in_t.f1 <span style="color:#ab5656">||</span> in_t.f3 <span style="color:#ab5656">||</span> in_t.f5 <span style="color:#ab5656">||</span> in_t.f7;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> instr(<span style="color:#880000">varchar</span>, <span style="color:#880000">integer</span>) <strong>RETURNS</strong> <span style="color:#880000">integer</span> <strong>AS</strong> $$
<strong>DECLARE</strong>
    v_string ALIAS <strong>FOR</strong> $<span style="color:#880000">1</span>;
    index ALIAS <strong>FOR</strong> $<span style="color:#880000">2</span>;
<strong>BEGIN</strong>
    <span style="color:#697070">-- some computations using v_string and index here</span>
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

注意：如果提示instr函数创建失败，是因为在public模式下，会与系统函数instr重名，所以最好是在用户同名模式下创建，例如用户myuser在库下应该会对应有myuser模式

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

复制 
<strong>CREATE</strong> <strong>OR</strong> REPLACE <strong>FUNCTION</strong> sales_tax(subtotal <span style="color:#880000">real</span>, <strong>OUT</strong> tax <span style="color:#880000">real</span>) <strong>AS</strong> $$
<strong>BEGIN</strong>
    tax :<span style="color:#ab5656">=</span> subtotal <span style="color:#ab5656">*</span> <span style="color:#880000">0.06</span>;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

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

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

复制 
<strong>SELECT</strong> sales_tax(<span style="color:#880000">100.00</span>);

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> sum_n_product(x <span style="color:#880000">int</span>, y <span style="color:#880000">int</span>, <strong>OUT</strong> sum <span style="color:#880000">int</span>, <strong>OUT</strong> prod <span style="color:#880000">int</span>) <strong>AS</strong> $$
<strong>BEGIN</strong>
    sum :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">+</span> y;
    prod :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">*</span> y;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

<strong>SELECT</strong> <span style="color:#ab5656">*</span> <strong>FROM</strong> sum_n_product(<span style="color:#880000">2</span>, <span style="color:#880000">4</span>);


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

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

复制 
<strong>DROP</strong> <strong>FUNCTION</strong> sum_n_product;
<strong>CREATE</strong> <strong>PROCEDURE</strong> sum_n_product(x <span style="color:#880000">int</span>, y <span style="color:#880000">int</span>, <strong>OUT</strong> sum <span style="color:#880000">int</span>, <strong>OUT</strong> prod <span style="color:#880000">int</span>) <strong>AS</strong> 
<strong>BEGIN</strong>
    sum :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">+</span> y;
    prod :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">*</span> y;
<strong>END</strong>;

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

复制 
<strong>CALL</strong> sum_n_product(<span style="color:#880000">2</span>, <span style="color:#880000">4</span>, <strong>NULL</strong>, <strong>NULL</strong>);

截图运行结果：

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> extended_sales(p_itemno <span style="color:#880000">int</span>)
<strong>RETURNS</strong> <strong>TABLE</strong>(quantity <span style="color:#880000">int</span>, total <span style="color:#880000">numeric</span>) <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> QUERY <strong>SELECT</strong> s.quantity, s.quantity <span style="color:#ab5656">*</span> s.price <strong>FROM</strong> sales <strong>AS</strong> s
                 <strong>WHERE</strong> s.itemno <span style="color:#ab5656">=</span> p_itemno;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
<strong>RETURNS</strong> anyelement <strong>AS</strong> $$
<strong>DECLARE</strong>
    <strong>result</strong> ALIAS <strong>FOR</strong> $<span style="color:#880000">0</span>;
<strong>BEGIN</strong>
    <strong>result</strong> :<span style="color:#ab5656">=</span> v1 <span style="color:#ab5656">+</span> v2 <span style="color:#ab5656">+</span> v3;
    <strong>RETURN</strong> <strong>result</strong>;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

复制 
<strong>DROP</strong> <strong>FUNCTION</strong> IF <strong>EXISTS</strong> add_three_values;
<strong>CREATE</strong> <strong>FUNCTION</strong> add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, <strong>OUT</strong> sum anyelement)
<strong>AS</strong> $$
<strong>BEGIN</strong>
    sum :<span style="color:#ab5656">=</span> v1 <span style="color:#ab5656">+</span> v2 <span style="color:#ab5656">+</span> v3;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)
<strong>RETURNS</strong> anycompatible <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> v1 <span style="color:#ab5656">+</span> v2 <span style="color:#ab5656">+</span> v3;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

上面用例引用方法如下：

复制 
<strong>SELECT</strong> add_three_values(<span style="color:#880000">1</span>, <span style="color:#880000">2</span>, <span style="color:#880000">4.7</span>);

该函数调用，会自动将参数值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 <strong>FOR</strong> oldname;

例如：

复制 
<strong>DECLARE</strong>
  prior ALIAS <strong>FOR</strong> <strong>old</strong>;
  updated ALIAS <strong>FOR</strong> <strong>new</strong>;

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

3.1.4 变量声明之Copying Types

语法如下：

复制 
name table.column<span style="color:#ab5656">%</span>TYPE
name variable<span style="color:#ab5656">%</span>TYPE

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

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

复制 
<strong>declare</strong>
  variable users.user_id<span style="color:#ab5656">%</span>TYPE;

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

复制 
<strong>declare</strong>
  variables users.user_id<span style="color:#ab5656">%</span>TYPE[];
  variables users.user_id<span style="color:#ab5656">%</span>TYPE <strong>ARRAY</strong>[<span style="color:#880000">4</span>];  <span style="color:#697070">-- equivalent to the above</span>

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

重点

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

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

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

复制 
<strong>CREATE</strong> <strong>TABLE</strong> sal_emp (
    name            text,
    pay_by_quarter  <span style="color:#880000">integer</span>[],
    schedule        text[][]
);
<strong>CREATE</strong> <strong>TABLE</strong> tictactoe (
    pay_by_quarter <span style="color:#880000">integer</span> <strong>ARRAY</strong>,
    sales <span style="color:#880000">integer</span> <strong>ARRAY</strong>[<span style="color:#880000">4</span>],
    squares <span style="color:#880000">integer</span>[<span style="color:#880000">3</span>][<span style="color:#880000">3</span>]
);

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

3.1.5 变量声明之Row Types

语法如下

复制 
name table_name<span style="color:#ab5656">%</span>ROWTYPE;
name composite_type_name;

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

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

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

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

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




3.1.6 变量声明之Record Types

语法如下：

复制 
name RECORD;

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

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

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

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> less_than(a text, b text) <strong>RETURNS</strong> <span style="color:#880000">boolean</span> <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> a <span style="color:#ab5656"><</span> b;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

复制 
<strong>SELECT</strong> less_than(f1, f3) <strong>FROM</strong> test;

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> less_than_1 (a text, b text) <strong>RETURNS</strong> <span style="color:#880000">boolean</span> <strong>AS</strong> $$
<strong>DECLARE</strong>
    local_a text :<span style="color:#ab5656">=</span> a;
    local_b text :<span style="color:#ab5656">=</span> b;
<strong>BEGIN</strong>
    <strong>RETURN</strong> local_a <span style="color:#ab5656"><</span> local_b;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> less_than_c(a text, b text) <strong>RETURNS</strong> <span style="color:#880000">boolean</span> <strong>AS</strong> $$
<strong>BEGIN</strong>
    <strong>RETURN</strong> a <span style="color:#ab5656"><</span> b <strong>COLLATE</strong> "C";
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

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

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

3.2 条件控制

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

其中IF语句有3种形式：

复制 
IF ... <strong>THEN</strong> ... <strong>END</strong> IF
IF ... <strong>THEN</strong> ... <strong>ELSE</strong> ... <strong>END</strong> IF
IF ... <strong>THEN</strong> ... ELSIF ... <strong>THEN</strong> ... <strong>ELSE</strong> ... <strong>END</strong> IF

CASE语句有2种形式：

复制 
<strong>CASE</strong> ... <strong>WHEN</strong> ... <strong>THEN</strong> ... <strong>ELSE</strong> ... <strong>END</strong> <strong>CASE</strong>
<strong>CASE</strong> <strong>WHEN</strong> ... <strong>THEN</strong> ... <strong>ELSE</strong> ... <strong>END</strong> <strong>CASE</strong>

3.2.1 IF-THEN

语法如下：

复制 
IF <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
    statements
<strong>END</strong> IF;

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

复制 
IF v_user_id <span style="color:#ab5656"><></span> <span style="color:#880000">0</span> <strong>THEN</strong>
    <strong>UPDATE</strong> users <strong>SET</strong> email <span style="color:#ab5656">=</span> v_email <strong>WHERE</strong> user_id <span style="color:#ab5656">=</span> v_user_id;
<strong>END</strong> IF;

3.2.2 IF-THEN-ELSE

语法如下：

复制 
IF <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
    statements
<strong>ELSE</strong>
    statements
<strong>END</strong> IF;

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

复制 
IF parentid <strong>IS</strong> <strong>NULL</strong> <strong>OR</strong> parentid <span style="color:#ab5656">=</span> <span style="color:#880000">''</span>
<strong>THEN</strong>
    <strong>RETURN</strong> fullname;
<strong>ELSE</strong>
    <strong>RETURN</strong> hp_true_filename(parentid) <span style="color:#ab5656">||</span> <span style="color:#880000">'/'</span> <span style="color:#ab5656">||</span> fullname;
<strong>END</strong> IF;

IF v_count <span style="color:#ab5656">></span> <span style="color:#880000">0</span> <strong>THEN</strong>
    <strong>INSERT</strong> <strong>INTO</strong> users_count (count) <strong>VALUES</strong> (v_count);
    <strong>RETURN</strong> <span style="color:#880000">'t'</span>;
<strong>ELSE</strong>
    <strong>RETURN</strong> <span style="color:#880000">'f'</span>;
<strong>END</strong> IF;

3.2.3 IF-THEN-ELSIF

语法如下：

复制 
IF <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
    statements
[ ELSIF <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
    statements
[ ELSIF <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
    statements
    ... ]
] 
[ <strong>ELSE</strong>
    statements ]
<strong>END</strong> IF;

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

复制 
IF number <span style="color:#ab5656">=</span> <span style="color:#880000">0</span> <strong>THEN</strong>
    <strong>result</strong> :<span style="color:#ab5656">=</span> <span style="color:#880000">'zero'</span>;
ELSIF number <span style="color:#ab5656">></span> <span style="color:#880000">0</span> <strong>THEN</strong>
    <strong>result</strong> :<span style="color:#ab5656">=</span> <span style="color:#880000">'positive'</span>;
ELSIF number <span style="color:#ab5656"><</span> <span style="color:#880000">0</span> <strong>THEN</strong>
    <strong>result</strong> :<span style="color:#ab5656">=</span> <span style="color:#880000">'negative'</span>;
<strong>ELSE</strong>
    <span style="color:#697070">-- hmm, the only other possibility is that number is null</span>
    <strong>result</strong> :<span style="color:#ab5656">=</span> <span style="color:#880000">'NULL'</span>;
<strong>END</strong> IF;

IF demo_row.sex <span style="color:#ab5656">=</span> <span style="color:#880000">'m'</span> <strong>THEN</strong>
    pretty_sex :<span style="color:#ab5656">=</span> <span style="color:#880000">'man'</span>;
<strong>ELSE</strong>
    IF demo_row.sex <span style="color:#ab5656">=</span> <span style="color:#880000">'f'</span> <strong>THEN</strong>
        pretty_sex :<span style="color:#ab5656">=</span> <span style="color:#880000">'woman'</span>;
    <strong>END</strong> IF;
<strong>END</strong> IF;

3.2.4 CASE search-expression WHEN

语法如下:

复制 
<strong>CASE</strong> <strong>search</strong>
<span style="color:#ab5656">-</span>expression
    <strong>WHEN</strong> expression [, expression [ ... ]] <strong>THEN</strong>
      statements
  [ <strong>WHEN</strong> expression [, expression [ ... ]] <strong>THEN</strong>
      statements
    ... ]
  [ <strong>ELSE</strong>
      statements ]
<strong>END</strong> <strong>CASE</strong>;

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

复制 
<strong>CASE</strong> x
    <strong>WHEN</strong> <span style="color:#880000">1</span>, <span style="color:#880000">2</span> <strong>THEN</strong>
        msg :<span style="color:#ab5656">=</span> <span style="color:#880000">'one or two'</span>;
    <strong>ELSE</strong>
        msg :<span style="color:#ab5656">=</span> <span style="color:#880000">'other value than one or two'</span>;
<strong>END</strong> <strong>CASE</strong>;

<strong>SELECT</strong> <span style="color:#ab5656">*</span> <strong>INTO</strong> myrec <strong>FROM</strong> emp <strong>WHERE</strong> empname <span style="color:#ab5656">=</span> myname;
IF <strong>NOT</strong> FOUND <strong>THEN</strong>
    RAISE EXCEPTION <span style="color:#880000">'employee % not found'</span>, myname;
<strong>END</strong> IF;

<strong>BEGIN</strong>
    <strong>SELECT</strong> <span style="color:#ab5656">*</span> <strong>INTO</strong> STRICT myrec <strong>FROM</strong> emp <strong>WHERE</strong> empname <span style="color:#ab5656">=</span> myname;
    EXCEPTION
        <strong>WHEN</strong> NO_DATA_FOUND <strong>THEN</strong>
            RAISE EXCEPTION <span style="color:#880000">'employee % not found'</span>, myname;
        <strong>WHEN</strong> TOO_MANY_ROWS <strong>THEN</strong>
            RAISE EXCEPTION <span style="color:#880000">'employee % not unique'</span>, myname;
<strong>END</strong>;

3.2.5 Searched CASE

语法如下：

复制 
<strong>CASE</strong>
    <strong>WHEN</strong> <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
      statements
  [ <strong>WHEN</strong> <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression <strong>THEN</strong>
      statements
    ... ]
  [ <strong>ELSE</strong>
      statements ]
<strong>END</strong> <strong>CASE</strong>;

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

复制 
<strong>CASE</strong>
    <strong>WHEN</strong> x <strong>BETWEEN</strong> <span style="color:#880000">0</span> <strong>AND</strong> <span style="color:#880000">10</span> <strong>THEN</strong>
        msg :<span style="color:#ab5656">=</span> <span style="color:#880000">'value is between zero and ten'</span>;
    <strong>WHEN</strong> x <strong>BETWEEN</strong> <span style="color:#880000">11</span> <strong>AND</strong> <span style="color:#880000">20</span> <strong>THEN</strong>
        msg :<span style="color:#ab5656">=</span> <span style="color:#880000">'value is between eleven and twenty'</span>;
<strong>END</strong> <strong>CASE</strong>;

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

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

3.3 循环控制

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

3.3.1 LOOP

语法如下：

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

3.3.2 EXIT

语法如下：

复制 
EXIT [ label ] [ <strong>WHEN</strong> <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression ];

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

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

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

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

复制 
LOOP
    <span style="color:#697070">-- some computations</span>
    IF count <span style="color:#ab5656">></span> <span style="color:#880000">0</span> <strong>THEN</strong>
        EXIT;  <span style="color:#697070">-- exit loop</span>
    <strong>END</strong> IF;
<strong>END</strong> LOOP;

LOOP
    <span style="color:#697070">-- some computations</span>
    EXIT <strong>WHEN</strong> count <span style="color:#ab5656">></span> <span style="color:#880000">0</span>;  <span style="color:#697070">-- same result as previous example</span>
<strong>END</strong> LOOP;

<span style="color:#ab5656"><<</span>ablock<span style="color:#ab5656">>></span>
<strong>BEGIN</strong>
    <span style="color:#697070">-- some computations</span>
    IF stocks <span style="color:#ab5656">></span> <span style="color:#880000">100000</span> <strong>THEN</strong>
        EXIT ablock;  <span style="color:#697070">-- causes exit from the BEGIN block</span>
    <strong>END</strong> IF;
    <span style="color:#697070">-- computations here will be skipped when stocks > 100000</span>
<strong>END</strong>;

3.3.3 CONTINUE

语句：

复制 
CONTINUE [ label ] [ <strong>WHEN</strong> <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression ];

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

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

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

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

复制 
LOOP
    <span style="color:#697070">-- some computations</span>
    EXIT <strong>WHEN</strong> count <span style="color:#ab5656">></span> <span style="color:#880000">100</span>;
    CONTINUE <strong>WHEN</strong> count <span style="color:#ab5656"><</span> <span style="color:#880000">50</span>;
    <span style="color:#697070">-- some computations for count IN [50 .. 100]</span>
<strong>END</strong> LOOP;

3.3.4 WHILE循环

语法如下：

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
WHILE <span style="color:#880000">boolean</span>
<span style="color:#ab5656">-</span>expression LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

复制 
WHILE amount_owed <span style="color:#ab5656">></span> <span style="color:#880000">0</span> <strong>AND</strong> gift_certificate_balance <span style="color:#ab5656">></span> <span style="color:#880000">0</span> LOOP
    <span style="color:#697070">-- some computations here</span>
<strong>END</strong> LOOP;

WHILE <strong>NOT</strong> done LOOP
    <span style="color:#697070">-- some computations here</span>
<strong>END</strong> LOOP;

3.3.5 FOR循环

语法如下：

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
<strong>FOR</strong> name <strong>IN</strong> [ REVERSE ] expression .. expression [ <strong>BY</strong> expression ] LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

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

复制 
<span style="color:#697070">-- 迭代变量i增序遍历，从1到10，增量默认为1</span>
<strong>FOR</strong> i <strong>IN</strong> <span style="color:#880000">1.</span>
<span style="color:#880000">.10</span> LOOP
    <span style="color:#697070">-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop</span>
<strong>END</strong> LOOP;
<span style="color:#697070">-- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为1</span>
<strong>FOR</strong> i <strong>IN</strong> REVERSE <span style="color:#880000">10.</span>
<span style="color:#880000">.1</span> LOOP
    <span style="color:#697070">-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop</span>
<strong>END</strong> LOOP;
<span style="color:#697070">-- 迭代变量i降序遍历(REVERSE)，从10到1，降量默认为2(BY 2)</span>
<strong>FOR</strong> i <strong>IN</strong> REVERSE <span style="color:#880000">10.</span>
<span style="color:#880000">.1</span> <strong>BY</strong> <span style="color:#880000">2</span> LOOP
    <span style="color:#697070">-- i will take on the values 10,8,6,4,2 within the loop</span>
<strong>END</strong> LOOP;

注：

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

3.3.6 Query Results作变循环迭代变量

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

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
<strong>FOR</strong> target <strong>IN</strong> query LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

示例如下：

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> refresh_mviews() <strong>RETURNS</strong> <span style="color:#880000">integer</span> <strong>AS</strong> $$
<strong>DECLARE</strong>
    mviews RECORD;
<strong>BEGIN</strong>
    RAISE NOTICE <span style="color:#880000">'Refreshing all materialized views...'</span>;
    <strong>FOR</strong> mviews <strong>IN</strong>
       <strong>SELECT</strong> n.nspname <strong>AS</strong> mv_schema,
              c.relname <strong>AS</strong> mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) <strong>AS</strong> owner
         <strong>FROM</strong> pg_catalog.pg_class c
    <strong>LEFT</strong> <strong>JOIN</strong> pg_catalog.pg_namespace n <strong>ON</strong> (n.oid <span style="color:#ab5656">=</span> c.relnamespace)
        <strong>WHERE</strong> c.relkind <span style="color:#ab5656">=</span> <span style="color:#880000">'m'</span>
     <strong>ORDER</strong> <strong>BY</strong> <span style="color:#880000">1</span>
    LOOP

        <span style="color:#697070">-- Now "mviews" has one record with information about the materialized view</span>

        RAISE NOTICE <span style="color:#880000">'Refreshing materialized view %.% (owner: %)...'</span>,
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        <strong>EXECUTE</strong> format(<span style="color:#880000">'REFRESH MATERIALIZED VIEW %I.%I'</span>, mviews.mv_schema, mviews.mv_name);
    <strong>END</strong> LOOP;

    RAISE NOTICE <span style="color:#880000">'Done refreshing materialized views.'</span>;
    <strong>RETURN</strong> <span style="color:#880000">1</span>;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

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

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
<strong>FOR</strong> target <strong>IN</strong> <strong>EXECUTE</strong> text_expression [ <strong>USING</strong> expression [, ... ] ] LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

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

3.3.7 数组的LOOP循环体结构

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

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
FOREACH target [ SLICE number ] <strong>IN</strong> <strong>ARRAY</strong> expression LOOP
    statements
<strong>END</strong> LOOP [ label ];

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

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> sum_a (<span style="color:#880000">int</span>[]) <strong>RETURNS</strong> int8 <strong>AS</strong> $$
<strong>DECLARE</strong>
  s int8 :<span style="color:#ab5656">=</span> <span style="color:#880000">0</span>;
  x <span style="color:#880000">int</span>;
<strong>BEGIN</strong>
  FOREACH x <strong>IN</strong> <strong>ARRAY</strong> $<span style="color:#880000">1</span>
  LOOP
    s :<span style="color:#ab5656">=</span> s <span style="color:#ab5656">+</span> x;
  <strong>END</strong> LOOP;
  <strong>RETURN</strong> s;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

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

下面用例通过一维数组SLICE迭代变量展示其用法。

复制 
<strong>CREATE</strong> <strong>FUNCTION</strong> scan_rows(<span style="color:#880000">int</span>[]) <strong>RETURNS</strong> void <strong>AS</strong> $$
<strong>DECLARE</strong>
  x <span style="color:#880000">int</span>[];
<strong>BEGIN</strong>
  FOREACH x SLICE <span style="color:#880000">1</span> <strong>IN</strong> <strong>ARRAY</strong> $<span style="color:#880000">1</span>
  LOOP
    RAISE NOTICE <span style="color:#880000">'row = %'</span>, x;
  <strong>END</strong> LOOP;
<strong>END</strong>;
$$ <strong>LANGUAGE</strong> plpgsql;

<strong>SELECT</strong> scan_rows(<strong>ARRAY</strong>[[<span style="color:#880000">1</span>,<span style="color:#880000">2</span>,<span style="color:#880000">3</span>],[<span style="color:#880000">4</span>,<span style="color:#880000">5</span>,<span style="color:#880000">6</span>],[<span style="color:#880000">7</span>,<span style="color:#880000">8</span>,<span style="color:#880000">9</span>],[<span style="color:#880000">10</span>,<span style="color:#880000">11</span>,<span style="color:#880000">12</span>]]);


3.3.8 ERROR抓取

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

复制 
[ <span style="color:#ab5656"><<</span>label<span style="color:#ab5656">>></span> ]
[ <strong>DECLARE</strong>
    declarations ]
<strong>BEGIN</strong>
    statements
EXCEPTION
    <strong>WHEN</strong> <strong>condition</strong> [ <strong>OR</strong> <strong>condition</strong> ... ] <strong>THEN</strong>
        handler_statements
    [ <strong>WHEN</strong> <strong>condition</strong> [ <strong>OR</strong> <strong>condition</strong> ... ] <strong>THEN</strong>
          handler_statements
      ... ]
<strong>END</strong>;

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

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

复制 
<strong>WHEN</strong> division_by_zero <strong>THEN</strong> ...
<strong>WHEN</strong> <strong>SQLSTATE</strong> <span style="color:#880000">'22012'</span> <strong>THEN</strong> ...

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

复制 
<strong>INSERT</strong> <strong>INTO</strong> mytab(firstname, lastname) <strong>VALUES</strong>(<span style="color:#880000">'Tom'</span>, <span style="color:#880000">'Jones'</span>);
<strong>BEGIN</strong>
    <strong>UPDATE</strong> mytab <strong>SET</strong> firstname <span style="color:#ab5656">=</span> <span style="color:#880000">'Joe'</span> <strong>WHERE</strong> lastname <span style="color:#ab5656">=</span> <span style="color:#880000">'Jones'</span>;
    x :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">+</span> <span style="color:#880000">1</span>;
    y :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">/</span> <span style="color:#880000">0</span>;
EXCEPTION
    <strong>WHEN</strong> division_by_zero <strong>THEN</strong>
        RAISE NOTICE <span style="color:#880000">'caught division_by_zero'</span>;
        <strong>RETURN</strong> x;
<strong>END</strong>;

当控制流到达分配的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 关键字来检索当前有关的异常消息。命令格式如下：

复制 
<strong>GET</strong> STACKED DIAGNOSTICS variable { <span style="color:#ab5656">=</span> <span style="color:#ab5656">|</span> :<span style="color:#ab5656">=</span> } item [ , ... ];

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

名称 类型 描述
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 没有设置变量值，则返回一个空字符串。例如：

复制 
<strong>DECLARE</strong>
  text_var1 text;
  text_var2 text;
  text_var3 text;
<strong>BEGIN</strong>
  <span style="color:#697070">-- some processing which might cause an exception</span>
  ...
EXCEPTION <strong>WHEN</strong> OTHERS <strong>THEN</strong>
  <strong>GET</strong> STACKED DIAGNOSTICS text_var1 <span style="color:#ab5656">=</span> MESSAGE_TEXT,
                          text_var2 <span style="color:#ab5656">=</span> PG_EXCEPTION_DETAIL,
                          text_var3 <span style="color:#ab5656">=</span> PG_EXCEPTION_HINT;
<strong>END</strong>;

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

GET DIAGNOSTICS语句

3.4 NULL语句

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

例如下面两个匿名块是等价的

复制 
<strong>BEGIN</strong>
    y :<span style="color:#ab5656">=</span> x <span style="color:#ab5656">/</span> <span style="color:#880000">0</span>;
EXCEPTION
    <strong>WHEN</strong> division_by_zero <strong>THEN</strong>
        <strong>NULL</strong>;  <span style="color:#697070">-- ignore the error</span>
<strong>END</strong>;

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

3.5 匿名块和存储过程实现

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

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

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

复制 
<strong>create</strong> <strong>or</strong> replace <strong>procedure</strong> cursor_function() <strong>as</strong> 
<strong>declare</strong>
    var1 <span style="color:#880000">int</span>;
    var2 <span style="color:#880000">int</span>;
    <strong>cursor</strong> c1 <strong>for</strong> <strong>select</strong> va1, va2 <strong>from</strong> <strong>table</strong> <strong>where</strong> xxx;
<strong>begin</strong> 
    <strong>open</strong> c1;
    loop <strong>fetch</strong> c1 <strong>into</strong> var1, var2;
    exit <strong>when</strong> c1<span style="color:#ab5656">%</span>notfound;
        body;
        raise notice <span style="color:#880000">'xx%, xx%'</span>,var1,var2;
    <strong>end</strong> loop;
    <strong>close</strong> c1;
<strong>end</strong>;
<span style="color:#ab5656">/</span>

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

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

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

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



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

复制 
<strong>create</strong> <strong>or</strong> replace <strong>procedure</strong> avg_score(id <span style="color:#880000">int</span>,coursename <span style="color:#880000">varchar</span>(<span style="color:#880000">20</span>) ,avgscore <strong>out</strong> <span style="color:#880000">int</span>)
<strong>as</strong>
<strong>begin</strong>
<strong>case</strong> <strong>when</strong> coursename<span style="color:#ab5656">=</span>
<span style="color:#880000">'math'</span> <strong>then</strong> 
<strong>select</strong> <span style="color:#397300">avg</span>(math) <strong>into</strong> avgscore <strong>from</strong> student <strong>where</strong> student_id<span style="color:#ab5656">=</span>id;
<strong>when</strong> coursename<span style="color:#ab5656">=</span>
<span style="color:#880000">'pysical'</span> <strong>then</strong> 
<strong>select</strong> <span style="color:#397300">avg</span>(pysical) <strong>into</strong> avgscore <strong>from</strong> student <strong>where</strong> student_id<span style="color:#ab5656">=</span>id;
<strong>when</strong> coursename<span style="color:#ab5656">=</span>
<span style="color:#880000">'music'</span> <strong>then</strong> 
<strong>select</strong> <span style="color:#397300">avg</span>(music) <strong>into</strong> avgscore <strong>from</strong> student <strong>where</strong> student_id<span style="color:#ab5656">=</span>id;
<strong>when</strong> coursename<span style="color:#ab5656">=</span>
<span style="color:#880000">'art'</span> <strong>then</strong> 
<strong>select</strong> <span style="color:#397300">avg</span>(art) <strong>into</strong> avgscore <strong>from</strong> student <strong>where</strong> student_id<span style="color:#ab5656">=</span>id;
<strong>end</strong> <strong>case</strong>;
<strong>end</strong>;
<span style="color:#ab5656">/</span>

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

4 反馈改进建议

如您在案例实操过程中遇到问题或有改进建议，可以到论坛帖评论区反馈即可，我们会及时响应处理，谢谢！

                                                                                </div>



Source link

未经允许不得转载：紫竹林-程序员中文网 » 基于开发者空间GaussDB云数据库的PLpgSQL实践一

相关推荐

评论 抢沙发

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

近期文章

关于我们 免责申明 意见反馈 隐私政策
程序员中文网：公益在线网站，帮助学习者快速成长！
关注微信 技术交流
推荐文章
每天精选资源文章推送
推荐文章
随时随地碎片化学习
推荐文章
发现有趣的

Copyright©2010-2022 鄂ICP备16010909号