Amazon Redshift
数据库开发人员指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

PL/pgSQL 的结构

PL/pgSQL 是一种程序性语言,具有与其他程序性语言相同的许多结构。

PL/pgSQL 是块结构语言。过程的整个正文在块中定义,其中包含变量声明和 PL/pgSQL 语句。语句还可以包含嵌套块,也就是子块。

使用分号作为声明和语句的结尾。在块或子块的 END 关键字之后添加分号。请勿在关键字 DECLARE 和 BEGIN 之后使用分号。

所有关键字和标识符均可以使用混合大小写来编写。除非括在双引号中,否则标识符隐式转换为小写。

双连字符 (--) 表示注释的开始,一直到这一行的结尾。/* 表示块注释的开始,一直到下一个 */ 为止。块注释不能嵌套。但是,您可以在块注释中包括双连字符注释,而双连字符可以隐藏块注释分隔符 /* 和 */。

在一个块中,语句部分中任何语句都可以是子块。您可以使用子块来逻辑分组,或者将变量本地化到一小组语句中。

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

在块之前的声明部分中声明的变量,每次进入块时将初始化为其默认值。换而言之,它们不是在每次函数调用中仅初始化一次。

下面是一个示例。

CREATE PROCEDURE update_value() AS $$ DECLARE value integer := 20; BEGIN RAISE NOTICE 'Value here is %', value; -- Value here is 20 value := 50; -- -- Create a subblock -- DECLARE value integer := 80; BEGIN RAISE NOTICE 'Value here is %', value; -- Value here is 80 END; RAISE NOTICE 'Value here is %', value; -- Value here is 50 END; $$ LANGUAGE plpgsql;

使用标签来标识在 EXIT 语句中使用的块,或者用于限定在块中声明的变量的名称。

请不要将为 PL/pgSQL 中用于分组语句的 BEGIN/END,与用于事务控制的数据库命令相混淆。PL/pgSQL 中的 BEGIN 和 END 仅用于分组。它们不启动或结束事务。

变量声明

在块中声明所有变量,但循环变量除外,此类变量应在块的 DECLARE 部分中声明。变量可使用任意有效的 Amazon Redshift 数据类型。有关支持的数据类型,请参阅数据类型

PL/pgSQL 变量可以为任意 Amazon Redshift 支持的数据类型,以及 RECORDrefcursor。有关 RECORD 的更多信息,请参阅 记录类型。有关 refcursor 的更多信息,请参阅 游标

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

在下文中您可以找到示例变量声明。

customerID integer; numberofitems numeric(6); link varchar; onerow RECORD;

对于迭代某个整数范围的 FOR 循环,其循环变量自动声明为整数变量。

如果给出了 DEFAULT 子句,则指定在进入块时赋给变量的初始值。如果未给出 DEFAULT 子句,则变量初始化为 SQL NULL 值。CONSTANT 选项防止向变量赋值,因此其值在块的持续过程中保持为常数。如果指定了 NOT NULL,则赋值 null 时会导致运行时错误。声明为 NOT NULL 的所有变量必须指定非 null 默认值。

在每次进入块时,对默认值求值。以此为例,将 now() 分配给类型为 timestamp 的变量会使变量具有当前函数调用的时间,而不是预编译函数时的时间。

quantity INTEGER DEFAULT 32; url VARCHAR := 'http://mysite.com'; user_id CONSTANT INTEGER := 10;

refcursor 数据类型是存储过程中游标变量的数据类型。refcursor 值可从存储过程中返回。有关更多信息,请参阅返回结果集

别名声明

如果存储过程的签名省略了参数名称,您可以为参数声明别名。

name ALIAS FOR $n;

内置变量

支持以下内置变量:

  • FOUND

  • SQLSTATE

  • SQLERRM

  • GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND 是一种布尔值类型的特殊变量。FOUND 在每个过程调用中开始为 false。以下类型的语句设置 FOUND:

  • SELECT INTO

    如果返回一行,则将 FOUND 设置为 true,未返回行时设置为 false。

  • UPDATE、INSERT 和 DELETE

    如果至少影响到一行,则将 FOUND 设置为 true,未影响任何行时设置为 false。

  • FETCH

    如果返回一行,则将 FOUND 设置为 true,未返回行时设置为 false。

  • FOR 语句

    如果 FOR 语句迭代了一次或多次,则将 FOUND 设置为 true,否则为 false。这适用于 FOR 语句的全部三种变体:整数 FOR 循环、记录集 FOR 循环和动态记录集 FOR 循环。

    FOUND 在 FOR 循环退出时设置。在循环的执行过程中,FOR 语句不修改 FOUND。但是,循环正文中其他语句的执行可能会更改它。

下面是一个示例。

CREATE TABLE employee(empname varchar); CREATE OR REPLACE PROCEDURE show_found() AS $$ DECLARE myrec record; BEGIN SELECT INTO myrec * FROM employee WHERE empname = 'John'; IF NOT FOUND THEN RAISE EXCEPTION 'employee John not found'; END IF; END; $$ LANGUAGE plpgsql;

在异常处理程序中,特殊变量 SQLSTATE 包含对应于所引发异常的错误代码。特殊变量 SQLERRM 包含与异常关联的错误消息。这些变量未在异常处理程序之外定义,如果使用会引发错误。

下面是一个示例。

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS $$ BEGIN UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith'; EXECUTE 'select invalid'; EXCEPTION WHEN OTHERS THEN RAISE INFO 'error message SQLERRM %', SQLERRM; RAISE INFO 'error message SQLSTATE %', SQLSTATE; END; $$ LANGUAGE plpgsql;

ROW_COUNT 与 GET DIAGNOSTICS 命令结合使用。它显示向下发送至 SQL 引擎的上一个 SQL 命令处理的行数。

下面是一个示例。

CREATE OR REPLACE PROCEDURE sp_row_count() AS $$ DECLARE integer_var int; BEGIN INSERT INTO tbl_row_count VALUES(1); GET DIAGNOSTICS integer_var := ROW_COUNT; RAISE INFO 'rows inserted = %', integer_var; END; $$ LANGUAGE plpgsql;

记录类型

RECORD 类型不是真实的数据类型,只是一个占位符。记录类型变量代入在 SELECT 或 FOR 命令期间向其分配的行的实际行结构。记录变量的子结构可在每次分配值时更改。在首次分配值之前,记录变量没有子结构。任何访问其中字段的尝试将引发运行时错误。

name RECORD;

下面是一个示例。

CREATE TABLE tbl_record(a int, b int); INSERT INTO tbl_record VALUES(1, 2); CREATE OR REPLACE PROCEDURE record_example() LANGUAGE plpgsql AS $$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT a FROM tbl_record LOOP RAISE INFO 'a = %', rec.a; END LOOP; END; $$;