这是本节的多页打印视图。点击此处打印.

PL/vSQL

1: 支持的类型
2: 范围和结构
3: 嵌入式 SQL
4: 控制流
5: 错误和诊断
6: 游标
7: PL/pgSQL 到 PL/vSQL 迁移指南

PL/vSQL 是一种功能强大且富有表现力的过程语言，用于创建可重用过程、操作数据和简化其他复杂的数据库例程。

Vertica PL/vSQL 在很大程度上与 PostgreSQL PL/pgSQL 兼容，语义差异很小。有关将 PostgreSQL PL/pgSQL 存储过程迁移到 Vertica 的详细信息，请参阅 PL/pgSQL 到 PL/vSQL 迁移指南。

有关 PL/vSQL 用法的实际示例，请参阅存储过程：用例和示例。

1 - 支持的类型

Vertica PL/vSQL 支持非复杂数据类型。以下类型仅作为变量受支持，而不能作为实参：

DECIMAL
NUMERIC
NUMBER
MONEY
UUID

2 - 范围和结构

PL/vSQL 使用块范围，其中块具有以下结构：

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
    ...
END [ label ];

声明

DECLARE 块中的变量 declarations 结构如下：

variable_name [ CONSTANT ] data_type [ NOT NULL ] [:= { expression | statement } ];

别名

别名是同一变量的备选名称。变量的别名不是副本，对任一引用的任何更改都会影响同一基础变量。

new_name ALIAS FOR variable;

在下方示例中，标识符 y 现在是变量 x 的别名，对 y 的更改反映在 x 中。

DO $$
DECLARE
    x int := 3;
    y ALIAS FOR x;
BEGIN
    y := 5; -- since y refers to x, x = 5
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;

INFO 2005:  x = 5, y = 5

BEGIN 和嵌套块

BEGIN 包含 statements。statement 定义为 PL/vSQL 的行或块。

内部块中声明的变量遮蔽外部块中声明的变量。要明确指定特定块中的变量，您可以使用 label（不区分大小写）命名块，然后使用以下语句引用该块中声明的变量：

label.variable_name

例如，由于存在遮蔽，在内部块中指定变量 x 会隐式引用 inner_block.x 而不引用 outer_block.x：


<<outer_block>>
DECLARE
    x int;
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 1000; -- implicitly specifies x in inner_block because of shadowing
        OUTER_BLOCK.x := 0; -- specifies x in outer_block; labels are case-insensitive
    END inner_block;
END outer_block;

NULL 语句

NULL 语句不起任何作用。它可以用作占位符语句，或者是一种显示代码块有意为空的方式。例如：

DO $$
BEGIN
    NULL;
END;
$$

备注

注释语法如下。您不能嵌套注释。

-- single-line comment

/* multi-line
comment
*/

3 - 嵌入式 SQL

您可以在存储过程中嵌入和执行 SQL 语句和表达式。

赋值

要保存表达式的值或返回的值，您可以将其赋值给变量：

variable_name := expression;
variable_name := statement;

例如，此过程将 3 赋值给 i，将 'message' 赋值给 v。

=> CREATE PROCEDURE performless_assignment() LANGUAGE PLvSQL AS $$
DECLARE
    i int;
    v varchar;
BEGIN
    i := SELECT 3;
    v := 'message';
END;
$$;

如果查询未返回任何行或者返回多个行，则此类赋值失败。要返回多个行，请使用 LIMIT 或截断赋值：

=> SELECT * FROM t1;
 b
---
 t
 f
 f
(3 rows)


=> CREATE PROCEDURE more_than_one_row() LANGUAGE PLvSQL as $$
DECLARE
    x boolean;
BEGIN
    x := SELECT * FROM t1;
END;
$$;
CREATE PROCEDURE

=> CALL more_than_one_row();
ERROR 10332:  Query returned multiple rows where 1 was expected

截断赋值

截断赋值将查询返回的第一行存储在变量中。行排序是不确定的，除非您指定了 ORDER BY 子句：

variable_name <- expression;
variable_name <- statement;

以下过程获取指定的查询所返回结果的第一行，并将其赋值给 x：

=> CREATE PROCEDURE truncating_assignment() LANGUAGE PLvSQL AS $$
DECLARE
    x boolean;
BEGIN
    x <- SELECT * FROM t1 ORDER BY b DESC; -- x is now assigned the first row returned by the SELECT query
END;
$$;

PERFORM

PERFORM 关键字运行 SQL 语句或表达式，放弃返回的结果。

PERFORM statement;
PERFORM expression;

例如，此过程将一个值插入表。INSERT 返回插入的行数，因此您必须将其与 PERFORM 配对。

=> DO $$
BEGIN
    PERFORM INSERT INTO coordinates VALUES(1,2,3);
END;
$$;

注意

如果 SQL 语句未返回任何行，或者您未将返回值赋值给变量，则必须使用 PERFORM。

EXECUTE

EXECUTE 允许您在执行期间动态构造 SQL 查询：

EXECUTE command_expression [ USING expression [, ... ] ];

command_expression 是一个 SQL 表达式，它可以将 PL/vSQL 变量和求值引向字符串字面量。该字符串字面量作为 SQL 语句执行，$1、$2、... 替换为对应的表达式。

使用 PL/vSQL 变量构造查询可能有危险，会将您的系统暴露给 SQL 注入，因此请使用 QUOTE_IDENT、QUOTE_LITERAL 和 QUOTE_NULLABLE 对其进行包装。

以下过程使用 WHERE 子句来构造查询：

DO $$
BEGIN
    EXECUTE 'SELECT * FROM t1 WHERE x = $1' USING 10; -- becomes WHERE x = 10
END;
$$;

以下过程使用 username 和 password 实参中的密码创建用户。由于构造的 CREATE USER 语句使用变量，请使用函数 QUOTE_IDENT 和 QUOTE_LITERAL，用 || 连接它们。

=> CREATE PROCEDURE create_user(username varchar, password varchar) LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE 'CREATE USER ' || QUOTE_IDENT(username) || ' IDENTIFIED BY ' || QUOTE_LITERAL(password);
END;
$$;

EXECUTE 是 SQL 语句，因此您可以将其赋值给变量或与 PERFORM 配对：

variable_name:= EXECUTE command_expression;
PERFORM EXECUTE command_expression;

FOUND（特殊变量）

特殊布尔变量 FOUND 初始化为 false，然后根据是否发生以下操作确定是赋值为 true 还是 false：

语句（不是表达式）返回具有非零行数的结果，或者
FOR 循环至少迭代一次

您可以使用 FOUND 区分返回 NULL 和返回 0 行。

在过程实参的作用域与其定义的最外层块之间存在特殊变量。这意味着：

特殊变量影射过程实参
在存储过程主体中声明的变量将影射特殊变量

下面的过程演示 FOUND 如何更改。在 SELECT 语句之前，FOUND 为 false；在 SELECT 语句之后，FOUND 为 true。

=> DO $$
BEGIN
    RAISE NOTICE 'Before SELECT, FOUND = %', FOUND;
    PERFORM SELECT 1; -- SELECT returns 1
    RAISE NOTICE 'After SELECT, FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  Before SELECT, FOUND = f
NOTICE 2005:  After SELECT, FOUND = t

同样，UPDATE、DELETE 和 INSERT 返回受影响的行数。在下一示例中，UPDATE 不更改任何行，但是返回 0 值以表示没有任何行受影响，因此 FOUND 设置为 true：

=> SELECT * t1;
  a  |  b
-----+-----
 100 | abc
(1 row)

DO $$
BEGIN
    PERFORM UPDATE t1 SET a=200 WHERE b='efg'; -- no rows affected since b doesn't contain 'efg'
    RAISE INFO 'FOUND = %', FOUND;
END;
$$;

INFO 2005:  FOUND = t

FOUND 开始时设置为 false，如果循环至少迭代一次，则设为 true：

=> DO $$
BEGIN
    RAISE NOTICE 'FOUND = %', FOUND;
    FOR i IN RANGE 1..1 LOOP -- RANGE is inclusive, so iterates once
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  FOUND = f
NOTICE 2005:  FOUND = t

DO $$
BEGIN
    RAISE NOTICE 'FOUND = %', FOUND;
    FOR i IN RANGE 1..0 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  FOUND = f
NOTICE 2005:  FOUND = f

4 - 控制流

控制流构造使您可以控制语句块应运行的次数和条件。

条件

IF/ELSIF/ELSE

IF/ELSIF/ELSE 语句允许您根据指定的条件执行不同的操作。

IF condition_1 THEN
  statement_1;
[ ELSIF condition_2 THEN
  statement_2 ]
...
[ ELSE
  statement_n; ]
END IF;

Vertica 将每个条件作为布尔值依次求值，直到找到一个为 true 的条件，然后执行语句块并退出 IF 语句。如果任何条件均不为 true，则执行 ELSE 块（如果存在）。


IF i = 3 THEN...
ELSIF 0 THEN...
ELSIF true THEN...
ELSIF x <= 4 OR x >= 10 THEN...
ELSIF y = 'this' AND z = 'THAT' THEN...

例如，此过程演示简单的 IF...ELSE 分支。由于 b 声明为 true，Vertica 执行第一个分支。

=> DO LANGUAGE PLvSQL $$
DECLARE
    b bool := true;
BEGIN
    IF b THEN
        RAISE NOTICE 'true branch';
    ELSE
        RAISE NOTICE 'false branch';
    END IF;
END;
$$;

NOTICE 2005:  true branch

CASE

CASE 表达式通常比 IF…ELSE 链更可读。在执行 CASE 表达式的分支之后，控制将跳转到封闭 END CASE 后的语句。

PL/vSQL CASE 表达式比 SQL Case 表达式更灵活、更强大，但后者更高效；在均可使用的情况下，您应该更偏好 SQL Case 表达式。

CASE [ search_expression ]
   WHEN expression_1 [, expression_2, ...] THEN
      when_statements
  [ ... ]
  [ ELSE
      else_statements ]
END CASE;

search_expression 求值一次，然后从上到下与每个分支中的 expression_n 进行比较。如果 search_expression 与给定 expression_n 相等，则 Vertica 针对 expression_n 执行 WHEN 块，并退出 CASE 块。如果找不到匹配的表达式，则执行 ELSE 分支（如果存在）。

Case 表达式必须具有匹配的 Case 或 ELSE 分支，否则 Vertica 将引发 Case_NOT_FOUND 错误。

如果忽略 search_expression，则其值默认为 true。

例如，此过程进行游戏 FizzBuzz。如果实参可被 3 整除，则输出 Fizz；如果实参可被 5 整除，则输出 Buzz；如果实参可被 3 和 5 整除，则输出 FizzBuzz。

=> CREATE PROCEDURE fizzbuzz(IN x int) LANGUAGE PLvSQL AS $$
DECLARE
    fizz int := x % 3;
    buzz int := x % 5;
BEGIN
    CASE fizz
        WHEN 0 THEN -- if fizz = 0, execute WHEN block
            CASE buzz
                WHEN 0 THEN -- if buzz = 0, execute WHEN block
                    RAISE INFO 'FizzBuzz';
                ELSE -- if buzz != 0, execute WHEN block
                    RAISE INFO 'Fizz';
            END CASE;
        ELSE -- if fizz != 0, execute ELSE block
            CASE buzz
                WHEN 0 THEN
                    RAISE INFO 'Buzz';
                ELSE
                    RAISE INFO '';
            END CASE;
    END CASE;
END;
$$;

=> CALL fizzbuzz(3);
INFO 2005:  Fizz

=> CALL fizzbuzz(5);
INFO 2005:  Buzz

=> CALL fizzbuzz(15);
INFO 2005:  FizzBuzz

循环

循环重复执行代码块，直到满足给定条件。

WHILE

WHILE 循环检查给定条件，如果条件为 true，则执行循环体，然后再次检查条件：如果为 true，循环体再次执行；如果为 false，则控制跳到循环体的末尾。

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

例如，此过程计算实参的阶乘：

=> CREATE PROCEDURE factorialSP(input int) LANGUAGE PLvSQL AS $$
DECLARE
    i int := 1;
    output int := 1;
BEGIN
    WHILE i <= input loop
        output := output * i;
        i := i + 1;
    END LOOP;
    RAISE INFO '%! = %', input, output;
END;
$$;

=> CALL factorialSP(5);
INFO 2005:  5! = 120

LOOP

此循环类型等同于 WHILE true，仅在遇到 RETURN 或 EXIT 语句或引发异常时终止。

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

例如，此过程输出从 counter 到 upper_bound（含）的整数：

DO $$
DECLARE
    counter int := 1;
    upper_bound int := 3;
BEGIN
    LOOP
        RAISE INFO '%', counter;
        IF counter >= upper_bound THEN
            RETURN;
        END IF;
        counter := counter + 1;
    END LOOP;
END;
$$;

INFO 2005:  1
INFO 2005:  2
INFO 2005:  3

FOR

FOR 循环在集合上迭代，集合可以是整体范围、查询或游标。

如果 FOR 循环至少迭代一次，则在循环结束后，特殊的 FOUND 变量设置为 true。否则，FOUND 设置为 false。

FOUND 变量可用于区分返回 NULL 和返回 0 行，或者在 LOOP 未运行时创建 IF 分支。

FOR (RANGE)

FOR (RANGE) 循环在表达式 left 和 right 指定的整数范围内迭代。

[ <<label>> ]
FOR loop_counter IN RANGE [ REVERSE ] left..right [ BY step ] LOOP
    statements
END LOOP [ label ];

loop_counter：

不必声明，它使用 left 值进行初始化
仅在 FOR 循环范围内可用

loop_counter 从 left 迭代到 right（包含），在每次迭代结尾以 step 递增。

相反，REVERSE 选项从 right 迭代到 left（包含），以 step 递减。

例如，下面是一个标准的递增 FOR 循环，其 step = 1：

=> DO $$
BEGIN
    FOR i IN RANGE 1..4 LOOP -- loop_counter i does not have to be declared
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'after loop: i = %', i; -- fails
END;
$$;

NOTICE 2005:  i = 1
NOTICE 2005:  i = 2
NOTICE 2005:  i = 3
NOTICE 2005:  i = 4
ERROR 2624:  Column "i" does not exist -- loop_counter i is only available inside the FOR loop

在下方示例中，loop_counteri 从 4 开始，在每次迭代结尾以 2 递减：

=> DO $$
BEGIN
    FOR i IN RANGE REVERSE 4..0 BY 2 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

NOTICE 2005:  i = 4
NOTICE 2005:  i = 2
NOTICE 2005:  i = 0

FOR (query)

FOR (QUERY) 循环在查询结果上进行迭代。

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

您可以在查询中包含 ORDER BY 子句，以使排序具有确定性。

与 FOR (RANGE) 循环不同，您必须声明 target 变量。这些变量的值在循环结束后保持不变。

例如，假设给定表 tuple：

=> SELECT * FROM tuples ORDER BY x ASC;
 x | y | z
---+---+---
 1 | 2 | 3
 4 | 5 | 6
 7 | 8 | 9
(3 rows)

此过程检索每行中的元组，并将它们存储在变量 a、b 和 c 中，在每次迭代后输出它们：

=>
=> DO $$
DECLARE
    a int; -- target variables must be declared
    b int;
    c int;
    i int := 1;
BEGIN
    FOR a,b,c IN QUERY SELECT * FROM tuples ORDER BY x ASC LOOP
        RAISE NOTICE 'iteration %: a = %, b = %, c = %', i,a,b,c;
        i := i + 1;
    END LOOP;
    RAISE NOTICE 'after loop: a = %, b = %, c = %', a,b,c;
END;
$$;

NOTICE 2005:  iteration 1: a = 1, b = 2, c = 3
NOTICE 2005:  iteration 2: a = 4, b = 5, c = 6
NOTICE 2005:  iteration 3: a = 7, b = 8, c = 9
NOTICE 2005:  after loop: a = 7, b = 8, c = 9

您还可以使用通过 EXECUTE 动态构建的查询：

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

下面的过程使用 EXECUTE 构建 FOR (QUERY) 循环，并将 SELECT 语句的结果存储在变量 x 和 y 中。此类语句的结果集只有一行，因此它只迭代一次。

=> SELECT 'first string', 'second string';
   ?column?   |   ?column?
--------------+---------------
 first string | second string
(1 row)

=> DO $$
DECLARE
    x varchar; -- target variables must be declared
    y varchar;
BEGIN
    -- substitute the placeholders $1 and $2 with the strings
    FOR x, y IN EXECUTE 'SELECT $1, $2' USING 'first string', 'second string' LOOP
        RAISE NOTICE '%', x;
        RAISE NOTICE '%', y;
    END LOOP;
END;
$$;

NOTICE 2005:  first string
NOTICE 2005:  second string

FOR (cursor)

FOR (CURSOR) 循环在绑定的、未打开的游标上进行迭代，为每次迭代执行一组 statements。

[ <<label>> ]
FOR loop_variable [, ...] IN CURSOR bound_unopened_cursor [ ( [ arg_name := ] arg_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

这种类型的 FOR 循环在循环开始时打开游标，在循环结束时关闭游标。

例如，此过程创建游标 c。该过程将 6 作为实参传递给游标，因此游标仅检索 y 坐标为 6 的行，将坐标存储在变量 x_、y_ 和 z_ 中，并在每次迭代结束时输出它们：

=> SELECT * FROM coordinates;
 x  | y | z
----+---+----
 14 | 6 | 19
  1 | 6 |  2
 10 | 6 | 39
 10 | 2 | 1
  7 | 1 | 10
 67 | 1 | 77
(6 rows)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
    x_ int;
    y_ int;
    z_ int;
BEGIN
    FOR x_,y_,z_ IN CURSOR c(6) LOOP
       RAISE NOTICE 'cursor returned %,%,% FOUND=%', x_,y_,z_,FOUND;
    END LOOP;
    RAISE NOTICE 'after loop: %,%,% FOUND=%', x_,y_,z_,FOUND;
END;
$$;

NOTICE 2005:  cursor returned 14,6,19 FOUND=f -- FOUND is only set after the loop ends
NOTICE 2005:  cursor returned 1,6,2 FOUND=f
NOTICE 2005:  after loop: 10,6,39 FOUND=t -- x_, y_, and z_ retain their values, FOUND is now true because the FOR loop iterated at least once

操作循环

RETURN

您可以使用 RETURN 退出整个过程（因此退出循环）。RETURN 是可选语句，可以添加它来向读取器发出过程结束的信号。

RETURN;

EXIT

与其他编程语言中的 break 或带标签 break 类似，EXIT 语句允许您提早退出循环，可以选择指定：

loop_label：退出的循环的名称
condition：如果 condition 为 true，则执行 EXIT 语句

EXIT [ loop_label ] [ WHEN condition ];

CONTINUE

CONTINUE 跳到循环的下一次迭代，而不执行 CONTINUE 本身之后的语句。您可以指定具有 loop_label 的特定循环：

CONTINUE [loop_label] [ WHEN condition ];

例如，此过程在其前两次迭代期间不输出，因为 CONTINUE 语句将在控制到达 RAISE NOTICE 语句之前执行并移动到循环的下一次迭代：

=> DO $$
BEGIN
    FOR i IN RANGE 1..5 LOOP
        IF i < 3 THEN
            CONTINUE;
        END IF;
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

NOTICE 2005:  i = 3
NOTICE 2005:  i = 4
NOTICE 2005:  i = 5

5 - 错误和诊断

ASSERT

ASSERT 是一个调试功能，它可以检查某个条件是否为 true。如果条件为 false，则 ASSERT 引发 ASSERT_FAILURE 异常，并显示可选错误消息。

要转义 '（单引号）字符，请使用 ''。同样，要转义 "（双引号）字符，请使用 ""。

ASSERT condition [ , message ];

例如，此过程检查 products 表中的行数，并使用 ASSERT 检查是否填充了该表。如果该表为空，则 Vertica 引发错误：


=> CREATE TABLE products(id UUID, name VARCHARE, price MONEY);
CREATE TABLE

=> SELECT * FROM products;
 id | name | price
----+------+-------
(0 rows)

DO $$
DECLARE
    prod_count INT;
BEGIN
    prod_count := SELECT count(*) FROM products;
    ASSERT prod_count > 0, 'products table is empty';
END;
$$;

ERROR 2005:  products table is empty

要让 Vertica 停止检查 ASSERT 语句，您可以设置布尔会话级别参数PLpgSQLCheckAsserts。

RAISE

RAISE 可以引发错误或输出用户指定的下列错误消息之一：

RAISE [ level ] 'format' [, arg_expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sql-state' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];

此过程演示各种 RAISE 级别：

=> DO $$
DECLARE
    logfile varchar := 'vertica.log';
BEGIN
    RAISE LOG 'this message was sent to %', logfile;
    RAISE INFO 'info';
    RAISE NOTICE 'notice';
    RAISE WARNING 'warning';
    RAISE EXCEPTION 'exception';

    RAISE NOTICE 'exception changes control flow; this is not printed';
END;
$$;

INFO 2005:  info
NOTICE 2005:  notice
WARNING 2005:  warning
ERROR 2005:  exception

$ grep 'this message was sent to vertica.log' v_vmart_node0001_catalog/vertica.log
<LOG> @v_vmart_node0001: V0002/2005: this message is sent to vertica.log

异常

EXCEPTION 块使您可以捕获和处理从 statements 引发的异常：

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

exception_condition 具有下列格式之一：

WHEN errcode_division_by_zero THEN ...
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
WHEN OTHERS THEN ...

OTHERS 是特殊条件，它捕获除 QUERY_CANCELLED、ASSERT_FAILURE 和 FEATURE_NOT_SUPPORTED 之外的所有异常。

当引发异常时，Vertica 检查异常列表，从上到下查找匹配的 exception_condition。如果找到匹配，它将执行 handler_statements，然后离开异常块范围。

如果 Vertica 找不到匹配，则会将异常传播到下一个封闭块。您可以使用 RAISE 在异常处理程序中手动执行此操作：

RAISE;

例如，以下过程在 inner_block 中用 3 除以 0，这是一个非法操作，将引发 division_by_zero 异常（SQL 状态 22012）。Vertica 检查内部 EXCEPTION 块以查找匹配的条件：

第一个条件检查 SQL 状态 42501，因此 Vertica 移动到下一个条件。
WHEN OTHERS THEN 捕获所有异常，因此它执行该块。
随后，基本 RAISE 将异常传播到 outer_block。
外部 EXCEPTION 块成功捕获异常并输出消息。

=> DO $$
<<outer_block>>
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 3 / 0; -- throws exception division_by_zero, SQLSTATE 22012
    EXCEPTION -- this block is checked first for matching exceptions
        WHEN SQLSTATE '42501' THEN
            RAISE NOTICE 'caught insufficient_privilege exception';
        WHEN OTHERS THEN -- catches all exceptions
            RAISE; -- manually propagate the exception to the next enclosing block
    END inner_block;
EXCEPTION -- exception is propagated to this block
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero exception';
END outer_block;
$$;

NOTICE 2005:  caught division_by_zero exception

SQLSTATE 和 SQLERRM 变量

当处理异常时，您可以使用以下变量检索错误信息：

SQLSTATE 包含 SQL 状态
SQLERRM 包含错误消息

有关详细信息，请参阅SQL 状态列表。

此过程通过尝试将 NULL 赋值给 NOT NULL 变量捕获引发的异常，并输出 SQL 状态和错误消息：

DO $$
DECLARE
    i int NOT NULL := 1;
BEGIN
    i := NULL; -- illegal, i was declared with NOT NULL
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'SQL State: %', SQLSTATE;
        RAISE WARNING 'Error message: %', SQLERRM;
END;
$$;

WARNING 2005:  SQLSTATE: 42809
WARNING 2005:  SQLERRM: Cannot assign null into NOT NULL variable

检索异常信息

您可以使用 GET STACKED DIAGNOSTICS 在异常处理程序中检索有关异常的信息：

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

其中 item 可以是以下任意一项：

例如，此过程有一个 EXCEPTION 块，它捕捉 division_by_zero 错误并输出 SQL 状态、错误消息和异常上下文：

=> DO $$
DECLARE
    message_1 varchar;
    message_2 varchar;
    message_3 varchar;
    x int;
BEGIN
    x := 5 / 0;
EXCEPTION
    WHEN OTHERS THEN -- OTHERS catches all exceptions
    GET STACKED DIAGNOSTICS message_1 = RETURNED_SQLSTATE,
                            message_2 = MESSAGE_TEXT,
                            message_3 = EXCEPTION_CONTEXT;

    RAISE INFO 'SQLSTATE: %', message_1;
    RAISE INFO 'MESSAGE: %', message_2;
    RAISE INFO 'EXCEPTION_CONTEXT: %', message_3;
END;
$$;

INFO 2005:  SQLSTATE: 22012
INFO 2005:  MESSAGE: Division by zero
INFO 2005:  EXCEPTION_CONTEXT: PL/vSQL procedure inline_code_block line 8 at static SQL

6 - 游标

游标是对查询结果集的引用，允许您一次查看一行结果。游标记住结果集中的位置，可以是以下位置之一：

结果行
第一行之前
最后一行之后

您还可以使用 FOR 循环在未打开的绑定游标上进行迭代。有关详细信息，请参阅控制流。

声明游标

绑定游标

要将游标绑定到声明中的语句，请使用 FOR 关键字：

cursor_name CURSOR [ ( arg_name arg_type [, ...] ) ] FOR statement;

游标的实参使您能够更好地控制要处理的行。例如，假设您有下表：

=> SELECT * FROM coordinates_xy;
 x | y
---+----
 1 |  2
 9 |  5
 7 | 13
...
(100000 rows)

如果仅对 y 为 6 的行感兴趣，则可以声明以下游标，然后在打开游标时提供实参 6：

c CURSOR (key int) FOR SELECT * FROM coordinates_xy WHERE y=key;

未绑定的游标

要声明未绑定到特定查询的游标，请使用 refcursor 类型：

cursor_name refcursor;

您可以随时使用打开对未绑定的游标进行绑定。

例如，要声明游标 my_unbound_cursor：

my_unbound_cursor refcursor;

打开和关闭游标

打开

打开游标将使用给定实参执行查询，并将游标放在结果集的第一行之前。查询结果的排序（因此结果集的开头）是不确定的，除非您指定了 ORDER BY 子句。

打开绑定的游标

打开声明期间绑定的游标：

OPEN bound_cursor [ ( [ arg_name := ] arg_value [, ...] ) ];

例如，给定以下声明：

c CURSOR (key int) FOR SELECT * FROM t1 WHERE y=key;

可以使用以下某项操作打开游标：

OPEN c(5);
OPEN c(key := 5);

关闭

当游标离开范围时，打开的游标将自动关闭，但您可以使用“关闭”命令提前关闭游标。关闭的游标可以稍后重新打开，这将重新执行查询并准备新的结果集。

CLOSE cursor;

打开未绑定的游标

对未绑定的游标进行绑定，然后打开游标：

OPEN unbound_cursor FOR statement;

您还可以使用 EXECUTE，因为它是语句：

OPEN unbound_cursor FOR EXECUTE statement_string [ USING expression [, ... ] ];

例如，将游标 c 绑定到表 product_data 的查询：

OPEN c for SELECT * FROM product_data;

提取行

FETCH 语句：

检索指定游标当前指向的行，并将其存储在某个变量中。
使游标前进到下一个位置。

variable [, ...] := FETCH opened_cursor;

检索的值存储在变量中。行通常有多个值，因此您可以每行使用一个变量。

如果 FETCH 成功检索值，则特殊变量 FOUND 设置为 true。否则，如果您在游标经过结果集的最后一行时调用 FETCH，它将返回 NULL，且特殊变量 FOUND 设置为 false。

下面的过程创建游标 c，将其绑定到 coordinates 表中的 SELECT 查询。该过程将实参 1 传递给游标，因此游标仅检索 y 坐标为 1 的行，将坐标存储在变量 x_、y_ 和 z_ 中。

只有两行的 y 坐标为 1，因此使用 FETCH 两次后，第三个 FETCH 开始返回 NULL 值，且 FOUND 设置为 false：

=> SELECT * FROM coordinates;
 x  | y | z
----+---+----
 14 | 6 | 19
  1 | 6 |  2
 10 | 6 | 39
 10 | 2 |  1
  7 | 1 | 10
 67 | 1 | 77
(6 rows)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
    x_ int;
    y_ int;
    z_ int;
BEGIN
    OPEN c(1); -- only retrieve rows where y=1
    x_,y_,z_ := FETCH c;
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- fetches the last set of results and moves to the end of the result set
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- cursor has advanced past the final row
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
END;
$$;

NOTICE 2005:  cursor returned 7, 1, 10, FOUND=t
NOTICE 2005:  cursor returned 67, 1, 77, FOUND=t
NOTICE 2005:  cursor returned <NULL>, <NULL>, <NULL>, FOUND=f

移动游标

MOVE 使打开的游标前进到下一个位置，而不检索该行。如果游标位置（在 MOVE 之前）未经过最后一行，则特殊 FOUND 变量设置为 true — 即，如果调用 FETCH 而不调用 MOVE，则会检索该行。

MOVE bound_cursor;

例如，此游标仅检索 y 坐标为 2 的行。结果集只有一行，因此使用 MOVE 两次会导致前进超过第一行（和最后一行），且将 FOUND 设置为 false：

 => SELECT * FROM coordinates WHERE y=2;
 x  | y | z
----+---+---
 10 | 2 | 1
(1 row)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
BEGIN
    OPEN c(2); -- only retrieve rows where y=2, cursor starts before the first row
    MOVE c; -- cursor advances to the first (and last) row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is true because the cursor points to a row in the result set
    MOVE c; -- cursor advances past the final row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is false because the cursor is past the final row
END;
$$;

NOTICE 2005:  FOUND=t
NOTICE 2005:  FOUND=f

7 - PL/pgSQL 到 PL/vSQL 迁移指南

虽然 Vertica PL/vSQL 在很大程度上与 PostgreSQL PL/pgSQL 兼容，但从 PostgreSQL PL/pgSQL 迁移时，存在一些易于解决的语义和 SQL 级别差异。

语言级别差异

下面列出了 Vertica PL/vSQL 与 PostgreSQL PL/pgSQL 之间的显著差异。在 Vertica PL/vSQL 中：

必须将 PERFORM 语句用于未返回任何值的 SQL 语句。
UPDATE/DELETE WHERE CURRENT OF 不受支持。
FOR 循环具有附加关键字：
- FOR (RANGE) 循环RANGE 关键字
- FOR (QUERY) 循环：QUERY 关键字
- FOR (CURSOR) 循环：CURSOR 关键字
默认情况下，NULL 不能强制转换为 FALSE。

解决方法：将 NULL 强制转换为 FALSE

与 PostgreSQL PL/pgSQL 不同，在 Vertica PL/vSQL 中，NULL 不可强制转换为 false。当赋值 NULL 时，预期获得布尔值的表达式会引发异常：

=> DO $$
BEGIN
    IF NULL THEN -- boolean value expected for IF
    END IF;
END;
$$;

ERROR 10268:  Query returned null where a value was expected

要使 NULL 强制转换为 false，请启用配置参数 PLvSQLCoerceNull：

=> ALTER DATABASE DEFAULT SET PLvSQLCoerceNull = 1;

计划的功能

在将来版本中，计划支持以下功能：

完整事务和会话语义。目前，存储过程在执行之前提交事务，每个嵌入式 SQL 语句在其自己的自动提交事务中执行。它具有以下含义： * 您不能回退。 * 会话级别的更改（如创建定向查询或设置会话级别参数）将成功，但是没有作用。
OUT/INOUT 参数模式。
FOREACH (ARRAY) 循环。
使用以下类型作为实参： * DECIMAL * NUMERIC * NUMBER * MONEY * UUID
非正向移动游标。
用于诊断的 CONTEXT/EXCEPTION_CONTEXT。

特殊变量 ROW_COUNT。

注意

一种解决方法是，您可以依赖 INSERT、UPDATE 和 DELETE 返回受影响的行数：

=> CREATE TABLE t1(i int);
CREATE TABLE
=> DO $$
DECLARE
x int;
BEGIN
x := INSERT INTO t1 VALUES (200);
RAISE INFO 'rows inserted: %', x;
END;
$$;
INFO 2005:  rows inserted: 1

SQL 级别差异

下面说明了 Vertica 和 PostgreSQL 之间在架构和 SQL 级别上的显著差异。在 Vertica 中：

一些数据类型大小不同 — 例如，Vertica 中的标准 INTEGER 为 8 字节，但 PostgreSQL 中为 4 字节。
INSERT、UPDATE 和 DELETE 返回受影响的行数。
某些 SQLSTATE 代码不同，这会影响异常处理。